Article Topics

All Articles

Peformance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...

How to Use System Monitor to Identify SQL Server Hardware Bottlenecks

By : Brad McGehee
Feb 20, 2008

Printer friendly

Performance Audit Checklist

Counter Name	Average	Minimum	Maximum
Memory: Pages/sec
Memory: Available Bytes
Physical Disk: % Disk time
Physical Disk: Avg. Disk Queue Length
Processor: % Processor Time
System: Processor Queue Length
SQL Server Buffer: Buffer Cache Hit Ratio
SQL Server General: User Connections

Enter your results in the table above.

Use Performance Monitor to Help Identify SQL Server Hardware Bottlenecks
In the past several articles, we have essentially taken an inventory of how your SQL Server’s hardware, operating system, SQL Server configuration, and database configuration are set up. These have been easy tasks, as all we essentially had to do is to look at the data and record it.

In this article, we will begin our first “investigative” work. We will be using System Monitor (also called Performance Monitor) to help us identify potential hardware bottlenecks. By monitoring a few key counters over a 24-hour period, you should get a good feel for any major hardware bottlenecks your SQL Server is experiencing. By identifying hardware bottlenecks, we will be in a better position to identify potentially obvious performance problems.

Ideally, you should use System Monitor to create a log of key counters for a period of 24 hours. You will want to select a "typical" 24 hour period when it comes to deciding when to create your System Monitor log. For example, pick a typical business day, not a weekend or holiday.

Once you have captured 24 hours of System Monitor data in a log, display the recommended counters in the Graph mode of System Monitor, and then record the average, minimum, and maximum values in the table above. Once you have done this, then compare your results with the analysis below. By comparing your results with the recommendations below, you should be able to quickly identify any potential hardware bottlenecks your SQL Server is experiencing.

How to Interpret Key System Monitor Counters
Below is a discussions of the various key System Monitor counters, their recommended values, and some options for helping to identify and resolve the hardware bottlenecks. Note that I have limited the number of System Monitor counters to watch. I have done so because our goal in this article is to find the easy and obvious performance problems. Many other System Monitor counters can be found discussed elsewhere on this website.

Memory: Pages/sec
This counter measures the number of pages per second that are paged out of RAM to disk, or paged into RAM from disk. The more paging that occurs, the more I/O overhead your server experiences, which in turn can decrease the performance of SQL Server. Your goal is to try to keep paging to a minimum, not to eliminate it.

Assuming that SQL Server is the only major application running on your server, then this figure should ideally average between zero and 20. You will most likely see spikes much greater than 20, which is normal. They key here is keeping the average pages per second less than 20.

If your server is averaging more than 20 pages per second, one of the more likely causes of this is a memory bottleneck due to a lack of needed RAM. Generally speaking, the more RAM a server has, the less paging it has to perform.

In most cases, on a physical server dedicated to SQL Server with an adequate amount of RAM, paging will average less than 20. An adequate amount of RAM for SQL Server is a server that has a Buffer Hit Cache Ratio (described in more detail later) of 99% and higher. If you have a SQL Server that has a Buffer Hit Cache Ratio of 99% or higher for a period of 24 hours, but you are getting an average paging level of over 20 during this same time period, this may indicate that you are running other applications on the physical server other than SQL Server. If this is the case, you should ideally remove those applications, allowing SQL Server to be the only major application on the physical server.

If your SQL Server is not running any other applications, and paging exceeds 20 on average for a 24-hour period, this may mean that you have changed the SQL Server memory settings. SQL Server should generally be configured so that it is set to the "Dynamically configure SQL Server memory" option, and the "Maximum Memory" setting should be set to the highest level. For optimum performance, SQL Server should be allowed to take as much RAM as it wants for its own use without having to compete for RAM with other applications. The above recommendation assumes that you are not using AWE memory. If you use AWE memory, then you will have to manually set the amount of memory used by SQL Server, as described elsewhere on this website.

Memory: Available Bytes
Another way to check to see if your SQL Server has enough physical RAM is to check the Memory Object: Available Bytes counter. This value should be greater than 5MB. If not, then your SQL Server needs more physical RAM. On a server dedicated to SQL Server, SQL Server attempts to maintain from 5-10MB of free physical memory. The remaining physical RAM is used by the operating system and SQL Server. When the amount of available bytes is near 5MB, or lower, most likely SQL Server is experiencing a performance hit due to lack of memory. When this happens, you either need to increase the amount of physical RAM in the server, reduce the load on the server, or change your SQL Server's memory configuration settings appropriately.

Next Page>>