Tuesday, 7 September 2010

Auto Close – SQL Server

I ran into an issue recently where I had a third party supplied database set to auto close. I couldn't work out why they would use such a setting but I could think of a couple of reasons not to including:

  • Resources used to maintain the state of the open database will always be ready and waiting when the database is in an ‘open’ state. Resources are allocated to an Open database that allow SQL Server to maintain that state, including memory for locks buffers etc. When a connection is made to the database these resources are ready. If Auto Close  the database ‘closes’ when the last connection disconnects and these resources are given up. When the next connection comes along you a may suffer a performance hit as the database 'starts' again and has to reallocate the necessary resources to the database. This was my problem my app was taking an eternity to log in when it hadn’t been used for a while.
  • According to SQL Server 2008 Books online the auto close feature will be removed in future.

I really struggled to think of a good use of auto close but at the same time thought that someone out there must have a use for it. So I posted a question on twitter using the #SQLHELP hashtag and I got several replies:

  • Not a good reason: Want to use a backup solution that cannot backup open files.
  • Auto close has been used effectively on POS (point-of-sale) applications that had minimal spec hard ware.
  • Web hosts use it, they cram 500 plus databases onto a server  and set it to auto close so the less used ones release resources

A big thanks to @Brento @SQLRich @onpnt @PaulWhiteNZ , @AaronBetrand, @banerjeeamit for their replies to my question

Buck Woody on his blog  suggests as a best practice it should be turned off and lists some excellent reasons for doing so.

The long and short of it, I have now turned off auto-close and the application which is not heavily used now fires up much more quickly.