sys.dm_os_latch_stats (Transact-SQL)

Returns information about all latch waits organized by class.

Column name	Data type	Description
latch_class	nvarchar(120)	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. [!UWAGA] 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.

Permissions

Requires VIEW SERVER STATE permission on the server.

Uwagi

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.

[!UWAGA]

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

[!UWAGA]

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 freespace 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 and DROP 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	Internal use only.
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.

Zobacz także

Odwołanie

DBCC SQLPERF (Transact-SQL)

Dynamic Management Views and Functions (Transact-SQL)

SQL Server Operating System Related Dynamic Management Views (Transact-SQL)