DBCC ShrinkDatabase, Will it cause Fragmentation in Index: Fragmentation Series 3

Recently we had a discussion if ShrinkDatabase will cause fragmentation and if we need to reindexing and Shrinking of database as a maintenance plan what should be order of operations: ShrinkDB followed by Reindexing or Reindexing post ShrinkDB.

Before starting as any good DBA would suggest, Shrinking a database or Database file should not be considered at all atleast of production, for it has adverse impact on performance. The reason file expanded was it needed space in first place so shrinking database / file to reclaim disk space is not right approach as database would grow again.

But there may be some scenarios where it makes sense to Shrink Database.. One that comes on top of my head is an example where a huge table is deleted from database as it is no longer required which accounts for 50% of database size. In such cases it may be absolutely valid for database to be shrunk.

Now since disclaimer is over :) would like to get into Nitty-gritty of Fragmentation with ShrinkDatabase.

For repro here is the plan: (Please do not try in Production Environment)

Step 1: Create Database (on drive that has couple of DBs free space)

Step 2: Create huge Table with lots of pages (couple of thousands amounting to GIGS)

Step 3: Create smaller table (Ensure pages for smaller table come in end after Huge Table)

Step 4: Create index on Smaller table

Step 5: Populate Data in Smaller Table

Step 6: Take DBCC IND output for Huge Table and Smaller Table.

Step 7: Run DBCC REINDEX on smaller table to ensure no fragmentation.

Step 7: Get sys.dm_db_index_physical_stats output for smaller tables.

Step 8: Get Sp_SpaceUsed for database.

Step 9: Truncate Huge Table

Step 10: Get SP_SpaceUsed again for Database

Step 11: Shrink Database using DBCC ShrinkDatabase

Step 12: Get DBCC IND output for smaller Table

Step 13: Get sys.dm_db_index_physical_stats for smaller table and compare fragmentation.

Since this notes is growing to be long will put it in a new series.. these above steps would prove that Shrinking would introduce fragmentation of indexes.

Summarizing:

* Shrinking Databases and Files should be avoided.

* If Shrinking fragmentation is introduced and rebuilding is necessary to remove fragmentation.

* Always follow Shrinking with Reindexing for optimal performance.