Solving SQL Server Issues: The Easy Pickings

There are several issues that can affect the health and stability of your SQL Server performance. Most can be easily resolved without an extreme technical intervention. Some can be resolved simply by adhering to good practices.

In our experience, there’s a great deal of low-hanging fruit when it comes to figuring out what’s wrong. We call these issues Easy Pickings because they’re easy to find and easy to fix. They typically are the most exposed problems with the easiest knobs and dials to tweak. Let’s pick some of that fruit!

Server Configurations

Server configurations and misconfigurations are the area you’ll want to check first. Some features have misleading names — they seem like they’ll help your server do more or run faster, but enabling them doesn’t always help performance.

Priority Boost is a great example. Enabling Priority Boost doesn’t help performance, but it commonly is enabled by well-meaning IT professionals. The default setting is 0, which is also the best option (if you need to change Priority Boost, a service restart may be needed).

Database configurations and misconfigurations can also be a source of problems for SQL Server. Page Verification became an obsolete feature as of SQL 2005, so CHECKSUM is a better option for checking for page-corruption issues. Using settings such as Auto-close or Auto-shrink can also cause problems, often with serious performance implications that can affect CPU and I/O, fragment database data, and cause the system to use unnecessary overhead.

When you have the configuration options on a SQL instance identified and set, you can use Policy Based Management as a tool to manage individual instances and ensure these configurations meet your standards. The tool lets a database administrator quickly compare server and database configurations against preset compliance thresholds. The evaluation results are easy to understand and act upon. In some cases it takes just a single operation to bring a configuration back to the proper, desired setting.

Backup Planning

Regular and reliable database backups are the foundation of database administration. Planning and implementing a backup plan can be a time-intensive, complex progress. But, with proper configuration, a good backup plan should run consistently and smoothly. There are only a few issues that commonly result in missing or dated backups, but these basic issues can sometimes be challenging to overcome.

For example, it’s helpful to reduce the time it takes to back up the database. You can delay backup verification for a separate time or reduce the job duration to eliminate potential backup failures. Having adequate disk capacity for the backup drive is also essential. There’s no good reason to have a backup failure as the result of insufficient free disk space.

Of course, there are plenty of other reasons for missing or dated backups, but having a simple SQL Maintenance Plan can prove a decent stopgap until a more detailed plan can be implemented.

More Issues to Consider

For a more comprehensive discussion of these and related SQL Server issues that can help you more fully leverage the time and resources invested in your SQL Server, please download our white paper written by Andy McDermid, Datavail’s SQL Server principal database administrator.

What kind of SQL Server challenges are you experiencing? Datavail’s experts can help by providing your organization with a comprehensive health check. This examines many of the issues discussed and provides diagnostic metrics for categories such as performance, availability, and security.

If you are interested in learning more, please contact Datavail.

Image by rmorijn/123RF.