Relational Database Systems

Database property Option in SQL Server 2005/2008 that should be turned ON

Database Property Options are divided into 6 Categories, these are:

  1. Automatic
  2. Cursor
  3. Miscellaneous
  4. Recovery
  5. Service Broker
  6. State

Intent of this article to guide you regarding Database Property Option that you should enable or disable and why. I will be covering following Categories:

  1. Automatic
  2. Recovery
  3. State

 Recommendation applies to SQL Server 2005 and SQL Server 2008

Automatic

  1. Auto Close : This option is by default disabled and should be disabled all time. Microsoft has confirmed that they are going to remove this option from future release. Please refer http://msdn.microsoft.com/en-us/library/ms135094.aspx
  2. Auto Create Statistics : Leave this option enabled because Microsoft has improved by deciding to use an internal columns named sysrowsetcolumns.rcmodified (which is undocumented) which can be seen if you connect to SQL Server using Dedicated Admin Connection. Statistics invalidation is now isolated to only columns that are heavily changed.
  3. Auto Shrink : Do not enable this option because it causes your Indexes to be fragmented. Another fact that I would like to mention here is "Avoid Shrinking your database" If you shrink then rebuild your indexes.
  4. Auto Update Statistics : Leave this option enabled unless you are manually updating statistics. Though enabling this option should not cause any issue but in case you find any issue then you can turn on Auto Update Statistics Asynchronously.
  5. Auto Update Statistics Asynchronously: By default this is disable and recommendation is to let it be disabled unless you find issue with "Auto Update Statistics".  When this option is enabled then statistics update are handled in background and future queries will get benefit. This option was first added in SQL Server 2005. If this option is enabled then you can NOT bring database in single user mode.

Recovery

  1. Page Verify : CHECKSUM is best choice because this option confirms that page corruption (if any) was not due to SQL Server. When this option is set then SQL Server computes checksum during write and read both, stores it in the page header and then finally write the page on disk.

State

Options under this category are not related with performance. I am writing detail about only two options which are important to know in all cases:

  1. Database Read-Only: This option is by default disabled and should be enabled ONLY when you want to bring your database in read_only mode.
  2. Restrict Access : This option is by default set to MULT_USER and it should be left in that way unless there is any issue in database or needs to perform some maintenance that can not be done in MULTI_USER mode. Other possible values for this option are "Single_User", "Restricted_User". In "Single_User" Mode, any user but only ONE at times can connect to database whereas in "Restricted_user" mode user who is member of db_owner, dbcreator and sysadmin can connect to the database.

Summary

Do not enable or disable any option without having proper understanding of that option, Most options are enabled by default as per Best Practice recommended by Microsoft and works good in most scenario. Hence, If you are planning to change any option then collect performance data before and after and compare to see if enabling/disabling option is NOT hurting your database.

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top