Database Settings

To provide the up-to-date statistics the query optimizer needs to make smart query optimization decisions, you will generally want to leave the "auto update statistics" database option on (the default setting). This helps to ensure that the optimizer statistics are valid, helping to ensure that queries are properly optimized when they are run.

But this option is not a panacea. When a SQL Server database is under very heavy load, sometimes the auto update statistics feature can update the statistics on large tables at inappropriate times, such as the busiest time of the day.

If you find that the auto update statistics feature is running at inappropriate times and hurting production performance, you may want to turn it off, and then manually update the statistics (using UPDATE STATISTICS) when the database is under a less heavy load.

But again, consider what will happen if you do turn off the auto update statistics feature. While turning this feature off may reduce some stress on your server by not running at inappropriate times of the day, it could also cause some of your queries not to be properly optimized, which could also put extra stress on your server during busy times.

Like many optimization issues, you will probably need to experiment to see if turning this option on or off is more effective for your environment. But as a rule of thumb, if your server is not maxed out, then leaving this option on is probably the best decision.

In SQL Server 2005, there is a new feature called Asynchronous Statistics Updating. If this feature is turned on, some of the problems described above are mitigated. When turned on, this feature tells SQL Server to queue the statistics updating and to run the current query using the current statistics instead of waiting for new statistics to be created. This prevents queries from having to wait to run. At the same time, it also means that the query will run using a bad query plan instead of a more optimum query plan.  [7.0, 2000, 2005] Updated 8-21-2006

*****

Many databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don't be tempted to use the "auto shrink" database option, as it can waste SQL Server resources unnecessarily.

By default, the auto_shrink option is turned off, which means that the only way to free up empty space in a database is to do so manually. If you turn it on, SQL Server will then check every 30 minutes to see if it needs to shrink the database. Not only does this use up resources that could better be used elsewhere, it also can cause unexpected bottlenecks in your database when the auto_shrink process kicks in and does its work.

If you need to shrink databases periodically, perform this step manually using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, or you can use the SQL Server Agent or create a Database Maintenance Plan to schedule regular file shrinking at times when your server is less busy. [7.0, 2000, 2005] Updated 8-21-2006

*****

This "auto close" database option is designed for use with the Desktop or Express versions of SQL Server 7.0, 2000 and 2005, not for the server versions. Because of this, it should not be turned on. What this option does is to close the database when the last database user disconnects from the database. When a connection requests access to the database, the database has to be reopened, and this takes time and overhead.

The problem with this is that if the database is accessed frequently, which is the most likely case, the database may have to close and reopen often, which puts a large performance drag on SQL Server and the applications or users making the connection. [7.0, 2000, 2005] Updated 8-21-2006