Home > IT Blogs > SQL Server with Mr. Denny > Auto Close is almost as bad as auto grow
>>VIEW ALL POSTS  

SQL Server with Mr. Denny

«
»
Aug 13 2009   11:21AM GMT

Auto Close is almost as bad as auto grow



Posted by: Denny Cherry
Performance Problems, SQL Server

In theory the auto close setting is a great idea. Free up resources for other databases to use when all the users are finished with the database.

In practice, its not so great.  If a single user connects and disconnects over and over (like say a single user using your website or even a few users using your website) every time a user connects the database has to be spun up and the data loaded into memory.  When the user disconnects the data is removed from cache, and the connection to the file is closed.  Then when the next user runs a query the file is opened again, and the data is loaded into cache.

This causes the users queries to run slower as the data must be pulled from disk each time instead of pulled from memory.  Plus there’s the time spent spinning up the IO thread, and opening the file.  And as I said last time, more time is bad.  Plane and simple.

Denny

  Bookmark and Share     Comment     RSS Feed     Email a friend

Comment on this Post

Leave a comment:

Cyberhh  |   Dec 10, 2009  5:13 PM (GMT)

Denny,

Is there a place and time for auto close? For instance when you have a database application that executes all it’s queries as the sa account? At the same time the majority of the reads are going to be to the same database(s) over and over, so your logic may still hold true.

Thanks


 

Mrdenny  |   Dec 14, 2009  6:49 AM (GMT)

Pretty much no. Having the application use the sa account wouldn’t change the desire to close the database. Auto-close simply flushes the data to disk removing it from the buffer and procedure cache which would then simply slow down the application the next time it needed access to the data.

About the only time that I could see auto close being worthwhile (and this would need some major testing and probably some special configurations to do correctly) would be if your OLTP database and your OLAP database shared a SQL Instance, and the OLTP database was only used during the day, and your OLAP database was only used at night. This would maximize the resources that each database would have access to. However on the flip side without auto-close enabled the SQL Server engine will remove data from the cache if it hasn’t been accessed to make run for the data which is being accessed so even then you would probably be better to leave the database not in the auto-close.