Why Not To Shrink A Database

In my last post, about maintenance plans, see here I spoke about the fragmentation caused by shrinking a database, and promised a follow-up post, with a demo of the issue. Well, this is it!

The problem arrises from the way the shrink occurs, and this applies to DBCC SHRINKFILE, DBCC SHRINKDATABASE and of course auto shrink. Basically, what happens is that SQL Server goes to the end of the file, picks up pages one-by-one and moves them to the first available space in the file. This can essentially reverse the order of your pages, turning perfectly defragmented indexes and perfectly fragmenting them.

For this demo, I am going to use use a table I created in my post here about why not to use GUIDs as Primary Keys, as it gives me a near perfectly fragmented set of indexes that I can defrag, and then mess up again!

So here is the fragmentation (found using sys.dm_db_index_physical_stats) before I have done anything...

 (No column name)   index_type_desc                 avg_fragmentation_in_percent avg_page_space_used_in_percent
  FragWithNewID     CLUSTERED INDEX            98.80952381                              72.04514455
  FragWithNewID     NONCLUSTERED INDEX    96.96969697                              78.59694836
  FragWithNewID     NONCLUSTERED INDEX    98                                             64.22041018

...Almost perfect fragmentation, so lets rebuild these indexes, and check the stats again...

ALTER INDEX [PK_Table_1] ON [dbo].[FragWithNewID] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )
GO
USE [GUIDFragmentation]
GO
ALTER INDEX [IX_Col1] ON [dbo].[FragWithNewID] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )
GO
USE [GUIDFragmentation]
GO
ALTER INDEX [IX_Col2] ON [dbo].[FragWithNewID] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )
GO

(No column name) index_type_desc                avg_fragmentation_in_percent   avg_page_space_used_in_percent
FragWithNewID    CLUSTERED INDEX           0                                                 99.2190140845071
FragWithNewID    NONCLUSTERED INDEX   1.88679245283019                       97.881504818384
FragWithNewID    NONCLUSTERED INDEX   3.03030303030303                       97.3163825055597

...Ahhh, that's better. Now lets shrink the database and try again...

DBCC SHRINKDATABASE(N'GUIDFragmentation' )
GO

(No column name)    index_type_desc                avg_fragmentation_in_percent avg_page_space_used_in_percent
FragWithNewID       CLUSTERED INDEX           73.7704918032787                     99.2190140845071
FragWithNewID       NONCLUSTERED INDEX   45.2830188679245                     97.881504818384
FragWithNewID       NONCLUSTERED INDEX   72.7272727272727                     97.3163825055597

...Uh, oh dear! Lets not do that again!