Database Properties

SQL Server includes several database property configuration options that can impact performance and recoverability in a production environment. In this post we will discuss the recommended configuration value as per the best practices of Microsoft.

  • Auto Close
  • Auto Create Statistics
  • Auto Shrink
  • Recovery – Page Verify
  • Recovery Model
  • Auto Update Statistics
  • Auto Update Statistics Asynchronously
  • Compatibility Level


Auto Close

When enabled, the AUTO_CLOSE option causes the database to shut down cleanly and the resources to be freed after the last database user exits. New connections to the database reopen it.

The AUTO_CLOSE option is not intended to be used for frequently accessed, highly concurrent shared databases. Thus, it should be enabled only for single-user desktop applications. Additionally, some of the performance issues experienced with AUTO_CLOSE prior to SQL Server 2005 have been resolved. However, performance issues can still occur in SQL Server 2005 because a database closure results in objects being cleared from the plan cache. Recommended/Default value is FALSE.

Auto Create Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If AUTO_CREATE_STATISTICS is disabled, the statistics will not be created on columns that are not indexed when needed by the query optimization process. The performance of incoming queries may suffer if AUTO_CREATE_STATISTICS is disabled. Thus, you should always keep this option enabled for each user database. Additionally, statistics are essential to the query optimization process. If new statistics are needed but have not been created, query performance will be affected. Recommended/Default value is TRUE.

Auto Shrink

The AUTO_SHRINK option, when enabled, causes data and log files to be automatically shrunk if more than 25 percent of a file has unused space. This option can cause performance and concurrency issues during the shrink process. It can also cause index fragmentation after a shrink process has been performed. Ensure that AUTO_SHRINK is turned off by default. If free space must be generated, do that by using the DBCC SHRINKFILE or DBCC SHRINKDATABASE commands. In addition, always rebuild or reorganize the indexes after a shrink operation in order to fix any fragmentation that might have occurred in the data files.

Torn Page Detection (Page Verify)

When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte database page where the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the result will indicate that a torn page was detected and that the page was initially written incorrectly.

Recovery Model

The recovery model controls transaction log maintenance and can have serious effects on a system if it is set incorrectly or inappropriately. If the wrong recovery model is specified for the back-up strategy being used, data loss can be experienced during a restore, or log files can grow to the point where they consume all space on the drive.

When the recovery model is set to Simple, no log backups are taken and log space is automatically reclaimed to keep space requirements small. This essentially eliminates the need to manage the transaction log space. However, in Simple mode, you can only recover to the end of the last backup. This is because there are no transaction log backups to apply in order to bring the database to a consistent current state.

When the recovery model is set to Full or Bulk-Logged, log backups are taken and the system can be restored to a current state or to a previous state. When Bulk-Logged is being used, certain operations are not logged. This reduces log space usage by special logging during most bulk operations. In addition, when the Bulk-Logged model is used, point in time recovery is not supported.

Auto Update Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If the statistics become outdated and significant changes have occurred against the underlying data, this can result in less than optimal query performance. If AUTO_UPDATE_STATISTICS is disabled, out-of-date statistics will not be automatically updated.

Ensure that AUTO_UPDATE_STATISTICS is enabled for each user database on the SQL Server instance. For highly volatile data, consider more frequent statistics updates using the system stored procedure, sp_updatestats, or the UPDATE STATISTICS command.

Auto Update Stats Async

AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to On, queries do not wait for the statistics to be updated before compiling. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay as a result of waiting for updated statistics, query response times are predictable. However, the out-of-date statistics may cause the query optimizer to choose a less efficient query plan.

You should consider setting the AUTO_UPDATE_STATISTICS_ASYNC option to On when the following characteristics apply to your application:

  • Client request timeouts caused by one or more queries waiting for updated statistics have been experienced.
  • Predictable query response times are required, even at the expense of occasionally running queries with less efficient query plans because of outdated statistics.

Compatibility Level

 Setting the databases to a lower compatibility level should be done only as an interim aid during migration. Keeping the databases at the same level indefinitely will disable some of the new features that later versions provide.


Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: