SQL Server Hardware Tuning and Performance Monitoring

Once we have tuned the SQL Server memory settings, it is a good idea to decide if you want SQL Server 7/2000 to tune the process memory automatically, or manually configure memory usage. For better performance, you can lock the amount of working set memory that SQL Server reserves. The trade-off here is that you may receive out of memory messages from other applications on the same server. If you do decide to fix the amount of working set memory, two configuration settings are necessary. First, equalize the MIN SERVER MEMORY and MAX SERVER MEMORY settings. Then turn on the SET WORKING SET SIZE configuration flag using sp_configure. MAX SERVER MEMORY should not exceed the RAM available for the server.

Due to the dynamic nature of the memory in SQL Server 7.0 and SQL server 2000, Microsoft removed support for one of the most useful tools available in SQL Server 6.5, DBCC MEMUSAGE. Now, to get this information, you have to monitor a variety of performance counters in the Buffer Manager object and the Cache Manager object since the information is no longer statically mapped in a table like sysconfigures.

While this handy tool is listed as unsupported and no longer returns the breakdown of memory usage, go ahead and try running it. You will see that this unsupported snapshot continues to return a top 20 list of buffered tables and indexes. When analyzing performance of an individual application in a test environment, it can be invaluable.

SQL Server Process Memory Tuning

Once you have gotten the overall OS and SQL server memory tuned, look further at the SQL Server memory usage. Four counters are desirable here:

• Process: Working Set:sqlserver
  
   
• SQL Server: Buffer Manager: Buffer Cache Hit Ratio
  
   
• SQL Server: Buffer Manager: Free Buffers
  
   
• SQL Server: Memory Manager: Total Server Memory (KB)

The process: Working Set:sqlserver instance shows the amount of memory that SQL Server is using. If the number is consistently lower than the amount SQL Server is configured to use by the MIN SERVER MEMORY and MAX SERVER MEMORY options, then SQL Server is configured for too much memory. Otherwise, you may need to increase RAM and MAX SERVER MEMORY.

Buffer Cache Hit Ratio should be consistently greater than 90. This indicates that the data cache supplied 90 per cent of the requests for data. If this value is consistently low, it is a very good indicator that more memory is needed by SQL Server. If Available Bytes is low, this means that we need to add more RAM.

When Free Buffers is low, this means that there is not enough RAM to maintain a consistent amount of data cache. It too is indicative of a need for more memory.

If Total Server Memory for SQL Server is consistently higher than the overall server memory, it indicates that there is not enough RAM.

CPU Performance Monitoring

In CPU performance monitoring, we are going to be using several counters:

• Processor: Percent Processor Time
  
   
• Processor: Percent Privileged Time
  
   
• Processor: Percent User Time
  
   
• System: Percent Total Processor Time

Generally, CPU performance monitoring is straightforward. You need to start by monitoring Processor: Percent Processor Time. If you have more than one processor, you should monitor each instance of this counter and also monitor System: Percent Total Processor Time to determine the average for all processors.

Utilization rates consistently above 80-90 percent may indicate a poorly-tuned or designed application. On the other hand, if you have put all the other recommendations of this article into use, they may indicate a need for a more powerful CPU subsystem. In general, I would spend a little bit of time analyzing the applications before immediately going out and buying more processors. Spending this time experimenting, to discover CPU performance problems and correcting them through software improvements, will often keep you from just spending money on a more powerful CPU that only covers up poorly written software for little or no time.

If you do see high CPU utilization, you will then want to monitor Processor: Percent Privileged Time. This is the time spent performing kernel level operations, such as disk I/O. If his counter is consistently above 80-90 per cent and corresponds to high disk performance counters, you may have a disk bottleneck rather than a CPU bottleneck.