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

Now you can search for spinlock and latch to i nd a list of extended events related to these areas, as
described in Table 7-3.

TABLE 7-3: Spinlock and Latch Extended Events

EXTENDED EVENT DESCRIPTION

spinlock_backoff

Spinlock backof

spinlock_backoff_warning Occurs when a spinlock backof warning is sent to the Error Log

latch_acquire_time Time taken to acquire a latch

latch_demoted Occurs when a SuperLatch is demoted to an ordinary latch

latch_promoted Occurs when a latch is promoted to a SuperLatch

latch_suspend_begin Occurs when the executing task must suspend while waiting

for a latch to become available in the requested mode

latch_suspend_end Occurs when the executing task is resumed after waiting for a

latch

latch_suspend_warning Occurs when there is a timeout waiting for a latch possibly

causing performance problems

You should now be able to create an XE session collecting these events. Bear in mind that you would
typically expect to see many more latch_acquire_time events occurring than the other event
types, and you might not want to bother collecting them.

LATCH CONTENTION EXAMPLES

All of the code in this section uses the Ch7Examples.sql code i le.

Earlier in the chapter, you saw a contrived example demonstrating why a latch is necessary. This section
looks at a couple of examples demonstrating contention issues involving latches and spinlocks. Some
of these examples are borrowed from various presentations involving the SQLCAT team at SQLBits
events in the U.K., and we are indebted to Thomas Kejser in particular for his work on these.

Inserts When the Clustered Index Key Is an Identity Field
A lot of advice suggests using an identity i eld for the clustered index on a table. Certainly there are
benei ts to doing this. An identity i eld is typically an int or bigint type, making it relatively small
compared to some other candidates for primary keys, in particular uniqueidentifier i elds, which
can cause frequent page splits, as well as being overly large, especially because clustered index keys
appear in nonclustered indexes as well.

However, for tables that use identity i elds for clustered index keys, when the number of inserts
scales up, the i nal page will become “hot,” and contention could occur.