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

In order for spinlock contention to be a concern, behavior as described
by the chart shown in Figure 7-3 would be exhibited, with the CPU
rising exponentially as the load increases, with transactions dropping as
with the latches. Keep in mind that you should also eliminate other
factors that may be responsible for the increased CPU load.

CPU

Sometimes the obvious needs to be stated — you need a benchmark to
tell you what “good performance” looks like, to weigh against what
you're seeing when troubleshooting. When you examine a system
without any historical background, you can sometimes recognize
undesirable behavior, but a particular system could easily exhibit
symptoms that are typical for it. Doctors use benchmarks when testing
their patients — some of whom exhibit levels that are not typical across
a broad population but are i ne for them.

Transactions per second

FIGURE 7-3

When you, in your role as database surgeon, open Performance Monitor, or PerfMon, and start
looking at the various levels, it helps to know what those levels were before the problems started.

Measuring Latch Contention
A latch is like a lock on a piece of memory. As more threads get involved, they will start to compete
to access the same pieces of memory, causing blocking. Blocking due to latch contention is exhibited
in waits; but unlike a lock, a latch can be released as soon as the physical operation is completed.

The main sources of data about latches are two DMVs called sys.dm_os_wait_stats and
sys.dm_os_latch_stats . The details of the values stored in these DMVs will be examined later in
an explanation of latch modes and latch types, but for the purposes of recognizing the symptoms, a
brief explanation will sufi ce. The DMVs are restarted when the service restarts, or when the
DBCC SQLPERF command is called to clear them, as in the following code (code i le Ch7Symptoms.sql ):

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);

Among the columns of the DMV sys.dm_os_wait_stats are ones called wait_type , wait_time_
ms and waiting_tasks_count . These three columns represent the wait type, the total number
of milliseconds that have been spent waiting on this wait type, and the number of times this
type of wait has occurred, respectively. Wait types that associate with latches start with LATCH_ ,
PAGELATCH_ , or PAGEIOLATCH_ .

Dividing the total wait time by the number of waits will give an average wait time (I'd recommend
wrapping the waiting_tasks_count column in the NULLIF function to avoid a division by zero
error). By querying this DMV repeatedly over time (as in the following example, code i le
Ch7Symptoms.sql , further illustrated in Figure 7-4), a picture can be built up of the frequency of
latch waits, along with the amount of time the system must wait for these latches. This picture
allows a database administrator to understand what kind of behavior is being exhibited — whether
it matches Figure 7-1 or Figure 7-2.