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_IONETWORK_IO
BROKER_TASK_STOPSSB_TASK_STOP
CLR_JOINCLR_TASK_JOIN
CLR_MEMORY_SPYCLR_MEMORY_SPY_ACCESS
CREATE_DATINISERVICEGET_DATINISERVICE
DBCC_SCALE_OUT_EXPR_CACHECHECK_EXPRESSION_CACHE
DBSTATEDB_STATE
DLL_LOADING_MUTEXDLL_LOAD
ERROR_REPORTING_MANAGERERROR_REPORTING_MGR
EXECUTION_PIPE_EVENT_INTERNALTWO_THREAD_PIPE_EVENT
FS_FC_RWLOCKFS_GC_RWLOCK
FT_IFTS_RWLOCKFT_RWLOCK
FT_IFTS_SCHEDULER_IDLE_WAITFT_SCHEDULER_IDLE_WAIT
FULLTEXT GATHERERFULLTEXT_GATHERER
HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LISTHADR_ARPROXY_NOTIFICATION_SUBSCRIBER_LIST
HADR_DATABASE_FLOW_CONTROLHADR_PARTNER_FLOW
HADR_DATABASE_VERSIONING_STATEHADR_VERSIONING_STATE
HADR_DATABASE_WAIT_FOR_RESTART__indexMUTEX_HADR_DATABASE_WAIT_FOR_RESTART
HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONINGHADR_WAIT_FOR_TRANSITION_TO_VERSIONING
HADR_FILESTREAM_BLOCK_FLUSHHADRFS_BLOCK_FLUSH
HADR_FILESTREAM_FILE_CLOSEHADRFS_FILE_CLOSE
HADR_FILESTREAM_FILE_REQUESTHADRFS_FILE_REQUEST
HADR_FILESTREAM_IOMGRHADRFS_IOMGR
HADR_FILESTREAM_IOMGR_IOCOMPLETIONHADRFS_IOMGR_IOCOMPLETION
HADR_FILESTREAM_MANAGERHADRFS_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_CONTROLHADR_TRANSPORT_FLOW
HTBUILDHASH_TABLE_BUILD
HTREPARTITIONHASH_TABLE_REPARTITION
INTERNAL_TESTING 
LAZYWRITER_SLEEPLZW_SLEEP
MD_AGENT_YIELDMETADATA_AGENT_YIELD
MD_LAZYCACHE_RWLOCKMETADATA_LAZYCACHE_RWLOCK
MISCELLANEOUSUNKNOWN
MSSEARCHMSSEARCH_COM
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDOPREEMPTIVE_FSRECOVER_CONDITIONALUNDO
PREEMPTIVE_OS_SQMLAUNCHPREEMPTIVE_SQMLAUNCH
PWAIT_ALL_COMPONENTS_INITIALIZEDALL_COMPONENTS_INITIALIZED
PWAIT_COOP_SCANCOOP_SCAN
PWAIT_EVENT_SESSION_INIT_MUTEXEVENT_SESSION_INIT_MUTEX
PWAIT_HADR_ACTION_COMPLETEDHADR_ACTION_COMPLETED
PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNCHADR_ARPROXY_NOTIFICATION_SUBSCRIBER_LIST
PWAIT_HADR_CLUSTER_INTEGRATIONHADR_CHANGE_NOTIFIER_TERMINATION_SYNC
PWAIT_HADR_FAILOVER_COMPLETEDHADR_CLUSTER_INTEGRATION
PWAIT_HADR_OFFLINE_COMPLETEDHADR_FAILOVER_COMPLETED
PWAIT_HADR_ONLINE_COMPLETEDHADR_OFFLINE_COMPLETED
PWAIT_HADR_POST_ONLINE_COMPLETEDHADR_ONLINE_COMPLETED
PWAIT_HADR_SERVER_READY_CONNECTIONSHADR_SERVER_READY_CONNECTIONS
PWAIT_HADR_WORKITEM_COMPLETEDHADR_WORKITEM_COMPLETED
PWAIT_MD_LOGIN_STATSMD_LOGIN_STATS
PWAIT_MD_RELATION_CACHEMD_RELATION_CACHE
PWAIT_MD_SERVER_CACHEMD_SERVER_CACHE
PWAIT_MD_UPGRADE_CONFIGMD_UPGRADE_CONFIG
PWAIT_PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOGPREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG
PWAIT_QRY_BPMEMORYQRY_BPMEMORY
PWAIT_REPLICA_ONLINE_INIT_MUTEXREPLICA_ONLINE_INIT_MUTEX
PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNCRESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC
PWAIT_SECURITY_CACHE_INVALIDATIONSECURITY_CACHE_INVALIDATION
QUERY_EXECUTION_INDEX_SORT_EVENT_OPENQUERY_EXEC_INDEXSORT_OPEN
REDO_THREAD_PENDING_WORKREDO_SIGNAL
REDO_THREAD_SYNCREDO_SYNC
RESOURCE_GOVERNOR_IDLE 
SCAN_CHAR_HASH_ARRAY_INITIALIZATIONSCAN_CHAR_HASH_ARRAY_INIT
SERVER_IDLE_CHECKSERVER_IDLE_LOCK
SNI_LISTENER_ACCESSLISTENER_UPDATE
SNI_TASK_COMPLETIONSNI_WAIT_TASK_FINISH
SP_PREEMPTIVE_SERVER_DIAGNOSTICS_SLEEPPREEMPTIVE_SP_SERVER_DIAGNOSTICS_SLEEP
THREADPOOLSOS_WORKER
TRAN_MARKLATCH_DTTRANMARKLATCH_DT
TRAN_MARKLATCH_EXTRANMARKLATCH_EX
TRAN_MARKLATCH_KPTRANMARKLATCH_KP
TRAN_MARKLATCH_NLTRANMARKLATCH_NL
TRAN_MARKLATCH_SHTRANMARKLATCH_SH
TRAN_MARKLATCH_UPTRANMARKLATCH_UP
VIA_ACCEPTVIA_ACCEPT_DONE
WAIT_XTP_GUESTXTP_GUEST
WAIT_XTP_TASK_SHUTDOWNXTP_TASK_SHUTDOWN
WAIT_XTP_TRAN_COMMITXTP_TRAN_COMMIT
WAITFOR_TASKSHUTDOWNTASKSHUTDOWN
XE_CALLBACK_LISTXE_CALLBACK
XTPPROC_PARTITIONED_STACK_CREATE__indexXTPPROC_PARTITIONED_STACK_CREATE