Or “Order the pages, shuffle the pages.”
Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again and, especially if the data files are on independent drives, there’s little difference between space free on the drive or space free in the data file. There is also a more insidious reason for not shrinking a database.
Let’s take a very simple database (The creation code is at the end of the post). I have two tables, both with a tens of thousands of rows. Both tables have a clustered index on a uniqueidentifier and are heavily fragmented (>99%).
DBCC SHOWCONTIG(LargeTable1) -- 99.30%
DBCC SHOWCONTIG(LargeTable2) -- 99.21%
To fix the fragmentation, rebuild both indexes. That fixes the fragmentation, but now the data file is using almost twice the space necessary.
DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)
So, shrink the database to release the wasted space back to the OS
DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free
That’s fixed the space issue. But now, have another look at those two indexes that were just rebuilt.