SQL Server Reorganize and Rebuild Indexes

Index Fragmentation (Rebuild or Reorganize an Index)

  • When index fragmentation occurs should you reorganize or rebuild a fragmented index (it depends)
  • Whenever an insert, update, or delete operations occur against the underlying data, SQL automatically maintains indexes
  • These operation cause index fragmentation and can cause performance issues
  • Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file
  • When you Rebuild an index SQL drops and re-creates the index and removes fragmentation, reclaims disk space by compacting and reorders the index rows in contiguous pages
  • When you Reorganize an index it defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level
  • The system function sys.dm_db_index_physical_stats, allows you to detect fragmentation
  • If avg_fragmentation_in_percent value
  • > 5% and < = 30%    REORGANIZE
  • > 30%                        REBUILD
  • Rebuilding an index can be executed online or offline.
  • Reorganizing an index is always executed online.

USE [master];
GO
 
CREATE DATABASE ShrinkDB;
GO

USE [ShrinkDB];
GO
 
-- Create an initial table at the 'front' of the data file
CREATE TABLE Initial (
    [col1] INT IDENTITY,
    [col2] CHAR (8000) DEFAULT 'Front');
GO

-- Insert data into Initial table
INSERT INTO Initial DEFAULT VALUES;
GO 1500

select * from Initial

--check the size of the database
sp_helpdb [ShrinkDB]
--14.83 MB
 
-- Create the second table, which will be created 'after' the initial table in the data file
CREATE TABLE second (
    [col1] INT IDENTITY,
    [col2] CHAR (8000) DEFAULT 'after');

--create a clusterd index on the second table
CREATE CLUSTERED INDEX [coll] ON second ([col1]);
GO
 
-- Insert data into second table
INSERT INTO second DEFAULT VALUES;
GO 1500

select * from second

--check db size 
sp_helpdb [ShrinkDB]

--database expanded due to insert of data in the second table (26.83 MB)
-- Check the fragmentation of the second table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'ShrinkDB'), OBJECT_ID (N'second'), 1, NULL, 'LIMITED');
GO

--notice that the fragmentation of the clustered index for the second table is almost zero before the shrink
--0.333333333333333
--We will now drop the initial table we created and execute the shrink command to reclaim the SPACE at the front of the data file
-- then see what happens to the fragmentaion.

DROP TABLE Initial;
GO

sp_helpdb [ShrinkDB]
-- 26.83 MB the data file has not shrunk due to the deletion of the initial table
-- Shrink the database
DBCC SHRINKDATABASE ([ShrinkDB]);
GO

--notice that the SPACE after the shrink went down from 26.83 to 15.02 mb
sp_helpdb [ShrinkDB]
--15.02 MB
 
-- But notice what happened to the fragmentation of the data file because of the shrinking of the database???
--when Checking the index fragmentation again, we notice that the fragmentation has drastically increased to almost 100%!!!
--this is because we have shuffled all the data pages and the index is not in a sorted position

SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'ShrinkDB'), OBJECT_ID (N'second'), 1, NULL, 'LIMITED');
GO

--99.6
--while the database has shrunk, and we have reclaimed space from the data file, we MUST now the fix the fragmented index of the table by rebuilding the index!!!
-- Rebuild or Reorganize the Clustered Index?
--If avg_fragmentation_in_percent value
--> 5% and < = 30%    REORGANIZE
--> 30%               REBUILD 


ALTER INDEX [coll] ON second REBUILD
GO

ALTER INDEX [coll] ON second REORGANIZE
GO

-- Checking the index fragmentation again indicates that the fragmentaion of the index has been restored, but notice the size of the data
--file when we run the sp_helpdb [ShrinkDB] - it has actually GROWN even more than it started from!!!

SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'ShrinkDB'), OBJECT_ID (N'second'), 1, NULL, 'LIMITED');
GO

--0.2%

sp_helpdb [ShrinkDB]
--41.81 MB

--the database file has grown because of the rebuilding of the index and the logging of the index