Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 22, 2002

Database Settings Optimization Tips

By Alexander Chigrik


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




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date