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

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

SQL Server 2016 쿼리 실행에 대한  스레드(오퍼레이터성능 통계

 

·         Version : SQL Server 2014 SP2, 2016

 

SQL Server 2016에서 쿼리 성능 문제를 해결할  참고   있는  가지 정보가 추가 되었다  대표적인 것이 라이브 쿼리 통계 이다.

·         SQL Server 2016 라이브 쿼리 통계 : http://sqlmvp.kr/220453033961

 

하지만 아직도 많은 쿼리 성능 문제를 해결하기 위한 메트릭은 부족한 편이다이번에 소개하는 기능은 showplan XML을 활성화하고 쿼리를 실행하였을때 각 스레드(오퍼레이터)에서 실행한 결과를 확인할 수 있는 기능이다예를 들어 SQL Server2014까지는 쿼리 실행결과 행결과를 반환하지만 스캔 성능에 관해 자세한 정보를 제공하지는 않았다.

<RunTimeInformation>

    <RunTimeCountersPerThread Thread = "0"ActualRows = "8001"ActualEndOfScans = "1"ActualExecutions = "1"/>

 </ RunTimeInfo

 

SQL Server 2016 부터는 실제 행 수를 포함하여 I/O read, CPU 시간 등의 내용이 스레드 별로 표시 된다.

<RunTimeInformation>

   <RunTimeCountersPerThread Thread="0" ActualRows="8001" ActualRowsRead="10000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="965" ActualCPUms="965" ActualScans="1" ActualLogicalReads="26073" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

</RunTimeInformation>

 

퀴리가 병렬로 수행되는 경우 아래와 같이 각 스레드의 실행 내용이 표시된다.

<RunTimeInformation>

    <RunTimeCountersPerThread Thread = "6"ActualRows = "0"ActualRowsRead = "932085"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "160"ActualCPUms = "158"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "8"ActualRows = "0"ActualRowsRead = "886279"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "138"ActualCPUms = "137"ActualScans = "1" 1 "ActualLogicalReads ="2341 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "10"ActualRows = "0"ActualRowsRead = "828520"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "121"ActualCPUms = "120"ActualScans = "1" 1 "ActualLogicalReads ="2192 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "12"ActualRows = "0"ActualRowsRead = "932085"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "155"ActualCPUms = "155"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "11"ActualRows = "0"ActualRowsRead = "828520"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "133"ActualCPUms = "132"ActualScans = "1" 1 "ActualLogicalReads ="2192 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "9"ActualRows = "0"ActualRowsRead = "724955"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "124"ActualCPUms = "124"ActualScans = "1" 1 "ActualLogicalReads ="1918 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "1"ActualRows = "0"ActualRowsRead = "724955"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "121"ActualCPUms = "120"ActualScans = "1" 1 "ActualLogicalReads ="1918 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "7"ActualRows = "0"ActualRowsRead = "414260"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "111"ActualCPUms = "109"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "5"ActualRows = "0"ActualRowsRead = "1035650"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "168"ActualCPUms = "165"ActualScans = "1" 1 "ActualLogicalReads ="2740 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "/>

    <RunTimeCountersPerThread Thread = "4"ActualRows = "8001"ActualRowsRead = "932086"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "160"ActualCPUms = "158"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "2"ActualRows = "0"ActualRowsRead = "828520"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "156"ActualCPUms = "130"ActualScans = "1" 1 "ActualLogicalReads ="2192 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "3"ActualRows = "0"ActualRowsRead = "932085"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = "  "ActualElapsedms = "159"ActualCPUms = "158"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "0"ActualRows = "0"Batches = "0"ActualEndOfScans = "0"ActualExecutionMode = "  "ActualElapsedms = "0"ActualCPUms = "0"ActualLogicalReads = 2 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "/>

 </ RunTimeInformation>

 

또한 새로운 확장이벤트(query_thread_profiler)를 도입하여 각 노드 및 스레드의 성능에 대한 통찰력을 제공한다아래 스크립트는 이벤트를 생성하는 예제이다.

CREATE EVENT SESSION [PerfStats_XE] ON SERVER

ADD EVENT sqlserver.query_thread_profile(

ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text))

ADD TARGET package0.ring_buffer(SET max_memory=(25600))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

 

 

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

 

 

2017-08-21 / 강성욱 / http://sqlmvp.kr

 

SQL SQL Server 2016, Live query stats, operator level performance stat, DBA, SQL Tuning, DB Tuning, xEvent, MS SQL




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
No. Subject Author Date Views
2111 SQL Operations Studio - Linux, MAC에서 사용할 수 있는 SQL DBMS 툴 jevida(강성욱) 2018.03.31 3069
2110 SQL Server 2016 Large RAM 및 Checkpoint jevida(강성욱) 2018.03.31 2128
2109 SQL Server 2016 JSON 형식을 일반 ROW 형식으로 반환하 jevida(강성욱) 2018.03.31 1669
2108 model Database 손상시 발생하는 영향 및 복구 방법 jevida(강성욱) 2018.03.31 1186
2107 In-Memory OLTP 사용시 메모리 할당량 초과 오류 jevida(강성욱) 2017.09.13 6760
» SQL Server 2016 쿼리 실행에 대한 각 스레드(오퍼레이터) 성능 통계 jevida(강성욱) 2017.09.13 7059
2105 Multisubnet환경의 AG 그룹에서 링크드 서버 사용시 주의점 jevida(강성욱) 2017.09.13 5920
2104 SQL Linux에서 Job Agent 설치 jevida(강성욱) 2017.09.13 5954
2103 SQL Linux에서 Windows SQL 백업 파일 복원 jevida(강성욱) 2017.09.13 5930
2102 Linux에서 Network I/O 확인 jevida(강성욱) 2017.09.13 6381
2101 SQL Linux에서traceflag 활성화 jevida(강성욱) 2017.09.13 5636
2100 SQL Linux에서 dump file 위치 변경 jevida(강성욱) 2017.09.13 5405
2099 SQL Linux에서 Port 변경 jevida(강성욱) 2017.09.13 6243
2098 Linux에서 DISK 공간 확인 jevida(강성욱) 2017.09.13 5611
2097 SQL Linux에서collation 변경 jevida(강성욱) 2017.09.13 3538
2096 SQL Linux에서 데이터 및 로그 파일의 기본 디렉토리 변경 jevida(강성욱) 2017.09.13 3871
2095 SQL Linux 기본Configure 명령 jevida(강성욱) 2017.09.13 3408
2094 SQL Linux에서 기본 백업 디렉토리 변경 jevida(강성욱) 2017.09.13 3842
2093 Linux에서 DISK I/O 사용량 확인 jevida(강성욱) 2017.09.13 3727
2092 Linux에서 CPU 사용량 확인 jevida(강성욱) 2017.09.13 2224





XE Login