Select Page

Solving SQL Server Issues: The Easy Pickings

Andy McDermid | | October 2, 2014

Easy PicksThere 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.

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
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017

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