확장이벤트와 dm_os_wait_stats 대기유형 매핑

확장이벤트와 dm_os_wait_stats 대기유형 매핑

 

• Version : SQL Server 2008, 2008R2, 2012

 

SQL Server 확장이벤트와 dm_os_wait_stats 대기유형의 매핑 관계를 알아본다. 아래 대기유형 목록은 SQL Server 2012 Sp1 Cu6 기반으로 제작 되었다.

 

[확장이벤트]

select * from sys.dm_xe_map_values

 

 

[sys.dm_os_wait_stats]

select * from sys.dm_os_wait_stats

 

 

 

[매핑 표]

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

 

 

[참고자료]

http://www.sqlskills.com/blogs/jonathan/mapping-wait-types-in-dm_os_wait_stats-to-extended-events/?utm_source=rss&utm_medium=rss&utm_campaign=mapping-wait-types-in-dm_os_wait_stats-to-extended-events

 

 

2013-12-02 / 강성욱 / http://sqlmvp.kr