SQL Server Best Practices: AutoClose Should be Off

SQL Server Best Practices: AutoClose Should be Off

Rate This
  • Comments 3

When SQL Server "opens" a database, resources are assigned to maintaining that state. Memory for locks, buffers, security tokens and so on is assigned, and there is associated CPU and even a little I/O. When connections are made to the database, these resources are ready and waiting. The AutoClose setting dictates how these resources are handled. If this setting is ON, then when the last user connection that disconnects, SQL Server releases all of those resources back to the server. That might sound like a good thing, but if you think about the pattern of connections, the "last" connection doesn't mean that someone might not connect one tenth of a second later. In that case, SQL Server would shut all of the resources down, and then have to spin them right back up again. As a result, the server makes the connection wait, which makes the system seem slower.

So the best practice is to leave this setting OFF. In fact, SQL Server 2008 Books Online states that this setting is going away anyway, so you should have them turned off (http://msdn.microsoft.com/en-us/library/ms135094(SQL.90).aspx).

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
Page 1 of 1 (3 items)