SQL Server 2005 Database Options and Configuration Performance Audit Checklist

Database Options and Configuration Settings

Database Configuration Settings	Default Value	Current Value
auto_close	off
auto_create_statistics	on
auto_update_statistics	on
auto_update_statistics_asynch	off
auto_shrink	off
read_only	off
page_verify	checksum
parameterization	simple
compatibility level	90
database auto grow	on
transaction log auto grow	on

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.

Auto_Update_Statistics_Asynch

You are familiar with the AUTO_UPDATE_STATISTICS database option just discussed. Under most conditions, this feature provides the query optimizer with up-to-date index and column statistics so that optimum query plans can be created to execute queries. But not always.

In SQL Server 2005, there is a new feature which can overcome some of the potential negatives of using this database option. There is a new database is called AUTO_UPDATE_STATISTICS_ASYNC. If this option is turned on for a database, and the AUTO_UPDATE_STATISTICS option is also turned on for the database, then the behavior of the statistics updating process changes. Now, instead of the query having to wait until the statistics are updated to execute (the default behavior), the query runs immediately using the old statistics and query plan. And in the background, a thread is spawned that performs the actual statistics update. When the statistics have been updated, they are now available the next time a query is run that needs them.

What this accomplishes is to make the performance of affected queries more predictable, and potentially eliminate application timeouts due to long running statistics updates. But, keep this in mind; it is also possible that this could make the query run even longer, not shorter? What? For example, if the rows in the table have changed so much that statistics need to be updated, then it is possible that using the old statistics and old query plan to run the query now, instead of waiting, could cause the query to take much more time because the old query plan and statistics are way too out of date, and a new, more efficient query plan, based on the new statistics, is needed.

This is virtually impossible to predict. Because of the potential possibility that outdated statistics and query plans could make queries run slower, Microsoft only recommends using AUTO_UPDATE_STATISTICS_ASYNC under two conditions:

• When it is more critical that queries perform with a predictable response time than to run less efficient query plans.
• When you have an application that times out because of waiting for statistics to be automatically updated.

If you don’t experience either of the above, then you should not use the AUTO_UPDATE_STATISTICS_ASYNC option.

The AUTO_UPDATE_STATISTICS_ASYNC database option, like so many options in SQL Server, have trade-offs. If you think this option could help you out in a particular situation, test it first on a test system. You don’t want to experiment on your production system.

Auto_Shrink

Some databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don't be tempted to use the auto_shrink database option to accomplish this task, as it can waste SQL Server resources unnecessarily.

By default, the auto_shrink option is turned off, which means that the only way to free up empty space in a database is to do so manually. If you turn this option on, SQL Server will check every 30 minutes to see if it needs to shrink the database. Not only does this use up resources that could better be used elsewhere, it also can cause unexpected bottlenecks in your database when the auto_shrink process kicks in and does its work at the worst possible time.

If you need to shrink databases periodically, perform this step manually using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, or you can use the SQL Server Agent, or create a Database Maintenance Plan, to schedule regular file shrinking during less busy times.

As part of your audit, if you find this option turned on, 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 off.

Compatibility Level

SQL Server 2005 has a database compatibility mode that allows applications written for previous versions of SQL Server to continue to run, even though SQL Server 2005 may have deprecated some of the functionality of the older versions. In you want maximum performance for your databases, you don't want to run your databases in compatibility mode, as not all new performance-related features are supported in compatibility mode.

Instead, your databases should be running in native SQL Server 2005. Of course, this may require you to modify your older application to make it SQL Server 2005 compliant, but in most cases, the additional work required to update your application will be more than paid for with improved performance.

SQL Server 7.0 compatibility level is referred to as "70". SQL Server 2000 compatibility level is referred to as "80". And SQL Server 2005 native mode is referred to as “90.” If you upgrade a database from a previous version of SQL Server, SQL Server 2005 will keep the database in the mode of the previous edition of SQL Server. Because of this, be sure to check to see what compatibility version your databases are currently running in under SQL Server 2005. If they are not running in “90” mode, test them to see if they will successfully run under “90” mode, and then change the setting appropriately.

Read_Only

If a database will be used for read-only purposes only, such as being used for reporting, consider setting the read_only setting on (the default setting is off). This will eliminate the overhead of locking in the database, and in turn, potentially boost the performance of queries that are being run against it. If you need to modify the database on rare occasions, you can turn the setting off, make your change, then turn it back on.

Page_Verify

Because data pages in SQL Server (8K) and Windows Server (512 bytes) are different sizes, it is possible during power failures, or if you are have disk driver or physical disk problems, for your database files to become physically corrupted.

Here's why. Every time the operating system writes an 8K SQL Server data page to disk, it must break up the data into multiple 512 byte pages to be stored by the OS on disk. After the first 512 byte of data is written, SQL Server assumes that the entire 8K has been written to disk successfully. So if a problem should occur before all of the 512 byte pages that make up the 8K SQL Server page are written, then SQL Server does not know what has happened. This is known as a torn page.

As you can imagine, this corrupts the data page, and in effect makes your entire database corrupt. There is no way to fix a database made corrupt due to a torn page, except by restoring a known good backup. One of the best ways to prevent this problem is to ensure your server has battery backup. But this does not prevent all problems, because a defective disk driver can also cause similar problems (I have seen this.)

