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.