In dem Artikel "Wer sind heute meine 5 Langläufer? " beschrieb ich ein gefundenes SQL-Statement mit dem man sich Infos zu den langsamsten SQL-Statement anzeigen lassen kann. Im Laufe der zeit modifizierte ich die Abfrage immer weiter.
Hier ist die verbesserte Fassung, die viele weitere interessante Angaben über den Befehl macht:
SELECT TOP(200)
qs.execution_count AS "Executions",
CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) AS "AvgDuration[ms]", – Umrechung in Millisekunden
CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) AS "AvgCpuTime[ms]", – Umrechung in Millisekunden
SUBSTRING(st.text,(qs.statement_start_offset+2)/2, – Offset wird in Bytes angegeben
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CAST(st.text AS NVARCHAR(MAX)))*2
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) AS "SqlStatement",
CAST(CAST(qs.last_elapsed_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) AS "LastDuration[ms]", – Umrechung in Millisekunden
CAST(CAST(qs.last_worker_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) AS "LastCpuTime[ms]", – Umrechung in Millisekunden
CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) AS "TotalDuration[ms]", – Umrechung in Millisekunden
CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) AS "TotalCpuTime[ms]", – Umrechung in Millisekunden
qs.total_logical_reads AS "TotalLogicalReads",
qs.total_physical_reads AS "TotalPhysicalReads",
qs.total_logical_writes AS "TotalLogicalWrites",
qs.creation_time AS "FirstExecution",
qs.last_execution_time AS "LastExecution",
db_name(st.dbid) AS "Database"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE qs.total_elapsed_time > 0
ORDER BY "AvgDuration[ms]" DESC, qs.execution_count DESC
Viel Erfolg damit… 🙂