Latches and Spinlocks - SQL Server 2012 Internals and Troubleshooting - page 184

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_%';
FIGURE 7-4
The DMV called sys.dm_os_latch_stats lists similar statistics for latch classes. This will be
described further later in the chapter.
Measuring Spinlock Contention
For the time being, you can think of a spinlock as a latch, except that if the memory needing access
is not available, the spinlock will keep checking it (known as spinning) for a while. There is slightly
more to it, but that will be kept for later in the chapter.
The main DMV for spinlocks is sys.dm_os_spinlock_stats . The metrics of concern for spinlocks
are around collisions and spins_per_collision , which are both columns in this DMV, along
with the name column for the type of spinlock. Collisions and spins will be described later in the
chapter. Here is an example of using sys.dm_os_spinlock_stats (code i le Ch7Symptoms.sql ),
further illustrated in Figure 7-5:
SELECT name, collisions, spins_per_collision
FROM sys.dm_os_spinlock_stats
ORDER BY spins_per_collision DESC;