SQL SERVER – Set AUTO_CLOSE Database Option to OFF for Better Performance

As many of you know, I am an SQL Server Consultant, who purely focus on SQL Server Performance. Every single day, I keep two slots each hour long available for users who need my help urgently. I never ever pre-book them because, I believe it is critical to help people who needs help NOW. Well, let us learn in this blog post about how I recently helped a customer to improve performance by turning off AUTO_CLOSE Database Option.

Recently, one of the my old customers pinged me on Skype and asked if I have some time to see why suddenly his SQL Server is poor. As I had an hour available, I decided to jump on the call with him and see if I can help him. Just a week before I had helped the same customer to do comprehensive performance tuning. The exercise had lasted almost a day and when we were done the performance of the system was under all the accepted levels.

However, when I logged in the performance of the server was really struggling. I can see without any clear reason, entire server was just delivering bad performance. As in my initial query, I could not find the reason for slow performance, I decided that I will start my entire comprehensive performance tuning exercise once again. However, as soon as I started the exercise, I noticed the AUTO_CLOSE database option. This particular option was set to ON.

Set AUTO_CLOSE Database Option to OFF

You can go to Your Database and Right Click on it. Click on Properties on the right, click menu and it will bring up the following screen. Over here you should change the Auto Close value to FALSE.

Alternatively, you can change this value to off by running following T-SQL command as well.

USE [master]
GO
ALTER DATABASE [SQLAuthority] SET AUTO_CLOSE OFF;
GO

Reason

When AUTO_CLOSE is set ON, it causes performance degradation on heavily used databases by increasing overhead of opening and closing the database after each connection. Additionally, when it is ON, it also flushes (removes) the procedure cache after each connection. There are very rare scenarios when you would need this particular setting on, otherwise in most of the cases, it is a good idea to leave it OFF.

Action Item for You

Go to your database and see what is the value of this particular setting. If it is set to ON (true), you want to check with your administrator why it is kept ON. If you are database administrator and you do not know the answer to this question, you can turn it OFF (false).

Summary

During performance tuning consultation, I go over many different such settings, which can drastically impact on SQL Server performance. Once I changed this particular setting on my customer’s server, its performance was back to normal as rest of the settings we had just fixed a week before.

Reference: Pinal Dave (https://blog.sqlauthority.com)