August 1, 2021 at 2:20 pm
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.
August 2, 2021 at 12:06 am
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".
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
Change is inevitable... Change for the better is not.
August 2, 2021 at 3:33 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 2, 2021 at 11:14 am
- Rebuild your non-unique non-clustered indexes that have NOT been disabled. Rebuild them from smallest to largest.
- Rebuild your previously disabled non-clustered indexes from smallest to largest.
- 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?
August 2, 2021 at 11:17 am
Jeff Moden wrote:
- Rebuild your non-unique non-clustered indexes that have NOT been disabled. Rebuild them from smallest to largest.
- Rebuild your previously disabled non-clustered indexes from smallest to largest.
- 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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply