{"id":170,"date":"2011-08-31T15:11:36","date_gmt":"2011-08-31T22:11:36","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=170"},"modified":"2011-08-31T15:11:36","modified_gmt":"2011-08-31T22:11:36","slug":"sql-server-partition-lock-escalation-property-query","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2011\/08\/sql-server-partition-lock-escalation-property-query\/","title":{"rendered":"SQL Server Partition Lock Escalation Property Query"},"content":{"rendered":"

Whenever I want to check a setting in SQL Server I always prefer to find it using a query.\u00a0 Every once in awhile these settings turn out to be on the verge of being undocumented.\u00a0 This is the case with table level lock escalation (also known as partition level lock escalation).\u00a0 The T-SQL query below brings back the information you're looking for.<\/p>\n

One other bit of trivia with regard to partition level lock escalation.\u00a0 The default is \"TABLE\" even when the table is partitioned.\u00a0 Although this may seem like an oversight on Microsoft's part, it is in fact intentional with the goal of preventing deadlocks in situations the original developers didn't account for.<\/p>\n

Wondering what the differences are between the three different LOCK_ESCALATION levels (be it TABLE, AUTO, or DISABLE)?\u00a0 See the bottom of this article for links to the best articles on the topic.<\/p>\n

--The following query finds the lock_escalation for SQL Server tables.<\/p>\n

SELECT
\nSCHEMA_NAME<\/span><\/span>(<\/span><\/span>SCHEMA_ID<\/span><\/span>)<\/span><\/span>AS<\/span><\/span> SchemaName
\n<\/span>,<\/span><\/span> t<\/span>.<\/span><\/span>name <\/span>AS<\/span><\/span> TableName
\n<\/span>,<\/span><\/span> lock_escalation_desc
\n<\/span>FROM
\nsys<\/span><\/span>.<\/span><\/span>tables<\/span><\/span>AS<\/span><\/span> t
\n<\/span>WHERE
\nt<\/span>.<\/span><\/span>schema_id<\/span><\/span>=<\/span><\/span>SCHEMA_ID<\/span><\/span>(<\/span><\/span>'YourSchema'<\/span><\/span>)
\n<\/span><\/span>AND
\nt<\/span>.<\/span><\/span>name <\/span>=<\/span><\/span>'YourTable'<\/span><\/span><\/p><\/blockquote>\n

Looking for more information on partition level lock escalation?\u00a0 Check the links below:<\/p>\n

The following post should be your first choice.\u00a0 The easy to understand examples provide real insight into how partition level lock escalation works and perhaps most importantly, demonstrates a common scenario where it can cause a deadlock.\u00a0 It turns out there's a reason LOCK_ESCALATION is set to \"TABLE\" by default.<\/p>\n

SQLskills.com: SQL Server 2008: Partition-level lock escalation details and examples<\/a><\/p>\n

Looking for the nitty gritty details on lock escalation?\u00a0 This MSDN article is the place to go if you want to be able to impress your friends at the next MS SQL User Group.\u00a0 (Do you know\u00a0the threshold for\u00a0lock escalation?\u00a0 Typically, locks are\u00a0escalated once 5,000 locks are acquired.)<\/p>\n

\u00a0MSDN: Lock Escalation(Database Engine)<\/a><\/p>\n

And finally, last but not least: Are you wondering what the difference is between the lock escalation levels of TABLE, AUTO, and DISABLE?\u00a0 The details below\u00a0are excerpted from the MSDN article ALTER TABLE (Transact-SQL)<\/a><\/p>\n

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )<\/p>\n

Specifies the allowed methods of lock escalation for a table.<\/p>\n

\n
AUTO<\/dt>\n
This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.<\/p>\n
    \n
  • If the table is partitioned, lock escalation will be allowed to partition. After the lock is escalated to the partition level, the lock will not be escalated later to TABLE granularity.<\/li>\n
  • If the table is not partitioned, the lock escalation will be done to the TABLE granularity.<\/li>\n<\/ul>\n<\/dd>\n
    TABLE<\/dt>\n
    Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.<\/p>\n<\/dd>\n
    DISABLE<\/dt>\n
    Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.<\/p>\n<\/dd>\n<\/dl>\n","protected":false},"excerpt":{"rendered":"

    Whenever I want to check a setting in SQL Server I always prefer to find it using a query.\u00a0 Every once in awhile these settings turn out to be on the verge of being undocumented.\u00a0 This is the case with table level lock escalation (also known as partition level lock escalation).\u00a0 The T-SQL query below […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,3],"tags":[30,31,10,29,4,9,11,5],"class_list":["post-170","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-scripts","tag-lock-escaltion","tag-locking","tag-ms-sql","tag-partition","tag-performance","tag-sql-server","tag-sql-server-2008","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=170"}],"version-history":[{"count":1,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/170\/revisions"}],"predecessor-version":[{"id":171,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/170\/revisions\/171"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=170"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}