데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

확장이벤트와 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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
» 확장이벤트와 dm_os_wait_stats 대기유형 매핑 jevida(강성욱) 2016.10.11 1890
1890 장기 트랜잭션 확인 및 경고 설정 jevida(강성욱) 2016.10.11 1423
1889 SQL 데이터 수집툴 DiagManager 사용법 jevida(강성욱) 2016.10.11 1321
1888 Windows Server 2012 R2 그룹 관리 서비스 계정 및 SQL 서버 jevida(강성욱) 2016.10.11 1833
1887 Kerbros 구성 관리자 툴 jevida(강성욱) 2016.10.11 1161
1886 SQL Server Geography 및 Geometory 데이터 형식 jevida(강성욱) 2016.10.11 2392
1885 SQL Server를 이용한 신용카드 보안 -조직이 PCI DSS 준수를 달성 하기 위한 요건 jevida(강성욱) 2016.10.11 1364
1884 TDE 암호화 사용하기 jevida(강성욱) 2016.10.11 3308
1883 SQL Server 암호화 하기 jevida(강성욱) 2016.10.11 2735
1882 의도하지 않은 분산트랜잭션 사용 jevida(강성욱) 2016.10.11 877
1881 Affinity I/O Mask jevida(강성욱) 2016.10.11 1168
1880 LPE_BATCH 스핀락 jevida(강성욱) 2016.10.11 882
1879 SQL Server 17953 오류 jevida(강성욱) 2016.10.11 984
1878 SQL 연결 18056 오류 jevida(강성욱) 2016.10.11 1362
1877 SQL 2008 R2 Sp1 적용과 9013 오류 - 포맷 섹터 크기에 따른 오류 jevida(강성욱) 2016.10.11 1809
1876 JDBC 로깅 파일 위치 설정 jevida(강성욱) 2016.10.11 958
1875 NUMA Node 메모리 블록 jevida(강성욱) 2016.10.11 1046
1874 AppDomain unloading 오류 로그 – CLR 오류 jevida(강성욱) 2016.10.11 1187
1873 SQL Server IO and Latch 설명 jevida(강성욱) 2016.10.11 3172
1872 마지막 백업 시간 및 DBCC 확인 [1] jevida(강성욱) 2016.10.08 1591





XE Login