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.
