DBA Scripts: Get Index Fragmentation

Part of the DBA-Tools Project.

Index fragmentation is one of those topics where the received wisdom, “rebuild your indexes every weekend”, causes almost as many problems as it solves. Rebuilding an index that doesn’t need it wastes a maintenance window, takes a schema lock on Standard Edition, and generates unnecessary transaction log. Not rebuilding an index that does need it leaves queries doing more physical I/O than they should. The goal is to find and fix the indexes actually causing a problem, and leave the rest alone.

This script scans every online user database for fragmented indexes above a sensible size threshold, and already classifies each one as REBUILD or REORGANIZE so you know what to do with it before you’ve even opened the result set.

---

Why Index Fragmentation Matters

When SQL Server inserts or updates rows, it maintains index order by splitting pages. Over time this creates two kinds of fragmentation. External fragmentation is when pages are out of logical order on disk, so what should be a sequential read becomes a scattered one. Internal fragmentation is when pages are only partially full, so more pages are needed to hold the same amount of data, meaning more I/O for the same query.

The practical effect: queries that should be doing efficient range scans do more physical reads than necessary. On large, heavily fragmented tables this shows up as elevated PAGEIOLATCH_SH waits.

• Matters more on high-volume OLTP tables with frequent writes and large range-scan queries, and on spinning disk
• Matters less on small tables, tables on NVMe SSD (where random I/O is cheap), heaps (no clustered index to fragment), and read-only databases

---

When to Run This Script

• Routine SQL Server health checks
• Performance reviews where I/O-bound queries are suspected
• Before scheduling or reviewing an index maintenance window
• Alongside Get Heaps and Duplicate Indexes as part of a general index-hygiene pass

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-IndexFragmentation
Category    : maintenance-and-reliability
Purpose     : Top fragmented indexes across all user databases, ranked by fragmentation pct.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : VIEW SERVER STATE
Notes       : Iterates every online user database and collects into a single result set.
              Uses LIMITED scan mode — faster than SAMPLED/DETAILED but still proportional
              to database size. Expect 30 s to several minutes on busy or large instances.
              Indexes under 1000 pages are excluded; fragmentation threshold is 10%.
              Run off-peak where possible.
*/
-- SAFE:ReadOnly
-- IMPACT:Medium
SET NOCOUNT ON;
-- Fixes : sql\maintenance\Generate-IndexMaintenanceScript.sql

CREATE TABLE #frag (
    database_name      sysname         NOT NULL,
    schema_name        sysname         NOT NULL,
    table_name         sysname         NOT NULL,
    index_name         sysname         NOT NULL,
    index_type         nvarchar(60)    NOT NULL,
    fragmentation_pct  decimal(5,1)    NOT NULL,
    page_count         bigint          NOT NULL,
    recommended_action varchar(10)     NOT NULL
);

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
USE ' + QUOTENAME(name) + N';
INSERT INTO #frag
    (database_name, schema_name, table_name, index_name,
     index_type, fragmentation_pct, page_count, recommended_action)
SELECT
    DB_NAME(),
    s.name,
    t.name,
    i.name,
    i.type_desc,
    CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)),
    ips.page_count,
    CASE
        WHEN ips.avg_fragmentation_in_percent >= 30 THEN ''REBUILD''
        WHEN ips.avg_fragmentation_in_percent >= 10 THEN ''REORGANIZE''
    END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') AS ips
JOIN sys.indexes AS i ON  ips.object_id = i.object_id
                      AND ips.index_id  = i.index_id
JOIN sys.tables  AS t ON  i.object_id   = t.object_id
JOIN sys.schemas AS s ON  t.schema_id   = s.schema_id
WHERE i.name IS NOT NULL
  AND ips.page_count                   >= 1000
  AND ips.avg_fragmentation_in_percent >= 10;
'
FROM sys.databases
WHERE state_desc  = 'ONLINE'
  AND database_id > 4;

IF LEN(@sql) > 0
    EXEC sys.sp_executesql @sql;

SELECT
    database_name,
    schema_name,
    table_name,
    index_name,
    index_type,
    fragmentation_pct,
    page_count,
    recommended_action
FROM   #frag
ORDER BY fragmentation_pct DESC;

DROP TABLE #frag;

