利用DMV找出查询最慢的语句

 1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 2 
 3 SELECT TOP 20 
 4 CAST(qs.total_elapsed_time/1000000.0 AS DECIMAL(28,2)) AS [Total Elaspsed Duration(s)],
 5 qs.execution_count, 
 6 SUBSTRING(qt.text,
 7             (qs.statement_start_offset/2)+1,
 8             ((CASE WHEN qs.statement_end_offset = -1 
 9                 THEN LEN(CONVERT(NVARCHAR(MAX),qt.text))*2
10                 ELSE qs.statement_end_offset 
11                 END - qs.statement_start_offset)/2)+1) 
12 AS [Individual Query],
13 qt.text AS [Parent Query], 
14 DB_NAME(qt.dbid) AS DatabaseName, 
15 qp.query_plan
16 FROM SYS.dm_exec_query_stats qs
17 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
18 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
19 ORDER BY total_elapsed_time DESC