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… :-)