here<\/a>) and I promised to do a follow-up once CTP-5 came out with syntax etc. So here it is.<\/p>\nA brief recap – lock escalation in SQL Server 2005 and before only allowed table-level lock escalation. If you have a partitioned table with queries going against different partitions, then table-level escalation is a pain because the whole table is suddenly locked and concurrent queries against distinct partitions can’t run. SQL Server 2008 gives the ability to escalate to a parttition lock, which won’t affect the queries on the other partitions.<\/p>\n
The lock escalation policy can only be set with ALTER TABLE after\u00a0a table has been created, and the policy can only be set at the table level. The syntax is<\/p>\n
<\/p>\n
ALTER TABLE<\/span>\u00a0TableName SET<\/span> (LOCK_ESCALATION = TABLE | AUTO | DISABLE);<\/span><\/p><\/blockquote>\nThe options mean:<\/p>\n
\n- \n
TABLE<\/span> – <\/strong>escalation will always be to the table level. This is the default.<\/div>\n<\/li>\n- \n
AUTO<\/span><\/strong> – escalation will be to the partition level if the table is partitioned; otherwise it will be to the table level<\/div>\n<\/li>\n- \n
DISABLE<\/span><\/strong> – escalation will be disabled. This does not guarantee that it will NEVER occur – there are some cases where it is necessary (Books Online gives the example of scanning a heap in the SERIALIZABLE<\/span> isolation level)<\/div>\n<\/li>\n<\/ul>\nThe only way I could find to check what the escalation policy for a table is set to is to use the sys.tables<\/span> catalog view:<\/p>\n <\/p>\n
SELECT<\/span> lock_escalation_desc <\/span>FROM<\/span> sys.tables <\/span>WHERE<\/span> name<\/span> =<\/span> ‘TableName’<\/span>;<\/span><\/span><\/p>\n <\/p><\/blockquote>\n
Let’s try it out. Here’s a script that creates a database with an example table with 3 partitions. The partition ranges are negative infinity to 7999, 8000 to 15999, 16000 to positive infinity.<\/p>\n
<\/p>\n
CREATE<\/span> DATABASE<\/span> LockEscalationTest<\/span>;
\n<\/span><\/span>GO<\/span><\/p>\n <\/p>\n
USE<\/span> LockEscalationTest<\/span>;
\n<\/span><\/span>GO<\/span><\/p>\n <\/p>\n
— Create three partitions: -7999, 8000-15999, 16000+
\nCREATE<\/span><\/span> PARTITION<\/span> FUNCTION<\/span> MyPartitionFunction <\/span>(<\/span>INT<\/span>) <\/span><\/span>AS<\/span><\/span> RANGE<\/span> RIGHT<\/span> FOR<\/span> VALUES<\/span> (<\/span>8000<\/span>,<\/span> 16000<\/span>);
\n<\/span><\/span>GO<\/span><\/p>\n <\/p>\n
CREATE<\/span> PARTITION<\/span> SCHEME<\/span> MyPartitionScheme <\/span><\/span><\/span>AS<\/span><\/span> PARTITION<\/span> MyPartitionFunction
\n<\/span><\/span><\/span>ALL<\/span><\/span> TO<\/span> (<\/span>[PRIMARY]<\/span>);
\n<\/span><\/span>GO<\/span><\/p>\n <\/p>\n
— Create a partitioned table
\nCREATE<\/span><\/span> TABLE<\/span> MyPartitionedTable <\/span>(<\/span>c1 <\/span>INT<\/span>);
\n<\/span><\/span>GO<\/span><\/p>\n <\/p>\n
CREATE<\/span> CLUSTERED<\/span> INDEX<\/span> MPT_Clust <\/span>ON<\/span> MyPartitionedTable <\/span>(<\/span>c1<\/span>)
\n<\/span><\/span>ON<\/span><\/span> MyPartitionScheme <\/span>(<\/span>c1<\/span>);
\n<\/span><\/span>GO<\/span><\/p>\n <\/p>\n
— Fill the table
\nSET<\/span><\/span> NOCOUNT<\/span> ON<\/span>;
\n<\/span><\/span>GO<\/span><\/p>\n <\/p>\n
DECLARE<\/span> @a <\/span>INT<\/span> =<\/span> 1<\/span>;
\n<\/span><\/span>WHILE<\/span><\/span> (<\/span>@a <\/span><<\/span> 17000<\/span>)
\n<\/span><\/span>BEGIN
\n<\/span>INSERT<\/span> INTO<\/span> MyPartitionedTable <\/span>VALUES<\/span> (<\/span>@a<\/span>);
\n<\/span><\/span>SELECT<\/span> @a <\/span>=<\/span> @a <\/span>+<\/span> 1<\/span>;
\n<\/span><\/span>END<\/span><\/span>;
\n<\/span><\/span>GO<\/span><\/p>\n <\/p><\/blockquote>\n
Now I’m going to explicitly set the escalation to TABLE and start a transaction that should cause lock escalation.<\/span><\/span><\/p>\n\nALTER TABLE<\/span> MyPartitionedTable <\/span><\/span>SET<\/span> (<\/span>LOCK_ESCALATION <\/span>=<\/span> TABLE<\/span>);
\n<\/span>GO<\/span><\/span><\/span><\/p>\nBEGIN TRAN
\nUPDATE<\/span> MyPartitionedTable <\/span>SET<\/span> c1 <\/span>=<\/span> c1 <\/span>WHERE<\/span> c1 <\/span><<\/span> 7500;
\n<\/span>GO<\/span><\/span><\/span><\/span><\/span><\/p>\n<\/blockquote>\nWe should be able to see the locks being held:<\/span><\/span><\/span><\/span><\/span><\/p>\nSELECT [resource_type]<\/span>,<\/span> [resource_associated_entity_id]<\/span>,<\/span> [request_mode]<\/span>,
\n<\/span>[request_type]<\/span>,<\/span> [request_status]<\/span> <\/span>FROM<\/span> sys.dm_tran_locks<\/span> WHERE<\/span> [resource_type]<\/span> <\/span><><\/span> ‘DATABASE’<\/span>;
\n<\/span>GO<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\nresource_type\u00a0\u00a0 resource_associated_entity_id request_mode\u00a0\u00a0 request_type\u00a0\u00a0 request_status
\n————— —————————– ————– ————– —————-
\nMETADATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Sch-S\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOCK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GRANT
\nMETADATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Sch-S\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOCK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GRANT
\nMETADATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Sch-S\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOCK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GRANT
\nOBJECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 X\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOCK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GRANT<\/span><\/span><\/span><\/span><\/span><\/p><\/blockquote>\nJust as we expected – an X table lock. Trying any query against the table fails now. Now I’ll rollback that transaction, set the escalation to partition-level and try again.<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n