Mapping wait types in dm_os_wait_stats to Extended Events

A few months back I received an email from a member of the community that was trying to filter the sqlos.wait_info event for some of the wait types that are filtered out by Glenn’s diagnostic queries, and to their dismay wasn’t able to find the specific wait types in the wait_types map in sys.dm_xe_map_values.  This scenario is something that I have long known about but never actually blogged about, though this blog post has been sitting in draft form since early 2012.  Now that things have started to slow down at the end of this year I took a little time and built a spreadsheet of the wait_type names in sys.dm_os_wait_stats that don’t match exactly to the map_value in sys.dm_xe_map_values.

I’d like to thank Bob Ward at Microsoft for taking the time to double check the spreadsheet I originally built for this blog post for accuracy and for the corrections on a few of the wait types he provided.  The list of wait types below was generated from SQL Server 2012 Service Pack 1 + Cumulative Update 6.

wait_type in sys.dm_os_wait_stats

map_value in sys.dm_xe_map_values

ASYNC_NETWORK_IO NETWORK_IO
BROKER_TASK_STOP SSB_TASK_STOP
CLR_JOIN CLR_TASK_JOIN
CLR_MEMORY_SPY CLR_MEMORY_SPY_ACCESS
CREATE_DATINISERVICE GET_DATINISERVICE
DBCC_SCALE_OUT_EXPR_CACHE CHECK_EXPRESSION_CACHE
DBSTATE DB_STATE
DLL_LOADING_MUTEX DLL_LOAD
ERROR_REPORTING_MANAGER ERROR_REPORTING_MGR
EXECUTION_PIPE_EVENT_INTERNAL TWO_THREAD_PIPE_EVENT
FS_FC_RWLOCK FS_GC_RWLOCK
FT_IFTS_RWLOCK FT_RWLOCK
FT_IFTS_SCHEDULER_IDLE_WAIT FT_SCHEDULER_IDLE_WAIT
FULLTEXT GATHERER FULLTEXT_GATHERER
HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST HADR_ARPROXY_NOTIFICATION_SUBSCRIBER_LIST
HADR_DATABASE_FLOW_CONTROL HADR_PARTNER_FLOW
HADR_DATABASE_VERSIONING_STATE HADR_VERSIONING_STATE
HADR_DATABASE_WAIT_FOR_RESTART __indexMUTEX_HADR_DATABASE_WAIT_FOR_RESTART
HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING HADR_WAIT_FOR_TRANSITION_TO_VERSIONING
HADR_FILESTREAM_BLOCK_FLUSH HADRFS_BLOCK_FLUSH
HADR_FILESTREAM_FILE_CLOSE HADRFS_FILE_CLOSE
HADR_FILESTREAM_FILE_REQUEST HADRFS_FILE_REQUEST
HADR_FILESTREAM_IOMGR HADRFS_IOMGR
HADR_FILESTREAM_IOMGR_IOCOMPLETION HADRFS_IOMGR_IOCOMPLETION
HADR_FILESTREAM_MANAGER HADRFS_MANAGER
HADR_RECOVERY_WAIT_FOR_CONNECTION __indexMUTEX_HADR_RECOVERY_WAIT_FOR_CONNECTION
HADR_RECOVERY_WAIT_FOR_UNDO __indexMUTEX_HADR_RECOVERY_WAIT_FOR_UNDO
HADR_TRANSPORT_FLOW_CONTROL HADR_TRANSPORT_FLOW
HTBUILD HASH_TABLE_BUILD
HTREPARTITION HASH_TABLE_REPARTITION
INTERNAL_TESTING  
LAZYWRITER_SLEEP LZW_SLEEP
MD_AGENT_YIELD METADATA_AGENT_YIELD
MD_LAZYCACHE_RWLOCK METADATA_LAZYCACHE_RWLOCK
MISCELLANEOUS UNKNOWN
MSSEARCH MSSEARCH_COM
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO PREEMPTIVE_FSRECOVER_CONDITIONALUNDO
PREEMPTIVE_OS_SQMLAUNCH PREEMPTIVE_SQMLAUNCH
PWAIT_ALL_COMPONENTS_INITIALIZED ALL_COMPONENTS_INITIALIZED
PWAIT_COOP_SCAN COOP_SCAN
PWAIT_EVENT_SESSION_INIT_MUTEX EVENT_SESSION_INIT_MUTEX
PWAIT_HADR_ACTION_COMPLETED HADR_ACTION_COMPLETED
PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC HADR_ARPROXY_NOTIFICATION_SUBSCRIBER_LIST
PWAIT_HADR_CLUSTER_INTEGRATION HADR_CHANGE_NOTIFIER_TERMINATION_SYNC
PWAIT_HADR_FAILOVER_COMPLETED HADR_CLUSTER_INTEGRATION
PWAIT_HADR_OFFLINE_COMPLETED HADR_FAILOVER_COMPLETED
PWAIT_HADR_ONLINE_COMPLETED HADR_OFFLINE_COMPLETED
PWAIT_HADR_POST_ONLINE_COMPLETED HADR_ONLINE_COMPLETED
PWAIT_HADR_SERVER_READY_CONNECTIONS HADR_SERVER_READY_CONNECTIONS
PWAIT_HADR_WORKITEM_COMPLETED HADR_WORKITEM_COMPLETED
PWAIT_MD_LOGIN_STATS MD_LOGIN_STATS
PWAIT_MD_RELATION_CACHE MD_RELATION_CACHE
PWAIT_MD_SERVER_CACHE MD_SERVER_CACHE
PWAIT_MD_UPGRADE_CONFIG MD_UPGRADE_CONFIG
PWAIT_PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG
PWAIT_QRY_BPMEMORY QRY_BPMEMORY
PWAIT_REPLICA_ONLINE_INIT_MUTEX REPLICA_ONLINE_INIT_MUTEX
PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC
PWAIT_SECURITY_CACHE_INVALIDATION SECURITY_CACHE_INVALIDATION
QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN QUERY_EXEC_INDEXSORT_OPEN
REDO_THREAD_PENDING_WORK REDO_SIGNAL
REDO_THREAD_SYNC REDO_SYNC
RESOURCE_GOVERNOR_IDLE  
SCAN_CHAR_HASH_ARRAY_INITIALIZATION SCAN_CHAR_HASH_ARRAY_INIT
SERVER_IDLE_CHECK SERVER_IDLE_LOCK
SNI_LISTENER_ACCESS LISTENER_UPDATE
SNI_TASK_COMPLETION SNI_WAIT_TASK_FINISH
SP_PREEMPTIVE_SERVER_DIAGNOSTICS_SLEEP PREEMPTIVE_SP_SERVER_DIAGNOSTICS_SLEEP
THREADPOOL SOS_WORKER
TRAN_MARKLATCH_DT TRANMARKLATCH_DT
TRAN_MARKLATCH_EX TRANMARKLATCH_EX
TRAN_MARKLATCH_KP TRANMARKLATCH_KP
TRAN_MARKLATCH_NL TRANMARKLATCH_NL
TRAN_MARKLATCH_SH TRANMARKLATCH_SH
TRAN_MARKLATCH_UP TRANMARKLATCH_UP
VIA_ACCEPT VIA_ACCEPT_DONE
WAIT_XTP_GUEST XTP_GUEST
WAIT_XTP_TASK_SHUTDOWN XTP_TASK_SHUTDOWN
WAIT_XTP_TRAN_COMMIT XTP_TRAN_COMMIT
WAITFOR_TASKSHUTDOWN TASKSHUTDOWN
XE_CALLBACK_LIST XE_CALLBACK
XTPPROC_PARTITIONED_STACK_CREATE __indexXTPPROC_PARTITIONED_STACK_CREATE

4 thoughts on “Mapping wait types in dm_os_wait_stats to Extended Events

    1. I don’t work at Microsoft, so I can’t tell you why they are different. In some cases it was simply because they dropped a prefix for readability but the internal maps didn’t change and XEvents loads the C++ header file into the map, so you get the internal values and not the translated one from dm_os_wait_stats.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.