August 12, 2019 at 5:47 pm
Hi,
Could you please let me know if the 'below script/code' OR..'Auto Update statistics'= True ( DB - Properties-Options ) does the SAME work for the specific DB. I see the below code running for some databases in SQL agent job. Now my questions is if we just set the Auto Update statistics = True on the desired databases there is no need for the job running with below code and added work for sql server? please let me know if I am missing anything here..Thanks!
EXECUTE dbo.IndexOptimize
@ Databases = 'AdventureWorks', -- or any other DB and add a step in agent with the same code and new DB name..
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'
August 12, 2019 at 6:12 pm
August 12, 2019 at 6:40 pm
Yeah however would like to know the difference. Thanks!
August 12, 2019 at 8:19 pm
The reason people setup jobs such as Ola Hallengren's scripts or to maintain statistics is that the Auto Update Statistics setting is often not enough to keep statistics up to date. This is primarily because SQL Server tries to guess when it needs to refresh statistics based on the number of INSERTS/UPDATES that have happened in the table. Older versions had a 20% rule, newer versions do have some more flexibility, but there are still cases where you would want to update statistics in addition to the Auto Update.
Here's a comprehensive article on the topic:
https://littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply