sys.dm_os_latch_stats (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Returns information about all latch waits organized by class.
Note
To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_os_latch_stats. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
| Column name | Data type | Description |
|---|---|---|
| latch_class | nvarchar(60) | Name of the latch class. |
| waiting_requests_count | bigint | Number of waits on latches in this class. This counter is incremented at the start of a latch wait. |
| wait_time_ms | bigint | Total wait time, in milliseconds, on latches in this class. Note: This column is updated every five minutes during a latch wait and at the end of a latch wait. |
| max_wait_time_ms | bigint | Maximum time a memory object has waited on this latch. If this value is unusually high, it might indicate an internal deadlock. |
| pdw_node_id | int | Applies to: Azure Synapse Analytics, Analytics Platform System (PDW) The identifier for the node that this distribution is on. |
Permissions
On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
Remarks
sys.dm_os_latch_stats can be used to identify the source of latch contention by examining the relative wait numbers and wait times for the different latch classes. In some situations, you may be able to resolve or reduce latch contention. However, there might be situations that will require that you to contact Microsoft Customer Support Services.
You can reset the contents of sys.dm_os_latch_stats by using DBCC SQLPERF as follows:
DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);
GO
This resets all counters to 0.
Note
These statistics are not persisted if SQL Server is restarted. All data is cumulative since the last time the statistics were reset, or since SQL Server was started.
Latches
A latch is an internal lightweight synchronization object similar to a lock, that is used by various SQL Server components. A latch is primarily used to synchronize database pages during operations such as buffer or file access. Each latch is associated with a single allocation unit.
A latch wait occurs when a latch request cannot be granted immediately, because the latch is held by another thread in a conflicting mode. Unlike locks, a latch is released immediately after the operation, even in write operations.
Latches are grouped into classes based on components and usage. Zero or more latches of a particular class can exist at any point in time in an instance of SQL Server.
Note
sys.dm_os_latch_stats does not track latch requests that were granted immediately, or that failed without waiting.
The following table contains brief descriptions of the various latch classes.
| Latch class | Description |
|---|---|
| ALLOC_CREATE_RINGBUF | Used internally by SQL Server to initialize the synchronization of the creation of an allocation ring buffer. |
| ALLOC_CREATE_FREESPACE_CACHE | Used to initialize the synchronization of internal free space caches for heaps. |
| ALLOC_CACHE_MANAGER | Used to synchronize internal coherency tests. |
| ALLOC_FREESPACE_CACHE | Used to synchronize the access to a cache of pages with available space for heaps and binary large objects (BLOBs). Contention on latches of this class can occur when multiple connections try to insert rows into a heap or BLOB at the same time. You can reduce this contention by partitioning the object. Each partition has its own latch. Partitioning will distribute the inserts across multiple latches. |
| ALLOC_EXTENT_CACHE | Used to synchronize the access to a cache of extents that contains pages that are not allocated. Contention on latches of this class can occur when multiple connections try to allocate data pages in the same allocation unit at the same time. This contention can be reduced by partitioning the object of which this allocation unit is a part. |
| ACCESS_METHODS_DATASET_PARENT | Used to synchronize child dataset access to the parent dataset during parallel operations. |
| ACCESS_METHODS_HOBT_FACTORY | Used to synchronize access to an internal hash table. |
| ACCESS_METHODS_HOBT | Used to synchronize access to the in-memory representation of a HoBt. |
| ACCESS_METHODS_HOBT_COUNT | Used to synchronize access to a HoBt page and row counters. |
| ACCESS_METHODS_HOBT_VIRTUAL_ROOT | Used to synchronize access to the root page abstraction of an internal B-tree. |
| ACCESS_METHODS_CACHE_ONLY_HOBT_ALLOC | Used to synchronize worktable access. |
| ACCESS_METHODS_BULK_ALLOC | Used to synchronize access within bulk allocators. |
| ACCESS_METHODS_SCAN_RANGE_GENERATOR | Used to synchronize access to a range generator during parallel scans. |
| ACCESS_METHODS_KEY_RANGE_GENERATOR | Used to synchronize access to read-ahead operations during key range parallel scans. |
| APPEND_ONLY_STORAGE_INSERT_POINT | Used to synchronize inserts in fast append-only storage units. |
| APPEND_ONLY_STORAGE_FIRST_ALLOC | Used to synchronize the first allocation for an append-only storage unit. |
| APPEND_ONLY_STORAGE_UNIT_MANAGER | Used for internal data structure access synchronization within the fast append-only storage unit manager. |
| APPEND_ONLY_STORAGE_MANAGER | Used to synchronize shrink operations in the fast append-only storage unit manager. |
| BACKUP_RESULT_SET | Used to synchronize parallel backup result sets. |
| BACKUP_TAPE_POOL | Used to synchronize backup tape pools. |
| BACKUP_LOG_REDO | Used to synchronize backup log redo operations. |
| BACKUP_INSTANCE_ID | Used to synchronize the generation of instance IDs for backup performance monitor counters. |
| BACKUP_MANAGER | Used to synchronize the internal backup manager. |
| BACKUP_MANAGER_DIFFERENTIAL | Used to synchronize differential backup operations with DBCC. |
| BACKUP_OPERATION | Used for internal data structure synchronization within a backup operation, such as database, log, or file backup. |
| BACKUP_FILE_HANDLE | Used to synchronize file open operations during a restore operation. |
| BUFFER | Used to synchronize short term access to database pages. A buffer latch is required before reading or modifying any database page. Buffer latch contention can indicate several issues, including hot pages and slow I/Os. This latch class covers all possible uses of page latches. sys.dm_os_wait_stats makes a difference between page latch waits that are caused by I/O operations and read and write operations on the page. |
| BUFFER_POOL_GROW | Used for internal buffer manager synchronization during buffer pool grow operations. |
| DATABASE_CHECKPOINT | Used to serialize checkpoints within a database. |
| CLR_PROCEDURE_HASHTABLE | Internal use only. |
| CLR_UDX_STORE | Internal use only. |
| CLR_DATAT_ACCESS | Internal use only. |
| CLR_XVAR_PROXY_LIST | Internal use only. |
| DBCC_CHECK_AGGREGATE | Internal use only. |
| DBCC_CHECK_RESULTSET | Internal use only. |
| DBCC_CHECK_TABLE | Internal use only. |
| DBCC_CHECK_TABLE_INIT | Internal use only. |
| DBCC_CHECK_TRACE_LIST | Internal use only. |
| DBCC_FILE_CHECK_OBJECT | Internal use only. |
| DBCC_PERF | Used to synchronize internal performance monitor counters. |
| DBCC_PFS_STATUS | Internal use only. |
| DBCC_OBJECT_METADATA | Internal use only. |
| DBCC_HASH_DLL | Internal use only. |
| EVENTING_CACHE | Internal use only. |
| FCB | Used to synchronize access to the file control block. |
| FCB_REPLICA | Internal use only. |
| FGCB_ALLOC | Use to synchronize access to round robin allocation information within a filegroup. |
| FGCB_ADD_REMOVE | Use to synchronize access to filegroups for add, drop, grow, and shrink file operations. |
| FILEGROUP_MANAGER | Internal use only. |
| FILE_MANAGER | Internal use only. |
| FILESTREAM_FCB | Internal use only. |
| FILESTREAM_FILE_MANAGER | Internal use only. |
| FILESTREAM_GHOST_FILES | Internal use only. |
| FILESTREAM_DFS_ROOT | Internal use only. |
| LOG_MANAGER | Internal use only. |
| FULLTEXT_DOCUMENT_ID | Internal use only. |
| FULLTEXT_DOCUMENT_ID_TRANSACTION | Internal use only. |
| FULLTEXT_DOCUMENT_ID_NOTIFY | Internal use only. |
| FULLTEXT_LOGS | Internal use only. |
| FULLTEXT_CRAWL_LOG | Internal use only. |
| FULLTEXT_ADMIN | Internal use only. |
| FULLTEXT_AMDIN_COMMAND_CACHE | Internal use only. |
| FULLTEXT_LANGUAGE_TABLE | Internal use only. |
| FULLTEXT_CRAWL_DM_LIST | Internal use only. |
| FULLTEXT_CRAWL_CATALOG | Internal use only. |
| FULLTEXT_FILE_MANAGER | Internal use only. |
| DATABASE_MIRRORING_REDO | Internal use only. |
| DATABASE_MIRRORING_SERVER | Internal use only. |
| DATABASE_MIRRORING_CONNECTION | Internal use only. |
| DATABASE_MIRRORING_STREAM | Internal use only. |
| QUERY_OPTIMIZER_VD_MANAGER | Internal use only. |
| QUERY_OPTIMIZER_ID_MANAGER | Internal use only. |
| QUERY_OPTIMIZER_VIEW_REP | Internal use only. |
| RECOVERY_BAD_PAGE_TABLE | Internal use only. |
| RECOVERY_MANAGER | Internal use only. |
| SECURITY_OPERATION_RULE_TABLE | Internal use only. |
| SECURITY_OBJPERM_CACHE | Internal use only. |
| SECURITY_CRYPTO | Internal use only. |
| SECURITY_KEY_RING | Internal use only. |
| SECURITY_KEY_LIST | Internal use only. |
| SERVICE_BROKER_CONNECTION_RECEIVE | Internal use only. |
| SERVICE_BROKER_TRANSMISSION | Internal use only. |
| SERVICE_BROKER_TRANSMISSION_UPDATE | Internal use only. |
| SERVICE_BROKER_TRANSMISSION_STATE | Internal use only. |
| SERVICE_BROKER_TRANSMISSION_ERRORS | Internal use only. |
| SSBXmitWork | Internal use only. |
| SERVICE_BROKER_MESSAGE_TRANSMISSION | Internal use only. |
| SERVICE_BROKER_MAP_MANAGER | Internal use only. |
| SERVICE_BROKER_HOST_NAME | Internal use only. |
| SERVICE_BROKER_READ_CACHE | Internal use only. |
| SERVICE_BROKER_WAITFOR_MANAGER | Used to synchronize an instance level map of wait queues. One queue exists per database ID, Database Version, and Queue ID tuple. Contention on latches of this class can occur when many connections are: In a WAITFOR(RECEIVE) wait state; calling WAITFOR(RECEIVE); exceeding the WAITFOR timeout; receiving a message; committing or rolling back the transaction that contains the WAITFOR(RECEIVE); You can reduce the contention by reducing the number of threads in a WAITFOR(RECEIVE) wait state. |
| SERVICE_BROKER_WAITFOR_TRANSACTION_DATA | Internal use only. |
| SERVICE_BROKER_TRANSMISSION_TRANSACTION_DATA | Internal use only. |
| SERVICE_BROKER_TRANSPORT | Internal use only. |
| SERVICE_BROKER_MIRROR_ROUTE | Internal use only. |
| TRACE_ID | Internal use only. |
| TRACE_AUDIT_ID | Internal use only. |
| TRACE | Internal use only. |
| TRACE_CONTROLLER | Internal use only. |
| TRACE_EVENT_QUEUE | Internal use only. |
| TRANSACTION_DISTRIBUTED_MARK | Internal use only. |
| TRANSACTION_OUTCOME | Internal use only. |
| NESTING_TRANSACTION_READONLY | Internal use only. |
| NESTING_TRANSACTION_FULL | Internal use only. |
| MSQL_TRANSACTION_MANAGER | Internal use only. |
| DATABASE_AUTONAME_MANAGER | Internal use only. |
| UTILITY_DYNAMIC_VECTOR | Internal use only. |
| UTILITY_SPARSE_BITMAP | Internal use only. |
| UTILITY_DATABASE_DROP | Internal use only. |
| UTILITY_DYNAMIC_MANAGER_VIEW | Internal use only. |
| UTILITY_DEBUG_FILESTREAM | Internal use only. |
| UTILITY_LOCK_INFORMATION | Internal use only. |
| VERSIONING_TRANSACTION | Internal use only. |
| VERSIONING_TRANSACTION_LIST | Internal use only. |
| VERSIONING_TRANSACTION_CHAIN | Internal use only. |
| VERSIONING_STATE | Internal use only. |
| VERSIONING_STATE_CHANGE | Internal use only. |
| KTM_VIRTUAL_CLOCK | Internal use only. |
Note
Documentation uses the term B-tree generally in reference to indexes. In rowstore indexes, the Database Engine implements a B+ tree. This does not apply to columnstore indexes or indexes on memory-optimized tables. For more information, see the SQL Server and Azure SQL index architecture and design guide.
See Also
DBCC SQLPERF (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
SQL Server, Latches Object