Slow performance after database shrink

  • Hi,

    We performed a database shrink on both log as well as data files as a last resort to save some space. It did helped us to resolve the space issue but the DB is running tremendously slow post that. We checked for the index fragmentation and reorganize/rebuilded them accordingly. Now I am not a DBA but somehow need to perform some of the activities. There is overall slowness in the DB not only in the tables with indexes but also in heap tables too. Data insert and select both are taking more time than it used to before shrinkage.

    Any pointers where should I look for the root cause and how to fix it? Any suggestions will be really helpful.

     

  • When you do a shrink-file on a data file, pages from the end of the file are moved to unused pages at the beginning of the file.  That very effectively will cause many indexes to be "inverted", which results in segment sizes of "1" just as surely as if the index were 99% fragmented and the pages aren't just out of order... they may be entirely reversed.

    The "root cause" you're looking for is the fact that you shrunk the database and it caused index inversion.

    Then, the massive secondary cause came into play... you did index maintenance but, if you were looking for fragmentation, you missed all the heaps because they don't "fragment".  They make forwarded rows instead and that means that you probably didn't rebuild the heaps that needed it.

    The reason why your data inserts are probably taking so long is because you did index maintenance on indexes that have a default "0" Fill Factor.  If they're prone to fragmenting, it's usually because of page splits and you just removed every last vestige of free space available in the index that was actually created by the act of page splitting but in a slower and more spread-out fashion before.  Of course, some of your heaps are backwards and those need to have their "tables" rebuilt.

    And that's all a bit of a catch 22 because you basically did a whole bunch of hole punching on the disk and your segment sizes are likely in the toilet even after rebuilds.

    I don't know how much offline time you might have but here's what I'd do because you need to get rid of a lot of the "hole punching".

    1.  Find the largest index .  If you have the space, create a filegroup/file just a little bigger than the index.  Do a CREATE INDEX WITH (DROP_EXISTING = ON) to move it to the new filegroup/file.  The reason why we're doing this is to temporarily make some room to help get rid of the hole punching and to make it so that the rebuild doesn't take up 100% more room equal to the size of the heap/index. If you have the room to do this to an extra 1 to 4 of your largest indexes, it would be worthwhile.
    2. Disable as many non-unique non-clustered indexes as possible.  This will also make a ton of room to help get rid of the hole punching.  Don't disable any unique indexes or other indexes that are enforcing constraints unless you also want to rebuild all of your FKs and possibly some other nasty stuff. 😉
    3. Rebuild your heaps from smallest to largest.
    4. Rebuild your non-unique non-clustered indexes that have NOT been disabled.  Rebuild them from smallest to largest.
    5. Rebuild your previously disabled non-clustered indexes from smallest to largest.
    6. Rebuild your clustered indexes from smallest to largest.
    7. Rebuild the largest index(es) you'd previously moved to a new filegroup/file to move it/them back to the primary file group from smallest to largest.
    8. Drop the now-empty new file and filegroup to return the disk space to the system.
    9. Disk space is pretty cheap... invest in some bigger disks.

    Hopefully, after all that, your segment sizes will be a who lot larger.  You should probably look into which indexes could make use of a Fill Factor.

    Whatever you do, stop using REORGANIZE unless you need to compress LOBS and then do a REBUILD right after that.  REORGANIZE doesn't work the way you probably think it does.

    If you ever need to shrink your database again (should be a VERY rare event), consider moving the largest index(es) to a new file group before you start.

     

    --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)
    Intro to Tally Tables and Functions

  • You likely shrunk the log file, but the db needs that log space so it is reallocating it.  Unfortunately, newly allocated log space requires pre-formatting.  That pre-formatting pauses all activity in the db until it is complete (SQL basically can't do anything on a read-write db without log space available just in case it's needed).

    The effect will be even far worse if you are allocating the log in 10% increments rather than a fixed amount.  If the log file (or any data files, for that matter) are incrementing by % rather than by fixed amount, change it immediately to a fixed amount!

    Back to the original issue: immediately pre-allocate the total log space the db ultimately will need, either in a single big chunk or in 2 (or more) smaller chunks.  Not too small, as the will give you too many (very) small VLFs.

    For example, say you have 100MB of log space but the db activity needs 5GB of total log space.  You could first bump the log size to 2.5GB, then to 5GB.  If it needed 10 total, to 5GB and then 10GB.  If more than that, you probably want to use more than 2 increments to allocate the total space.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden wrote:

    1. Rebuild your non-unique non-clustered indexes that have NOT been disabled.  Rebuild them from smallest to largest.
    2. Rebuild your previously disabled non-clustered indexes from smallest to largest.
    3. Rebuild your clustered indexes from smallest to largest.

    Shouldn't you rebuild clustered indexes before non-clustered?

    I think rebuilding a clustered index will fragment non-clustered indexes but rebuilding non-clustered indexes won't touch other indexes?

  • Jonathan AC Roberts wrote:

    Jeff Moden wrote:

    1. Rebuild your non-unique non-clustered indexes that have NOT been disabled.  Rebuild them from smallest to largest.
    2. Rebuild your previously disabled non-clustered indexes from smallest to largest.
    3. Rebuild your clustered indexes from smallest to largest.

    Shouldn't you rebuild clustered indexes before non-clustered?

    I think rebuilding a clustered index will fragment non-clustered indexes but rebuilding non-clustered indexes won't touch other indexes?

    That only occurs with heaps.  Rebuilding clustered indexes never fragments non-clustered indexes nor will it cause the non-clustered indexes to automatically rebuild.

    --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)
    Intro to Tally Tables and Functions

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

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