(─━┘_└━─)/ 머리만1톤(Head1Ton)

실행 소요 시간이 가장 긴 상위 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