If you are worried about getting torn pages in your SQL Server databases, you can have SQL Server tell you if they occur (although it can't prevent them or fix them after they have occurred).

In SQL Server 2005, there is a new option called Page_Verify. In earlier versions of SQL Server, it was known as TORN_PAGE_DETECTION.

Page_Verify has three options you can set: NONE, CHECKSUM, and TORN_PAGE_DETECTION.

When NONE is specified, database page writes will not perform a CHECKSUM or TORN_PAGE_DETECTION. This offers the least protection, but the highest performance.

When CHECKSUM is specified, the Database Engine calculates a checksum over the contents of each page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recalculated and compared to the original checksum value. This option, which is the default option, offers the best tradeoff in protection and performance.

If the option TORN_PAGE_DETECTION is selected, a specific bit for each 512 byte sector in the 8 KB database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the bits stored in the page header and are compared. This option, offers the best protection, but the least performance.

As a DBA, you will have to decide for yourself which option best fits your needs for data integrity and performance.

Parameterization

One of the main benefits of using a stored procedure to execute Transact-SQL code is that once a stored procedure is compiled and executed the first time, the query plan is cached by SQL Server. So the next time the same stored procedure is run (assuming the same connection parameters are used), SQL Server does not have to recompile the stored procedure again, instead reusing the query plan created during the first compilation of the stored procedure. If the same stored procedure is called over and over again, with the query plan being reused each time, this can help reduce the burden on SQL Server’s resources, boosting its overall performance.

But as we all know, not all SQL Server-based applications use stored procedures to communicate with SQL Server. Many times the Transact-SQL is sent from the application to SQL Server in the form of a static or dynamic statement. Because SQL Server is pretty smart, by default, it has the ability to do something called simple parameterization.

Essentially, simple parameterization means that SQL Server can take a look at the static or dynamic Transact-SQL being sent to it from an application, and if it finds any values that it considers to be a parameter, it will parameterize the Transact-SQL, which allows the resulting query plan to be reused, much like how SQL Server can reuse the query plans of stored procedures

But what if your application uses mostly complex queries, queries that cannot be automatically parameterized by SQL Server using simple parameterization? This is where a new feature of SQL Server 2005 comes to the rescue. This new feature is called forced parameterization. When forced parameterization is turned on, it tells SQL Server to force the parameterization of virtually all SELECT, INSERT, UPDATE, and DELETE statements. There are a few types of queries that cannot be forced, but the exceptions are few.

With forced parameterization turned on, SQL Server will perform fewer compilations of statements because it now has the ability to re-use more query plans that before, helping to reduce resource usage and boosting performance.

But I know what you are thinking, there is no such thing as a free lunch. Am I right? And you are right. Like most aspects of SQL Server performance tuning, there are tradeoffs to be considered.

Here are some of those trade-offs:

• Since SQL Server has to force parameterization on virtually all statements, it has to perform a little extra more work up front to perform the parameterization.
• Some queries, that have widely changing parameters, may end up using inappropriate query plans, reducing performance.
• Because literal constants in a query are changed to parameters, the Query Optimizer might not always choose the best plan, reducing performance.
• The Query Optimizer may not choose the ideal plan for queries on partitioned tables and distributed partitioned views.
• The Query Optimizer is less likely to match a query to an index view or an index on a computed column.

Essentially, Microsoft recommends that using forced parameterization is best used in limited situations, such as those applications that experience high volumes of concurrent queries that use static or dynamic Transact-SQL, and not stored procedures. This is the situation that best lends itself to forced parameterization. If your application does not fall into this category, and you turn forced parameterization on, it is very possible that the performance could get worse.

Forced parameterization is turned on and off at the database level. In other words, it is an all or nothing setting. Either all the static and dynamic Transact-SQL uses simple parameterization or forced parameterization.


Viewing Database Configuration Settings

In this section, we will only be taking a look at two database configuration settings and examine how they can affect performance. The best way to view these is to use Management Studio, following these steps. Right-click on the database you want to check out, and then Properties|Files, you see a screen like this one.

What we want to check out is the Autogrowth settings for the data and log files.

We will be discussing both database auto grow and transaction log auto grow together because they are so closely related.

If your set your SQL Server 2005 databases and transaction logs to grow automatically (which is the default setting), keep in mind that every time this feature kicks in, it takes up a little extra CPU and I/O time. Ideally, we want to minimize how often automatic growth occurs in order to reduce unnecessary overhead.

One way to help do this is to size the database and transaction logs as accurately as possible to their "final" size when they are first created. Sure, this is virtually impossible to get right-on-target. But the more accurate your estimates, the less SQL Server will have to automatically grow its database and transaction logs, helping to boost performance of SQL Server.

This recommendation in particular is important to follow for transaction logs. This is because the more times that SQL Server has to increase the size of a transaction log, the more transaction log virtual files that have to be created and maintained by SQL Server, which increases recovery time, should your transactions log need to be restored. A transaction virtual file is used by SQL Server to internally divide and manage the physical transaction log file.

The default growth amount is 10% for databases and transaction logs. This automatic growth number may or may not be ideal for your database or transaction log. If you find that your database or log is growing automatically often (such as daily or several times a week), change the growth percentage to a larger number, such as 20% or 30%. Each time the database or log has to be increased, SQL Server will suffer a small performance hit. By increasing the amount the database grows each time, the less often it will have to grow. Another option is to manually increase the file sizes during less busy times of the day.

If your database is very large, 10GB or larger, you may want to use a fixed growth amount instead of a percentage growth amount. This is because a percentage growth amount can be large on a large database. For example, a 10% growth rate on a 10GB database means that when the database grows, it will increase by 1GB. This may or may not be what you want. If this is more than you want, then choose a fixed growth rate, such as 100MB at a time, might be more appropriate.

As part of your audit, you will need to carefully evaluate your databases to see how the above advice applies to them, then take the appropriate action.

Now What?

Your goal should be to perform this part of the performance audit, as described on this page, for each of the databases in each of your SQL Servers, and then use this information to make changes as appropriate, assuming you can.