我如何找出重击SQL Server的原因？ [英] How do I find out what is hammering my SQL Server?

问题描述

今天的大部分时间里，我的SQL Server CPU的使用率一直在90％左右。

My SQL Server CPU has been at around 90% for the most part of today.

由于该原因，我无法重启它

I am not in a position to be able to restart it due to it being in constant use.

是否有可能找出SQL中导致这种CPU过载的原因？

Is it possible to find out what within SQL is causing such a CPU overload?

我已经运行了SQL事件探查器，但是发生了很多事情，很难确定是否有任何特别的原因引起了它。

I have run SQL Profiler but so much is going on it's difficult to tell if anything in particular is causing it.

我已经运行了sp_who2，但是不确定所有含义是什么确切地讲，如果可以在这里确定可能的问题。

I have run sp_who2 but am not sure what everything means exactly and if it is possible to identify possible problems in here.

要阻止任何可能只是被大量使用的响应，今天才开始使用

To pre-empt any "it's probably just being used a lot" responses, this has only kicked in today from perfectly normal activitly levels.

我一直在寻找在SQL中导致CPU悲伤的原因。

I'm after any way of finding what is causing CPU grief within SQL.

推荐答案

在此我进行尽职调查，您已确认SQL进程实际上消耗了CPU（perfmon进程类别计数器将对此进行确认）。通常，在这种情况下，需要对相关性能计数器进行抽样，并将它们与在正常负载运行条件下建立的基准进行比较。解决此问题后，我建议您为以后的比较建立这样的基准。

I assume due diligence here that you confirmed the CPU is actually consumed by SQL process (perfmon Process category counters would confirm this). Normally for such cases you take a sample of the relevant performance counters and you compare them with a baseline that you established in normal load operating conditions. Once you resolve this problem I recommend you do establish such a baseline for future comparisons.

您可以确切地找到每个CPU周期在SQL上花费的位置。但是知道在哪里看需要很多知识和经验。是SQL 2005/2008还是2000？
幸运的是，对于2005年及以后的版本，有一些现成的解决方案。您已经在这里获得了约翰·萨姆森（John Samson）的答案。我想添加一条建议，以下载并安装 SQL Server性能仪表盘报告。其中一些报告包括按时间或按I / O的热门查询，最常用的数据文件等，您可以快速了解问题所在。输出既是数字的又是图形的，因此对于初学者来说更有用。

You can find exactly where is SQL spending every single CPU cycle. But knowing where to look takes a lot of know how and experience. Is is SQL 2005/2008 or 2000 ? Fortunately for 2005 and newer there are a couple of off the shelf solutions. You already got a couple good pointer here with John Samson's answer. I'd like to add a recommendation to download and install the SQL Server Performance Dashboard Reports. Some of those reports include top queries by time or by I/O, most used data files and so on and you can quickly get a feel where the problem is. The output is both numerical and graphical so it is more usefull for a beginner.

我还建议使用亚当的活跃人物

最后但同样重要的是，我建议您下载并阅读有关性能分析的MS SQL客户咨询团队白皮书： SQL 2005等待和队列

And last but not least I recommend you download and read the MS SQL Customer Advisory Team white paper on performance analysis: SQL 2005 Waits and Queues.

我的建议也是查看I / O。如果您向服务器添加了浪费缓冲池的负载（即，它需要大量数据以至于它从内存中逐出了缓存的数据页），结果将是CPU的显着增加（听起来令人惊讶，但这是事实）。罪魁祸首通常是一个新查询，它端对端扫描一个大表。

My recommendation is also to look at I/O. If you added a load to the server that trashes the buffer pool (ie. it needs so much data that it evicts the cached data pages from memory) the result would be a significant increase in CPU (sounds surprising, but is true). The culprit is usually a new query that scans a big table end-to-end.

这篇关于我如何找出重击SQL Server的原因？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！