如何查找性能最差的SQL语句

--总耗时最长
SELECT TOP 10
        total_worker_time / 1000 AS N'总消耗CPU 时间(ms)' ,
        execution_count N'运行次数' ,
        qs.total_worker_time / qs.execution_count / 1000 AS N'平均消耗CPU 时间(ms)' ,
        last_execution_time AS N'最后一次执行时间' ,
        max_worker_time / 1000 AS N'最大执行时间(ms)' ,
        SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
                  ( CASE WHEN qs.statement_end_offset = -1
                         THEN DATALENGTH(qt.text)
                         ELSE qs.statement_end_offset
                    END - qs.statement_start_offset ) / 2 + 1) AS N'使用CPU的语法' ,
        qt.text N'完整语法'
FROM    sys.dm_exec_query_stats qs WITH ( NOLOCK )
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE   execution_count > 1
ORDER BY max_worker_time DESC

--平均耗时最长
SELECT TOP 10
        ( total_elapsed_time / execution_count ) / 1000 N'平均时间ms' ,
        total_elapsed_time / 1000 N'总花费时间ms' ,
        total_worker_time / 1000 N'所用的CPU总时间ms' ,
        total_physical_reads N'物理读取总次数' ,
        total_logical_reads / execution_count N'每次逻辑读次数' ,
        total_logical_reads N'逻辑读取总次数' ,
        total_logical_writes N'逻辑写入总次数' ,
        execution_count N'执行次数' ,
        SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.text)
                        ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) N'完整语法' ,
        creation_time N'语句编译时间' ,
        last_execution_time N'上次执行时间'
FROM    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE   SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.text)
                        ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) NOT LIKE '%tch%'
ORDER BY total_elapsed_time / execution_count DESC

--物理读耗时最长
SELECT TOP 10
        qs.total_physical_reads N'物理读取总次数' ,
        qs.execution_count N'执行次数' ,
        qs.total_physical_reads / qs.execution_count / 1000 AS N'平均时间ms' ,
SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.text)
                        ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) N'完整语法'
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_physical_reads DESC  

--逻辑读耗时最长
SELECT TOP 10
        qs.total_logical_reads N'逻辑读取总次数' ,
        qs.execution_count N'执行次数' ,
        qs.total_logical_reads / qs.execution_count / 1000 AS N'平均时间ms' ,
       SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.text)
                        ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) N'完整语法'
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_logical_reads DESC 

--查看SQL语句CPU高的语句
SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes
--    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
--CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC
-- 若需要查看语句执行的计划，进一步分析语句，可以去掉注释，但可能导致查询结果变慢

--查询当前占用cpu时间比较多的语句
SELECT TOP 10  dest.[text] AS 'sql语句' 
,der.[cpu_time] as 'cpu时间'
FROM sys.[dm_exec_requests] AS der  CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest  
 WHERE [session_id]>50  ORDER BY [cpu_time] DESC 

sys.dm_exec_query_stats 的详细说明：http://msdn.microsoft.com/zh-cn/library/ms189741(v=sql.110).aspx

相关链接地址：http://blog.csdn.net/prince_lintb/article/details/50855797