{"id":574,"date":"2011-04-26T11:41:00","date_gmt":"2011-04-26T11:41:00","guid":{"rendered":"\/blogs\/paul\/post\/SOS_SCHEDULER_YIELD-waits-and-the-LOCK_HASH-spinlock.aspx"},"modified":"2020-10-19T12:07:41","modified_gmt":"2020-10-19T19:07:41","slug":"sos_scheduler_yield-waits-and-the-lock_hash-spinlock","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/","title":{"rendered":"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock"},"content":{"rendered":"<p>Continuing my series on advanced performance troubleshooting &#8211; see these two posts for the scripts I&#8217;ll be using and an introduction to the series:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wait-statistics-or-please-tell-me-where-it-hurts\/\">Wait statistics, or please tell me where it hurts<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/advanced-performance-troubleshooting-waits-latches-spinlocks\/\">Advanced performance troubleshooting: waits, latches, spinlocks<\/a><\/li>\n<\/ul>\n<p>In this blog post I&#8217;d like to show you an example of <em>SOS_SCHEDULER_YIELD<\/em> waits occurring and how it can seem like a spinlock is the cause.<\/p>\n<p>(Note: you can read more about\u00a0<em><a href=\"http:\/\/www.sqlskills.com\/help\/waits\/sos_scheduler_yield\/\" target=\"_blank\" rel=\"noopener noreferrer\">SOS_SCHEDULER_YIELD<\/a><\/em> waits in my sqlperformance.com blog post <a href=\"http:\/\/sqlperformance.com\/2014\/02\/sql-performance\/knee-jerk-waits-sos-scheduler-yield\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.)<\/p>\n<p>I originally published this blog post and then had a discussion with good friend Bob Ward from Product Support who questioned my conclusions given what he&#8217;s seen (thanks Bob!). After digging in further, I found that my original post was incorrect, so this is the corrected version.<\/p>\n<p>The <em>SOS_SCHEDULER_YIELD<\/em> wait means that an executing thread voluntarily gave up the CPU to allow other threads to execute. The SQL Server code is sprinkled with &#8220;voluntary yields&#8221; in places where high CPU usage may occur.<\/p>\n<p>One such place where a thread will sleep but not explicitly yield is when backing off after\u00a0a spinlock collision waiting to see if it can get access to the spinlock. A spinlock is a very lightweight synchronization mechanism deep inside SQL Server that protects access to a data structure (not database data itself). See the end of the second blog post above for more of an explanation on spinlocks.<\/p>\n<p>When a thread voluntarily yields, it does not go onto the waiting tasks list &#8211; as it&#8217;s not waiting for anything &#8211;\u00a0but instead goes to the bottom of the Runnable Queue for that scheduler. <em>SOS_SCHEDULER_YIELD<\/em> waits by themselves are not cause for concern unless they are the majority of waits on the system, and performance is suffering.<\/p>\n<p>To set up the test, I&#8217;ll create a simple database and table:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;master];\r\nGO\r\nDROP DATABASE &#x5B;YieldTest];\r\nGO\r\n\r\nCREATE DATABASE &#x5B;YieldTest];\r\nGO\r\nUSE &#x5B;YieldTest];\r\nGO\r\n\r\nCREATE TABLE &#x5B;SampleTable] (&#x5B;c1] INT IDENTITY);\r\nGO\r\nCREATE NONCLUSTERED INDEX &#x5B;SampleTable_NC] ON &#x5B;SampleTable] (&#x5B;c1]);\r\nGO\r\n\r\nSET NOCOUNT ON;\r\nGO\r\nINSERT INTO &#x5B;SampleTable] DEFAULT VALUES;\r\nGO 100\r\n<\/pre>\n<p>Then I&#8217;ll clear out wait stats and latch stats:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC SQLPERF (N'sys.dm_os_latch_stats', CLEAR);\r\nGO\r\n\r\nDBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);\r\nGO\r\n<\/pre>\n<p>And then fire up 50 clients running the following code (I just have a CMD script that fires up 50 CMD windows, each running the T-SQL code):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;YieldTest];\r\nGO\r\nSET NOCOUNT ON;\r\nGO\r\n\r\nDECLARE @a INT;\r\nWHILE (1=1)\r\nBEGIN\r\n    SELECT @a = COUNT (*) FROM &#x5B;YieldTest]..&#x5B;SampleTable] WHERE &#x5B;c1] = 1;\r\nEND;\r\nGO\r\n<\/pre>\n<p>And the CPUs in my laptop are jammed solid (snapped from my desktop and rotated 90 degrees to save space):<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg\" alt=\"\" \/><\/p>\n<p>Wow!<\/p>\n<p>Looking at perfmon:<\/p>\n<p><img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/4\/perfmon.jpg\" alt=\"\" \/><\/p>\n<p>The CPU usage is not Windows (<em>% User Time<\/em>), and of all the counters I usually monitor (I&#8217;ve cut them all out here for clarity) I can see a sustained very high <em>Lock Requests\/sec<\/em> for Object and Page locks (almost 950 thousand requests per second for both types! Gotta love my laptop :-)<\/p>\n<p>So let&#8217;s dig in. First off, looking at wait stats (using the script in the wait stats post referenced above):<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nWaitType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Wait_S\u00a0 Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S\r\n------------------- ------- ---------- -------- --------- ---------- --------- -------- --------\r\nSOS_SCHEDULER_YIELD 4574.77 0.20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4574.57\u00a0 206473\u00a0\u00a0\u00a0 99.43\u00a0\u00a0\u00a0\u00a0\u00a0 0.0222\u00a0\u00a0\u00a0 0.0000\u00a0\u00a0 0.0222\r\n<\/pre>\n<p><em>SOS_SCHEDULER_YIELD<\/em> at almost 100% of the waits on the system means that I&#8217;ve got CPU pressure &#8211; as I saw from the CPU graphs above. The fact that nothing else is showing up makes me suspect this is a spinlock issue.<\/p>\n<p>Checking in the sys.dm_os_waiting_tasks output (see script in the second blog post referenced above), I see nothing waiting, and if I refresh a few times I see\u00a0the occasional <em><a href=\"http:\/\/www.sqlskills.com\/help\/waits\/async_network_io\/\" target=\"_blank\" rel=\"noopener noreferrer\">ASYNC_NETWORK_IO<\/a><\/em> and\/or <em><a href=\"http:\/\/www.sqlskills.com\/help\/waits\/preemptive_os_waitforsingleobject\/\" target=\"_blank\" rel=\"noopener noreferrer\">PREEMPTIVE_OS_WAITFORSINGLEOBJECT<\/a><\/em> wait type &#8211; which I&#8217;d expect from the CMD windows running the T-SQL code.<\/p>\n<p>Checking the latch stats (again, see script in the second blog post referenced above) shows no latch waits apart from a few <em><a href=\"http:\/\/www.sqlskills.com\/help\/latches\/buffer\/\" target=\"_blank\" rel=\"noopener noreferrer\">BUFFER<\/a><\/em> latch waits.<\/p>\n<p>So now let&#8217;s look at spinlocks. First off I&#8217;m going to take a snapshot of the spinlocks on the system:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n-- Baseline\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n    WHERE &#x5B;name] = N'##TempSpinlockStats1')\r\n    DROP TABLE &#x5B;##TempSpinlockStats1];\r\nGO\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<\/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 &#8211; see the post above for example code.)<\/p>\n<p>Then wait 10 seconds or so for the workload to continue&#8230; and then grab another snapshot of the spinlocks:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n-- Capture updated stats\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n    WHERE &#x5B;name] = N'##TempSpinlockStats2')\r\n    DROP TABLE &#x5B;##TempSpinlockStats2];\r\nGO\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<\/pre>\n<p>Now running the code I came up with to show the difference between the two snapshots:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\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>&nbsp;<\/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\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DiffCollisions DiffSpins\u00a0 SpinsPerCollision DiffSleepTime DiffBackoffs\r\n---- ------------------------------- -------------- ---------- ----------------- ------------- ------------\r\n     LOCK_HASH\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6191134\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4005774890 647\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 686\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1601383\r\n     OPT_IDX_STATS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1164849\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 126549245\u00a0 108\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 57\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7555\r\n     SOS_OBJECT_STORE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 73\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 305\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\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 0\r\n     SOS_WAITABLE_ADDRESS_HASHBUCKET 115\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 44495\u00a0\u00a0\u00a0\u00a0\u00a0 386\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 3\r\n     XDESMGR\u00a0\u00a0\u00a0\u00a0\u00a0\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 0\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 0\r\n<\/pre>\n<p>This is telling me that there is massive contention for the <em>LOCK_HASH<\/em> spinlock, further backed up by the <em>OPT_IDX_STATS<\/em>\u00a0spinlock (which controls access to the metrics tracked by <em>sys.dm_db_index_usage_stats<\/em>). The <em>LOCK_HASH<\/em> spinlock protects access to the hash buckets used by the lock manager to efficiently keep track of the lock resources for locks held inside SQL Server and to allow efficient searching for lock hash collisions (i.e. does someone hold the lock we want in an incompatible mode?). In this case the contention is so bad that instead of just spinning, the threads are actually backing off and letting other threads execute to allow progress to be made.<\/p>\n<p>And that makes perfect sense because of what my workload is doing &#8211;\u00a050 concurrent connections all trying to read the same row on the same page in the same nonclustered index.<\/p>\n<p>But is that the cause of the <em>SOS_SCHEDULER_YIELD<\/em> waits? To prove it one way or the other, I created an Extended Event session that would capture call stacks when a wait occurs:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n-- Note that before SQL 2012, the wait_type to use is 120, and \r\n-- on 2014 SP1 the wait_type to use is 123. You MUST verify the\r\n-- map_value to use on your build.\r\n-- On SQL 2012 the target name is 'histogram' but the old name still works.\r\nCREATE EVENT SESSION &#x5B;MonitorWaits] ON SERVER\r\nADD EVENT &#x5B;sqlos].&#x5B;wait_info]\r\n\t(ACTION (&#x5B;package0].&#x5B;callstack])\r\n\tWHERE &#x5B;wait_type] = 124) -- SOS_SCHEDULER_YIELD only\r\nADD TARGET &#x5B;package0].&#x5B;asynchronous_bucketizer] (\r\n    SET filtering_event_name = N'sqlos.wait_info',\r\n    source_type = 1, -- source_type = 1 is an action\r\n    source = N'package0.callstack') -- bucketize on the callstack\r\nWITH (MAX_MEMORY = 50MB, max_dispatch_latency = 5 seconds)\r\nGO\r\n\r\n-- Start the session\r\nALTER EVENT SESSION &#x5B;MonitorWaits] ON SERVER STATE = START;\r\nGO\r\n\r\n-- TF to allow call stack resolution\r\nDBCC TRACEON (3656, -1);\r\n--DBCC TRACEON (2592, -1); -- SQL Server 2019+ requires this too\r\nGO\r\n\r\n-- Let the workload run for a few seconds\r\n\r\n-- Get the callstacks from the bucketizer target\r\n-- Are they showing calls into the lock manager?\r\nSELECT\r\n    &#x5B;event_session_address],\r\n    &#x5B;target_name],\r\n    &#x5B;execution_count],\r\n    CAST (&#x5B;target_data] AS XML)\r\nFROM sys.dm_xe_session_targets &#x5B;xst]\r\nINNER JOIN sys.dm_xe_sessions &#x5B;xs]\r\n    ON (&#x5B;xst].&#x5B;event_session_address] = &#x5B;xs].&#x5B;address])\r\nWHERE &#x5B;xs].&#x5B;name] = N'MonitorWaits';\r\nGO\r\n\r\n-- Stop the event session\r\nALTER EVENT SESSION &#x5B;MonitorWaits] ON SERVER STATE = STOP;\r\nGO<\/pre>\n<p>I also made sure to have the correct symbol\u00a0files in the\u00a0\\binn directory (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-download-a-sqlservr-pdb-symbol-file\/\">How to download a sqlservr.pdb symbol file<\/a>). After running the workload and examining the callstacks, I found the majority of the waits were coming from voluntary yields deep in the Access Methods code. An example call stack is:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nIndexPageManager::GetPageWithKey+ef &#x5B; @ 0+0x0\r\nGetRowForKeyValue+146 &#x5B; @ 0+0x0\r\nIndexRowScanner::EstablishInitialKeyOrderPosition+10a &#x5B; @ 0+0x0\r\nIndexDataSetSession::GetNextRowValuesInternal+7d7 &#x5B; @ 0+0x0\r\nRowsetNewSS::FetchNextRow+12a &#x5B; @ 0+0x0\r\nCQScanRangeNew::GetRow+6a1 &#x5B; @ 0+0x0\r\nCQScanCountStarNew::GetRowHelper+44 &#x5B; @ 0+0x0\r\nCQScanStreamAggregateNew::Open+70 &#x5B; @ 0+0x0\r\nCQueryScan::Uncache+32f &#x5B; @ 0+0x0\r\nCXStmtQuery::SetupQueryScanAndExpression+2a2 &#x5B; @ 0+0x0\r\nCXStmtQuery::ErsqExecuteQuery+2f8 &#x5B; @ 0+0x0\r\nCMsqlExecContext::ExecuteStmts&amp;amp;lt;1,1&amp;amp;gt;+cca &#x5B; @ 0+0x0\r\nCMsqlExecContext::FExecute+58b &#x5B; @ 0+0x0\r\nCSQLSource::Execute+319\r\n<\/pre>\n<p>[Edit: check out how to do this in the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-whitepapers-on-latches-and-spinlocks-published\/\" target=\"_blank\" rel=\"noopener noreferrer\">spinlock whitepaper<\/a>]<\/p>\n<p>This is clearly (to me) nothing to do with the <em>LOCK_HASH<\/em> spinlock, so that&#8217;s a red herring. In this case, I&#8217;m just CPU bound.\u00a0When a thread goes to sleep when backing off from a spinlock, it directly calls Windows <em>Sleep()<\/em> &#8211; so it does not show up as a SQL Server wait type at all, even though the sleep call is made from the SQL OS layer. Which is a bummer.<\/p>\n<p>How to get around that and reduce CPU usage? This is really contrived workload, but this can occur for real. Even if I try using <em>WITH (NOLOCK)<\/em>, the NOLOCK\u00a0seek\u00a0will take a table SCH_S (schema-stability) lock to make sure the table structure doesn&#8217;t change while the seek is occurring, so that only gets rid of the page locks, not the object locks and doesn&#8217;t help CPU usage. With this (arguably weird) workload, there are a few things I could do (just off the top of my head):<\/p>\n<ul>\n<li>Enable read_commited_snapshot for the database, which will reduce the <em>LOCK_HASH<\/em> spinlock backoffs<\/li>\n<li>Scale-out the connections to a few copies of the database, updated using replication<\/li>\n<li>Do some mid-tier or client-side caching, with some notification mechanism of data changes (e.g. a DDL trigger firing a Service Broker message)<\/li>\n<\/ul>\n<p>So this was an example of where wait stats lead to having to look at spinlock stats, but that the spinlock, on even *deeper* investigation, wasn&#8217;t the issue at all. Cool stuff.<\/p>\n<p>Next time we&#8217;ll look at another cause of <em>SOS_SCHEDULER_YIELD<\/em> waits.<\/p>\n<p>Hope you enjoyed this &#8211; let me know your thoughts!<\/p>\n<p>Cheers<\/p>\n<p>PS In the latest SQLskills Insider Quick Tips newsletter from last week, I did a video demo of looking at transaction log IOs and waits &#8211; you can get it <a href=\"https:\/\/www.sqlskills.com\/joincommunity.asp\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Continuing my series on advanced performance troubleshooting &#8211; see these two posts for the scripts I&#8217;ll be using and an introduction to the series: Wait statistics, or please tell me where it hurts Advanced performance troubleshooting: waits, latches, spinlocks In this blog post I&#8217;d like to show you an example of SOS_SCHEDULER_YIELD waits occurring and [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57,66,84,101],"tags":[],"class_list":["post-574","post","type-post","status-publish","format-standard","hentry","category-locking","category-performance-tuning","category-spinlocks","category-wait-stats"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock - 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\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Continuing my series on advanced performance troubleshooting &#8211; see these two posts for the scripts I&#8217;ll be using and an introduction to the series: Wait statistics, or please tell me where it hurts Advanced performance troubleshooting: waits, latches, spinlocks In this blog post I&#8217;d like to show you an example of SOS_SCHEDULER_YIELD waits occurring and [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-04-26T11:41:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-19T19:07:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg\" \/>\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=\"10 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\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/\",\"name\":\"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg\",\"datePublished\":\"2011-04-26T11:41:00+00:00\",\"dateModified\":\"2020-10-19T19:07:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock\"}]},{\"@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":"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock - 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\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/","og_locale":"en_US","og_type":"article","og_title":"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock - Paul S. Randal","og_description":"Continuing my series on advanced performance troubleshooting &#8211; see these two posts for the scripts I&#8217;ll be using and an introduction to the series: Wait statistics, or please tell me where it hurts Advanced performance troubleshooting: waits, latches, spinlocks In this blog post I&#8217;d like to show you an example of SOS_SCHEDULER_YIELD waits occurring and [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/","og_site_name":"Paul S. Randal","article_published_time":"2011-04-26T11:41:00+00:00","article_modified_time":"2020-10-19T19:07:41+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/","name":"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg","datePublished":"2011-04-26T11:41:00+00:00","dateModified":"2020-10-19T19:07:41+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/4\/lotsofcpu.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sos_scheduler_yield-waits-and-the-lock_hash-spinlock\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock"}]},{"@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\/574","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=574"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/574\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=574"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=574"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=574"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}