初涉SQL Server性能有关问题（4/4）：列出最耗资源的会话

在上3篇文章里，我们讨论了列出反映服务器当前状态的不同查询。

• 初涉SQL Server性能问题（1/4）：服务器概况
• 初涉SQL Server性能问题（2/4）：列出等待资源的会话
• 初涉SQL Server性能问题（3/4）：列出阻塞的会话

这篇文章我们看下从计划缓存里列出执行状态。

 1 /***************************************************************************************** 2 List heavy query based on CPU/IO. Change the order by clause appropriately 3 ******************************************************************************************/ 4 SELECT TOP 20 5 DB_NAME(qt.dbid) AS DatabaseName 6 ,DATEDIFF(MI,creation_time,GETDATE()) AS [Age of the Plan(Minutes)] 7 ,last_execution_time AS [Last Execution Time] 8 ,qs.execution_count AS [Total Execution Count] 9 ,CAST((qs.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) [Total Elapsed Time(s)]10 ,CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)]11 ,CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total CPU time (s)]12 ,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU]13 ,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]14 ,CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]15 ,CAST((qs.total_physical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Physical Read]16 ,CAST((qs.total_logical_reads) / qs.execution_count AS DECIMAL(28, 2))  AS [Avg Logical Reads]17 ,CAST((qs.total_logical_writes) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical Writes]18 ,max_physical_reads19 ,max_logical_reads20 ,max_logical_writes21 , SUBSTRING (qt.TEXT,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -122    THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 223    ELSE qs.statement_end_offset24    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]25 , qt.TEXT AS [Batch Statement]26 , qp.query_plan27 FROM SYS.DM_EXEC_QUERY_STATS qs28 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt29 CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle) qp30 WHERE qs.total_elapsed_time > 031 ORDER BY 32 [Total CPU time (s)] 33 --[Avg Physical Read]34 --[Avg Logical Reads]35 --[Avg Logical Writes]36 --[Total Elapsed Time(s)]37 --[Total Execution Count]38 DESC 

输出结果的每列说明介绍如下：

• DatabaseName 执行计划的数据库环境（数据库名）。
• Age of the Plan(Minutes) 计划缓存里计划的生存期，单位为分钟。
• Last Execution Time 这个计划的上次执行日期和时间。
• Total Execution Count   自上次编译后，总执行次数；在执行计划生存期内[Age of the Plan(Minutes)]，总执行次数（自上次编译后）。
• Total Elapsed Time(s)   执行这个计划总执行次数后[Total Execution Count]的总占用时间，单位为秒。
• Average Execution time(s) 这个计划每次执行的平均时间，单位为秒。
• Total CPU time (s)  执行这个计划总执行次数后[Total Execution Count]的总CPU时间，单位为秒。
• % CPU 与Total Elapsed Time(s)相比，CPU占用时间比。
• % Waiting  与Total Elapsed Time(s)相比，等待资源占用时间比。
• CPU time average (s) 每次执行的平均CPU时间，单位为秒。
• Avg Physical Read 每次执行的平均物理读数。
• Avg Logical Reads 每次执行的平均逻辑读数。
• Avg Logical Writes 每次执行的平均逻辑写数。
• max_physical_reads 每次执行的时候，出新最大物理读数。
• max_logical_reads 每次执行的时候，出新最大逻辑读数。
• max_logical_writes 每次执行的时候，出新最大逻辑写数。
• Individual Query  批处理语句的部分信息。
• Batch Statement  批处理查询。
• query_plan XML格式的执行计划，点击后我们可以看图示执行计划。

一般我们可以分析前5条记录(通过修改排序规则)的具体语句信息。大多数情况，我们会发现问题出现在临时表的滥用，distinct语句，游标，不合适的表连接条件，不合适的索引等等。其他经常发生的问题是，存储过程对数据库的大量调用（CPU消耗和执行时间都很小）。这个需要和开发人员反馈，修改下具体的实现方式。如果数据经常被调用，可以在程序里使用缓存方法避免与服务器的多次交互。有些对数据库的调用只是检查结果数据是否有改变。有些对数据库的调用是为检查数据库表里是否有新记录，且必须马上处理的。为了完成这些操作，程序会在1秒内多次查询表来找出未处理的记录。这个可以通过程序的异步调用来往表里插入数据来解决，或可以使用.net框架里的sqlDependency来解决。（sqlDependency提供了这样一种能力:当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的。）

1楼acepro

补充个：查找缺失的索引，SELECT DB_NAME(A.database_id) 数据库名 ,，--A.index_handle , A.database_id , A.object_id , ---此为id标识数据， A.equality_columns 构成相等查询的列 , A.inequality_columns 构成不等查询的列 , A.included_columns 查询的涵盖列 , A.[statement] 索引缺失的表 ,， #39;CREATE NONCLUSTERED INDEX INDEXIX_#39; + REPLACE(SUBSTRING(A.statement , CHARINDEX(#39;[dbo].[#39; , A.statement) + 7 ,， LEN(A.statement) - CHARINDEX(#39;[dbo].[#39; , A.statement)) , #39;]#39; , #39;#39;) + #39;_#39;， + CONVERT(VARCHAR(10) , a.index_handle) + #39; ON #39; + A.statement + #39;( #39; + ISNULL(A.equality_columns , A.inequality_columns) + #39;)#39;， + ( CASE WHEN A.included_columns IS NULL THEN #39;#39;， ELSE #39; INCLUDE (#39; + ISNULL(A.included_columns , #39;#39;) + #39; )#39;， END ) AS 创建索引的参考SQL， ,C.user_seeks 查找次数，,C.avg_total_user_cost 组织索引消耗 ，,C.avg_user_impact 用户平均收益，--,C.avg_total_system_cost 索引减少的系统查询的平均成本 ，--,C.avg_system_impact 用户系统收益，--,C.*，FROM sys.dm_db_missing_index_details A，LEFT JOIN sys.dm_db_missing_index_groups B ON A.index_handle=B.index_handle，LEFT JOIN sys.dm_db_missing_index_group_stats C ON B.index_group_handle=C.group_handle，WHERE DB_NAME(A.database_id) LIKE #39;%ZXFinance%#39;，ORDER BY C.avg_total_user_cost * C.avg_user_impact * (C.user_seeks + C.user_scans)DESC;，--说明，，--avg_total_user_cost ，，--float ，，--可通过组中的索引减少的用户查询的平均成本。，，--avg_user_impact ，，--float ，，--实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。，， ，，--avg_total_user_cost ，，--float ，，--可通过组中的索引减少的用户查询的平均成本。，，--avg_user_impact ，，--float ，，--实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。