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
% 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
DECLARE @LoopCounter int
SELECT @LoopCounter = 1000000
DECLARE @CustomerID int
WHILE @LoopCounter <> 0
SELECT @CustomerID = CustomerID
WHERE FirstName like '%whatever%'
SELECT @LoopCounter = @LoopCounter -1
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
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.
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.