A good approach to use when investigating a SQL Server performance problem is to start at a high level and then zoom in on the problem area. If you start your investigation with these 4 counters you will have a good idea of whether the problem is memory, disk, or CPU.
SQL Server:Buffer Manager Page Life Expectancy:
SQL Server:Buffer Manager Page Life Expectancy
Even when it isn’t doing anything, SQL Server will consume most memory. Look at “Page life expectancy” to know if SQL Server has a memory problem.
A lot of novices will monitor SQL Server’s memory use by looking at the amount of RAM SQL Server (sqlservr.exe) is using. The problem with this is that SQL Server will slowly ramp up its memory use to the maximum amount it has been configured to use and never release it. So if you didn’t know this, you might think that SQL Server has a memory leak. Even if SQL Server has very little load, it will continue to use most of the memory on the server.
Page life expectancy (PLE) allows you to look inside SQL Servers memory buffer pool and see if it has internal memory pressure or not. The counter will tell you how many seconds SQL Server is able to keep a page of data (or 8KB) in memory. If there is memory pressure, then it will constantly need to free pages to read in more data, so the lower this number is, the more internal memory pressure SQL Server has. The generally accepted threshold for PLE is 300 – meaning that it should not be below 300 for extended periods.
Memory: Available Mbytes
Memory: Available Mbytes
This counter shows you how many megabytes of memory are free on the server. Generally speaking you should always have more than 1GB of memory free but it really depends on what other programs are running on the server and the total memory that is installed. If there is not enough free memory then Windows could start paging out SQL Server’s memory which is disastrous for performance. Depending on your version of SQL Server you will also see the following message in the SQL Server errorlog: “A significant part of SQL Server process memory has been paged out. This may result in performance degradation.”
PhysicalDisk: Avg. Disk Sec/Read and Write:
PhysicalDisk: Avg. Disk Sec/Read and Write
This is sometimes referred to as disk latency. It measures the amount of time that it takes to write or read data from disk or perform a single “I/O”. It is good to cross check with the counter “Disk: Avg. Disk Bytes/Read” and “Avg. Disk Bytes/Write” because the size of a disk read or write can have a direct impact on the time it takes to do the read or write. The general recommendations on this counter is that it should be lower than 20 milliseconds. If you are seeing high numbers for this counter then it could mean that your disks are a bottleneck.
Processor: %Processor Time
Processor: %Processor Time
If you are seeing extended periods where this counter is above 80% then CPU can be a bottleneck on your server. The first thing to check is whether SQL Server is using the CPU or if it is another process. You can do this by checking the counter Process: % Processor Time for the sqlservr instance. I haven’t come across this problem nearly as much as I have come across low PLE or high disk latency
Conclusion
The performance counters above are a good start from where you can decide to start drilling down to other ones. For example, if % Processor Time is high, then you should look if the SQL Server executable is using the CPU and if this is the case then you could look at the amount of re-compiles that are being done by SQL Server.
Where this can get tricky is to know how all the pieces fit together. For example, you may see that the disk latency is high and think that you have a disk bottleneck. But what could also be happening is that the OS has paged out SQL Server’s memory to disk and that the high disk latency is just a symptom of a memory shortage. So use these 4 counters as a starting point but don’t jump to conclusions without doing more investigation.