Measure Internal Latch Contention with sys.dm_os_latch_stats

calendar Oct 21, 2026 / · 8 min read · sql server sys.dm_os_latch_stats sys.dm_os_wait_stats sys.dm_os_sys_info sys.dm_pdw_nodes_os_latch_stats latch contention latches wait statistics performance tuning dmv  ·
Share on: linkedin copy

When LATCH_* waits show up near the top of sys.dm_os_wait_stats, you know threads are waiting on non-buffer latches. You don't know which engine structure they're waiting for. sys.dm_os_latch_stats splits those waits by latch class. The script below ranks the non-buffer classes that make up the first 95% of latch wait time and returns the seconds waited, the wait count, the percentage and the average wait for each class.

Purpose and Overview

The job is to turn a generic latch wait into a named structure, such as a filegroup, the log manager or the root of a B-tree. Non-buffer latch waits appear in wait stats as a LATCH_* wait type, and once they appear, sys.dm_os_latch_stats gives the cumulative breakdown by class. A latch wait starts when a request cannot be granted immediately because another thread holds the latch in a conflicting mode.

The script leaves out one class. All buffer latch waits fall under BUFFER, and that class covers every use of page latches. Those waits are already reported in wait stats as PAGELATCH_* and PAGEIOLATCH_*. With BUFFER filtered out, the result covers non-buffer latches only and says nothing about page latch contention. The counters are cumulative since the last reset or startup and are lost on restart.

The view applies to SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics and SQL database in Microsoft Fabric. It is documented back to SQL Server 2005, and every column the script reads exists there. On SQL Server and Managed Instance, reading it requires VIEW SERVER STATE. On SQL Server 2022 and later, it requires VIEW SERVER PERFORMANCE STATE. On Azure SQL Database Basic, S0, S1 and elastic pools, it requires the server admin account, the Microsoft Entra admin account or membership in ##MS_ServerStateReader##.

Code Breakdown

The script reads the view once into a CTE, joins the CTE to itself to build a running total and stops at 95% of non-buffer latch wait time.

 1WITH [Latches] AS
 2    (SELECT
 3        [latch_class],
 4        [wait_time_ms] / 1000.0 AS [WaitS],
 5        [waiting_requests_count] AS [WaitCount],
 6        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
 7        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
 8    FROM sys.dm_os_latch_stats
 9    WHERE [latch_class] NOT IN (
10        N'BUFFER')
11    AND [wait_time_ms] > 0
12)
13SELECT
14    MAX ([W1].[latch_class]) AS [LatchClass], 
15    CAST (MAX ([W1].[WaitS]) AS DECIMAL(14, 2)) AS [Wait_S],
16    MAX ([W1].[WaitCount]) AS [WaitCount],
17    CAST (MAX ([W1].[Percentage]) AS DECIMAL(14, 2)) AS [Percentage],
18    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (14, 4)) AS [AvgWait_S]
19FROM [Latches] AS [W1]
20INNER JOIN [Latches] AS [W2]
21    ON [W2].[RowNum] <= [W1].[RowNum]
22GROUP BY [W1].[RowNum]
23HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
24GO

The Latches CTE

The WHERE clause removes BUFFER and every class with zero wait time. Dropping the idle classes keeps them out of the result, and it means the average calculation later in the script can't divide by zero.

WaitS divides by 1000.0 rather than 1000. The decimal literal converts milliseconds to seconds without integer division cutting off the fraction.

SUM ([wait_time_ms]) OVER() has an empty OVER clause, so the total is taken across every row that passed the WHERE filter. Percentage is therefore each class's share of non-buffer latch wait time, not its share of all wait time on the instance. A class at 80% here can still be a minor problem if LATCH_* waits are a small part of total waits.

Ranking with ROW_NUMBER

ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) numbers the classes from most to least wait time. The running total in the outer query depends on this number.

wait_time_ms is updated every five minutes during a latch wait and again when the wait ends. A long wait that is still running can be missing up to five minutes of time, so a class with one stuck thread ranks lower than it will once that wait finishes.

The triangular self-join and the 95% cut-off

[W2].[RowNum] <= [W1].[RowNum] joins each class to itself and to every class ranked above it. Row 3 joins to rows 1, 2 and 3. Grouped by [W1].[RowNum], SUM ([W2].[Percentage]) is the running total up to and including that class.

The HAVING clause subtracts the class's own percentage, which leaves the running total of the classes above it. A class is kept while that earlier total is under 95. The class that crosses 95% is therefore included, and every class below it is cut. To see more of the tail, raise the 95 on the commented line.

Each W1 group holds one row, so the MAX() calls around the W1 columns return that row's values. They are there only so the columns can appear in a grouped query.

AvgWait_S and the casts

AvgWait_S divides total seconds by the number of waits. waiting_requests_count is incremented at the start of a latch wait, so any class with recorded wait time has a count of at least one. Rows with zero wait time are filtered out, so the division is safe.

The same timing rule skews the average during a long wait. The count goes up when the wait starts, but its time is added only in five-minute steps. A class with waits in progress shows a lower average than it will later.

Wait_S and Percentage are cast to DECIMAL(14, 2). AvgWait_S uses four decimal places, so it shows down to a tenth of a millisecond. Smaller averages show as 0.0000.

Reading the output

What the common classes mean:

The script doesn't return max_wait_time_ms. If that value is unusually high, it might indicate an internal deadlock, so query it separately when one class looks stuck.

Key Benefits and Use Cases

  • Read-only on a live instance — the script only selects from the view and resets nothing, so other tools that read the same counters see no change.
  • Checks a partitioning fix — partitioning a heap with ALLOC_FREESPACE_CACHE contention gives each partition its own latch. On a non-production instance, clear the counters with DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR) and re-run the workload, and the ranking shows whether the class has dropped.
  • Same query on Azure — the view keeps its name in Azure SQL Database and Azure SQL Managed Instance, so one script covers on-premises and cloud instances.
  • Gives support a ranked list — some latch contention requires a case with Microsoft Customer Support Services, and a short list of ranked classes is a clear place to start one.

Performance Considerations

Practical Tips

Conclusion

This script is the step after wait stats. Once LATCH_* waits take a real share of wait time, it names the classes responsible and how much each contributes. After it come the fixes for each class (file sizing, log truncation, partitioning, queue-table design) and, for the hardest non-buffer cases, memory dumps analyzed with Windows debugging tools.

References

Posts in this series