Use CPU Instance Counters for Better Insight
Windows Performance Monitor, a management tool included with Windows, is a key tool for understanding the performance characteristics of your database server. Performance Monitor lets you observe a wealth of metrics about the activity of the operating system and applications such as SQL Server 2008. Performance Monitor can be used to observe these metrics interactively in real time or to log them to disk for later review.
When using Performance Monitor one of the counters that you'll probably be interested in is % Processor Time. This will tell you how busy the processors are by taking the percentage of time the thread of the Idle process is running and then subtracting that from 100%.
Something to watch out for is making sure that you're NOT using the _Total counter for % Processor Time if your SQL Server has more than 1 CPU. The reason for this is that if a process is only using a single thread it could be maxing out one of your CPUs at 100% while the other CPUs are not being utilized at all. To better illustrate this point we're going to use the test database 2008FeaturesDB that we created in Part 1 of our Backup Compression Mini Lab blog post:
The query we're going to use is the same query from Part 2 of our Backup Compression with Resource Governor Mini Lab blog post:
USE [2008FeaturesDB]
GO
DECLARE @LoopCounter int
SELECT @LoopCounter = 1000000
DECLARE @CustomerID int
WHILE @LoopCounter <> 0
BEGIN
SELECT @CustomerID = CustomerID
FROM dbo.Customers
WHERE FirstName like '%whatever%'
SELECT @LoopCounter = @LoopCounter -1
END
We're going to set our MAX Degree of Parallelism to 1. What this is going to do is on our test box (which has 4 CPUs) we're going to force SQL Server to only use 1 (out of the 4 available) CPUs. We're going to run our query and using Perfmon we're going to look at % Processor Time for the _Total instance:
This looks like we're seeing acceptable CPU utilization with our average being about 30%. If you were investigating a problem and saw this level of activity you might not think CPU is your performance bottleneck.
Now to illustrate the point of this post let's add % Processor Time for Instance 0 and see what we come up with:
I chose to highlight the 0 instance % Processor Time so that you can better see that it's pinned at 100% utilization. If we were not looking at the individual CPU instance's % Processor Time this processor being 100% utilized would not of been noticed/found.
The Best Practices for Running Dassault Enovia on SQL Server 2008 whitepaper provides a great table of some of the Performance Monitor counters that you would be interested in when monitoring the performance of your database server. There are a wealth of counters that you can track with Performance Monitor that can provide valuable insight into the performance of our database server, but it is important to know what the metrics are telling you so that you can take the appropriate action.
Please check out the Best Practices for Running Dassault Enovia on SQL Server 2008 whitepaper for more information
_____________________________________________________________________________________________