database SQL Server 2008 CPU 使用率高
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10319575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 High CPU usage
提问by Juan
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.
我们有一台装有 SQL Server 2008 的 Windows 2008 R2 服务器。该服务器有多个 .net 站点，上面有 SQL 服务器数据库。
We are currently experiencing an average CPU usage of 95%, and SQL Server is responsible for most of that usage.
我们目前的平均 CPU 使用率为 95%，而 SQL Server 负责大部分使用情况。
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
- 获得最多 CPU 密集查询的数据库
- 负责 CPU 密集型查询的进程
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.
使事情复杂化的一件事是我们有多个站点和使用相同数据库的“cron”。所以一旦我确定了数据库，我还需要得到一些关于哪个站点/cron 负责它的提示。
I would really appreciate any help on this as this issue is making our sites really slow...
我真的很感激这方面的任何帮助，因为这个问题使我们的网站变得非常缓慢......
Thanks
谢谢
回答by Aaron Bertrand
You can identify costly queries (and the databases they are associated with) using the DMVs, e.g. from this TechNet article:
您可以使用 DMV 识别成本高昂的查询（以及与它们关联的数据库），例如来自这篇 TechNet 文章：
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:
这些将告诉您重击查询，但不幸的是，它不会精确定位可能具有大量小型查询的数据库，这些查询单独使用少量 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 -- 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).
这些查询都没有标识运行它们的应用程序，并且使用的 DMV 不存储该信息（您必须在操作中捕获查询并在 sys.dm_exec_sessions 中记录应用程序名称，或查看跟踪）。
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).
当然，您可以使用市场上的各种 3rd 方性能工具来自动化这项工作（免责声明：我为其中一个工作，SQL Sentry，它生产Performance Advisor，它可以完成上述所有工作，包括跟踪高成本查询和维护有关它们在哪个数据库中运行以及调用它们的应用程序的信息）。
回答by Amal Dev
Alternatively you can use the Activity monitor to view the health status of your servers. From there you will be able to single out long running queries, any locks such as row lock, table lock etc etc
或者，您可以使用活动监视器查看服务器的健康状态。从那里你将能够挑出长时间运行的查询，任何锁，如行锁、表锁等
