SQL Server 2008 CPU使用率高 [英] SQL Server 2008 High CPU usage

问题描述

我们有一个带有SQL Server 2008的Windows 2008 R2服务器。该服务器有多个.net网站上有SQL服务器数据库。

我们目前的平均CPU使用率为95％，而SQL Server负责大部分的使用。

我想确定哪个网站负责这个，所以我们可以选择它或移动到另一个服务器。但我没有找到任何直接的方式看这个。

我一直在寻找，如果我能找到：

• 负责CPU密集型查询的进程

有一件事，也使事情变得复杂的是，我们有多个网站和crons使用同一个数据库。所以一旦我确定数据库，我还需要得到一些网站/ cron负责的提示。

我真的很感谢任何帮助，因为这个问题正在使我们的网站真的很慢...

感谢

解决方案

您可以使用DMV识别昂贵的查询（以及与之相关联的数据库），例如此TechNet文章：

SELECT TOP 50
[使用的平均CPU] = total_worker_time / qs.execution_count，
[使用的总CPU] = total_worker_time，
[执行计数] = qs.execution_count，
[Individual Query] = SUBSTRING（qt.text，qs.statement_start_offset / 2，
（CASE WHEN qs.statement_end_offset = -1
THEN LEN （NVARCHAR（MAX），qt.text））* 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset）/ 2）
，[Parent Query] = qt.text
，DatabaseName = DB_NAME（qt.dbid）
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text（qs.sql_handle）as qt
ORDER BY [使用的平均CPU] DESC;


这些将告诉你关于重击键查询，但不幸的是，非常高容量的小查询，其单独使用CPU的小比特，但是总体上使用大比特。您可以使用此查询从Glenn Allan Berry的DMV查询：

  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  - 系统数据库
 AND DatabaseID<> 32767  -  ResourceDB 
 ORDER BY row_num OPTION（RECOMPILE）; 
  

这两个查询都不识别运行它们的应用程序，并且使用的DMV不会存储该信息（您必须捕获该行为中的查询，并记下sys.dm_exec_sessions中的应用程序名称，或查看跟踪）。

当然，您可以自动执行此工作市场上的各种第三方性能工具（免责声明：我为其中一个工作，SQL Sentry，他生产 Performance Advisor ，它执行上述所有操作，包括跟踪高成本查询和维护有关它们运行的​​数据库的信息，以及称为它们的应用程序）。

We have a Windows 2008 R2 Server with a SQL Server 2008 in it. That server has multiple .net sites that have SQL server databases on it.

We are currently experiencing an average CPU usage of 95%, and SQL Server is responsible for most of that usage.

I would like to identify which site is responsible for this so we can either optmize it or move it to another server. But I didnt find any direct way of looking this.

I've been looking if I could find:

• The database that is getting the most CPU intensive queries
• The process that is responsible for CPU intensive queries

One thing that also complicates the things is that we have multiple sites and "crons" using the same database. So once I identify the database I would also need to get some hints of which site/cron is responsible for it.

I would really appreciate any help on this as this issue is making our sites really slow...

Thanks

解决方案

You can identify costly queries (and the databases they are associated with) using the DMVs, e.g. from this TechNet article:

SELECT TOP 50
 [Average CPU used] = total_worker_time / qs.execution_count,
 [Total CPU used] = total_worker_time,
 [Execution count] = qs.execution_count,
 [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

These will tell you about heavy hitter queries, but unfortunately it won't pinpoint a database that might have very high volume of small queries that are using small bits of CPU individually but large bits in aggregate. You can do that with this query from Glenn Allan Berry's DMV queries:

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);

Neither of these queries identifies the application that ran them, and the DMVs used don't store that information (you would have to catch the queries in the act and note the application name in sys.dm_exec_sessions, or review a trace).

Of course you can automate this work with a variety of 3rd party performance tools on the market (disclaimer: I work for one of them, SQL Sentry, who produces Performance Advisor, which does all of the above, including keeping track of high-cost queries and maintaining the information about which database they ran in and what application called them).

这篇关于SQL Server 2008 CPU使用率高的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！