Select Page

Solving SQL Server Issues: The Best Bang for the Buck

Andy McDermid | | October 6, 2014

Best BangThere 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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Tips for Upgrading From SQL 2008 to 2012 or 2014

It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.

Andy McDermid | April 8, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS