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

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