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”
Hi Jonathan,
And why the names are different? Any good reason to justify that? I really don’t understand…
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.