Database Optimization Tips

Database Settings Optimization Tips

*****

You can turn off the 'auto create statistics' database option.
When this database option is set to true, statistics are automatically created on columns used in a predicate. By default, this database option is set to true. Because auto creation statistics results in some performance degradation, you can turn off this database option and create statistics manually during off-peak times by using the CREATE STATISTICS statement. By the way, in most cases, it will not provide some performance benefits.

*****

You can turn off the 'auto update statistics' database option.
When this database option is set to true, existing statistics are automatically updated when the statistics become out-of-date. By default, this database option is set to true. Because auto update statistics results in some performance degradation, you can turn off this database option and update statistics manually during off-peak times by using the UPDATE STATISTICS statement. By the way, in most cases, it will not provide some performance benefits.

*****

Turn off the 'autoclose' database option.
When this option is turned on, the database's resources are freed after the last user exits. When the new user will connect to database, the database should be reopened, which takes some time. So, do hot set this database option to true on your production server. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.

*****

Turn off the 'autoshrink' database option.
When this database option is set to true, the database files will be periodically shrink. Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to on. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.

*****

You can turn on the 'read-only' database option to prevent users to modify the database's data.
By default, this database option is set to false. If you have data that should not be modified, you can place it into another database and set for this database the 'read-only' option to true. It can increase the speed of your queries. If you need to allow permissions management (for example, prevent some users to select data from some tables), you should create another filegroup and make only this filegroup read-only, because when the 'read-only' database option is set to true, the database's system tables will be also read-only and this will prevent the permissions management.

*****

You can turn on the 'select into/bulkcopy' database option to allow SELECT INTO statements and nonlogged bulk copies.
The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:
1. The database option 'select into/bulkcopy' is set to true.
2. The target table is not being replicated.
3. The TABLOCK hint is specified.
4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.
By default, this database option is set to false.

*****

You can turn off the 'trunc. log on chkpt.' database option to prevent the transaction log from truncating on checkpoint.
This option can be set if the transaction log grows very quickly to prevent the transaction log from filling rapidly and running out of disk space. If you set the 'trunc. log on chkpt.' database option to true, the transaction log cannot be backed up, so you cannot restore your data to the point of failure (only to the time when the last full backup was made). So, the general recommendation about this option is allow it to be turned off, and make the transaction log backup periodically to truncate the log. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.

Note. You can set the above database options by using the sp_dboption system stored procedure or Enterprise Manager. If you want to set the above database options for the newly created database, you should set these options for the model database.