sys.dm_os_latch_stats (Transact-SQL)

sys.dm_os_latch_stats (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns information about all latch waits organized by class.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_latch_stats.

Column nameData typeDescription
latch_classnvarchar(120)Name of the latch class.
waiting_requests_countbigintNumber of waits on latches in this class. This counter is incremented at the start of a latch wait.
wait_time_msbigintTotal 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_msbigintMaximum time a memory object has waited on this latch. If this value is unusually high, it might indicate an internal deadlock.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

On SQL Server requires VIEW SERVER STATE permission on the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

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.

System_CAPS_ICON_note.jpg 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.

A latch is a lightweight synchronization object that is used by various SQL Server components. A latch is primarily used to synchronize database pages. 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.

System_CAPS_ICON_note.jpg 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 classDescription
ALLOC_CREATE_RINGBUFUsed internally by SQL Server to initialize the synchronization of the creation of an allocation ring buffer.
ALLOC_CREATE_FREESPACE_CACHEUsed to initialize the synchronization of internal freespace caches for heaps.
ALLOC_CACHE_MANAGERUsed to synchronize internal coherency tests.
ALLOC_FREESPACE_CACHEUsed 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_CACHEUsed 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_PARENTUsed to synchronize child dataset access to the parent dataset during parallel operations.
ACCESS_METHODS_HOBT_FACTORYUsed to synchronize access to an internal hash table.
ACCESS_METHODS_HOBTUsed to synchronize access to the in-memory representation of a HoBt.
ACCESS_METHODS_HOBT_COUNTUsed to synchronize access to a HoBt page and row counters.
ACCESS_METHODS_HOBT_VIRTUAL_ROOTUsed to synchronize access to the root page abstraction of an internal B-tree.
ACCESS_METHODS_CACHE_ONLY_HOBT_ALLOCUsed to synchronize worktable access.
ACCESS_METHODS_BULK_ALLOCUsed to synchronize access within bulk allocators.
ACCESS_METHODS_SCAN_RANGE_GENERATORUsed to synchronize access to a range generator during parallel scans.
ACCESS_METHODS_KEY_RANGE_GENERATORUsed to synchronize access to read-ahead operations during key range parallel scans.
APPEND_ONLY_STORAGE_INSERT_POINTUsed to synchronize inserts in fast append-only storage units.
APPEND_ONLY_STORAGE_FIRST_ALLOCUsed to synchronize the first allocation for an append-only storage unit.
APPEND_ONLY_STORAGE_UNIT_MANAGERUsed for internal data structure access synchronization within the fast append-only storage unit manager.
APPEND_ONLY_STORAGE_MANAGERUsed to synchronize shrink operations in the fast append-only storage unit manager.
BACKUP_RESULT_SETUsed to synchronize parallel backup result sets.
BACKUP_TAPE_POOLUsed to synchronize backup tape pools.
BACKUP_LOG_REDOUsed to synchronize backup log redo operations.
BACKUP_INSTANCE_IDUsed to synchronize the generation of instance IDs for backup performance monitor counters.
BACKUP_MANAGERUsed to synchronize the internal backup manager.
BACKUP_MANAGER_DIFFERENTIALUsed to synchronize differential backup operations with DBCC.
BACKUP_OPERATIONUsed for internal data structure synchronization within a backup operation, such as database, log, or file backup.
BACKUP_FILE_HANDLEUsed to synchronize file open operations during a restore operation.
BUFFERUsed 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_GROWUsed for internal buffer manager synchronization during buffer pool grow operations.
DATABASE_CHECKPOINTUsed to serialize checkpoints within a database.
CLR_PROCEDURE_HASHTABLEInternal use only.
CLR_UDX_STOREInternal use only.
CLR_DATAT_ACCESSInternal use only.
CLR_XVAR_PROXY_LISTInternal use only.
DBCC_CHECK_AGGREGATEInternal use only.
DBCC_CHECK_RESULTSETInternal use only.
DBCC_CHECK_TABLEInternal use only.
DBCC_CHECK_TABLE_INITInternal use only.
DBCC_CHECK_TRACE_LISTInternal use only.
DBCC_FILE_CHECK_OBJECTInternal use only.
DBCC_PERFUsed to synchronize internal performance monitor counters.
DBCC_PFS_STATUSInternal use only.
DBCC_OBJECT_METADATAInternal use only.
DBCC_HASH_DLLInternal use only.
EVENTING_CACHEInternal use only.
FCBUsed to synchronize access to the file control block.
FCB_REPLICAInternal use only.
FGCB_ALLOCUse to synchronize access to round robin allocation information within a filegroup.
FGCB_ADD_REMOVEUse to synchronize access to filegroups for ADD and DROP file operations.
FILEGROUP_MANAGERInternal use only.
FILE_MANAGERInternal use only.
FILESTREAM_FCBInternal use only.
FILESTREAM_FILE_MANAGERInternal use only.
FILESTREAM_GHOST_FILESInternal use only.
FILESTREAM_DFS_ROOTInternal use only.
LOG_MANAGERInternal use only.
FULLTEXT_DOCUMENT_IDInternal use only.
FULLTEXT_DOCUMENT_ID_TRANSACTIONInternal use only.
FULLTEXT_DOCUMENT_ID_NOTIFYInternal use only.
FULLTEXT_LOGSInternal use only.
FULLTEXT_CRAWL_LOGInternal use only.
FULLTEXT_ADMINInternal use only.
FULLTEXT_AMDIN_COMMAND_CACHEInternal use only.
FULLTEXT_LANGUAGE_TABLEInternal use only.
FULLTEXT_CRAWL_DM_LISTInternal use only.
FULLTEXT_CRAWL_CATALOGInternal use only.
FULLTEXT_FILE_MANAGERInternal use only.
DATABASE_MIRRORING_REDOInternal use only.
DATABASE_MIRRORING_SERVERInternal use only.
DATABASE_MIRRORING_CONNECTIONInternal use only.
DATABASE_MIRRORING_STREAMInternal use only.
QUERY_OPTIMIZER_VD_MANAGERInternal use only.
QUERY_OPTIMIZER_ID_MANAGERInternal use only.
QUERY_OPTIMIZER_VIEW_REPInternal use only.
RECOVERY_BAD_PAGE_TABLEInternal use only.
RECOVERY_MANAGERInternal use only.
SECURITY_OPERATION_RULE_TABLEInternal use only.
SECURITY_OBJPERM_CACHEInternal use only.
SECURITY_CRYPTOInternal use only.
SECURITY_KEY_RINGInternal use only.
SECURITY_KEY_LISTInternal use only.
SERVICE_BROKER_CONNECTION_RECEIVEInternal use only.
SERVICE_BROKER_TRANSMISSIONInternal use only.
SERVICE_BROKER_TRANSMISSION_UPDATEInternal use only.
SERVICE_BROKER_TRANSMISSION_STATEInternal use only.
SERVICE_BROKER_TRANSMISSION_ERRORSInternal use only.
SSBXmitWorkInternal use only.
SERVICE_BROKER_MESSAGE_TRANSMISSIONInternal use only.
SERVICE_BROKER_MAP_MANAGERInternal use only.
SERVICE_BROKER_HOST_NAMEInternal use only.
SERVICE_BROKER_READ_CACHEInternal use only.
SERVICE_BROKER_WAITFOR_MANAGERInternal use only.
SERVICE_BROKER_WAITFOR_TRANSACTION_DATAInternal use only.
SERVICE_BROKER_TRANSMISSION_TRANSACTION_DATAInternal use only.
SERVICE_BROKER_TRANSPORTInternal use only.
SERVICE_BROKER_MIRROR_ROUTEInternal use only.
TRACE_IDInternal use only.
TRACE_AUDIT_IDInternal use only.
TRACEInternal use only.
TRACE_CONTROLLERInternal use only.
TRACE_EVENT_QUEUEInternal use only.
TRANSACTION_DISTRIBUTED_MARKInternal use only.
TRANSACTION_OUTCOMEInternal use only.
NESTING_TRANSACTION_READONLYInternal use only.
NESTING_TRANSACTION_FULLInternal use only.
MSQL_TRANSACTION_MANAGERInternal use only.
DATABASE_AUTONAME_MANAGERInternal use only.
UTILITY_DYNAMIC_VECTORInternal use only.
UTILITY_SPARSE_BITMAPInternal use only.
UTILITY_DATABASE_DROPInternal use only.
UTILITY_DYNAMIC_MANAGER_VIEWInternal use only.
UTILITY_DEBUG_FILESTREAMInternal use only.
UTILITY_LOCK_INFORMATIONInternal use only.
VERSIONING_TRANSACTIONInternal use only.
VERSIONING_TRANSACTION_LISTInternal use only.
VERSIONING_TRANSACTION_CHAINInternal use only.
VERSIONING_STATEInternal use only.
VERSIONING_STATE_CHANGEInternal use only.
KTM_VIRTUAL_CLOCKInternal use only.

DBCC SQLPERF (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft