DMV를 통하여 부하쿼리를 알아보자(100912)

목적 : Dinamic Management View를 통하여 부하가 걸린 쿼리를 알아보는 방법을 학습

 

 쿼리 내역 색출 방식 

 

 

 

 Sys.dm_exec_Query_stats 주요 컬럼 상세

 

상세 내역은 http://technet.microsoft.com/ko-kr/library/ms189741.aspx 를 참조 한다.

위의 주요 컬럼 으로 IO(logical_reads, logical_write), CPU(worker_time), duration(elapsed_time)  에 대한 부하 쿼리를 조회 할 수 있다.

 

 

Top 10 CPU 부하 쿼리

SELECT  TOP 10  ROW_NUMBER() over(ORDER BY (total_worker_time / qs.execution_count) DESC) AS SEQ, Execution_count, Last_EXECUTION_TIME, plan_generation_num,

       (total_elapsed_time/execution_count) AS AvgDuration,

       (total_logical_reads + total_logical_writes) / qs.execution_count AS [Average IO],

       (total_worker_time / qs.execution_count) AS [Average CPU used],

       (total_elapsed_time - total_worker_time) / qs.execution_count AS [Average Time Blocked],        

       SUBSTRING (qt.text,qs.statement_start_offset/2,

                                                       (

                                                          CASE WHEN qs.statement_end_offset = -1

                                                               THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)

                                                        ) * 2

                                                        ELSE qs.statement_end_offset

                                                        END - qs.statement_start_offset)/2) AS [Individual Query], 

        qt.text AS [Parent Query] ,DB_NAME(qt.dbid) AS DatabaseName

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt  --> 함수 호출  

WHERE  qt.DBID > 4  --> System DB 에서 실행되지 않은 쿼리

 AND Last_EXECUTION_TIME between DATEADD(dd,datediff(dd,0,Getdate()-1),0) AND DATEADD(dd,datediff(dd,0,Getdate()) ,0)  --> 쿼리가 실행된 날짜 지정

AND qt.text NOT LIKE '%sys%'      --> 부하쿼리가 시스템용 쿼리가 아닌것 즉 사용자가 실행한 쿼리

 

쿼리 설명

Total_worker_time(누적) 에서 쿼리가 실행된 횟수를 나뉘어서 가장 큰 값이 나오는 쿼리  구하는 로직

참고로 worker_time(CPU) 시간이 elapsed_time(duration) 시간 보다 길 경우가 있다.

WindowsNT는 시분할 시스템으로  위와 같은 경우는 병렬처리로 간주 할 수 있다. 즉 프로세서가 2개인 경우 Worker_time은 * 2 를 한다.

Statement_start_offset과 Statement_end_offset 값은 SQL Server가 이러한 유형의 데이터를 유니코드로 저장하기위해 2로 나눔

Statement_end_offset 값이 -1 이면 해당 문장이 일괄처리의 끝 의미 (부모 즉 SP 안에 속해있는 문장이 아니라는 의미)

SubStrng() 함수는 -1값을 해석할 수 없으므로 -1 대신 (statement_end_offset - qs.statement_start_offset)/2)를 사용

해당 문장으로 SP 혹은 일괄처리시 많은 리소스 혹은 장시간 걸린 해당 문장만을 추출 할 수 있다.

위의 문장에서 윈도우절(over()) 만 변경하면 CPU, IO, BLOCK, DURAION 부하 쿼리를 쉽게 구할 수 있다.