В этом посте представлены все классы Latches, начиная с SQL Server 2005, дана детальная информация о каждом из них. Эту библиотеку создал Paul Randal, она является единственной в своём роде.
Для того, чтобы понять какие виды ожиданий преобладают на вашем сервере, вам достаточно выполнить следующий запрос (будьте внимательны, информация представлена за весь период с прошлой перезагрузки экземпляра):
WITH Latches AS (SELECT latch_class, wait_time_ms / 1000.0 AS WaitS, waiting_requests_count AS WaitCount, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum FROM sys.dm_os_latch_stats WHERE latch_class NOT IN ( 'BUFFER') AND wait_time_ms > 0 ) SELECT W1.latch_class AS LatchClass, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(14, 2)) AS Percentage, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S FROM Latches AS W1 INNER JOIN Latches AS W2 ON W2.RowNum <= W1.RowNum WHERE W1.WaitCount > 0 GROUP BY W1.RowNum, W1.latch_class, W1.WaitS, W1.WaitCount, W1.Percentage HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold GO
После определения преобладающих ожиданий, найдите его в списке в этом посту и перейдите по ссылке. Всё описание на английском.
Люди, которые помогают создавать библиотеку: Jonathan Kehayias, Erin Stellato, Tim Radney, Anthony Nocentino, Arun Sirpal
SQL Server Latch Classes
A
ACCESS_METHODS_ACCESSOR_CACHE
ACCESS_METHODS_BULK_ALLOC
ACCESS_METHODS_CACHE_ONLY_HOBT_ALLOC
ACCESS_METHODS_COLUMN_CACHE_SHARING
ACCESS_METHODS_DATASET_PARENT
ACCESS_METHODS_HOBT
ACCESS_METHODS_HOBT_COUNT
ACCESS_METHODS_HOBT_FACTORY
ACCESS_METHODS_HOBT_VIRTUAL_ROOT
ACCESS_METHODS_INDEX_BUILD_ALLOCATION
ACCESS_METHODS_IOAFF_KEY_RANGE_GENERATOR
ACCESS_METHODS_IOAFF_KEY_TARGET_PAGE_CNT
ACCESS_METHODS_IOAFF_QUEUE
ACCESS_METHODS_IOAFF_READAHEAD
ACCESS_METHODS_IOAFF_READAHEAD_QUEUE
ACCESS_METHODS_IOAFF_WAITING_WORKER_QUEUE
ACCESS_METHODS_KEY_RANGE_GENERATOR
ACCESS_METHODS_SCAN_RANGE_GENERATOR
ACCESS_METHODS_STRING_COMPARE
ALLOC_CACHE_MANAGER
ALLOC_CREATE_FREESPACE_CACHE
ALLOC_CREATE_RINGBUF
ALLOC_EXTENT_CACHE
ALLOC_FREESPACE_CACHE
ALLOC_IAM_PAGE_RANGE_CACHE
APPEND_ONLY_STORAGE_FIRST_ALLOC
APPEND_ONLY_STORAGE_INSERT_POINT
APPEND_ONLY_STORAGE_MANAGER
APPEND_ONLY_STORAGE_UNIT_MANAGER
ASYNC_TRANSPORT_MULTI_SESSION
B
BACKUP_FILE_HANDLE
BACKUP_INSTANCE_ID
BACKUP_LOG_REDO
BACKUP_MANAGER
BACKUP_MANAGER_DIFFERENTIAL
BACKUP_OPERATION
BACKUP_RESULT_SET
BACKUP_TAPE_POOL
BLOB_ACCESS_LEASE_RENEWAL
BUFFER
BUFFER_POOL_GROW
C
CHECK_PRINT_RECORD
CLR_DATAT_ACCESS
CLR_PROCEDURE_HASHTABLE
CLR_UDT_HASHTABLE
CLR_UDX_STORE
CLR_XVAR_PROXY_LIST
COLUMNSTORE_ATTRIB_CACHE
COLUMNSTORE_INDEX_BUILD
COLUMNSTORE_LOAD_DELETE_BUFFERS
COLUMNSTORE_LOAD_METADATA
COLUMNSTORE_OBJECT
COLUMNSTORE_ROWGROUP_COLLECTION
CSIBUILD_HASHDICT_BUCKET
CSIBUILD_HASHDICT_GLOBAL
CSIBUILD_STRINGSTORE_GLOBAL
D
DATABASE_AUTONAME_MANAGER
DATABASE_CHECKPOINT
DATABASE_MIRRORING_CONNECTION
DATABASE_MIRRORING_MANAGER_COMMAND
DATABASE_MIRRORING_REDO
DATABASE_MIRRORING_SERVER
DATABASE_MIRRORING_STREAM
DBCC_CHECK_AGGREGATE
DBCC_CHECK_RESULTSET
DBCC_CHECK_TABLE_INIT
DBCC_CHECK_TRACE_LIST
DBCC_FILE_CHECK_OBJECT
DBCC_FS_FILE_CHECK_STATE
DBCC_HASH_DLL
DBCC_MULTIOBJECT_SCANNER
DBCC_OBJECT_METADATA
DBCC_PERF
DBCC_PFS_STATUS
E
F
FCB
FCB_REPLICA
FGCB_ADD_REMOVE
FGCB_ALLOC
FILE_MANAGER
FILEGROUP_MANAGER
FILESTREAM_ALLOCATION_MANAGER
FILESTREAM_CONTAINER_HEADER
FILESTREAM_DFS_ROOT
FILESTREAM_DOWNLEVEL_LOGGING
FILESTREAM_FCB
FILESTREAM_FILE_MANAGER
FILESTREAM_GHOST_FILES
FT Compidx mg
FULLTEXT_ADMIN
FULLTEXT_AMDIN_COMMAND_CACHE
FULLTEXT_CRAWL_CATALOG
FULLTEXT_CRAWL_DM_LIST
FULLTEXT_CRAWL_LOG
FULLTEXT_DOCUMENT_ID
FULLTEXT_DOCUMENT_ID_NOTIFY
FULLTEXT_DOCUMENT_ID_TRANSACTION
FULLTEXT_FILE_MANAGER
FULLTEXT_LANGUAGE_TABLE
FULLTEXT_LOGS
G
H
HADR_BACKUP_LOCK_RESQUEUE_TABLE
HADR_BACKUP_TASK_TABLE
HADR_XDES_TABLE
HOBT_LOBPAGEINFO
K
L
LOG_MANAGER
LOGBLOCK_GENERATIONS
M
METADATA_CLONE_ACCESS_TICKET
METADATA_SEQUENCE_GENERATOR
MSQL_TRANSACTION_MANAGER
N
NESTING_TRANSACTION_FULL
NESTING_TRANSACTION_READONLY
NESTING_TRANSACTION_READONOY
Q
QUERY_OPTIMIZER_ID_MANAGER
QUERY_OPTIMIZER_VD_MANAGER
QUERY_OPTIMIZER_VIEW_REP
QUERY_STORE_ACCESS_CACHE
R
RECOVERY_BAD_PAGE_TABLE
RECOVERY_MANAGER
S
SECURITY_ACCESS_CHECK_CACHE
SECURITY_ASYMMETRIC_KEY_INIT
SECURITY_CERTIFICATE_INIT
SECURITY_CRYPTO
SECURITY_EXECUTE_AS_ENCRYPTION_TOKEN_CREATE
SECURITY_EXECUTE_AS_ENCRYPTION_TOKEN_INIT
SECURITY_KEY_LIST
SECURITY_KEY_RING
SECURITY_OBJPERM_CACHE
SECURITY_OPERATION_RULE_TABLE
SECURITY_PRIMARY_LOOKUP
SECURITY_ROW_LEVEL_SECURITY_CACHE
SECURITY_USER_INSTANCE
SECURITY_XPCMDSHELL
SERVICE_BROKER_DISPATCHER
SERVICE_BROKER_FORWARDING_MGR
SERVICE_BROKER_MAP_MANAGER
SERVICE_BROKER_MIRROR_ROUTE
SERVICE_BROKER_READ_CACHE
SERVICE_BROKER_TRANSMISSION_INIT
SERVICE_BROKER_TRANSMISSION_STATE
SERVICE_BROKER_TRANSMISSION_TRANSACTION_DATA
SERVICE_BROKER_TRANSMISSION_UPDATE
SERVICE_BROKER_TRANSMISSION_WORK
SERVICE_BROKER_TRANSMISSION_WORKTABLE
SERVICE_BROKER_TRANSMITTER
SERVICE_BROKER_TRANSMITTER_ASB
SERVICE_BROKER_TRANSMITTER_EVENTS
SERVICE_BROKER_TRANSPORT
SERVICE_BROKER_TRANSPORT_MEMORY_NOTIF
SERVICE_BROKER_TRANSPORT_TIMER_EVENTS
SERVICE_BROKER_WAITFOR_MANAGER
SERVICE_BROKER_WAITFOR_TRANSACTION_DATA
SPACE_CACHES_MANAGER
SPACEMGR_ALLOCACHE_CREATE_RINGBUF
SPACEMGR_ALLOCEXTENT_CACHE
SPACEMGR_CREATE_FREESPACE_CACHE
SPACEMGR_CREATE_RINGBUF
SPACEMGR_FREESPACE_CACHE
SPACEMGR_IAM_PAGE_RANGE_CACHE
SYNC_DBTABLE
T
TRACE
TRACE_AUDIT_ID
TRACE_CONTROLLER
TRACE_EVENT_QUEUE
TRACE_ID
TRANSACTION_DISTRIBUTED_MARK
TRANSACTION_OUTCOME
TRANSACTION_WORKSPACE_MANAGER
U
UTILITY_DATABASE_DROP
UTILITY_DEBUG_FILESTREAM
UTILITY_DYNAMIC_MANAGER_VIEW
UTILITY_DYNAMIC_VECTOR
UTILITY_LOCK_INFORMATION
UTILITY_SPARSE_BITMAP
V
VERSIONING_STATE
VERSIONING_STATE_CHANGE
VERSIONING_TRANSACTION
VERSIONING_TRANSACTION_CHAIN
VERSIONING_TRANSACTION_LIST
X
XFCB_CREDENTIAL
XFCB_CREDENTIAL_MANAGER
XFCB_LATCH_RENEWAL
XSTORE_CREDENTIAL
XSTORE_CREDENTIAL_MANAGER
