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