This script scans every online user database using LIMITED scan mode, fast enough for most environments though less precise than DETAILED, and returns every index over 1,000 pages with fragmentation of 10% or higher, already classified as REBUILD or REORGANIZE.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Find fragmented indexes across all databases:
.\run.ps1 Get-IndexFragmentation

# To run against a remote sql server:
.\run.ps1 Get-IndexFragmentation -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/performance/indexes/Get-IndexFragmentation.sql
• powershell/wrappers/performance/indexes/Get-IndexFragmentation.ps1

---

Example Output

FragmentationWeapon is a lab table built specifically to fragment badly; 99.1% on a 238,205-page clustered index is about as severe as it gets, and a realistic stand-in for what a genuinely neglected table looks like.

---

Understanding the Results

Column	What It Means
fragmentation_pct	Percentage of pages out of order. The primary sort column.
page_count	Number of 8KB pages in the index. Fragmentation on a 100,000-page index matters far more than on a 1,000-page one.
recommended_action	REBUILD at ≥30%, REORGANIZE at 10–29%. See below for when to override this.
index_type	CLUSTERED fragmentation affects every read of the table; NONCLUSTERED affects only queries using that specific index.

Rule of thumb: 10–30% → REORGANIZE. Over 30% → REBUILD, online if the edition allows it. Over 30% on Standard Edition with a large table and no maintenance window → REORGANIZE instead and accept the trade-off.

---

How to Fix Index Fragmentation

REORGANIZE defragments the leaf level in place, page by page. It’s always online, can be interrupted and restarted, but doesn’t update statistics and can’t compact below the current fill factor.

REBUILD drops and recreates the index with a fresh fill factor, updates statistics as a side effect, and reclaims all free space. On Enterprise/Developer with WITH (ONLINE = ON) the table stays accessible throughout; on Standard Edition a rebuild takes a schema modification lock, and the table is inaccessible for the duration, potentially minutes to hours on a large table.

-- Enterprise / Developer: online rebuild (no table lock)
ALTER INDEX [IX_Example] ON [dbo].[YourTable]
    REBUILD WITH (ONLINE = ON);

-- Standard Edition: offline rebuild (table unavailable for the duration)
ALTER INDEX [IX_Example] ON [dbo].[YourTable]
    REBUILD;

-- Always available, no table lock, suited to lower fragmentation
ALTER INDEX [IX_Example] ON [dbo].[YourTable]
    REORGANIZE;

The companion script Generate-IndexMaintenanceScript.sql generates these ALTER INDEX statements automatically for everything this script flags, reviewed against the same thresholds. Run the diagnostic first to understand scope, then the generator to produce the actual DDL, rather than looping blindly through every fragmented index.

---

Best Practices

• Prioritise CLUSTERED indexes on high-write tables with >30% fragmentation first; every range scan against that table is affected.
• If fragmentation returns to a high level within a week of the last rebuild, the index is fragmenting faster than the maintenance cycle can keep up. Consider a higher fill factor or more frequent maintenance rather than just rebuilding again.
• On all-flash storage, the threshold for caring can reasonably shift upward. Random I/O is cheap on NVMe; some environments only rebuild past 50%.
• LIMITED scan mode can miss some fragmentation. Use DETAILED for precise numbers if you have the time budget for a longer scan.
• REORGANIZE does not update statistics. If query plans look stale, that needs a separate UPDATE STATISTICS pass, or use REBUILD instead.

---

Related Scripts

You may also find these scripts useful:

• Index Maintenance (hub)

• Duplicate Indexes

• Heaps
• Index Design Issues
• Index Fragmentation Across Databases
• Index Usage Stats
• Missing Indexes
• Unused Indexes

---

Frequently Asked Questions

Does index fragmentation always slow down queries?

Not always. On small tables, or on NVMe SSD storage where random I/O is cheap, high fragmentation can exist without any noticeable query impact. It matters most on large, high-volume tables doing range scans on spinning or network-attached storage.

Does a SQL Server restart fix fragmentation?

No. Fragmentation is a property of the pages on disk, not something that resets with the service. Only a REBUILD or REORGANIZE changes it.

---

Summary

Fragmentation is one of the easiest index health checks to automate, but the value is in fixing the indexes that matter and leaving the rest alone, not in rebuilding everything on a fixed weekly schedule regardless of whether it needs it.

Run this script as part of your regular health checks, feed the results into Generate-IndexMaintenanceScript.sql to produce the actual remediation, and schedule REBUILDs that need an offline window deliberately rather than as a surprise.