Database Configuration Settings Performance Checklist

Compatibility Level

SQL Server 7.0 and 2000 have a database compatibility mode that allows applications written for previous versions of SQL Server to run under SQL Server 7.0 or 2000. In you want maximum performance for your database, you don't want to run your database in compatibility mode (not all new performance-related features are supported).

Instead, your databases should be running in native SQL Server 7.0 or 2000 mode (depending on which version you are currently running). Of course, this may require you to modify your application to make it SQL Server 7.0 or 2000 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" and SQL Server 2000 compatibility level is referred to as "80".

Database and Transaction Log Auto Grow

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 7.0 or SQL 2000 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. Sure, this is virtually impossible to get right-on-target. But the more accurate your estimates (and some times it takes some time to come up with a good estimate), the less SQL Server will have to automatically grow its database and transaction logs, helping to boost performance of your application.

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.

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, 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.

Once you have completed this part of the performance audit, you are now ready to audit the use of indexes in your databases.