Solving SQL Server Issues: The Best Bang for the Buck

There are many issues that can affect the health and stability of your SQL Server performance. Most can be easily resolved without extreme technical intervention. Some issues require a bit of evaluation and analysis to resolve.

Some configurations have measurable effects on performance – these may be specific to the operating system, SQL instance, or the database. Because these adjustments result in a significant return on investment, providing stability and performance through simple tuning, we think these solutions give you the Best Bang for the Buck.

First Steps

Some of the first configurations you’ll want to check are for both the Server Minimum and Maximum memory. The default configurations are not ideal for use with production SQL instances. The Maximum Memory value, for example, controls the size of the buffer pool where the data pages reside. To improve performance, it’s important to allow SQL Server as much of the available RAM as possible so that disk I/O is minimized. At the same time, the Maximum Memory needs to be capped in order to reserve some resources for the operating system and other applications on the server.

Microsoft SQL Server MVP Jonathan Kehayias suggests reserving 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4 to 16 GB, and 1 GB for every 8 GB RAM installed in configurations with more than 16 GB RAM. Remaining memory can be assigned for use by SQL with the Maximum Memory setting.

You also want to prevent other processes from poaching SQL memory. One option is to Make sure the Minimum Memory value is equal to the Maximum Memory value. This effectively makes the buffer pool’s RAM usage static.

To best monitor SQL memory usage, you can use the perfmon counters Total Server Memory (KB) and Target Server Memory (KB).

Index Maintenance

Index maintenance is another area that yields nice performance results with a little regular maintenance. You’ll want to make certain that indexes operate efficiently using a high use count per used-disk space ratio. That is, the more indexes used for a given size of indexes on disk, the more efficient the overall index layout will be.

The biggest drag on performance results from a combination of duplicate, overlapping, and unused indexes. For example, if there’s one index accessed for a regular but infrequent query, it may be prudent to disable the index until it is needed.

Another thing to check is whether statistics are maintained and regularly updated. If they are not, queries may have issues. You want to use Auto Update Statistics. But by itself that may not be enough, for very large tables manual statistics updates are required. It’s a great idea to incorporate statistics into your general maintenance plan.

How Can Datavail Help?

What SQL Server performance challenges are you experiencing? Our experts can help by providing your organization with a comprehensive health check that examines many of the issues discussed here. The Datavail Health Check provides clients with an actionable tool they can use within their enterprise.

If you are interested in learning more about performance tuning or optimization as well as our health checks, please contact Datavail.

To help you more fully leverage the time and resources invested in your SQL Server, please download our white paper written by Andy McDermid, SQL Server principal database administrator. In this paper, these and other SQL Server issues are discussed in more detail.

Image by funniefarm5/123RF.