DB/MS SQL

[DMV] Slowest queries

charom 2011. 9. 27. 10:19
Here is example to find which query is the slowest query in my db. 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20 
  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))   
									AS [Total Elapsed Duration (s)]
, qs.execution_count
  , 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) + 1) AS [Individual Query] 
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName 
  , qp.query_plan
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
ORDER BY total_elapsed_time DESC  


This is the result of my db.