Post reply

IX rebuilds, stats , stats with full scan

  • November 22, 2023 at 7:29 pm

    #4332632

    Hello Gurus,

    so the App team complained that from 14 Nov , every hour they are seeing sporadic timeouts , ofcourse the tables being touched are 60 M rows each and have data since 2019,  This is on Azure MI Business critical.  MS recommends running update stats Tablename with fullscan on the large table , But that will change the exec plan, I believe, Regular maintenance is done via Ola Hallegran IX rebuild scripts followed by exec sp_updatestats ( which uses a small sample ) and completes in 5 minutes  on the powerful 40 CPu machine.  Should the sequence be , just Ola's script followed by exec sp_updatestats with no  Custom IX Rebuilds with (online=on) and update stats tablename with fullscan every now and then to fix performance issues on the fly?

    Thank you for the inputs.

     

  • November 23, 2023 at 4:28 pm

    #4333170

    Just a few "litmus strip" questions to start with:

    1. What is the exact call code that you're using to call Ola's script? (Please post it for the database in question without the name of the database so as to not expose that to the public).
    2. How often are you doing your "regular" index maintenance?
    3. Do any of the indexes that you're rebuilding have a Fill Factor of "0"?
    4. Are you excluding the indexes that have been rebuilt from the sp_updatestats run?
    5. When WAS the last time you did a FULL SCAN stats update on the indexes for the "large table" table that MS was talking about?
    6. What changed in the week prior to Nov 14th?
    7. This one is super important... you say that "every hour they are seeing sporadic timeouts".  Do they coincide with any particular activity such as a scheduled job (either internal or externally controlled)?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

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