Capturing spinlock statistics for a period of time

This is a quick post to blog a script that allows spinlock statistics to be captured for a defined period of time (as I need to reference it in my next post). Enjoy!

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##TempSpinlockStats1')
	DROP TABLE [##TempSpinlockStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##TempSpinlockStats2')
	DROP TABLE [##TempSpinlockStats2];
GO

-- Baseline
SELECT * INTO [##TempSpinlockStats1]
FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO

-- Now wait...
WAITFOR DELAY '00:00:05';
GO

-- Capture updated stats
SELECT * INTO [##TempSpinlockStats2]
FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO

-- Diff them
SELECT
		'***' AS [New],
		[ts2].[name] AS [Spinlock],
		[ts2].[collisions] AS [DiffCollisions],
		[ts2].[spins] AS [DiffSpins],
		[ts2].[spins_per_collision] AS [SpinsPerCollision],
		[ts2].[sleep_time] AS [DiffSleepTime],
		[ts2].[backoffs] AS [DiffBackoffs]
	FROM [##TempSpinlockStats2] [ts2]
	LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
		ON [ts2].[name] = [ts1].[name]
	WHERE [ts1].[name] IS NULL
UNION
SELECT
		'' AS [New],
		[ts2].[name] AS [Spinlock],
		[ts2].[collisions] - [ts1].[collisions] AS [DiffCollisions],
		[ts2].[spins] - [ts1].[spins] AS [DiffSpins],
		CASE ([ts2].[spins] - [ts1].[spins]) WHEN 0 THEN 0
			ELSE ([ts2].[spins] - [ts1].[spins]) /
				([ts2].[collisions] - [ts1].[collisions]) END
				AS [SpinsPerCollision],
		[ts2].[sleep_time] - [ts1].[sleep_time] AS [DiffSleepTime],
		[ts2].[backoffs] - [ts1].[backoffs] AS [DiffBackoffs]
	FROM [##TempSpinlockStats2] [ts2]
	LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
		ON [ts2].[name] = [ts1].[name]
	WHERE [ts1].[name] IS NOT NULL
	AND [ts2].[collisions] - [ts1].[collisions] > 0
ORDER BY [New] DESC, [Spinlock] ASC;
GO

Example output (trimmed to fit here):

New  Spinlock                DiffCollisions   DiffSpins   SpinsPerCollision DiffSleepTime   DiffBackoffs
---- ----------------------  ---------------- ----------- ----------------- --------------- ------------
     ALLOC_CACHES_HASH       999              257750      258               0               5
     BLOCKER_ENUM            103              27250       264               0               2
     CMED_HASH_SET           286              71500       250               0               0
     COMPPLAN_SKELETON       148              37000       250               0               0
     DBTABLE                 14               3500        250               0               0
     FGCB_PRP_FILL           4                1000        250               0               0
     FREE_SPACE_CACHE_ENTRY  983              255250      259               0               7
     LOGCACHE_ACCESS         3353             314628      93                0               2241
     LOGFLUSHQ               797              206250      258               0               10
     OPT_IDX_STATS           147              36750       250               0               0
     RESQUEUE                23               5750        250               0               0
     SECURITY_CACHE          106              26500       250               0               0
     SOS_CACHESTORE          235              60750       258               0               3
     SOS_OBJECT_STORE        55               13750       250               0               0
     SOS_SCHEDULER           219              54750       250               0               0
     SOS_SUSPEND_QUEUE       72               18000       250               0               0
     SOS_TASK                69               18750       271               0               2
     SQL_MGR                 394              98500       250               0               0
     XDES                    52               13000       250               0               0
     XDESMGR                 840              341500      406               0               16
     XTS_MGR                 165              42750       259               0               2

7 thoughts on “Capturing spinlock statistics for a period of time

  1. Hello Sir,

    What are the differences between spinlock wait “SOS_CACHESTORE” & “SOS_CACHESTORE_CLOCK”.

    I did google about SOS_CACHESTORE_CLOCK but did not find anything :(

    Please help me to understand it.

    Thank You,
    Vimal

      1. Paul – Can you provide some generic direction on how or where to start troubleshooting if SOS_CACHESTORE_CLOCK is at the top of the list? Is this a support case with Microsoft or a performance tuning engagement with a company like SQL Skills? Thanks in advance for your time.

        1. That’s related to cache store clock hand ticks, so you could look to see which store is ticking and there’s a direction to investigate further. If it’s causing you a performance issue, I’d go with Microsoft as we’re choc-a-bloc at present so wouldn’t be able to help for a while.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.