[SQL 2005 GUIDE] 동적 관리 뷰 활용 - 쿼리 실행 확인하기

sys.dm_exec_query_stats 동적 관리 뷰를 이용하여 캐싱되어 있는 쿼리 실행 계획에 대한 집계 성능 통계를 확인할 수 있습니다. 이 뷰에는 각 쿼리 계획에 대한 행이 포함되어 있으며, 캐시에서 실행 계획이 제거되면 이 뷰에서도 해당 행이 제거됩니다.

 

실행 소요 시간이 가장 긴 상위 50개의 쿼리 정보 조회하기

SELECT TOP 50              sum(qs.total_worker_time) as total_cpu_time,              sum(qs.execution_count) as total_execution_count,              count(*) as  '#_statements',              qt.dbid, qt.objectid, qs.sql_handle,              qt.[text] FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) as qt GROUP BY qt.dbid,qt.objectid, qs.sql_handle,qt.[text] ORDER BY sum(qs.total_worker_time) DESC,qs.sql_handle; GO

 

CPU Time 순으로 상위 50개 쿼리 정보 조회하기

SELECT TOP 50 qs.total_worker_time/qs.execution_count as [Avg CPU Time],              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 query_text,              qt.dbid,              qt.objectid FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Avg CPU Time] DESC; GO

 

평균 I/O가 높은 상위 50개 쿼리 정보 조회하기

SELECT TOP 50 (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], 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 query_text,                            qt.dbid, dbname=db_name(qt.dbid),                            qt.objectid,                            qs.sql_handle,                            qs.plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY [Avg IO] DESC; GO

 

CLR 내에서의 평균 사용 시간 확인하기

SELECT TOP 5 creation_time, last_execution_time, total_clr_time, total_clr_time/execution_count AS [Avg CLR Time], last_clr_time, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [Avg CLR Time] DESC; GO

 

병렬로 실행 중인 프로세스 확인하기

SELECT r.session_id,              r.request_id,              max(isnull(exec_context_id, 0)) AS number_of_workers,              r.sql_handle,              r.statement_start_offset,              r.statement_end_offset,              r.plan_handle FROM sys.dm_exec_requests r              join sys.dm_os_tasks t ON r.session_id = t.session_id              join sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE s.is_user_process = 0x1 GROUP BY r.session_id, r.request_id, r.sql_handle, r.plan_handle,              r.statement_start_offset, r.statement_end_offset HAVING max(isnull(exec_context_id, 0)) > 0; GO

 

재컴파일 확인하기

SELECT TOP 25              sql_text.text,              sql_handle,              plan_generation_num,              execution_count,              dbid,              objectid FROM sys.dm_exec_query_stats a              CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text WHERE plan_generation_num >1 ORDER BY plan_generation_num DESC; GO