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)
12 Comments. Leave new
Very helpful to know! Thank you!
Useful to know Pinal
This little script checks every database
select name, is_auto_close_on
from master.sys.databases
Thanks it is a good handy script.
Hi Pinal,
Thanks for sharing such valuable information on this blog.
Hi Damian,
Thanks for the script. Instead of searching all databases for AUTO_CLOSE option, scripts makes our work easy.
Was this setting on the previous week or was it turned on inadvertently?
This command is useful when database is not used regularly ,will help to release memory of server once its off when other database required space to work .
You shouldn’t brag about your indecency, though.
Very useful
Yes sorry what’s indecent?
Even i don’t know :)
My application is a traditional client/server application. In this case, the user(s) will work for hours during the day on the same connection (each user), and then quit the application which closes the connection before exiting. Will it make sense to leave the option ON in this scenario? Is connecting/disconnecting to the database the same as opening/closing the database?
Hii Pinal,
We have Connection lost issue on production Environment,
Sometime Connection is lost,Can you suggest the Causes for this.
If you suggest its great help for us,
Many thanks!