Four Resource Bottlenecks to Monitor in SQL Server 2008 for Better Performance

Looking to improve Microsoft SQL Server performance? I've found that resource bottlenecks are often the the most common issues with SQL Server 2008 performance. You can monitor SQL Server performance with a range of tools built into the server. In my experience four main culprits are often the key to finding, monitoring and resolving SQL Server 2008 performance issues.

1. CPU Bottlenecks
Monitoring the CPU load can identify systems that are over worked. Generally, when a processor sustains a rate above 80% the condition should be evaluated and the usage reduced. While you can buy more hardware, you should also look at the queries consuming the most load and attempt to optimize CPU consumption.

Metrics to monitor:
Processor:% Processor Time: Sustained above 80% indicates a problem

2. Memory Bottlenecks
There are multiple ways in SQL server and the base OS to use or reserve memory. It is important to monitor the overall physical and virtual memory to ensure it is not fully allocated. When the memory is fully utilized, your system works harder to move items around and is less efficient, resulting in a slower system.

Metrics to monitor:
Memory: Available MBytes: less than 50-100 likely indicates a problem but, you may need to see how your local system responds in relationship to the available memory for a more precise number
Monitor the windows event log for errors that indicate the virtual memory has run low

3. Disk I/O Constraints
The SQL server reads and writes to the database on a regular basis. A slow response during processing can result in decreased SQL performance. Improving the disk I/O with hardware is one solution, but you should also ensure that memory problems are not making the problem worse. In addition, consider data compression strategies and review query plans for missing indexes with the database tuning advisor to improve performance.

Metrics to monitor:
PhysicalDisk Object: Avg. Disk Queue: When operating regularly above 2 this indicates an I/O bottleneck
Avg. Disk Sec/Read & Avg. Disk Sec/write: Less than 20ms is normally fine, but beyond 30 is likely to cause slowdowns.
Physical Disk: %Disk Time: Numbers above 50% indicate an I/O bottleneck

4. TempDB Issues
The tempDB provides a storage place for objects, tables and stored procedures. The tempDB can affect both performance and disk space usage which can reduce the efficiency of the SQL Server and any other applications running on the same server.

Metrics to monitor:
Space used: Ensure this does not exceed 80% utilization.
Free Space in tempdb: Monitor and evaluate the proper levels for baseline operations.

I've found that monitoring these four common resource issues can help troubleshoot and resolve many common SQL Server 2008 bottlenecks. If you don't have time to use a range of tools to monitor all these metrics, consider a solution that provides an integrated view of all the SQL Server metrics that you need. dopplerVUE is a network management solution with an SQL application center that displays system responsiveness, application services, server and application utilization and alarm conditions all in a single window.