Enabling AUTO_CLOSE is a bad idea?

From a personal perspective, allowing a production database to run with AUTO_CLOSE option is not the best practice. Let me explain why you should not enable AUTO_CLOSE and the consequences of using this option.

The other day, I had to look in Error Log on a test server. After a two-minute timeout, I saw a great number of messages stored in the log, and I decided to check the log size using xp_enumerrorlogs:


As usual, on test servers, I don’t bother with the Error Log size, because each start of SQL Server initiates cyclic change of log files: the current errorlog is renamed to errorlog.1, an empty file errorlog is created and the earliest errorlog.6 is deleted.

When I need to clean the logs, sp_cycle_errorlog can be helpful. But before cleaning the logs, it fell into my mind to see what interesting staff is recorded there.

I’ve read the current log with the stored procedure xp_readerrorlog:

And then I saw dozens of messages of this type:

On the one hand, there is nothing wrong with that. At each start, SQL Server opens data files and checks the boot page:

But after I have filtered by the message of interest, the results made me curious:

A great number of such messages may result from the AUTO_CLOSE option set to ON.

According to the online documentation, when you turn on the AUTO_CLOSE option, a database is shut down automatically and flush all resources after the last user logs off. When a new connection is requested, the database will automatically reopen…and so on ad infinitum.

Some time ago, I’ve read that in earlier versions of SQL Server, AUTO_CLOSE was a synchronous process, which could cause long delays at repeated opening and closing of database files. Starting in SQL Server 2005, the AUTO_CLOSE process became asynchronous, and partially the problem is gone now. But there are many issues with AUTO_CLOSE that still remain.

To optimize performance, SQL Server changes pages in the buffer cache and does not write these pages to disk after each modification. Instead, SQL Server creates a checkpoint, at which it writes current pages modified in the memory, along with transaction log information from the memory to disk. When a database is shut down, CHECKPOINT is automatically executed. Accordingly, the disk load may greatly increase with the repeated database shutdowns.

Moreover, each database shutdown flushes the procedure cache. So, when the database reopens, the execution plans will have to be generated over again. But what is even worse, at the shutdown, the buffer cache also flushes, which increases disk load upon running queries.

What does Microsoft thinks about AUTO_CLOSE?

“When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection”

However, there are couple of nuances. In SQL Server 2000 or any Express edition, when you create a new database, the AUTO_CLOSE option will be enabled by default:

However, if you look at the upside, such SQL Server Express behavior is easy to explain, because this version sets a limit on the size of RAM usage – a maximum of 1 GB.

But in future, if you will need to deploy a database using a script, it is better to be on the safe side and explicitly disable AUTO_CLOSE:

In the course of work, I’ve noticed one other interesting thing – when calling certain system functions or views, all databases with enabled AUTO_CLOSE options will open:

We call p1:

But p2 “wakes up” as well:

And finally we get to the main point. On a server, different users actively accessed metadata, making databases with enabled AUTO_CLOSE open, which, in turn, caused the Error Log growth.

Preventive measures, by the way, are very simple:

All tests were implemented on Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64).

Conclusion

It may seem logical to close database that isn’t in use to release resources and improve performance. But, in fact, it’s harming your database far more than helping. So, unless you are absolutely sure that this feature is essential for you, the best practice is to leave the AUTO_CLOSE setting OFF.

Leave a Comment