Lang laufende SQL Statements finden

14. Februar 2011 – 14:17

Mit dem folgenden Statement kann man Langläufer-Statements ausfindig machen:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Alle Ausführungszeiten in Mikrosekunden (µs)!
SELECT TOP 10
    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 [SQL statement]
    , qs.execution_count [Total Executions]
    , qs.total_elapsed_time [Total execution TIME]
    , qs.last_elapsed_time [LAST execution TIME]
    , qs.min_elapsed_time [MIN execution TIME]
    , qs.max_elapsed_time [MAX execution TIME]
    , qs.total_clr_time [.NET Total execution TIME]
    , qs.last_clr_time [LAST .NET TIME]
    , qs.min_clr_time [MIN .NET TIME]
    , qs.max_clr_time [MAX .NET TIME]
FROM sys.dm_exec_query_stats AS qs
    CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_elapsed_time DESC

Das Ergebnis sieht dann in etwa so aus:

Long running statements

Beitrag kommentieren