Here are seven little known tips that you can use to ensure your databases are performing in the most efficient manner possible.
1. 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 any performance
benefits.
2. 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 the auto update
statistics option 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 any performance benefits.
3. Turn off the 'autoclose' database option.
When this option is turned on, the database's resources are freed after
the last user exits. When a new user connects to the database, the
database should be re-opened, which takes some time. So, do not 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.
4. Turn off the 'autoshrink' database option.
When this database option is set to true, the database files will
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 the 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.
5. You can turn on the 'read-only' database option to prevent users
from modifying 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 that has the 'read-only' option set to true. It can increase the speed of your queries.
If you need to allow permissions management (for example, prevent some
users from selecting 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 also be read-only, which will prevent permissions
management.
6. 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.
7. 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 to 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.
»
See All Articles by Columnist Alexander Chigrik