Should Auto_Close database property be ON or OFF in SQL Server?

One of my friends just leased one dedicated SQL Server hosting server with managed support for his own business purpose. Though it is hosted at hosting company’s data centre with very good hardware configuration and being used for his own (one) database only, it was performing very slowly at certain point of time though there is no heavy work load.

He asked me if I can help him out in this matter so I had quick look into it and after sometime I found Auto_Close property a culprit. 

Let me first tell you what this property is and how & where does it comes into the picture.

When we first time access or open the database in SQL Server, our database is assigned some resources to maintain its own stat, some memory for its use, some buffer space and many more. Auto_Close helps you to free up these resources when last user disconnects from the database.

This is good thing to keep unused resource free but what if your last user disconnects and every resource gets free for claim and after few minutes of freeing up resource another user came to connect to this database? Obviously SQL Server will assign all resource to the database again and user has to wait until all resource assigned to the database means, slow performance for that user first time.

If you are having only one or may be few databases in your instance, I highly recommend keep Auto_Close property to OFF. I have seen that hosting company keep these settings on for their shared hosting server where you can find hundreds of databases in one instance.

BTW, As per Microsoft, this feature will be removed from later version so this is one more reason not to use this setting in your database.

Here is the TSQL to check whether Auto_Close is ON or OFF for your database.

SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsAutoCLose');

Here is the syntax which helps you to set ON or OFF for Auto_Close property in your database.

Alter Database YourDatabaseName Set Auto_Close ON

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”