Latches and Spinlocks

Overview

The Waits report can tell you that queries are waiting on LATCH_EX or LATCH_SH, but that wait only says “an internal structure”. Which structure it is decides the fix: ACCESS_METHODS_DATASET_PARENT is parallel scans, FGCB_ADD_REMOVE is data file growth, LOG_MANAGER is log growth, and a hot LOCK_HASH or SOS_CACHESTORE spinlock each point somewhere else again.

The Latches and Spinlocks report names the latch class or spinlock and the fix:

  • every latch class with any waiting, with its waits, wait time, average and longest wait,
  • every spinlock with any activity, with its collisions, spins, backoffs and sleep time,
  • two bar charts of the top latch classes by wait time and the top spinlocks by spins,
  • a Notable flag on the latch classes worth acting on,
  • a knowledge pane with what the picked latch class or spinlock protects, what usually fixes it, and buttons that open the report that finds the queries or the setting behind it.

The numbers can be everything since SQL Server started, or only what happened during a sample of 10, 30 or 60 seconds.

Where to find it

Route How
Server tree Right-click the server → Instance Level Reports → Latches and Spinlocks
Instance reports navigator Performance group
Related Links bar From Waits and Parallelism Calibration
Report arrows Previous is Last DBCC CheckDB Known Good by Database, next is License and Edition Footprint

The page title reads Latches and Spinlocks for <server name>.

The toolbar

Control What it does
Since startup Reads both views once and shows everything since SQL Server started. This is what the page opens on
Sample Reads both views, waits for the sample length, reads them again and shows only the difference
10 s / 30 s / 60 s The sample length (30 seconds by default). Your choice is remembered. Changing it redraws nothing; it decides what the next sample does
Latch classes / Spinlocks Which grid is shown below the charts
Cancel Shown while a sample runs. Stops it and keeps what was on the page

While a sample runs the toolbar says how many seconds are left, and the progress bar fills. A sample runs on its own connection, so both reads come from the same session. Moving to another page cancels it.

Since startup or a sample

Both views are cumulative since the instance started, or since somebody cleared them with DBCC SQLPERF.

Since startup is one read. The rate column is per hour of uptime, worked out from sqlserver_start_time in sys.dm_os_sys_info and the server’s own clock, so it is an average over the whole time the instance has been up.

Sample is two reads. The rate column is per second of the sample, measured on the server’s clock. A latch class or spinlock with no activity during the sample is left out. Two things make a sample unusable, and the page says so rather than drawing negative numbers:

  • SQL Server restarted during the sample. The start time changed between the two reads, so the counters began again from zero.
  • A counter went down between the two reads. The statistics were cleared during the sample (DBCC SQLPERF with CLEAR), so no difference across it means anything.

In both cases the charts and grid are empty, the reason is shown, and the toolbar reads The sample could not be used. Take another sample.

Which latch classes are notable

A latch class is marked Notable when either is true over the window being shown:

  • more than 100 ms of waiting per second measured, or
  • more than 10 ms per wait on average.

BUFFER is never flagged and is left out of the chart. It is where every PAGELATCH_* and PAGEIOLATCH_* wait is counted, which the Waits report already splits into pages in memory and pages being read from disk, and at thousands of times the other classes it would flatten every other bar. It is still listed in the grid.

Reading the page

The heading says what the numbers cover: “Since SQL Server started at … (… ago); rates are per hour of uptime”, or “Sampled for 30 seconds, ending HH:mm:ss server time”, followed by how many latch classes are notable.

The charts. The left chart is the top 15 latch classes by wait time (BUFFER left out), in blue, with notable classes in orange. The right chart is the top 15 spinlocks by spins, in green, with a thinner red bar under each for backoffs. Backoffs are drawn on their own scale, the largest backoff count filling the width, because they run far below spins and would not be seen on the same axis. The number beside each bar is the wait time or the spin count. Hover a bar for the waits and average wait of a latch class, or the spins, collisions, backoffs and spins per collision of a spinlock. Click a bar to pick that row in the grid; a spinlock bar switches the grid to Spinlocks, and a latch bar back to Latch classes.

When a chart has nothing to draw it says why, for example “No latch class other than BUFFER has waited since SQL Server started.”, “No spinlock collided during the sample.” or “The spinlock statistics could not be read: …”.

The Latch classes grid lists every latch class with activity, most wait time first:

Column What it shows
Latch Class The class name from sys.dm_os_latch_stats
Area The family it belongs to, such as Parallel queries, File growth or Page allocation
Waits Waits in the window
Wait ms Total wait time in the window
Avg Wait ms Wait ms divided by waits
Wait ms per Hour / Wait ms per Second Per hour of uptime since startup, per second in a sample
Longest Wait ms The longest single wait since startup. The view keeps only a maximum, so this is never a difference, even in a sample
Notable Yes when the class meets the rule above. The name and this column are drawn in orange
What It Means What the latch class protects

The Spinlocks grid lists every spinlock with activity, most spins first:

Column What it shows
Spinlock The name from sys.dm_os_spinlock_stats
Area The family it belongs to, such as Locking, Caches or Scheduling
Collisions Times a thread found the spinlock held
Spins Times a thread spun waiting for it
Spins per Collision Spins divided by collisions
Spins per Hour / Spins per Second Per hour of uptime since startup, per second in a sample
Backoffs Times a thread gave up spinning and slept
Sleep ms Time spent sleeping after backoffs
What It Means What the spinlock protects

