Why not to shrink your database

I receive a lot of client calls in regards to SQL Server performance.  One of the most common issues is slow query performance and high CPU usage.  Normally, this is caused by index fragmentation or poorly written queries.  Today, I'm going to cover index fragmentation and what usually causes it.

A lot of new DBAs are interested in saving disk space.  To achieve this goal, they will often perform a database shrink, sometimes even after they rebuild the indexes in a maintenance plan that runs every night.

A database shrink, not to be confused with a file shink of the log file, will reclaim unused space in the database file.  The unused space is a result of data being deleted or moved due to a full page.  Honestly, it sounds like a good idea, until you step back and look at what an index actually is, how it works, and how it is relevant to data.

A clustered index actually works at the data level.  In the index B-tree, the clustered index catalogs the data on the actual page it resides on.  So, if data is moved, then that part of the index is fragmented.  During a shink, SQL will re-arrange or move the data to other pages which causes index fragmentation.  Below is an example.

USE master;
GO


--If the database MyDB exists, delete it
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name=N'MyDB')
DROP DATABASE MyDB;
GO


--Creates the database MyDB
CREATE DATABASE MyDB
GO
use MyDB
GO


--Creates a table Persons1 with 2 fields, ID and Column1
CREATE TABLE Persons1 (ID INT IDENTITY, Column1 Char(8000) DEFAULT 'Test')
GO


--Creates a clustered index on the ID field of the persons1 table
create clustered index idx_ID on dbo.persons1(ID)
GO


--Inserts 1280 records to the Persons1 table
INSERT INTO Persons1 DEFAULT VALUES;
GO 1280


--Shows the percentage of framentation of the clustered index
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats (
    DB_ID ('MyDB'), OBJECT_ID ('Persons1'), 1, NULL, 'LIMITED');
GO

--Deletes the first 500 rows of the Person1 table
delete TOP(500) from Persons1
GO


--Shows the percentage of framentation of the clustered index
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats (
    DB_ID ('MyDB'), OBJECT_ID ('Persons1'), 1, NULL, 'LIMITED');
GO

--Shrinks the database files, reclaiming space in the files.  It also
--fragements the clustered index extremely bad
dbcc shrinkdatabase(MyDB)
GO

--Shows the percentage of framentation of the clustered index
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats (
    DB_ID ('MyDB'), OBJECT_ID ('Persons1'), 1, NULL, 'LIMITED');
GO

With my results of a database shrink using 2 fields and 780 records, a shrink caused roughly 63% fragmentation.  Imagine what the results would be on a database with a lot of tables, records, and indexes.  So, shrinking the database is never a good idea as it causes massive fragmentation of the indexes.