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

FIGURE 7-5
The sys.dm_os_spinlock_stats DMV can also be cleared using DBCC SQLPERF .
Contention Indicators
Locks are used to maintain the ACID properties of a transaction, but latches are used to provide
consistency over a memory operation. A spinlock should be considered as similar, in that a resource
is needed for a particular operation, but is not available.
Therefore, when you see higher than normal wait stats associated with latches and spinlocks, you
may need to investigate further.
However, only seeing higher than normal wait stats does not in and of itself indicate a contention
problem. It is perfectly reasonable to expect that if your system is busier now than when you took
your benchmark, your wait stats would be higher. You should look for the proportion of wait stats
compared to others, and compare this to the throughput being achieved. You might also want to
consider how many transactions per second you're managing to achieve as you increase the load,
and the effect of extra processors.
One strong indicator of latch contention can be seen when the proportion of latch-related wait stats
increases unreasonably as your throughput increases, as shown in Figure 7-2.
For spinlock contention, a strong indicator is the number of spins per collision increasing,
typically combined with increased CPU. Spinning is an activity that requires CPU effort, so if
spinning increases disproportionally, CPU is likely to do the same. CPU may go up simply because
of a busier system, but if the transactions per second counters are leveling off while CPU is
increasing, then this would suggest the CPU is being used for something else — such as spinning.
This is the behavior shown by the graph in Figure 7-3.
SUSCEPTIBLE SYSTEMS
In SQL Server, you lock something to use it. A latch is similarly applied to a piece of memory when
it is used. In the physical world, suppose you want to sit in a chair. If it isn't occupied, then you have
no problem. At home, even if you live with family, the chances of i nding the chair unoccupied are