{"id":4555,"date":"2015-10-16T02:48:46","date_gmt":"2015-10-16T09:48:46","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4555"},"modified":"2016-05-10T08:56:31","modified_gmt":"2016-05-10T15:56:31","slug":"low-priority-locking-wait-types","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/","title":{"rendered":"Low priority locking wait types"},"content":{"rendered":"

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here<\/a>.]<\/p>\n

SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations require.<\/p>\n

At the start of any online index operation, it acquires\u00a0a S (share) table lock. This lock will be blocked until all transactions that are changing the table have committed, and while the lock is pending, it will block any transactions wanting to change the table in any way. The S lock is only held for a short amount of time, then dropped to an IS (Intent-Share) lock for the long duration of the operation. At\u00a0the end\u00a0of any online index operation, it acquires\u00a0a SCH-M\u00a0(schema modification) table lock, which you can think of as a super-exclusive lock. This lock will be blocked by any transaction accessing or changing the table, and while the lock is pending, it will block any transactions wanting to read or change the table in any way.<\/p>\n

The new syntax allow you to specify how long the online index operation will wait for each of these locks, and what to do when the timeout expires (nothing: NONE<\/em>, kill the online index operation: SELF<\/em>, or kill the blockers of the online index operation: BLOCKERS<\/em>\u00a0– see Books Online<\/a> for more info).\u00a0While the online index operation is blocked, it shows a different lock wait type than we’re used to seeing, and any lock requests are allowed to essentially jump over the online index operation in the lock pending queues – i.e. the online index operation waits with lower priority than everything else on the system.<\/p>\n

To demonstrate this, I’ve got a table called NonSparseDocRepository<\/em>, with a clustered index called NonSparse_CL<\/em>, and 100,000 rows in the table.<\/p>\n

First, I’ll kick off an online index rebuild of the clustered index, specifying a 1 minute wait, and to kill itself of the wait times out:<\/p>\n

\r\nALTER INDEX [NonSparse_CL] ON [nonsparsedocrepository] REBUILD\r\nWITH (FILLFACTOR = 70, ONLINE = ON (\r\n\tWAIT_AT_LOW_PRIORITY (\r\n\t\tMAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)\r\n\t)\r\n);\r\nGO\r\n<\/pre>\n
I let it run for ten seconds or so, so make sure it got past the initial table S lock required. Now, in another connection, I’ll start a transaction that takes an IX table lock, which will block the final SCH-M lock the online index operation requires:<\/p>\n
\r\nBEGIN TRAN;\r\nGO\r\n\r\nUPDATE [NonSparseDocRepository]\r\nSET [c4] = '1'\r\nWHERE [DocID] = 1;\r\nGO\r\n<\/pre>\n
And then I’ll wait until the drive light on my laptop goes off, which lets me know that the online index rebuild is stalled. If I look in sys.dm_os_waiting_tasks<\/em> (using the script in this post<\/a>), I’ll see the rebuild is blocked (script output heavily edited for clarity and brevity):<\/p>\n
\r\nsession_id exec_context_id scheduler_id wait_duration_ms wait_type                blocking_session_id resource_description\r\n57         0               4            7786             LCK_M_SCH_M_LOW_PRIORITY 58                  objectlock\r\n<\/pre>\n
Look at the wait type: LCK_M_SCH_M_LOW_PRIORITY<\/em>. The _LOW_PRIORITY<\/em> suffix indicates that this is a special lock wait attributable to the online index operation being blocked.<\/p>\n
This also neatly proves that the wait-at-low-priority feature applies to both the blocking locks that online index operations require, even if the first one isn’t blocked.<\/p>\n
And eventually the online index operation fails, as follows:<\/p>\n
\r\nMsg 1222, Level 16, State 56, Line 1\r\nLock request time out period exceeded.\r\n<\/pre>\n
If I leave that open transaction in the other connection (holding its IX table lock), and try the index rebuild again, with the exact same syntax, it’s immediately blocked and the sys.dm_os_waiting_tasks<\/em> script shows:<\/p>\n
\r\nsession_id exec_context_id scheduler_id wait_duration_ms wait_type                blocking_session_id resource_description\r\n57         0               4            8026             LCK_M_S_LOW_PRIORITY     58                  objectlock\r\n<\/pre>\n
This shows that the initial blocking lock is blocked, and is waiting at low priority.<\/p>\n
So if either of these wait types show up during your regular wait statistics analysis, now you know what’s causing them.<\/p>\n","protected":false},"excerpt":{"rendered":"
[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,57,101],"tags":[],"class_list":["post-4555","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-locking","category-wait-stats"],"yoast_head":"\nLow priority locking wait types - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Low priority locking wait types - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2015-10-16T09:48:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-05-10T15:56:31+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/\",\"name\":\"Low priority locking wait types - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2015-10-16T09:48:46+00:00\",\"dateModified\":\"2016-05-10T15:56:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Low priority locking wait types\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Low priority locking wait types - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/","og_locale":"en_US","og_type":"article","og_title":"Low priority locking wait types - Paul S. Randal","og_description":"[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/","og_site_name":"Paul S. Randal","article_published_time":"2015-10-16T09:48:46+00:00","article_modified_time":"2016-05-10T15:56:31+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/","name":"Low priority locking wait types - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2015-10-16T09:48:46+00:00","dateModified":"2016-05-10T15:56:31+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/low-priority-locking-wait-types\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Low priority locking wait types"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4555"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4555\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}