UPDATE STATISTICS

bbrown
bbrown Member Posts: 3,268
edited 2011-03-14 in SQL Performance
Any ideas on ways to reduce the run time required for "UPDATE STATISTICS". Client is increasing their weekly operating hours and the maintenance windows are shrinking.
There are no bugs - only undocumented features.

Answers

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    On which tables should you do this? Most tables do not change that much in an ERP system.

    I would rely on indexmaintenance to update the statistics.
    Marije Brummel | Freelance Business Central Specialist
  • bbrown
    bbrown Member Posts: 3,268
    That's in line with what I'm thinking. That doing a generic "Update Statistics" doing too much work. Much of which has no value. Could you expand on "I would rely on indexmaintenance to update the statistics"? I am also doing an index maintenance task with the following criteria:

    Frag < 10% - Skip
    Frag >10<30 - REORGANIZE
    Frag >= 30 - REBUILD

    If I look at the job history I'll see mosting REORGANIZE with REBUILD being very rare. The table that are the most concern are GL and Value Entry. Would I be better off with a more selective "Update Statistics" job?
    There are no bugs - only undocumented features.
  • stryk
    stryk Member Posts: 645
    How do you perform this "Update Statistics"? With the MP task, or some script? How big is the database?

    I'd recommend to use the "sp_updatestats" in combination with "sp_createstats 'indexonly'" as this - in my experience - gives sufficient stats maintenance within an acceptable runtime ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • bbrown
    bbrown Member Posts: 3,268
    stryk wrote:
    How do you perform this "Update Statistics"? With the MP task, or some script? How big is the database?

    I'd recommend to use the "sp_updatestats" in combination with "sp_createstats 'indexonly'" as this - in my experience - gives sufficient stats maintenance within an acceptable runtime ...

    The database is about 350 GB (used). I switched last night form using a "Update Statistics" script to using sp_updatestats. Runtime went from 90 minutes to 15 minutes. The script was apparently doing too much work. Likely updatign stats on indexes that reallydid not need it.

    Thanks for the replies.
    There are no bugs - only undocumented features.

Categories