SQL Server Best Practices: AutoClose Should be Off


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).

Comments (3)

  1. Sam Ahmed says:

    Informative

    Thanks a lot

  2. Gregory Ferdinandsen says:

    I was just looking at one of our SQL 2012 servers and auto-close is no longer an option.  Myself, I have not used it since my 6.5 days, but am very glad to see it's gone away!