SQL Server Latch and Spinlock Extended Events

By: Christian Bolton, Justin Langford, Glenn Berry, Gavin Payne, Rob Farley


This article simply explains which extended events are available for latches and spinlocks.

If you open the New Session Wizard for Extended Events from SQL Server 2012 Management Studio, you will reach a screen from which you select the events you wish to capture. After reaching this, first scroll the Event library section to reveal the Channel drop-down box. Then, as shown in the figure below, check Debug, which is unchecked by default.

 

C 07f 021

 

Now you can search for spinlock and latch to find a list of extended events related to these areas, as described in Table 1.

 

Table 1. Spinlock and Latch Extended Events

Extended Event Description
spinlock_backoff Spinlock backoff
spinlock_backoff_warning Occurs when a spinlock backoff 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.

Leave a Reply