Lock Escalation - Pro SQL Server Internals - page 414

Figure 20-6. Blocked and blocking sessions due to lock escalation
The key point here is that you have two object-level locks. The blocked session is trying to acquire intent lock on
the object level while the blocking session holds incompatible full lock.
If you look at the blocked process report, you see that the blocked process is waiting on the intent lock on the
object, as shown in Listing 20-1.
Listing 20-1. Blocked Process Report (Partial)
<blocked-process-report>
<blocked-process>
<process id="process3e99330c8" taskpriority="0" logused="0" waitresource="OBJECT: 14:469576711:0 "
waittime="28054" ownerId="771300" transactionname="user_transaction" lasttranstarted="2013-06-
08T14:56:30.483" XDES="0x3e6ac4d28" lockMode="IX" schedulerid="2" kpid="2384" status="suspended"
spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-06-08T14:56:30.470"
lastbatchcompleted="2013-06-08T14:56:30.470" lastattention="1900-01-01T00:00:00.470"
clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQL2012-STD1" hostpid="828"
loginname="SQL2012-STD1\Administrator" isolationlevel="read committed (2)" xactid="771300"
currentdb="14" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
Keep in mind that there could be other reasons for the sessions to acquire full object locks or be blocked waiting
for intent lock on the table. You must correlate the information from the other venues (SQL Traces, Extended Events,
and so on) to be sure blocking occurs because of lock escalation.
Although lock escalation can introduce blocking issues, it helps to preserve SQL Server memory. The large
number of locks held by the instance reduces the size of the Buffer Pool. As a result, you have fewer data pages in the
cache, which could lead to the higher number of the physical I/O operations and degrade the performance of the
queries. In addition, SQL Server could terminate the queries with error 1204 when there is no available memory to
store the lock information. Figure 20-7 shows just such an error message.
Figure 20-7. Error 1204
In SQL Server 2008 and above, you can control escalation behavior at the table level by using the ALTER TABLE
SET LOCK_ESCALATION statement. This option affects lock escalation behavior for all—clustered and non-clustered—
indexes defined on the table. Three options are available:
DISABLE : This option disables lock escalation for a specific table.
TABLE : SQL Server escalates locks to the table level. This is the default option.
AUTO : SQL Server escalates locks to the partition level when the table is partitioned or to
the table level when the table is not partitioned. Use this option with the large partitioned
tables, especially when there are large reporting queries running on the old data.