WITH DB_cpu_Stats AS ( SELECT DatabaseID,DB_Name(DatabaseID) AS [DatabaseName],SUM(total_worker_time) AS [cpu_Time_Ms] FROM sys.dm_exec_query_stats AS qs CROSS APPLY ( SELECT CONVERT(int,value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID ) SELECT ROW_NUMBER() OVER(ORDER BY [cpu_Time_Ms] DESC) AS [row_num],DatabaseName,[cpu_Time_Ms],CAST([cpu_Time_Ms] * 1.0 / SUM([cpu_Time_Ms]) OVER() * 100.0 AS DECIMAL(5,2)) AS [cpuPercent] FROM DB_cpu_Stats --WHERE DatabaseID > 4 -- system databases --AND DatabaseID <> 32767 -- ResourceDB ORDER BY row_num OPTION (RECOMPILE);
并且下面的查询告诉问题是主数据库和分发数据库(大约90％)：
DECLARE @total INT SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK) join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid SELECT sb.name 'database',@total 'system cpu',SUM(cpu) 'database cpu',CONVERT(DECIMAL(4,1),CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,@total)*100) '%' FROM sys.sysprocesses sp (NOLOCK) JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid --WHERE sp.status = 'runnable' GROUP BY sb.name ORDER BY CONVERT(DECIMAL(4,@total)*100) desc
解决方法
第一个查询使用sys.dm_exec_query_stats来获取cpu信息(即total_worker_time).如果您转到该DMV的MSDN文档的链接页面,您将看到一个简短的3句简介,其中2个句子为我们提供了解这个信息背景的大部分内容(“它有多可靠”和“它如何与sys.sysprocesses相比”).这两句话是：
Returns aggregate performance statistics for cached query plans in sql Server. … When a plan is removed from the cache,the corresponding rows are eliminated from this view
第一句“返回聚合性能统计信息”告诉我们,此DMV中的信息(与其他几个一样)是累积的,并不仅仅针对当前正在运行的查询.这也是由DMV中的字段指示的,该字段不是问题,execution_count中的查询的一部分,其再次显示这是累积数据.通过将一些指标除以execution_count,可以获得平均值等,这是非常方便的.
第二句“从缓存中删除的计划也将从此DMV中删除”,表明它根本不是一个完整的图片,特别是如果服务器已经有一个非常完整的计划缓存并且正处于负载状态,因此即将到期计划有点频繁.此外,当服务器重置时,大多数DMV都会重置,因此即使计划到期时未删除这些行,它们也不是真正的历史记录.
现在让我们将上述内容与sys.sysprocesses进行对比.此系统视图仅显示当前正在运行的内容,就像sys.dm_exec_connections,sys.dm_exec_sessions和sys.dm_exec_requests的组合(在sys.dm_exec_sessions的链接页面上说明).与sys.dm_exec_query_stats DMV相比,这是一个完全不同的服务器视图,即使在进程完成后也保存数据.意思是,相对于“第二个查询的结果是错误的吗？”问题,他们没有错,他们只是属于性能统计的不同方面(即时间框架).
因此,使用sys.sysprocesses的查询只是“立即查看”.并且使用sys.dm_exec_query_stats的查询主要(可能)查看自上次重新启动sql Server服务(或显然系统重启)以来发生的情况.对于一般的性能分析,似乎sys.dm_exec_query_stats要好得多,但同样,它会一直丢弃有用的信息.并且,在这两种情况下,您还需要首先考虑@Aaron在问题注释(自删除)中关于“database_id”值的准确性所提出的要点(即它仅反映启动代码的活动数据库),不一定是“问题”发生的地方).
但是,如果您只是需要/想要了解所有数据库中正在发生的事情,可能是因为现在情况正在放缓,您最好使用sys.dm_exec_connections,sys.dm_exec_sessions和sys的组合. dm_exec_requests(而不是已弃用的sys.sysprocesses).请记住,您正在查看/查询查询,而不是数据库,因为查询可以跨多个数据库连接,包括来自一个或多个数据库的UDF等.
编辑：
如果总体问题是减少高cpu消费者,那么寻找占用最多cpu的查询,因为数据库实际上不占用cpu(查看每个数据库可能在托管公司工作,其中每个数据库被隔离并拥有一个不同的客户).
以下查询将帮助识别具有高平均cpu使用率的查询.它会压缩query_stats DMV中的数据,因为这些记录可以多次显示相同的查询(是的,查询批次的相同子集),每个都有不同的执行计划.
;WITH cte AS ( SELECT stat.[sql_handle],stat.statement_start_offset,stat.statement_end_offset,COUNT(*) AS [NumExecutionPlans],SUM(stat.execution_count) AS [TotalExecutions],((SUM(stat.total_logical_reads) * 1.0) / SUM(stat.execution_count)) AS [AvgLogicalReads],((SUM(stat.total_worker_time) * 1.0) / SUM(stat.execution_count)) AS [Avgcpu] FROM sys.dm_exec_query_stats stat GROUP BY stat.[sql_handle],stat.statement_end_offset ) SELECT CONVERT(DECIMAL(15,5),cte.Avgcpu) AS [Avgcpu],CONVERT(DECIMAL(15,cte.AvgLogicalReads) AS [AvgLogicalReads],cte.NumExecutionPlans,cte.TotalExecutions,DB_NAME(txt.[dbid]) AS [DatabaseName],OBJECT_NAME(txt.objectid,txt.[dbid]) AS [ObjectName],SUBSTRING(txt.[text],(cte.statement_start_offset / 2) + 1,( (CASE cte.statement_end_offset WHEN -1 THEN DATALENGTH(txt.[text]) ELSE cte.statement_end_offset END - cte.statement_start_offset) / 2 ) + 1 ) FROM cte CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt ORDER BY cte.Avgcpu DESC;
