SQL Server with Mr. Denny

Aug 13 2009   11:21AM GMT

Auto Close is almost as bad as auto grow

Denny Cherry Denny Cherry Profile: Denny Cherry

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

2  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • cyberhh
    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
    40 pointsBadges:
    report
  • Denny Cherry
    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.
    66,010 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: