{"id":579,"date":"2011-04-13T07:32:00","date_gmt":"2011-04-13T07:32:00","guid":{"rendered":"\/blogs\/paul\/post\/Advanced-performance-troubleshooting-waits-latches-spinlocks.aspx"},"modified":"2017-04-13T12:47:37","modified_gmt":"2017-04-13T19:47:37","slug":"advanced-performance-troubleshooting-waits-latches-spinlocks","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/","title":{"rendered":"Advanced SQL Server performance tuning"},"content":{"rendered":"<p>(Check out my Pluralsight online training course: <em><a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-waits\" target=\"_blank\">SQL Server: Performance Troubleshooting Using Wait Statistics<\/a>\u00a0<\/em>and my\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/waits\/\" target=\"_blank\">comprehensive library of all wait types and latch classes<\/a>.)<\/p>\n<p>It&#8217;s all very well having whizz-bang 3rd-party performance monitoring and troubleshooting tools, but sometimes you have to get deeper into what&#8217;s going on with SQL Server than any of these tools can go. Or you have to call\u00a0Customer Support or Premier Support so *they* can dive in deeper.<\/p>\n<p>Typically you or they are going to make use of four DMVs that give increasingly advanced information about what&#8217;s going on for use in performance troubleshooting:<\/p>\n<ul>\n<li><em><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms179984.aspx\" target=\"_blank\">sys.dm_os_wait_stats<\/a><\/em><\/li>\n<li><em><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms188743.aspx\" target=\"_blank\">sys.dm_os_waiting_tasks<\/a><\/em><\/li>\n<li><em><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms175066.aspx\" target=\"_blank\">sys.dm_os_latch_stats<\/a><\/em><\/li>\n<li><em>sys.dm_os_spinlock_stats<\/em> (this one isn&#8217;t documented at all and is only mentioned in a few places online)<\/li>\n<\/ul>\n<p>A few weeks ago I kicked off a survey to find out whether you&#8217;ve heard of or used these DMVs. In this post I&#8217;m going to present the survey results and explain a bit about these DMVs, focusing the most attention on latches and spinlocks. This started out as a small post but grew into a 10-page, 2500 word article :-)<\/p>\n<p>Here are the results (in each of the Other values, a few people asked what DMVs are &#8211; see <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms188754.aspx\" target=\"_blank\">Dynamic Management Views and Functions<\/a> in BOL).<\/p>\n<h2>sys.dm_os_wait_stats<\/h2>\n<p><img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/4\/dmv1.jpg\" alt=\"\" \/><\/p>\n<p>Other values:<\/p>\n<ul>\n<li>12 x Yes, more than once or twice but not routinely.<\/li>\n<li>2 x Only because of your wait statistics post.<\/li>\n<\/ul>\n<p>The survey results are not surprising, especially among readers of my blog.<\/p>\n<p>Wait statistics are the bread-and-butter of performance tuning. SQL Server is keeping track of what resources threads need to wait for, and how long they need to wait. By analyzing which resources (and combinations of resource) are being waited for the most, you can get an idea of where to start digging in further. An example might be that if most of the waits are <em><a href=\"https:\/\/www.sqlskills.com\/help\/waits\/pageiolatch_sh\/\" target=\"_blank\">PAGEIOLATCH_SH<\/a><\/em> waits, and this wasn&#8217;t the case in your wait stats baseline, you might look at the I\/O subsystem performance using the <em>sys.dm_io_virtual_file_stats<\/em> DMV (which I blogged about <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-examine-io-subsystem-latencies-from-within-sql-server\/\" target=\"_blank\">here<\/a>).<\/p>\n<p>Last December I wrote a long blog post introducing wait statistics, showing how to use the <em>sys.dm_os_wait_stats<\/em> DMV, giving links to resources, and explaining the most common ones that people see in the field based on data from more than 1800 SQL Servers\u00a0&#8211; see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wait-statistics-or-please-tell-me-where-it-hurts\/\" target=\"_blank\">Wait statistics, or please tell me where it hurts<\/a>.<\/p>\n<h2>sys.dm_os_waiting_tasks<\/h2>\n<p><img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/4\/dmv2.jpg\" alt=\"\" \/><\/p>\n<p>Other values:<\/p>\n<ul>\n<li>6 x Yes, More than once or twice but not routinely.<\/li>\n<\/ul>\n<p>I&#8217;m surprised that these results don&#8217;t tie in more closely with the results for <em>sys.dm_os_wait_stats<\/em>, but they&#8217;re reasonably close.<\/p>\n<p>The <em>sys.dm_os_waiting_tasks<\/em> DMV shows you what is currently being waited on by everything running on the system.<\/p>\n<p>I created a scenario with 200 clients creating and dropping small temp tables to create tempdb latch contention. Using the DMV, I can see what&#8217;s being waited on (I&#8217;ve removed the columns describing blocking from the output in this case to make it fit on screen):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT * FROM sys.dm_os_waiting_tasks;\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nwaiting_task_address session_id exec_context_id wait_duration_ms\u00a0\u00a0\u00a0\u00a0 wait_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 resource_address\u00a0\u00a0 resource_description\r\n-------------------- ---------- --------------- -------------------- ------------------ ------------------ --------------------\r\n0x000000000044E508\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4091305\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XE_DISPATCHER_WAIT NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\r\n0x000000000044E988\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4121252\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FSAGENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\r\n0x000000000044EBC8\u00a0\u00a0 20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4121251\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BROKER_TRANSMITTER NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\r\n0x000000000044F4C8\u00a0\u00a0 63\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000088FFEED8 2:1:1139\r\n0x000000000044EE08\u00a0\u00a0 64\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_UP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FE8AD8 2:1:1\r\n0x000000000044F288\u00a0\u00a0 87\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 PAGELATCH_UP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FE8AD8 2:1:1\r\n0x000000000044F048\u00a0\u00a0 91\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000088FFEED8 2:1:1139\r\n0x000000000044F948\u00a0\u00a0 92\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 61\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000088FFEED8 2:1:1139\r\n0x000000000044F708\u00a0\u00a0 101\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FEEC58 2:1:120\r\n0x000000000044FDC8\u00a0\u00a0 103\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 37\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_UP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FE8AD8 2:1:1\r\n0x000000008744E088\u00a0\u00a0 118\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FEEC58 2:1:120\r\n0x000000008744E2C8\u00a0\u00a0 121\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 66\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_UP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FE8AD8 2:1:1\r\n0x000000008744E508\u00a0\u00a0 122\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 33\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FEEC58 2:1:120\r\n0x000000008744E748\u00a0\u00a0 155\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FEEC58 2:1:120\r\n0x000000008744E988\u00a0\u00a0 158\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 27\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FEEC58 2:1:120\r\n0x000000008744EBC8\u00a0\u00a0 163\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 34\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_EX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FEEC58 2:1:120\r\n0x000000008744EE08\u00a0\u00a0 168\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 66\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_UP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FE8AD8 2:1:1\r\n0x000000008744F048\u00a0\u00a0 179\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 26\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PAGELATCH_UP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0x0000000080FE8AD8 2:1:1\r\n.\r\n.\r\n<\/pre>\n<p>As you can see, the classic tempdb latch contention is showing &#8211; page ID (2:1:1) &#8211; the first PFS page in tempdb. (See <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core\/\" target=\"_blank\">here for more on tempdb contention<\/a>, and <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/\" target=\"_blank\">here for more on PFS pages<\/a>.)<\/p>\n<p>My colleague Joe Sack created a script that pulls in data from a bunch of other DMVs to make the <em>sys.dm_os_waiting_tasks<\/em> output more useful, which I&#8217;ve modified into the following (note that &#8216;text&#8217; on one line does not have delimiters because that messes up the code formatting plugin):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT\r\n    &#x5B;owt].&#x5B;session_id],\r\n    &#x5B;owt].&#x5B;exec_context_id],\r\n    &#x5B;ot].&#x5B;scheduler_id],\r\n    &#x5B;owt].&#x5B;wait_duration_ms],\r\n    &#x5B;owt].&#x5B;wait_type],\r\n    &#x5B;owt].&#x5B;blocking_session_id],\r\n    &#x5B;owt].&#x5B;resource_description],\r\n    CASE &#x5B;owt].&#x5B;wait_type]\r\n        WHEN N'CXPACKET' THEN\r\n            RIGHT (&#x5B;owt].&#x5B;resource_description],\r\n                CHARINDEX (N'=', REVERSE (&#x5B;owt].&#x5B;resource_description])) - 1)\r\n        ELSE NULL\r\n    END AS &#x5B;Node ID],\r\n    --&#x5B;es].&#x5B;program_name],\r\n    &#x5B;est].text,\r\n    &#x5B;er].&#x5B;database_id],\r\n    &#x5B;eqp].&#x5B;query_plan],\r\n    &#x5B;er].&#x5B;cpu_time]\r\nFROM sys.dm_os_waiting_tasks &#x5B;owt]\r\nINNER JOIN sys.dm_os_tasks &#x5B;ot] ON\r\n    &#x5B;owt].&#x5B;waiting_task_address] = &#x5B;ot].&#x5B;task_address]\r\nINNER JOIN sys.dm_exec_sessions &#x5B;es] ON\r\n    &#x5B;owt].&#x5B;session_id] = &#x5B;es].&#x5B;session_id]\r\nINNER JOIN sys.dm_exec_requests &#x5B;er] ON\r\n    &#x5B;es].&#x5B;session_id] = &#x5B;er].&#x5B;session_id]\r\nOUTER APPLY sys.dm_exec_sql_text (&#x5B;er].&#x5B;sql_handle]) &#x5B;est]\r\nOUTER APPLY sys.dm_exec_query_plan (&#x5B;er].&#x5B;plan_handle]) &#x5B;eqp]\r\nWHERE\r\n    &#x5B;es].&#x5B;is_user_process] = 1\r\nORDER BY\r\n    &#x5B;owt].&#x5B;session_id],\r\n    &#x5B;owt].&#x5B;exec_context_id];\r\nGO\r\n<\/pre>\n<p>There&#8217;s too much information in the output to usefully show in this post, but I can see the actual T-SQL statements being run (in this case a lot of <em>DROP TABLE<\/em> and <em>SELECT * INTO<\/em>\u00a0of global temp tables) and the XML query plans. Clicking on one of them in SSMS gives me the actual plan &#8211; very cool:<\/p>\n<p><img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/4\/dmv5.jpg\" alt=\"\" \/><\/p>\n<p>This means I can see from the <em>sys.dm_os_wait_stats<\/em> DMV what the prevalent resource waits are, then use the <em>sys.dm_os_waiting_tasks<\/em> DMV to see which queries are waiting for those resources &#8211; and then dive in deeper to see why.<\/p>\n<h2>sys.dm_os_latch_stats<\/h2>\n<p><img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/4\/dmv3.jpg\" alt=\"\" \/><\/p>\n<p>As I suspected most readers have heard of latches, but 75% of respondents haven&#8217;t used the DMV or have only used it once or twice.<\/p>\n<p>A latch is a lightweight synchronization mechanism that protects access to read and change\u00a0in-memory structures &#8211; for instance, 8KB page buffers in the buffer pool (latch class\u00a0= <em><a href=\"http:\/\/www.sqlskills.com\/help\/latches\/buffer\/\" target=\"_blank\">BUFFER<\/a><\/em>), or the data structure that represents a database&#8217;s data and log files (latch class\u00a0= <em><a href=\"https:\/\/www.sqlskills.com\/help\/latches\/fgcb_add_remove\/\" target=\"_blank\">FGCB_ADD_REMOVE<\/a><\/em>). A latch is only held for the duration of the operation, unlike a lock which may be held until a transaction commits. One example of locks and latches &#8211; imagine a table where an update query has caused lock escalation so that a table X lock is held on the table. As the query continues updating more records in the table, it won&#8217;t acquire any more locks, but any data and index pages that are updated in memory must be EX (exclusive) latched before the update can occur. The latch acts as the synchronization mechanism to prevent two threads updating the page at the same time, or a thread reading the page while another is in the middle of updating it. Another example is if you run a select query using <em>NOLOCK<\/em> &#8211; although the query will not acquire SH (share) locks at any level in the table, the threads must acquire SH latches on pages before they can be read &#8211; to synchronize with possible concurrent updaters.<\/p>\n<p>If a thread requires a latch it will be moved from <em>RUNNING<\/em> to <em>SUSPENDED<\/em> and put on the waiter list to await notification that the latch has been acquired in the requested mode.<\/p>\n<p>Latch waits correspond to <em><a href=\"https:\/\/www.sqlskills.com\/help\/waits\/latch_ex\/\" target=\"_blank\">LATCH_XX<\/a><\/em> waits in the output from the <em>sys.dm_os_wait_stats<\/em> DMV, so digging into to which latches are accounting for most waits can show where a bottleneck is on the system.<\/p>\n<p>You can reset latch wait statistics just like regular wait statistics using:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);\r\nGO\r\n<\/pre>\n<p>An example set of output from the DMV is:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT * FROM sys.dm_os_latch_stats\r\nWHERE &#x5B;waiting_requests_count] &gt; 0\r\nORDER BY &#x5B;wait_time_ms] DESC;\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nlatch_class\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 waiting_requests_count wait_time_ms\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max_wait_time_ms\r\n--------------------------------- ---------------------- -------------------- --------------------\r\nBUFFER\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 113181121\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 466697044\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1233\r\nACCESS_METHODS_HOBT_COUNT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 66676\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 331193\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 577\r\nACCESS_METHODS_HOBT_VIRTUAL_ROOT\u00a0 15018\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 68865\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 125\r\nLOG_MANAGER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 130\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5610\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 234\r\nFGCB_ADD_REMOVE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 299\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5073\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 32\r\nTRACE_CONTROLLER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\nVERSIONING_TRANSACTION_LIST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\nACCESS_METHODS_HOBT_FACTORY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 64\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n<\/pre>\n<p>You can also aggregate them in the same way as I described in my <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wait-statistics-or-please-tell-me-where-it-hurts\/\" target=\"_blank\">big wait stats blog post<\/a>, using code below:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nWITH &#x5B;Latches] AS\r\n\t(SELECT\r\n\t\t&#x5B;latch_class],\r\n\t\t&#x5B;wait_time_ms] \/ 1000.0 AS &#x5B;WaitS],\r\n\t\t&#x5B;waiting_requests_count] AS &#x5B;WaitCount],\r\n\t\t100.0 * &#x5B;wait_time_ms] \/ SUM (&#x5B;wait_time_ms]) OVER() AS &#x5B;Percentage],\r\n\t\tROW_NUMBER() OVER(ORDER BY &#x5B;wait_time_ms] DESC) AS &#x5B;RowNum]\r\n\tFROM sys.dm_os_latch_stats\r\n\tWHERE &#x5B;latch_class] NOT IN (\r\n\t\tN'BUFFER')\r\n\t--AND &#x5B;wait_time_ms] &gt; 0\r\n\t)\r\nSELECT\r\n\tMAX (&#x5B;W1].&#x5B;latch_class]) AS &#x5B;LatchClass],\r\n\tCAST (MAX (&#x5B;W1].&#x5B;WaitS]) AS DECIMAL(14, 2)) AS &#x5B;Wait_S],\r\n\tMAX (&#x5B;W1].&#x5B;WaitCount]) AS &#x5B;WaitCount],\r\n\tCAST (MAX (&#x5B;W1].&#x5B;Percentage]) AS DECIMAL(14, 2)) AS &#x5B;Percentage],\r\n\tCAST ((MAX (&#x5B;W1].&#x5B;WaitS]) \/ MAX (&#x5B;W1].&#x5B;WaitCount])) AS DECIMAL (14, 4)) AS &#x5B;AvgWait_S]\r\nFROM &#x5B;Latches] AS &#x5B;W1]\r\nINNER JOIN &#x5B;Latches] AS &#x5B;W2]\r\n\tON &#x5B;W2].&#x5B;RowNum] &lt;= &#x5B;W1].&#x5B;RowNum]\r\nGROUP BY &#x5B;W1].&#x5B;RowNum]\r\nHAVING SUM (&#x5B;W2].&#x5B;Percentage]) - MAX (&#x5B;W1].&#x5B;Percentage]) &lt; 95; -- percentage threshold\r\nGO\r\n<\/pre>\n<p>Here&#8217;s an example after clearing the latch stats and running the tempdb contention test (I described above) for 30 seconds:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nLatchClass\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Wait_S\u00a0 WaitCount\u00a0 Percentage\u00a0 AvgWait_S\r\n--------------------------------- ------- ---------- ----------- ----------\r\nACCESS_METHODS_HOBT_COUNT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7.92\u00a0\u00a0\u00a0 1471\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 75.41\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.0054\r\nACCESS_METHODS_HOBT_VIRTUAL_ROOT\u00a0 1.38\u00a0\u00a0\u00a0 393\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13.15\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.0035\r\nLOG_MANAGER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1.20\u00a0\u00a0\u00a0 12\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11.44\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.1001\r\n<\/pre>\n<p>Most of the latch classes are undocumented, but I&#8217;ll be shedding light on them as I blog more about latch stats.<\/p>\n<h2>sys.dm_os_spinlock_stats<\/h2>\n<p><img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/4\/dmv4.jpg\" alt=\"\" \/><\/p>\n<p>Other values:<\/p>\n<ul>\n<li>1 x Just learned of this at SQLskills training!<\/li>\n<li>1 x Learned about it, after you tweeted about it on 3\/23 to a co-worker.<\/li>\n<\/ul>\n<p>This is really cool that more than 40% of respondents have never heard of this DMV or spinlocks &#8211; education time!<\/p>\n<p>A spinlock is another lightweight synchronization mechanism used to control access to certain data structures in the engine &#8211; used when the time that the spinlock will be held is very short. They are different from latches because\u00a0a thread waiting for a latch will yield the scheduler and go onto the waiter list whereas a thread waiting to acquire a spinlock will burn some CPU &#8220;spinning&#8221; to see if it can get the CPU before giving up and backing off (yielding the scheduler) before trying again. This may allow another thread to execute that is holding the spinlock and eventually release it, allowing the system to proceed (yes, a thread can yield the scheduler and move to the waiter list while holding a spinlock!) because another thread can then acquire the spinlock.<\/p>\n<p>It is perfectly normal for spinlock collisions and spins to occur on a busy system, but sometimes a bottleneck can occur on systems with larger numbers of CPUs where collisions are more likely &#8211; this can drain CPU resources while many threads are spinning trying to acquire the spinlock.<\/p>\n<p>Running the DMV shows you the list of all spinlocks on the system (all of which are undocumented &#8211; but I&#8217;ll be working on that going forward) &#8211; here is some partial output:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT * FROM sys.dm_os_spinlock_stats\r\nORDER BY &#x5B;spins] DESC;\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nname\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 collisions\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 spins\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 spins_per_collision sleep_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 backoffs\r\n------------------ -------------------- -------------------- ------------------- -------------------- -----------\r\nLOCK_HASH\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3629624\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4402099957\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1212.825\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 561\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 817819\r\nSOS_CACHESTORE\u00a0\u00a0\u00a0\u00a0 11992297\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3352117666\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 279.5226\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6093\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 71948\r\nOPT_IDX_MISS_KEY\u00a0\u00a0 63329610\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2036811058\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 32.16207\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15830\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 180845\r\nSOS_TLIST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9769744\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 574740437\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 58.82861\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 320\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3619\r\nSOS_SCHEDULER\u00a0\u00a0\u00a0\u00a0\u00a0 2137875\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 107392996\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 50.23352\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 557\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7753\r\nMUTEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 676406\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 83493780\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 123.4374\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 340\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3300\r\nLOGCACHE_ACCESS\u00a0\u00a0\u00a0 2210697\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 83204315\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 37.63714\u00a0\u00a0\u00a0\u00a0\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 252366\r\nSOS_RW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 264489\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 70122059\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 265.1228\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 14\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 799\r\nXDESMGR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 346005\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 61031449\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 176.3889\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 216\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3788\r\nSOS_SUSPEND_QUEUE\u00a0 3397384\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 53752545\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15.82174\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 120\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2384\r\nOPT_IDX_STATS\u00a0\u00a0\u00a0\u00a0\u00a0 129814\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19800952\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 152.5332\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 27\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 356\r\nBACKUP_CTX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29730\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16784471\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 564.5635\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1645\r\nLOCK_RESOURCE_ID\u00a0\u00a0 17558\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4363116\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 248.4973\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 375\r\nSOS_TASK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 206597\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1898063\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9.187273\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 171\r\nXVB_LIST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 266112\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 882691\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3.316991\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 63\r\n.\r\n.\r\n<\/pre>\n<p>On 2005 you&#8217;ll need to use <em>DBCC SQLPERF (&#8216;spinlockstats&#8217;)<\/em> and use <em>INSERT\/EXEC<\/em> to get the results into a table. Eric Humphrey (<a href=\"http:\/\/www.erichumphrey.com\/\" target=\"_blank\">blog<\/a>|<a href=\"https:\/\/twitter.com\/lotsahelp\" target=\"_blank\">twitter<\/a>) put the code together:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nIF OBJECT_ID (N'tempdb..#TempSpinlockStats1') IS NOT NULL\r\n    DROP TABLE &#x5B;#TempSpinlockStats1];\r\nGO\r\nCREATE TABLE &#x5B;#TempSpinlockStats1] (\r\n    &#x5B;name]                NVARCHAR(30) NOT NULL,\r\n    &#x5B;collisions]          BIGINT NOT NULL,\r\n    &#x5B;spins]               BIGINT NOT NULL,\r\n    &#x5B;spins_per_collision] FLOAT NOT NULL,\r\n    &#x5B;sleep_time]          BIGINT NOT NULL,\r\n    &#x5B;backoffs]            BIGINT NOT NULL\r\n);\r\nINSERT INTO &#x5B;#TempSpinlockStats1] EXEC ('DBCC SQLPERF(''spinlockstats'')');\r\nGO\r\n<\/pre>\n<p>The <em>LOCK_HASH<\/em> spinlock, for instance, is used by the lock manager to look at one of the hash buckets holding lock resource hashes to tell whether lock can be granted or not.<\/p>\n<p>The sleep_time is an aggregate of how much time is spent sleeping between spin cycles when a backoff occurs.<\/p>\n<p>I&#8217;ve put together some code that will allow you to see what spinlock activity occurs between two times. The code captures the output from the DMV into two temp tables, with whatever time period you want in between (to allow you to run a command), and then shows the difference between the two sets of data. I&#8217;ll show an example of running <em>DBCC CHECKDB<\/em>.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n\tWHERE &#x5B;name] = N'##TempSpinlockStats1')\r\n\tDROP TABLE &#x5B;##TempSpinlockStats1];\r\n\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n\tWHERE &#x5B;name] = N'##TempSpinlockStats2')\r\n\tDROP TABLE &#x5B;##TempSpinlockStats2];\r\nGO\r\n\r\n-- Baseline\r\nSELECT * INTO &#x5B;##TempSpinlockStats1]\r\nFROM sys.dm_os_spinlock_stats\r\nWHERE &#x5B;collisions] &gt; 0\r\nORDER BY &#x5B;name];\r\nGO\r\n\r\n-- Now do something\r\nDBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;\r\nGO\r\n\r\n-- Capture updated stats\r\nSELECT * INTO &#x5B;##TempSpinlockStats2]\r\nFROM sys.dm_os_spinlock_stats\r\nWHERE &#x5B;collisions] &gt; 0\r\nORDER BY &#x5B;name];\r\nGO\r\n\r\n-- Diff them\r\nSELECT\r\n    '***' AS &#x5B;New],\r\n    &#x5B;ts2].&#x5B;name] AS &#x5B;Spinlock],\r\n    &#x5B;ts2].&#x5B;collisions] AS &#x5B;DiffCollisions],\r\n    &#x5B;ts2].&#x5B;spins] AS &#x5B;DiffSpins],\r\n    &#x5B;ts2].&#x5B;spins_per_collision] AS &#x5B;SpinsPerCollision],\r\n    &#x5B;ts2].&#x5B;sleep_time] AS &#x5B;DiffSleepTime],\r\n    &#x5B;ts2].&#x5B;backoffs] AS &#x5B;DiffBackoffs]\r\nFROM &#x5B;##TempSpinlockStats2] &#x5B;ts2]\r\nLEFT OUTER JOIN &#x5B;##TempSpinlockStats1] &#x5B;ts1]\r\n    ON &#x5B;ts2].&#x5B;name] = &#x5B;ts1].&#x5B;name]\r\nWHERE &#x5B;ts1].&#x5B;name] IS NULL\r\nUNION\r\nSELECT\r\n    '' AS &#x5B;New],\r\n    &#x5B;ts2].&#x5B;name] AS &#x5B;Spinlock],\r\n    &#x5B;ts2].&#x5B;collisions] - &#x5B;ts1].&#x5B;collisions] AS &#x5B;DiffCollisions],\r\n    &#x5B;ts2].&#x5B;spins] - &#x5B;ts1].&#x5B;spins] AS &#x5B;DiffSpins],\r\n    CASE (&#x5B;ts2].&#x5B;spins] - &#x5B;ts1].&#x5B;spins]) WHEN 0 THEN 0\r\n        ELSE (&#x5B;ts2].&#x5B;spins] - &#x5B;ts1].&#x5B;spins]) \/\r\n            (&#x5B;ts2].&#x5B;collisions] - &#x5B;ts1].&#x5B;collisions]) END\r\n            AS &#x5B;SpinsPerCollision],\r\n    &#x5B;ts2].&#x5B;sleep_time] - &#x5B;ts1].&#x5B;sleep_time] AS &#x5B;DiffSleepTime],\r\n    &#x5B;ts2].&#x5B;backoffs] - &#x5B;ts1].&#x5B;backoffs] AS &#x5B;DiffBackoffs]\r\nFROM &#x5B;##TempSpinlockStats2] &#x5B;ts2]\r\nLEFT OUTER JOIN &#x5B;##TempSpinlockStats1] &#x5B;ts1]\r\n    ON &#x5B;ts2].&#x5B;name] = &#x5B;ts1].&#x5B;name]\r\nWHERE &#x5B;ts1].&#x5B;name] IS NOT NULL\r\n    AND &#x5B;ts2].&#x5B;collisions] - &#x5B;ts1].&#x5B;collisions] &gt; 0\r\nORDER BY &#x5B;New] DESC, &#x5B;Spinlock] ASC;\r\nGO\r\n<\/pre>\n<p>And the output is as follows:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nNew\u00a0 Spinlock\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DiffCollisions\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DiffSpins\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SpinsPerCollision DiffSleepTime\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DiffBackoffs\r\n---- ------------------ -------------------- -------------------- ----------------- -------------------- ------------\r\n***\u00a0 DBCC_CHECK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 24\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 DIAG_MANAGER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 FCB_REPLICA_SYNC\u00a0\u00a0 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16147\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1614.7\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 LSID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 QUERYEXEC\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 X_PACKET_LIST\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 X_PORT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 227\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28.375\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 XACT_WORKSPACE\u00a0\u00a0\u00a0\u00a0 11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n***\u00a0 XID_ARRAY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     BUF_FREE_LIST\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     HOBT_HASH\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     LOCK_HASH\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1818\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 606\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     SOS_RW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 500\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 250\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     SOS_SCHEDULER\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     SOS_SUSPEND_QUEUE\u00a0 11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 39\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     SOS_TASK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n     SOS_TLIST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\r\n<\/pre>\n<p>You can see here which spinlocks were acquired to run the\u00a0<em>DBCC CHECKDB<\/em> commands &#8211; those marked with *** did not appear in the &#8216;before&#8217; set of spinlock stats. More on all of these in future posts.<\/p>\n<p>You can also investigate spinlocks using extended events &#8211; again, more on that in future.<\/p>\n<h2>Summary<\/h2>\n<p>It&#8217;s possible to dive really deeply into what&#8217;s happening inside SQL Server using these four DMVs. Spinlocks in particular &#8211; what each means, what\u00a0each controls\u00a0and what contention on each\u00a0them means (plus what you can do about it) &#8211;\u00a0involve a lot of knowledge of what&#8217;s going on inside the engine, and I&#8217;m planning to spread some of that knowledge going forward &#8211; there&#8217;s an enormous amount of information to be published about latches and spinlocks.<\/p>\n<p>Hope you&#8217;ll join me to learn about these &#8211; let me know if you find this stuff interesting and useful.<\/p>\n<p>Thanks!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics\u00a0and my\u00a0comprehensive library of all wait types and latch classes.) It&#8217;s all very well having whizz-bang 3rd-party performance monitoring and troubleshooting tools, but sometimes you have to get deeper into what&#8217;s going on with SQL Server than any of these tools can [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,55,66,84,91,101],"tags":[],"class_list":["post-579","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-latches","category-performance-tuning","category-spinlocks","category-surveys","category-wait-stats"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Advanced SQL Server Performance Tuning<\/title>\n<meta name=\"description\" content=\"Learn how to use wait statistics, latch statistics, and spinlock statistics in SQL Server.\" \/>\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\/advanced-performance-troubleshooting-waits-latches-spinlocks\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Advanced SQL Server Performance Tuning\" \/>\n<meta property=\"og:description\" content=\"Learn how to use wait statistics, latch statistics, and spinlock statistics in SQL Server.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-04-13T07:32:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:47:37+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=\"13 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\/advanced-performance-troubleshooting-waits-latches-spinlocks\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/\",\"name\":\"Advanced SQL Server Performance Tuning\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2011-04-13T07:32:00+00:00\",\"dateModified\":\"2017-04-13T19:47:37+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"description\":\"Learn how to use wait statistics, latch statistics, and spinlock statistics in SQL Server.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Advanced SQL Server performance tuning\"}]},{\"@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":"Advanced SQL Server Performance Tuning","description":"Learn how to use wait statistics, latch statistics, and spinlock statistics in SQL Server.","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\/advanced-performance-troubleshooting-waits-latches-spinlocks\/","og_locale":"en_US","og_type":"article","og_title":"Advanced SQL Server Performance Tuning","og_description":"Learn how to use wait statistics, latch statistics, and spinlock statistics in SQL Server.","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/","og_site_name":"Paul S. Randal","article_published_time":"2011-04-13T07:32:00+00:00","article_modified_time":"2017-04-13T19:47:37+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/","name":"Advanced SQL Server Performance Tuning","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2011-04-13T07:32:00+00:00","dateModified":"2017-04-13T19:47:37+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"description":"Learn how to use wait statistics, latch statistics, and spinlock statistics in SQL Server.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Advanced SQL Server performance tuning"}]},{"@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\/579","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=579"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/579\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=579"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=579"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=579"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}