Question: How to Enable Auto Update Statistics and Auto Create Statistics?

Answer: I have received this question after watching my free webinar on group by conference by one of the user. I really loved his question so I am re-posting the question over here.


I love your energy in while you presented the session. I think I tried one of your suggestions of enabling auto update statistics and auto create statistics on our primary database. Trust me, after making the changes my database is running extremely fast and my customers are congratulating me. Now the real challenge is that we have over 100s of database and I need to enable these settings where it is off. If I go and do it manually everywhere it will take forever, is there any shortcut for it?

~ Nitin S”

Well, Nitin is actually referring my webinar over here. In this webinar, I have shared three very important tips for improving your SQL Server’s performance. During SQL Server Performance Tuning Practical Workshop, I also discuss about this in detail.

Here is the script which will generate T-SQL Script which you can use to enable Auto Update Statistics and Auto Create Statistics for all the database where it is set to off.

FROM sys.databases
WHERE is_auto_update_stats_on = 0;
FROM sys.databases
WHERE is_auto_create_stats_on = 0;

Once you run above script, it will generate output as displayed in the following screen. Run the script in SSMS and you will enable required settings for your database.

Reference: Pinal Dave (

First appeared on How to Enable Auto Update Statistics and Auto Create Statistics with T-SQL – Interview Question of the Week #108

Read the complete post at…/

About the Author

Pinal Dave

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Start the discussion at