Database Reference
In-Depth Information
A figure of 5,000 Context Switches/sec per processor is excellent to fair. High numbers are largely dictated by the
speed of your CPUs, so measure performance over time and compare this number to your baseline to understand
when you may be deviating.
Batch Requests/Sec
Batch Requests/sec gives you a good indicator of just how much load is being placed on the system, which has a
direct correlation to how much load is being placed on the processor. Since you could see a lot of low-cost queries on
your system or a few high-cost queries, you can't look at this number by itself but must reference the other counters
defined in this section; 10,000 requests in a second would be considered a busy system. Greater values may be
cause for concern, completely depending on what is normal for your system. The best way to know which value has
meaning within your own systems is to establish a baseline and then monitor from there. Just remember that a high
number here is not necessarily cause for concern. If all your other resources are in hand and you're sustaining a high
number of batch requests/sec, it just means your server is busy.
SQL Compilations/Sec
The SQL Compilations/sec counter shows both batch compiles and statement recompiles as part of its aggregation.
This number can be extremely high when a server is first turned on (or after a failover or any other startup type event),
but it will stabilize over time. Once stable, significant or sustained spikes in compilations different from a baseline
measure is cause for concern and will certainly manifest as problems in the processor since query compilation is an
expensive operation. If you are working with some type of object-relational mapping engine, such as nHibernate or
Entity Framework, a high number of compilations might be normal, though no less costly. Chapter 14 covers SQL
compilation in detail.
SQL Recompilations/Sec
SQL Recompilations/sec is a measure of the recompiles of both batches and statements. A high number of recompiles
can lead to processor stress. Because statement recompiles are part of this count, it can be much higher than in
versions of SQL Server prior to 2005. Chapter 17 covers query recompilation in detail.
Other Tools for Measuring CPU Performance
You can use the DMOs to capture information about your CPU as well. The information in these DMOs will have to be
captured by running the query and then keeping the information as part of your baseline measurement.
Sys.dm_os_wait_stats
Wait statistics are a good way to understand whether there are bottlenecks on the system. You can't simply say
something greater than
x
is a bad number, though. You need to gather metrics over time in order to understand what
represents normal on your system. The deviations from that are interesting. Queries against this DMO that look for
signal wait time can indicate CPU bottlenecks.
