Nalezení problémových dotazů

SELECT 
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (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
, (total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms] 
, max_elapsed_time/1000 AS [MaxExecTime in ms]
, min_elapsed_time/1000 AS [MinExecTime in ms]
, (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
, max_logical_reads AS MaxLogicalReads
, min_logical_reads AS MinLogicalReads
, max_logical_writes AS MaxLogicalWrites
, min_logical_writes AS MinLogicalWrites
, qs.execution_count AS NumberOfExecs
, qs.total_rows
, qs.last_rows
, qs.min_rows 
, qs.max_rows
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE qt.dbid = db_id()
ORDER BY (total_elapsed_time/execution_count) DESC;

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *

Můžete používat následující HTML značky a atributy: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>