Databases Reference
In-Depth Information
Configuration-BasedCPUBottlenecks
Typical configuration-based CPU bottlenecks could be related to incorrect configuration of Priority Boost,
Maximum Degree of Parallelism, Max Worker Threads, Affinity Mask, and Hyper Threading. While
handling processor issues, it's important to remember that SQL Server handles logical processors. This
means SQL Server views all processors as equal, whether they're actual physical processors, multi-core,
or hyper threaded. There is no way for SQL Server to differentiate between these types because to a large
extent they appear as equal to Windows, too.
Throughout any performance investigation, it's important to keep in mind the sp_configure settings
that relate to CPU configuration as these can significantly affect the workload shape and size on the
processors. Unfortunately, there are no counters that provide direct recommendations for many of these
settings, although Books Online and other whitepapers from Microsoft exist to assist in determining
optimal configuration.
For example, when investigating a performance scenario involving uneven CPU load, it's imperative to
understand the Affinity Mask and Maximum Degree of Parallelism settings as these should be considered
when analyzing any performance logs.
PagingtoDiskCanCauseHighCPU
SQL Server should always run entirely in memory. Performance will suffer considerably if Windows
uses the paging file to service SQL Server. There's a curious side effect of a server running low on
memory, which is high CPU utilization. This is somewhat non-intuitive but is reasonably easy to
identify, once you know what to look for! If you're monitoring a server which is seeing excessive CPU and
you've identified this as privilege mode CPU activity, you should also monitor the Memory — Pages/
sec counter.
This counter reveals the total number of pages that are input and output per second. On its own, this
counter isn't sufficient to diagnose a low memory condition, but if you combined it with high privilege
mode CPU and look at Memory — Available Bytes, a picture should start to form.
Best Practice
Ensure that the SQL Server buffer pool and memtoleave areas are sized to fit into
application mode memory on your servers. This is especially important on multi-instance
servers and failover clusters to ensure consistent performance and predictable
failover times.
Schema-BasedCPUBottlenecks
There are a number of scenarios whereby issues with a database schema may result in excessive CPU
utilization. To a certain extent, your ability to be able to proactively prevent some of these events is
limited. However, recognizing the symptoms and being able to positively identify the problem will go
Search WWH ::
Custom Search