[MSSQL] 13. DMV, DMF - 동적관리뷰&함수

13. DMV - 동적관리뷰 

동적 관리 뷰 및 함수는 서버 인스턴스 상태 모니터링, 문제 진단 및 성능 튜닝에 사용할 수 있는 서버 상태 정보를 반환합니다.

 

동적 관리 뷰 또는 함수를 쿼리하려면 개체에 대한 SELECT 권한과 VIEW SERVER STATE 또는 VIEW DATABASE STATE 권한이 필요합니다. 

 

- 동적관리뷰 개수는? : 아래 쿼리를 활용하여 확인해보니 총 273개입니다.

SELECT N'SYS.' + NAME AS NAME, TYPE_DESC 
  FROM SYS.SYSTEM_OBJECTS 
 WHERE NAME LIKE N'DM[_]%' 
 ORDER BY NAME
 

 

 

모두를 알기는 불가능하니, 자주 쓰는 DMV에 대해서 알아보겠습니다.

 

(1) sys.dm_exec_query_stats - 캐시된 쿼리 계획에 대한 집계 성능 통계

ex - 평균 CPU 시간별로 상위 5개의 쿼리 찾기

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;  

 

(2) sys.dm_exec_sql_text(sql_handle)  - 지정 된 sql_handle 에 의해 식별 되는 SQL 일괄 처리의 텍스트를 반환

- text 컬럼에 SQL 쿼리 실행 텍스트가 나옴.

 SELECT B.text,* 
  FROM sys.dm_exec_query_stats AS A
  CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B  
 ORDER BY A.creation_time DESC

 

(3) sys.dm_exec_procedure_stats - 저장 프로시저에 대한 집계 성능 통계

- 평균 경과 시간으로 식별된 상위 10개의 저장 프로시저 찾기 예제

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',   
       d.cached_time, d.last_execution_time, d.total_elapsed_time,  
       d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
       d.last_elapsed_time, d.execution_count  
  FROM sys.dm_exec_procedure_stats AS d  
 ORDER BY [total_worker_time] DESC;  

 

(4) sys.dm_exec_requests - 실행 되는 각 요청에 대한 정보를 반환

(5) sys.dm_exec_query_plan - 계획 핸들로 지정한 일괄 처리에 대한 XML 형식의 실행 계획을 반환