• pcd_au - Sunday, February 11, 2018 8:40 PM

    I'm stumped and need the assistance of the wider community to understand what is going on.

    • Disable all non clustered indexes
    • Shrink the database - no truncate
    • Rebuild all clustered indexes
    • Rebuild all non clustered indexes (re-enabling them)
    • Shrink the database - truncate only

    Performance improves significantly, but fragmentation levels are around the same.

    I'm at a loss to explain what is going on here, can anyone help me understand why this is occurring?

    pcd
    PS Please don't go on about not shrinking the database - I'm fully aware of that etc. The suggestion came from someone on the team.

    Performance of what "improves significantly"?

    It could be from things like the fact that disabling an index also disables any FK that may be based on those indexes.  Of course, that won't affect SELECTs.... just INSERTs, DELETEs, and some UPDATEs.  Any FKs of that nature must be rebuilt using ALTER TABLE CHECK CONSTRAINT to become active again. 

    The other thing is, how are you doing your normal index maintenance?  If it's a mature database and it normally does most REORGANIZE instead of REBUILD, that could be it.  I'm working on a project that may prove that but I'm not done yet so I can only say that as a suggestion rather than a demonstrable fact.

    And, sorry... I have to say that routine shrinking of files or databases is a total waste of time. 😉  Doing it for reasons of recovering space after a "rampant growth accident" might also the wrong thing to do if you don't do it correctly and, it looks like you might be doing it incorrectly.  If you pack everything down and then rebuild your indexes, you will have wasted space equal to about 120% of your largest pagecount index and that last shrink with TRUNCATE ONLY won't take it (unwanted free space) out because your index rebuilds build a new copy of the index at the logical end of the file (after a shrink) because there's no room at the beginning of the shrunk file.  If the goal really is to recover disk space, then you need to create a new temporary file group, rebuild your largest index(es) to that (moving the index using the DROP EXISTING option), do your first shrink, do all your other rebuilds not once but twice to avoid the end-of-file problem, and then rebuild the largest index back to that original file group and drop the temporary file group.  Again, that's just a suggestion at this point... I'm not done experimenting with all this and it could be several months (if at all) before I have a demonstrable example.

    --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)