Troubleshooting a Slow Database Server: Is SQL Server the Cause?
From an end user perspective, there’s no way of knowing where the slowdown is coming from. It could logically be CPU, memory, disk, network, or any of the more specific sub-components of each of those broad categories. So the first thing you need to do is to determine the offending component and then you can snipe the specific cause.
Let’s look at the easiest way to determine the over-arching cause of a system slowdown.
Open Perfmon (I’m assuming in this article that you already know the extreme basics of how to work Perfmon) and you’ll notice the following counters are already loaded into the collection:
- Avg. Disk Queue Length
- % Processor Time
I’m now going to talk about each one of these briefly to give you an idea of what to look for.
Pages/secThis is how much paging your system is doing in and out of virtual memory. If you’re doing a lot of paging then this could be an indication that you’ve got other applications on the box taking memory away from the database. There’s no real right answer on what this counter should be so you should have a baseline to tell you how it usually operates. It should be as close to 0 as possible though. It’s also notable that this counter alone won’t really do much for you if you’re on a dedicated database box as databases don’t tend to page much, if any at all. This is one reason why it’s good to know if your box is dedicated to SQL Server.
Avg. Disk Queue LengthThis counter can be misleading because while you may be looking at what seems to be a high number, it may be perfectly fine for your system. The general rule is that a disk queue should be the number of spindles x 2. OK, so what does that mean? I’ll give you a really simple example. Let’s say you have only a single disk on your server; just a single SCSI hard drive and nothing else. Your acceptable disk queue is 2 because you only have 1 spindle (hard drive) and you’re generally allowed a queue of 2 per spindle. So if you have a SAN with 50 spindles in your array, your acceptable disk queue is 100 (that’s 50 x 2, right?).
So to get any real use out of this counter you’ll need to know something about the underlying storage, or you’ll have to have a baseline. And again, I can’t stress the baseline enough because even though rule of thumb may allow you a disk queue of 100, if your system typically runs with a queue of 10-15, then 100 goes back to being unacceptable. There’s simply no substitute for having a picture of normal conditions on your box.
% Processor TimeThis counter can be taken at face value. If you’re running a high CPU, then it’s a pretty good indicator that CPU is the culprit. If this is the case then you’ll need to determine which process is using the lion’s share of the CPU though and that takes another counter. If you do determine you’re looking at a CPU issue, then you’ll need to add in the Process\% Processor Time counter and either add the counter for all processes (don’t use _Total) or pick the most likely suspects and keep adding them until you find the offender. If sqlserver.exe is the clear offender, then you can now either get your DBA involved or move to the internal SQL Server methods for troubleshooting performance issues. And again, it’s best to have a baseline. If your database is pegging the CPU at 93%, but it’s usually at 90-95%, then CPU probably isn’t your culprit because you’re operating within normal parameters. So know what your system looks like when it’s running normally.
NetworkingThe reason why we’re not really worried about monitoring networking counters is because the only counters that would be of use to us are the internal windows and NIC counters, and these are rarely at fault. If there is a network issue, it’s typically in the network itself which you can’t diagnose with any of these counters. And while this happens from time to time, it’s really something you’ll check in round 4 or 5 of your troubleshooting and isn’t really interesting to us here in round 1.
These are the very basics of how to determine whether your database is causing your system slowdown. And for every rule I gave above I can think of a handful of exceptions so nothing is written in stone. You can use Task Manager if you like, but any questions that surface force you to open up Perfmon anyway, so you’d might as well start there. Perfmon also allows you to save counter collections so you don’t have to redefine your measures every time.