One of my friends just leased one dedicated SQL Serverhosting server with managed support for his own business purpose. Though it ishosted at hosting company’s data centre with very good hardware configurationand being used for his own (one) database only, it was performing very slowly atcertain point of time though there is no heavy work load.
He asked me if I can help him out in this matter so I hadquick 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 SQLServer, our database is assigned some resources to maintain its own stat, somememory for its use, some buffer space and many more. Auto_Close helps you tofree up these resources when last user disconnects from the database.
This is good thing to keep unused resource free but what ifyour last user disconnects and every resource gets free for claim and after fewminutes of freeing up resource another user came to connect to this database? ObviouslySQL Server will assign all resource to the database again and user has to waituntil all resource assigned to the database means, slow performance for thatuser first time.
If you are having only one or may be few databases in yourinstance, I highly recommend keep Auto_Close property to OFF. I have seen thathosting company keep these settings on for their shared hosting server whereyou can find hundreds of databases in one instance.
BTW, As per Microsoft, this feature will be removed from laterversion
so this is one more reason not to use this setting in yourdatabase.
Here is the TSQL to check whether Auto_Close is ON or OFFfor your database.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsAutoCLose’);
Here is the syntax which helps you to set ON or OFF forAuto_Close property in your database.
Alter Database YourDatabaseName SetAuto_Close ON
Reference: Ritesh Shah
Ask me any SQL Server related question at my “ASK Profile”