数据库SQL优化分析查询语句总结

方法一：

?

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

示例：


方法二：

?

SELECT  DB_ID(DB.dbid) '数据库名'

      , OBJECT_ID(db.objectid) '对象'

      , QS.creation_time '编译计划的时间'

      , QS.last_execution_time '上次执行计划的时间'

      , QS.execution_count '执行的次数'

      , QS.total_elapsed_time / 1000 '占用的总时间（秒）'

      , QS.total_physical_reads '物理读取总次数'

      , QS.total_worker_time / 1000 'CPU 时间总量（秒）'

      , QS.total_logical_writes '逻辑写入总次数'

      , QS.total_logical_reads N'逻辑读取总次数'

      , QS.total_elapsed_time / 1000 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) AS '执行语句'

FROM    sys.dm_exec_query_stats AS QS CROSS APPLY

        sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN

        ( SELECT    *

          FROM      sys.dm_exec_cached_plans cp CROSS APPLY

                    sys.dm_exec_query_plan(cp.plan_handle)

        ) DB

            ON QS.plan_handle = DB.plan_handle

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 '%fetch%'

                      ORDER BY QS.total_elapsed_time / 1000 DESC

示例：

方法三：

?

SELECT s2.dbid,

    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,

      ( (CASE WHEN statement_end_offset = -1

         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)

         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,

    execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time,

    max_worker_time,    total_physical_reads,     last_physical_reads,

    min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes

FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 

WHERE s2.objectid is null

ORDER BY s1.total_worker_time desc

示例：

方式四：

选取了前10个最耗CPU时间的会话：

?

SELECT TOP 10

    [session_id],

    [request_id],

    [start_time] AS '开始时间',

    [status] AS '状态',

    [command] AS '命令',

    dest.[text] AS 'sql语句',

    DB_NAME([database_id]) AS '数据库名',

    [blocking_session_id] AS '正在阻塞其他会话的会话ID',

    [wait_type] AS '等待资源类型',

    [wait_time] AS '等待时间',

    [wait_resource] AS '等待的资源',

    [reads] AS '物理读次数',

    [writes] AS '写次数',

    [logical_reads] AS '逻辑读次数',

    [row_count] AS '返回结果行数'

FROM sys.[dm_exec_requests] AS der

    CROSS APPLY

    sys.[dm_exec_sql_text](der.[sql_handle]) AS dest

WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb' 

ORDER BY [cpu_time] DESC

示例：

资料来源：
http://www.cnblogs.com/sdadx/p/6510213.html
http://blog.csdn.net/tianlianchao1982/article/details/5562035
https://www.cnblogs.com/xunziji/archive/2012/03/20/2408348.html