In the previous article, I have shared the importance of SQL Server Statistics and why it is important for the query optimizer.
To create and update statistics is very important for Query Optimizer to choose best optimized execution plan.
In this post, I am sharing script to enable and disable auto_create and auto_update of Statistics on a Database.
Auto Create Statistics:
If statistics are not already available, Query Optimizer creates statistics on individual columns.
The name of the auto-created statistics includes the column name and the object ID in hexadecimal format: _WA_Sys_
1234567 -- To Enable.ALTER DATABASE Database_NameSET AUTO_CREATE_STATISTICS ON-- To Disable.ALTER DATABASE Database_NameSET AUTO_CREATE_STATISTICS OFF
Auto Update Statistics:
If Query Optimizer finds that Statistics is out of date, it updates require statistics automatically.
It takes decisions base on old number record count and current column modification counter.
The default option is ON.
The outdated statistics can cause a lot of performance issues so it is recommended to enable it.
1234567 -- To Enable.ALTER DATABASE Database_NameSET AUTO_UPDATE_STATISTICS ON-- To Disable.ALTER DATABASE Database_NameSET AUTO_UPDATE_STATISTICS OFF
Auto Update Statistics Asynchronously:
If you enable this option, the Query Optimizer will run the query first and update the outdated statistics afterwards.
When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query.
1234567 -- To Enable.ALTER DATABASE Database_NameSET AUTO_UPDATE_STATISTICS_ASYNC ON-- To Disable.ALTER DATABASE Database_NameSET AUTO_UPDATE_STATISTICS_ASYNC OFF
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.