【SQL】效能查詢

識別前 20 個在讀取 I/O 時耗用最多資源的查詢  

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),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

執行結果(參考):

列出執行速度較慢的SQL statement

select top 50 * from
(
SELECT    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS StatementText,
last_execution_time as LastExecuted,
last_elapsed_time/1000000.0 as ElapsedTimeInSeconds,
execution_count as ExecCountFromLastCompile
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
) t
where ElapsedTimeInSeconds > 1
order by ElapsedTimeInSeconds desc

執行結果(參考) :

PS: ElapsedTimeInSeconds 是執行花費時間（秒）

以上查詢語法需要在 Master底下執行