Hover a row for its name, what it protects and what usually fixes it. The grids export to CSV and Excel like every other grid.

The knowledge pane

Pick a row, or click a bar, and a pane opens on the right with:

  • the numbers for the window: waits, wait time, average per wait, the rate and the longest wait for a latch class; collisions, spins, spins per collision, backoffs and sleep time for a spinlock,
  • WHAT IT PROTECTS,
  • WHAT USUALLY FIXES IT,
  • Open buttons for the reports the text points at (for example Open Parallelism Calibration for ACCESS_METHODS_DATASET_PARENT, Open File Utilization for FGCB_ADD_REMOVE, Open Optimizer Effort and Open Plan Cache for SOS_CACHESTORE), and
  • Close.

For a notable latch class the pane says why it is notable. For a spinlock it adds that spins on their own are normal: a spinlock matters when its backoffs climb along with its spins and CPU is high without a query to account for it. The text in the pane can be selected and copied.

The report carries its own catalog of latch classes and spinlocks. A name that is not in it still gets text, placed by its prefix (ACCESS_METHODS_, ALLOC_, FGCB_, VERSIONING_, LOCK_, SOS_, LOG, XDES and others) or a plain default, and the pane says “This class is not in the report’s catalog; the text above comes from its name.”

The catalog’s areas:

Area Latch classes and spinlocks in the catalog
Parallel queries ACCESS_METHODS_DATASET_PARENT, ACCESS_METHODS_SCAN_RANGE_GENERATOR, ACCESS_METHODS_KEY_RANGE_GENERATOR, NESTING_TRANSACTION_READONLY, NESTING_TRANSACTION_FULL, X_PACKET_LIST
Index structure ACCESS_METHODS_HOBT_VIRTUAL_ROOT, ACCESS_METHODS_HOBT_COUNT, ACCESS_METHODS_HOBT, ACCESS_METHODS_HOBT_FACTORY, ACCESS_METHODS_CACHE_ONLY_HOBT_ALLOC, ACCESS_METHODS_ACCESSOR_CACHE, HOBT_HASH
Page allocation ACCESS_METHODS_BULK_ALLOC, ALLOC_FREESPACE_CACHE, ALLOC_EXTENT_CACHE, SPACEMGR_ALLOCEXTENT_CACHE, FGCB_ALLOC
File growth FGCB_ADD_REMOVE, FCB, FCB_REPLICA
Transaction log LOG_MANAGER, LOGCACHE_ACCESS, LOGFLUSHQ
Row versioning VERSIONING_TRANSACTION_LIST, VERSIONING_STATE_CHANGE
Columnstore COLUMNSTORE_OBJECT, COLUMNSTORE_COLUMNDATASET_SESSION_LIST
Locking LOCK_HASH
Security LOCK_RW_SECURITY_CACHE, SECURITY_CACHE
Caches SOS_CACHESTORE, SOS_OBJECT_STORE
Scheduling SOS_SCHEDULER, SOS_SUSPEND_QUEUE, SOS_TASK, SOS_RW, RESQUEUE
Memory SOS_BLOCKALLOCPARTIALLIST, SOS_RESOURCE_CLERK_LIST
Transactions XDESMGR, XDES
Other BUFFER (Data pages), TRACE_CONTROLLER (Tracing), SERVICE_BROKER_WAITFOR_MANAGER (Service Broker), METADATA_SEQUENCE_GENERATOR (Sequences), BACKUP_OPERATION and BACKUP (Backup), DBCC_CHECK_AGGREGATE and DBCC_OBJECT_METADATA (DBCC), DATABASE_MIRRORING_CONNECTION (Mirroring), CMED_HASH_SET (Metadata), OPT_IDX_STATS (Optimizer), MUTEX (Internal), DP_LIST (Checkpoint), DBTABLE (Databases)

Spinlocks are not documented one by one, so their text is limited to what each is known to protect and the pattern that makes it hot, and says so where the honest answer is a support case.

Copying

Right-click the grid for Copy the findings as text, which copies the page title, the heading, every notable latch class with its wait time, waits, average and fix, and the top five spinlocks by spins with their backoffs. Right-click the charts, or use the grid’s menu, to copy the charts as a picture.

Permissions and versions

  • sys.dm_os_latch_stats and sys.dm_os_sys_info need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later). Without it the page says “The latch and spinlock statistics could not be read from this instance.” with the permission it needs and what the server said. When the connection check already knows the login lacks it, the menu item is shown muted and says what it needs when clicked.
  • sys.dm_os_spinlock_stats is undocumented, so it is read separately inside TRY/CATCH. If a build does not have it or refuses it, the spinlock half of the page says so and the latch half still works.
  • There is no version gate: both views and sqlserver_start_time exist on every supported version.
  • Each read has a 60 second timeout.
  • Waits for the LATCH_*, PAGELATCH_* and PAGEIOLATCH_* waits this page breaks down.
  • Parallelism Calibration for the parallel scans behind ACCESS_METHODS_DATASET_PARENT.
  • File Utilization for the file sizes and growth settings behind FGCB_ADD_REMOVE.
  • Optimizer Effort for the compile churn behind SOS_CACHESTORE.
  • TempDB Metadata Contention for page latching on tempdb’s system tables.
  • Optimized Locking for the locking behind LOCK_HASH.