Update Stats or Code

  • sizal0234

    SSCrazy

    Points: 2194

    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 11 months ago by  sizal0234.
  • Ronzo

    Hall of Fame

    Points: 3741

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


    Have Fun!
    Ronzo

  • sizal0234

    SSCrazy

    Points: 2194

    Yeah however would like to know the difference. Thanks!

  • Chris Harshman

    SSC-Forever

    Points: 42108

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply