Diagnose latch and spinlock contention
Query wait statistics and spinlock statistics to find latch and spinlock contention in SQL Server.
Latch and spinlock contention shows up as waits on SQL Server internal synchronization objects. Two system views measure it: sys.dm_os_wait_stats for latch waits, and sys.dm_os_spinlock_stats for spinlock collisions.
Latch waits
The query below lists the LATCH_%, PAGELATCH_% and PAGEIOLATCH_% wait types with the average wait per waiting task. Large averages on these types point to latch contention.
SELECT
wait_type,
wait_time_ms,
waiting_tasks_count,
wait_time_ms / NULLIF(waiting_tasks_count,0) AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'LATCH_%'
OR wait_type LIKE 'PAGELATCH_%'
OR wait_type LIKE 'PAGEIOLATCH_%';
Spinlock statistics
The query below lists the spinlocks with the most spins per collision first.
SELECT name, collisions, spins_per_collision
FROM sys.dm_os_spinlock_stats
ORDER BY spins_per_collision DESC;