0

If I have an index with a default fill factor of 100%, and modifications result in a large amount of internal fragmentation (page splits, free space, etc.) so that many pages end up around 60% full instead of 100%, would an index rebuild/reorganize compact the data into fewer data pages, thus resulting in more available space reported in the database?

If the previous is true, does it then make sense to reorganize indexes before performing a shrink operation to maximize the space gained by the shrink (followed by another reorganize after the shrink to reduce fragmentation caused by the shrink)?

Finally, does DBCC Shrinkfile compact data pages the way a rebuild does, or does it only move data pages around?

Improve this question

2 Answers 2

Reset to default
0

would an index rebuild/reorganize compact the data into fewer data pages, thus resulting in more available space reported in the database?

Yes. You are correct.

maximize the space gained by the shrink

Usually it doesn't worth the efforts.

followed by another reorganize after the shrink to reduce fragmentation caused by the shrink

All operations: shrink and reorg are pretty expensive so it doesn't make sense to run them 'just in case'.

does DBCC Shrinkfile compact data pages the way a rebuild does, or does it only move data pages around?

It only moves data pages.

From my point of view you are fighting for space (cheapest resource for modern databases) by CPU, memory and network bandwidth (more expensive ones)

Improve this answer
1
  • Thank you, this answers everything. Agreed on your last point regarding fighting for space. I have to work with limited storage at the moment so I'm trying to optimize this process as much as possible. Now that I know that regaining some extra available space from a reorganize is possible on paper, I'll test it to see what the potential gains are.
    – Marcus
    Commented Jan 26 at 14:07
0

would an index rebuild/reorganize compact the data into fewer data pages, thus resulting in more available space reported in the database?

The purpose of index maintenance is to reduce index fragmentation, not save space. But in its process it needs to utilize available space that's almost twice the size of the index to do the actual reorganization of the data in it. So effectively index maintenance is wasteful and consumes more space.

It's also a waste of an operation and almost never needed. It's heavy, causes resource contention, and any perceived performance gains from it are actually from secondary related processes that occur (statistics updates and clearing query plans from the cache) that can be ran individually and separately.

If the previous is true, does it then make sense to reorganize indexes before performing a shrink operation to maximize the space gained by the shrink

Big nope. Shrinking causes indexes to re-fragment, undoing most of the work you just spent on doing index maintenance. They're counterintuitive to each other.

Also, shrinking is a heavy operation that can cause resource contention too, and should rarely be used. It's meant for only special one-off cases.

Here's some related resources from experts:

  1. Index Maintenance Madness
  2. Stop Shrinking Your Database Files. Seriously. Now.
Improve this answer
26
  • Agreed on most points. But the storage space available to me is very limited so needs must sometimes. Regarding fragmentation post-shrink, I usually resolve that with a reorganize (which only grows out the transaction log and is easily shrinkable).
    – Marcus
    Commented Jan 26 at 14:09
  • @Marcus Storage space is cheap, slow databases aren't. But to each their own. Shrinking I don't have as much of a problem with. Index maintenance is something that should just straight up be removed from the product though. "Regarding fragmentation post-shrink, I usually resolve that with a reorganize (which only grows out the transaction log and is easily shrinkable)." - Heh, you realize you just said you'll shrink your database to save space but run index maintenance to regrow it to fix fragmentation but then shrink it again to fix the growth which will now result in fragmentation again?
    – J.D.
    Commented Jan 26 at 14:32
  • ^...it's a vicious cycle that doesn't actually help you and a trap people fall into, as discussed in some of the articles I mentioned and other related articles on there. Thanks for listening to my TED Talk, feel free to upvote if you desire, so others may find it helpful as well.
    – J.D.
    Commented Jan 26 at 14:34
  • I'm shrinking the data file initially – the follow up index maintenance will only grow the log file which can be shrunk easily. This leaves the DB occupying a smaller size than before sans fragmentation. It can be cyclical for DBs that are expected to grow again, but sometimes it's necessary because of storage space constraints. I agree index maintenance isn't worth the effort in most cases, but if I can avoid 99% fragmentation on all tables post a required shrink then it's not an issue.
    – Marcus
    Commented Jan 27 at 9:01
  • @Marcus That's a lot of unneeded heavy work against your database. I guess resource contention isn't a concern though. "but if I can avoid 99% fragmentation on all tables post a required shrink then it's not an issue" - If the 99% fragmentation doesn't mean anything in reality, then why does it matter to you?
    – J.D.
    Commented Jan 27 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.