如何查找性能最差的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 

博客转载链接地址：http://blog.csdn.net/prince_lintb/article/details/50855797