SQL Server: Don’t Enable Auto Close Database Option

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Microsoft SQL Server Database Auto Close option is also crucial during the performance tuning activities.

When you set Auto_Close ON, SQL database automatically shut down after last user exit and free all the occupied resources. When a user wants to connect the database, it opens database related connections and starts utilizing resource again.

This looks like a good thing where the database can free all the resources when they are not in use.

Please do not forget about query cache and other procedure cache areas which are playing an important role in query execution.

If ou set Auto_Close ON, it removes all stored query cache, procedure cache, buffer cache which degrades the performance while accessing the database again. The biggest problem is that SQL Server has to load data back into the buffer to satisfy performance, and any queries fired against the database itself have to be re-compiled as well.

In very rare cases, you require to enable Auto Close for example, Your organization has thousand of databases and limited resources.
In general, Database Auto Close option should disable which requires very limited resources in idle mode.

So now, during your performance optimization activities, please check about Auto Close option. There are DMVs available to check the status of different types of cache data.

Using SSMS, Set Auto Close OFF: