데이터베이스 개발자 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
1910 64비트 버전의 SQL Server 버퍼 풀 메모리 페이지 수 줄이는 방법 jevida(강성욱) 2016.10.12 2466
1909 누락된 공유 잠금 (Missing Shared Locks) jevida(강성욱) 2016.10.12 1564
1908 Ad Hoc Distributed Queries 옵션 jevida(강성욱) 2016.10.12 3087
1907 높은 MAXDOP은 쿼리를 느리게 만들 수 있는가? jevida(강성욱) 2016.10.12 1308
1906 변경된 테이블 이름 복구하기 jevida(강성욱) 2016.10.12 1303
1905 Sys,dm_exec_connections jevida(강성욱) 2016.10.12 1928
1904 SQL Server 시작 매개 변수 설정 jevida(강성욱) 2016.10.12 2489
1903 AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성 향상 jevida(강성욱) 2016.10.12 1785
1902 쿼리 대기 옵션 jevida(강성욱) 2016.10.12 1573
1901 Index create memory 설정 jevida(강성욱) 2016.10.12 1234
1900 Min memory per query 옵션 jevida(강성욱) 2016.10.12 1682
1899 SQL Server 에디션 다운그레이드와 제한된 기능 확인 jevida(강성욱) 2016.10.12 2124
1898 Downgrade from SQL Server Ent to Std Edition jevida(강성욱) 2016.10.12 1340
1897 트랜잭션 로그 여유 공간 모니터링 jevida(강성욱) 2016.10.12 1352
1896 SQL Server가 서비스 격리를 처리하는 방법 jevida(강성욱) 2016.10.12 1145
1895 쿼리 매개변수화 확인하기 jevida(강성욱) 2016.10.12 1434
1894 SQL Server에서 Drop 및 Delete 사용자 찾기 jevida(강성욱) 2016.10.12 1629
1893 SQL Server 트랜잭션 로그 읽기 jevida(강성욱) 2016.10.12 2085
1892 MaxBCPThreads에 따른 BCP 병렬출력 jevida(강성욱) 2016.10.11 1090
» 확장이벤트와 dm_os_wait_stats 대기유형 매핑 jevida(강성욱) 2016.10.11 1886





XE Login