SQL Server 2005 Database Options and Configuration Performance Audit Checklist

By : Brad McGehee Sep 20, 2007

Printer friendly

Database Options and Configuration Settings

Enter your results in the table above. 

Every Database Needs to Be Audited

. When compared to some of our other performance audit tasks, you will find this audit task one of the easiest. For convenience, you may want to photocopy the above chart, producing one copy for each database that you will be auditing.

As a part of our database settings audit, we will be taking a look at two different types of settings: database options and database configuration settings. As in previous sections of our performance audit, we will only focus on those database options and settings that are directly related to performance, ignoring the rest.

Both database options and database configuration settings can be viewed or modified using Management Studio or modified with the ALTER DATABASE command.

The first section of the database settings performance checklist focuses on database options, and the second section focuses on database configuration settings. While similarly related, they are viewed and changed in different ways, as explained below.

Viewing Database Options

There are two main ways to view the various database options for each database on a single SQL Server instance, neither is ideal.

One option is to run the select all the rows from the sys.databases management view:

SELECT * FROM sys.databases

This provides an output similar to this:

The above output has been severely truncated because it is so wide. In addition, the data in the columns are often difficult to interpret, unless you have a lot of codes memorized.

Generally, it is easier to view database options using Management Studio. If you go to any database in Management Studio, right-click on it, and then select Properties|Options, and you see a screen like the one below, which is much easier to read, even though you will have to scroll through the window to view all the options.

Let's take a look at the performance-related database options and see how they affect SQL Server's performance. Not all database options affect performance, so many of them are skipped as part of this performance audit.

Auto_Close

This database option is designed for use with the SQL Server 2005 Express Edition, not for the other editions of SQL Server. Because of this, it should not be turned on (which it is not, by default). What this option does is to close the database when the last database user disconnects from the database. When a new connection requests access to the database after it has been closed, then the database has to be reopened, which takes time and overhead, hurting performance.

As part of your audit, if you find this option turned on, and you are not using SQL Server 2005 Express, then you will need to research why it was turned on. If you can't find the reason, or if the reason is poor, turn this option off.

Auto_Create_Statistics

When auto_create_statistics is turned on (which it is by default), statistics are automatically created on all columns used in the WHERE clause of a query. This occurs when a query is optimized by the Query Optimizer for the first time, assuming the column doesn't already have statistics created for it. The addition of column statistics can greatly aid the Query Optimizer so that it can help create an optimum execution plan for the query.

If this option is turned off, then missing column statistics are not automatically created, when can mean that the Query Optimizer may not be able to produce the optimum execution plan for the query, and the query's performance may suffer. You can still manually create column statistics if you like, even when this option is turned off.

There is really no down-side to using this option. The very first time that column statistics are created, there will be a short delay as they are created before the query runs for the first time, causing the query to potentially take a little longer to run. But once the column statistics have been created, each time the same query runs, it should now run more efficiently than if the statistics did not exist in the first place.

As part of your audit, if you find this option turned off, you will need to research why it was turned off. If you can't find the reason, or if the reason is poor, turn this option on.

Auto_Update_Statistics

In order for the Query Optimizer to make smart query optimization decisions, column and index statistics need to be up-to-date. The best way to ensure this is to leave the auto_update_statistics database option on (the default setting). This helps to ensure that the optimizer statistics are valid, helping to ensure that queries are properly optimized when they are run.

But this option is not a panacea. When a SQL Server database is under very heavy load, sometimes the auto_update_statistics feature can update the statistics on large tables at inappropriate times, such as the busiest time of the day.

If you find that the auto_update_statistics feature is running at inappropriate times, you may want to turn it off, and then manually update the statistics (using UPDATE STATISTICS) when the database is under a less heavy load.

But again, consider what will happen if you do turn off the auto_update_statistics feature. While turning this feature off may reduce some stress on your server by not running at inappropriate times of the day, it could also cause some of your queries not to be properly optimized, which could also put extra stress on your server during busy times.

On the other hand, sometimes the auto_update_statistics feature doesn’t update statistics often enough, causing inefficient execution plans to be created. If this is the case, you may want to update statistics more often, perhaps nightly, or on some other more frequent schedule. When you manually update statistics, you don’t have to update every table in your database. You can pick and choose those tables with keeping current statistics updated is a problem.

Like many optimization issues, you will probably need to experiment to see if turning this option on or off is more effective for your environment. But as a rule of thumb, if your server is not maxed out, then leaving this option on is probably the best decision.