SQLcommitted

SQL Server 2008 R2  Database properties – Option Page – Part-1

Not only DBA , I believe anybody working on SQL Server database whether as a SQL Developer or BI Developer all would have seen the below screen.

Using this screen we can set different SQL Server database settings.

In this page there are 4 sections i.e.

1-      Collation

2-      Recovery Model

3-      Compatibility Level

4-      Other options

Let’s try to explore different option available in Other Options section.

The Other Options section grouped into 6 categories as :

1-      Automatic

2-      Cursor

3-      Miscellaneous

4-      Recovery

5-      Service Broker

6-      State

In Part-1 we’ll cover Automatic category also called Auto options

These are the options which SQL Server might take it automatically. All attributes under this sections takes Boolean value only ( Either TRUE  or FALSE)

AUTO CLOSE :

When this option is set to TRUE, the database is closed and shut down cleanly when the last user of the database exits. In result it will free all resources  used by database.

It will reopen again once user tries to use the database. This options mostly used for personal SQL SERVER database as it allow to manage database file as normal files.

If the database is used by an application which connect and disconnect to SQL SERVER repeatedly  then it is advisable not to set this option TRUE to avoid over head of closing and reopening the database between each connection.

AUTO CREATE STATISTICS:

Statistics is used by query optimizer to determine how to evaluate a query.

Statistics is a different topic in SQL SERVER , I’ll be writing on Statistics for you all very soon.

By default this option is set to TRUE. If It is TRUE,  SQL SERVER automatically creates statistics on columns referenced in a “WHERE” clause or “ON” clause .

It creates statistics on column when any missing statistics needed by the query optimizer.

Adding statistics helps to improve query performance ( How it improve performance?, I’ll be writing on it very soon).

AUTO SHRINK:

If this option is TRUE then both Data files and  Log files can be shrunk by SQL Server. File shrinking happened periodically.

To shrink a log file, you have to take the backup the log file or the recovery model set to SIMPLE ( I’ll be writing on Recovery model very soon).

It is not recommended to use this option.

Now a Question for you ,  When to use AUTO_SHRINK option?

AUTO UPDATE STATISTICS:

By Default this option is set to TRUE.  If this option is on existing statistics are updated if the table data has changed.

SQL SERVER maintains a counter of the modification made to a table and it uses this counter to determine when the statistics are outdated.

It updates the statistics when any out of date statistics needed by the query optimizer during query optimization.

AUTO UPDATE STATISTICS ASYNCHRONOUSLY:

If It is set to TRUE, It will not wait for the query which initiate to update the out of date statistics before compiling the query.

Subsequent queries will use the updated statistics when they are available.

If it is set to false, The query which initiate to update the out of date statistics will wait until the statistics is updated. Once the statistics is updated the query will use the updated statistic to prepare the query plan.