Post reply

Update Stats or Code

  • August 12, 2019 at 5:47 pm

    #3670792

    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'

    • This topic was modified 5 years, 4 months ago by  sizal0234.

  • August 12, 2019 at 6:12 pm

    #3670799

    For context, this appears to be referencing the Ola Hallengren utility code.

    Have Fun!
    Ronzo

  • August 12, 2019 at 6:40 pm

    #3670813

    Yeah however would like to know the difference. Thanks!

  • August 12, 2019 at 8:19 pm

    #3670826

    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