We have an SQL server with about 40 different (about 1-5GB each) databases. The server is an 8 core 2.3G CPU with 32Gigs of RAM. 27Gig is pinned to SQL Server. The CPU utliziation is mostly close to 100% always and memory consumption is about 95%. The problem here is the CPU which is constantly close to 100% and trying to understand the reason.
我们有一个SQL服务器，有大约40种不同的（每个大约1-5GB）数据库。该服务器是一个8核2.3G CPU，具有32G的RAM。 27Gig固定到SQL Server。 CPU的运行总是接近100％，内存消耗约为95％。这里的问题是CPU经常接近100％，并试图了解原因。
I have run an initial check to see which database contributes to high CPU by using - this script but I could not substantiate in detail on whats really consuming CPU. The top query (from all DBs) only takes about 4 seconds to complete. IO is also not a bottleneck.
我已经运行了初步检查，看看哪个数据库通过使用 - 这个脚本有助于高CPU，但我无法详细说明真正消耗CPU的数据。最高查询（来自所有数据库）只需要大约4秒钟即可完成。 IO也不是瓶颈。
Would Memory be the culprit here? I have checked the memory split and the OBJECT CACHE occupies about 80% of memory allocated (27G) to SQL Server. I hope that is normal provided there are lot of SPs involved. Running profiler, I do see lot of recompiles, but mostly are due to "temp table changed", "deferred compile" etc and am not clear if these recompiles are a result of plans getting thrown out of cache due to memory pressure
记忆会成为罪魁祸首吗？我检查了内存分裂，OBJECT CACHE占用了大约80％的内存分配（27G）到SQL Server。如果涉及到很多SP，我希望这是正常的。运行探查器，我看到很多重新编译，但主要是由于“临时表更改”，“延迟编译”等，并不清楚这些重新编译是否是由于内存压力计划被抛出缓存的结果
Appreciate any thoughts.
欣赏任何想法。
2 个解决方案
#1
18
You can see some reports in SSMS:
您可以在SSMS中看到一些报告：
Right-click the instance name / reports / standard / top sessions
右键单击实例名称/ reports / standard / top会话
You can see top CPU consuming sessions. This may shed some light on what SQL processes are using resources. There are a few other CPU related reports if you look around. I was going to point to some more DMVs but if you've looked into that already I'll skip it.
您可以看到CPU消耗最多的会话。这可能会说明SQL进程正在使用资源。如果你环顾四周，还有一些其他与CPU相关的报告。我打算指出一些更多的DMV，但是如果你已经研究过，我会跳过它。
You can use sp_BlitzCache to find the top CPU consuming queries. You can also sort by IO and other things as well. This is using DMV info which accumulates between restarts.
您可以使用sp_BlitzCache查找占用CPU最多的查询。您也可以按IO和其他方式进行排序。这是使用DMV信息，它在重新启动之间累积。
This article looks promising.
这篇文章很有前景。
Some * goodness from Mr. Ozar.
来自Ozar先生的一些堆栈溢出的好处。
#2
0
According to this article on sqlserverstudymaterial;
根据sqlserverstudymaterial上的这篇文章;
Remember that "%Privileged time" is not based on 100%.It is based on number of processors.If you see 200 for sqlserver.exe and the system has 8 CPU then CPU consumed by sqlserver.exe is 200 out of 800 (only 25%).
请记住，“％Privileged time”不是基于100％。它基于处理器数量。如果你看到sqlserver.exe为200，系统有8个CPU，则sqlserver.exe消耗的CPU为800个中的200个（仅25％）。
If "% Privileged Time" value is more than 30% then it's generally caused by faulty drivers or anti-virus software. In such situations make sure the BIOS and filter drives are up to date and then try disabling the anti-virus software temporarily to see the change.
如果“％Privileged Time”值超过30％，则通常是由故障驱动程序或防病毒软件引起的。在这种情况下，请确保BIOS和过滤器驱动器是最新的，然后尝试暂时禁用防病毒软件以查看更改。
If "% User Time" is high then there is something consuming of SQL Server. There are several known patterns which can be caused high CPU for processes running in SQL Server including
如果“％User Time”很高，那么SQL Server会消耗掉一些东西。有几种已知的模式可能会导致SQL Server中运行的进程的CPU率很高
#1
18
You can see some reports in SSMS:
您可以在SSMS中看到一些报告：
Right-click the instance name / reports / standard / top sessions
右键单击实例名称/ reports / standard / top会话
You can see top CPU consuming sessions. This may shed some light on what SQL processes are using resources. There are a few other CPU related reports if you look around. I was going to point to some more DMVs but if you've looked into that already I'll skip it.
您可以看到CPU消耗最多的会话。这可能会说明SQL进程正在使用资源。如果你环顾四周，还有一些其他与CPU相关的报告。我打算指出一些更多的DMV，但是如果你已经研究过，我会跳过它。
You can use sp_BlitzCache to find the top CPU consuming queries. You can also sort by IO and other things as well. This is using DMV info which accumulates between restarts.
您可以使用sp_BlitzCache查找占用CPU最多的查询。您也可以按IO和其他方式进行排序。这是使用DMV信息，它在重新启动之间累积。
This article looks promising.
这篇文章很有前景。
Some * goodness from Mr. Ozar.
来自Ozar先生的一些堆栈溢出的好处。
#2
0
According to this article on sqlserverstudymaterial;
根据sqlserverstudymaterial上的这篇文章;
Remember that "%Privileged time" is not based on 100%.It is based on number of processors.If you see 200 for sqlserver.exe and the system has 8 CPU then CPU consumed by sqlserver.exe is 200 out of 800 (only 25%).
请记住，“％Privileged time”不是基于100％。它基于处理器数量。如果你看到sqlserver.exe为200，系统有8个CPU，则sqlserver.exe消耗的CPU为800个中的200个（仅25％）。
If "% Privileged Time" value is more than 30% then it's generally caused by faulty drivers or anti-virus software. In such situations make sure the BIOS and filter drives are up to date and then try disabling the anti-virus software temporarily to see the change.
如果“％Privileged Time”值超过30％，则通常是由故障驱动程序或防病毒软件引起的。在这种情况下，请确保BIOS和过滤器驱动器是最新的，然后尝试暂时禁用防病毒软件以查看更改。
If "% User Time" is high then there is something consuming of SQL Server. There are several known patterns which can be caused high CPU for processes running in SQL Server including
如果“％User Time”很高，那么SQL Server会消耗掉一些东西。有几种已知的模式可能会导致SQL Server中运行的进程的CPU率很高