Understanding Microsoft SQL Index Fragmentation and How to Manage It

Introduction

Indexes in SQL Server play a crucial role in improving query performance by allowing faster access to data. However, over time, these indexes can become fragmented, leading to slower queries and increased system resource usage. In this post, we’ll explore what index fragmentation is, its types, and how to address it effectively.

What Is Index Fragmentation?

Index fragmentation occurs when the logical order of data pages in an index no longer matches their physical order on disk. This misalignment can cause SQL Server to work harder to retrieve ordered data, negatively impacting performance.

Types of Fragmentation:

1. Internal Fragmentation:

   • Happens when data pages contain excessive free space, often due to page splits during inserts or updates.
   • Leads to inefficient use of storage and additional I/O operations.

2. External Fragmentation:

• Occurs when the logical sequence of pages doesn’t align with their physical storage order.
• Results in extra effort for SQL Server to return ordered results.

When to Reorganize or Rebuild Indexes

To manage fragmentation, SQL Server provides two options:

• Reorganize:
  • A lightweight, online operation that defragments the index at the leaf level by reordering pages.
  • Minimal system resource usage and can be safely interrupted.
• Rebuild:
  • A more intensive process that completely recreates the index, removing fragmentation.
  • Can be done online or offline, depending on your SQL Server edition.
  • Requires more resources but provides thorough optimization.

Key Considerations for Online Index Rebuilds:

• Enterprise Edition: Supports online rebuilds, allowing uninterrupted access to data.
• Standard and Other Editions: Requires offline rebuilds, during which data access is temporarily restricted.

Best Practices

• Reorganize when fragmentation levels are between 5% and 30%.
• Rebuild when fragmentation exceeds 30%.

These thresholds may vary depending on workload and system specifics. Regular monitoring of fragmentation levels helps maintain optimal performance.

------------Code----------

SET NOCOUNT ON;

-- Create a temporary table for results
IF OBJECT_ID('tempdb..#Fragmentation') IS NOT NULL DROP TABLE #Fragmentation;

CREATE TABLE #Fragmentation (
    DatabaseName NVARCHAR(128),
    TableName NVARCHAR(128),
    IndexName NVARCHAR(128),
    IndexType NVARCHAR(60),
    AvgFragmentationPercent FLOAT,
    FragmentCount INT
);

-- Declare variables
DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

-- Iterate through all databases
DECLARE dbCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = '
    USE [' + @DBName + '];
    INSERT INTO #Fragmentation
    SELECT 
        DB_NAME() AS DatabaseName,
        OBJECT_NAME(ips.object_id) AS TableName,
        i.name AS IndexName,

        CASE 
            WHEN i.type = 1 THEN ''Clustered Index''
            WHEN i.type = 2 THEN ''Non-Clustered Index''
            WHEN i.type = 3 THEN ''XML Index''
            ELSE ''Unknown''
        END AS IndexType,

        ips.avg_fragmentation_in_percent AS AvgFragmentationPercent,

        ips.page_count AS FragmentCount

    FROM 
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') ips

    JOIN 
        sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id

    WHERE 
        ips.avg_fragmentation_in_percent > 5

    AND 
        ips.page_count > 1;';

    EXEC sp_executesql @SQL;
    FETCH NEXT FROM dbCursor INTO @DBName;
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

-- Display results
SELECT * FROM #Fragmentation
ORDER BY DatabaseName, AvgFragmentationPercent DESC;

-- Clean up
DROP TABLE #Fragmentation;