检测闩锁/自旋锁争用

1.检测闩锁争用

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_%'

2.检测自旋锁争用

SELECT name,collisions,spins_per_collision 
FROM sys.dm_os_spinlock_stats
order by spins_per_collision desc;