SQL: Is there a place for AutoClose in SQL Server databases?

If you’ve hung around the SQL Server community for any length of time, you’ll have heard the endless jibes about the AutoShrink option for databases. Using it is almost always a bad idea. In fact, shrinking databases is typically not a great idea but the AutoShrink option is considered universally bad and there are calls for it to be removed as an option.

We often joke that “auto shrink” should be renamed to “auto fragment my filesystem”, because that’s typically the outcome.

A related but quite separate option though is AutoClose. It tends to get lumped into the same discussions but while using it is also typically not a great idea, this one has a number of valid use cases.

When the SQL Server service starts, the default action is for it to open all the attached database files, and to run recovery on those databases. The files are closed when SQL Server shuts down.

If AutoClose is enabled, SQL Server doesn’t open the files on startup, but waits until someone tries to access the database. It then makes them wait until the files are opened, and continues with the request. The files are closed again when the last user disconnects from the database.

This option was introduced back when you could run SQL Server on Windows 98 (yes Windows 98) and the FAT32 file system. There was real concern that database files that were left open unnecessarily could easily end up messed up by FAT32, which didn’t cope well with unplanned shutdowns.

The worst case scenario for AutoClose was a single user system. I was once sent to a government client by the local Microsoft office when I was living in Brisbane. They had a SQL Server application that was performing appallingly. They had thrown hardware at the problem that must have cost close to the national debt but still it performed badly.

The reason that I found was that they had AutoClose enabled. They had an Access application, and every control on the screen was separately bound back to a connection to SQL Server. So as the single user navigated around the screen, the files in the back end were being spun up, a query run, then spun down again, all the time. I can’t tell you just how slow the system was. It was breathtakingly bad.

The hint that they could have seen if they’d looked was that the SQL Server logs were full of file opens and closes. Disabling AutoClose for the database fixed it immediately.

But it left me wondering about valid use cases for AutoClose. And then I got to see one. I had a customer who was processing real-time data from oil-drilling rigs. They had a separate database for each rig and they had many thousands of rigs ie: many thousands of databases. At any point in time, only a handful of the databases was in use.

[caption id=“attachment_2831” align=“alignnone” width=“567”] Image by Andrew Wulf[/caption]

If you’ve ever tried to restart a SQL Server system with tens of thousands of databases, or if you’ve ever tried to use SQL Server Management Studio or other tools with such a system, you’ll understand the remaining beauty of AutoClose. When SQL Server starts, it then needs to only open a handful of files. As long as a particular database stays in use, its files stay open. When it’s no longer in use, the files are closed.

A bonus is that the associated memory caches, etc. for the database that is being auto-closed, are also freed up.

Next time you hear that AutoClose is never a good idea and should be removed, keep in mind that that’s not 100% true. There are scenarios where it makes perfect sense, even today.

2018-04-02