[MSSQL] 21. DMV를 활용하여 자주 쓰는 스크립트 만들기

13번에서 DMV를 소개했지만 다시 한번 공부해보고자 정리!

 

DMV를 활용해서 직접 확인하는 경우도 있지만

보통은 스크립트로 저장해서 필요할때마다 확인하는 경우가 많은 것 같다.(나의 경우..)

DMV 개수도 많고 외워두면 편하긴 하지만 필요할때마다 일일히 짜기도 귀찮기도해서

몇가지 자주쓰는 스크립트로 DMV를 활용할 수 있도록 만들어 두는 것이 좋을 것 같다.

(사실 노트북에 있는데 귀찮아서 찾아보고 공부!!)

출처는 구글링을 통해 여기저기서 가져오고 필요한것과 필요없는것을 수정하는 방식으로...

 

(1) CPU 소모량이 많은 Stored Procedure 찾기

SELECT db_name(C.dbid) AS DBName 
       ,object_schema_name(C.objectid,dbid) AS SchemaName 
       ,object_name(C.objectid,dbid) AS StoredProcedure 
       ,sum(B.execution_count) AS Execution_count 
       ,sum(B.total_worker_time) AS total_cpu_time 
       ,sum(B.total_worker_time) / (sum(B.execution_count) * 1.0) AS avg_cpu_time 
FROM sys.dm_exec_cached_plans AS A
JOIN sys.dm_exec_query_stats  AS B on A.plan_handle=B.plan_handle
CROSS APPLY sys.dm_exec_sql_text(A.plan_handle)  AS C
 WHERE db_name(C.dbid) is not null and A.objtype='proc'
GROUP BY db_name(C.dbid), object_schema_name(objectid,C.dbid), object_name(objectid,C.dbid)
 ORDER BY sum(B.total_worker_time) DESC
 

 

 

(2) 비용이 드는 쿼리 찾기

SELECT TOP 20   
       SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,  
       (( CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(qt.TEXT)  
          ELSE qs.statement_end_offset 
          END - qs.statement_start_offset)/2)+1) AS 수행쿼리,  
        qs.execution_count              AS 쿼리계획이_마지막컴파일후_실행횟수,  
        qs.total_logical_reads          AS 쿼리계획이_컴파일후_실행될때_수행한_총논리적읽기수,  
        qs.last_logical_reads           AS 마지막_수행한_논리적읽기수,  
        qs.total_logical_writes         AS 쿼리계획이_컴파일후_실행될때_수행한_총논리적쓰기수,   
        qs.last_logical_writes          AS 마지막_수행한_논리적쓰기수,  
        qs.total_worker_time            AS [쿼리계획이_컴파일후_실행될때_사용된_총CPU시간(마이크로초)],   
        qs.last_worker_time             AS [마지막_실행될때_사용된_CPU시간(마이크로초)],  
        qs.total_elapsed_time/1000000     AS [실행완료하는데 소요된 총 경과시간(마이크로초)_초로변환],  
        qs.last_elapsed_time/1000000     AS [최근 실행완료하는데 소요된 경과시간(마이크로초)_초로변환],  
        qs.last_execution_time AS 해당쿼리가_마지막으로_실행된_시간 
  FROM       sys.dm_exec_query_stats AS qs 
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp  
  ORDER BY    qs.last_worker_time DESC    
 

 

 

(3) 쿼리계획에 대한 요약 성능 확인

읽기, 쓰기, 총 행, 완료 시간, 계획 해시, 메모리 부여 정보, 스레드, 열 저장소 통계, 유출 및 병렬 처리 정도와 같은 단일 계획에 연결된 통계

 

SELECT TOP 5 
      query_stats.query_hash AS "Query Hash",  
      SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",  
      MIN(query_stats.statement_text) AS "Statement Text"   
FROM (SELECT QS.*,   
            SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
            ((CASE statement_end_offset
              WHEN -1 THEN DATALENGTH(ST.text)  
              ELSE QS.statement_end_offset END  - QS.statement_start_offset)/2) + 1) AS statement_text  
FROM sys.dm_exec_query_stats AS QS   
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats 
GROUP BY query_stats.query_hash   
 ORDER BY 2 DESC;  

 

더 만들어보기.....~!!