In my previous tip, Tuning SQL Server performance via disk arrays and disk partitioning, we talked about how important it is to ensure that your storage was set up correctly to optimize SQL Server performance. However, storage isn't the only part of SQL Server hardware that needs special consideration when designing your infrastructure.
SQL Server memory can also impact performance. While having too much memory in a SQL Server system is a waste of money, having too little memory is extremely detrimental to performance. Unfortunately, determining when you need more memory in the system can be a bit tricky. When memory problems begin, you'll start to see an increase in disk I/O, as well as an increase in disk queuing. You'll also see a decrease in the buffer cache hit ratio and page life expectancy. As memory requirements increase, you may begin to see these error messages in the log file:
A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: %n seconds. Working set (KB): %w, committed (KB): %c, memory utilization: %u.
SQL Server has encountered %o occurrence(s) of IO requests taking longer than 15 seconds to complete on file [%f] in database [%d] (%i). The OS file handle is %h. The offset of the latest long IO is: %l.
Unfortunately, this is not the only time these errors are reported, so you have to use them along with the performance monitor metrics to determine that memory is actually low.
When dealing with SQL Server memory issues, there are a few options to resolve the problems. The easiest solution is to increase server memory, which increases the amount of buffer
cache available. This adds to the amount of data in memory and reduces your disk I/O. Other potential solutions include removing clustered indexes for extremely large tables and using only nonclustered indexes for the table, including the Primary Key.
This will only make a difference when the clustered index is being used for lookups, and clustered index seeks are used. If another index is in use, it will not relieve any memory pressure, as the clustered index won't be in memory. If you're using clustered index scans, then this turns into table scans that load the table into memory instead of the index. If clustered index scans are being performed, then a new nonclustered index may help the situation without removing the index.
How to monitor CPU queuing
The CPU is another piece of hardware that can cause potential performance problems. Most people only look at the speed of or number of CPUs. However, just like disks, CPUs can become bottlenecked. If there is a CPU bottleneck, you may not even see the CPU performance at 100%. CPUs have command queues in much the same way that disks have I/O queues. Commands are loaded into a CPU queue and the operation waits for the CPU to become available before performing the operation. As CPUs became faster, we could do things much faster within the CPU, but we could still only do the same number of things at one time. Now, as dual-core, tri-core and quad-core CPUs become available, we can process more commands at one time.
You can monitor your CPU queue using SQL Server Performance Monitor. You'll find PerfMon under the System object, with the counter name "Processor Queue Length." Pretty much any queue length other than zero indicates a need to increase the number of operations that SQL Server can perform at any one time. It doesn't indicate a need for faster CPUs, but a need for more CPU cores. Today's newest servers support 32 cores per server, and some of the most advanced servers support up to 64 cores -- when chases are scaled together support for 64 cores can be built (available only from certain vendors).
In parts one and two, I've pointed out a variety of places within the hardware that impact whether your SQL Server system will run at peak performance. These tips are not the be-all, end-all solutions to performance problems. Table design and index tuning always have been and will continue to be extremely important. Today's SQL Server is expected to do more work for more hours of the day, which makes hardware tuning more important to the success of the database platform. With these tools in your arsenal to combat performance problems, you'll be able to get every ounce of performance from the existing hardware with no or minimal hardware upgrades to the platform. But when you do need to make those purchasing decisions, use these tips to make the correct purchasing decisions to get the most upgrade for your dollars spent.
| ABOUT THE AUTHOR: |
|
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Check out his blog:
SQL Server with Mr. Denny
|
|
