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

recorded. Usefully, this DMV also provides a spins_per_collision column, saving the user from
doing the calculation.

I'm sure you can imagine that the number of spins is potentially quite large. Let's just say that it's
a good thing that this column is a bigint type, which handles numbers up to 19 digits long. I don't
think the correct technical term is actually “gazillions,” but it feels right when you take a look at
this DMV on busy systems that have been up for a while.

The sleep_time and backoffs columns simply report the amount of time that has been spent
sleeping on spinlocks, and the number of backoffs.

Performance Monitor
Performance Monitor provides several useful counters to keep
an eye on. Figure 7-19 shows a typical screenshot containing
the list of counters in the SQLServer:Latches category for a
machine.

Table 7-2 describes these counters.

FIGURE 7-19

TABLE 7-2: Useful Performance Monitor Counters

COUNTER DESCRIPTION

Average Latch Wait Time (ms) Average latch wait time (in milliseconds) for latch requests that

had to wait

Latch Waits/sec Number of latch requests that could not be granted

immediately and had to wait before being granted

Number of SuperLatches Number of latches that are currently SuperLatches

SuperLatch Demotions/sec Number of SuperLatches that have been demoted to regular

latches

SuperLatch Promotions/sec Number of latches that have been promoted to SuperLatches

Total Latch Wait Time (ms) Total latch wait time (in milliseconds) for latch requests that had

to wait in the last second

These performance counter values are also available using the DMV sys.dm_os_performance_
counters (see Figure 7-20) (code i le Ch7Monitoring.sql ):

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Latches%';