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

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.

“Pinal,

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.

SELECT
'ALTER DATABASE ' +name
+' SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT'
FROM sys.databases
WHERE is_auto_update_stats_on = 0;

SELECT
'ALTER DATABASE ' +name
+' SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT'
FROM sys.databases
WHERE is_auto_create_stats_on = 0;

Once you run the 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. You can always reach out to me with follow up question on twitter.

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