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