Index Fragmentation Above Threshold: Fix in 3 Steps

What You're Dealing With

Index fragmentation means your data pages are out of order—like a bookshelf where someone shoved books in randomly, leaving gaps. SQL Server has to read more pages than necessary, and your queries crawl. You'll see high logical reads, blocking, and complaints from users about reports taking forever.

I've seen this blow up on OLTP systems where inserts and updates are constant. Had a client last month—their main order table hit 70% fragmentation. Their nightly batch job went from 20 minutes to 3 hours. The fix took 10 minutes.

Step 1: Quick Fix (30 seconds) – Check Fragmentation Level

Before you do anything, run this query to see what you're dealing with. Don't guess—measure.

SELECT 
    OBJECT_NAME(ips.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
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 > 30
ORDER BY ips.avg_fragmentation_in_percent DESC;

If you see fragmentation under 30%, skip to Step 2—you might not need a full rebuild. Over 30%? That's your target. Over 60%? You're in trouble. Ignore indexes with page_count under 1000—rebuilding small indexes wastes I/O.

Step 2: Moderate Fix (5 minutes) – Reorganize vs Rebuild

Here's the rule I use: Fragmentation between 10% and 30%? Reorganize. Over 30%? Rebuild. Reorganize defragments by moving pages around—it's online, no blocking, but slower for high fragmentation. Rebuild drops and recreates the index—faster but locks the table (unless you use ONLINE option in Enterprise Edition).

Reorganize (for moderate fragmentation)

ALTER INDEX [IndexName] ON [Schema].[TableName] REORGANIZE;

Run this during off-peak hours if possible. It's safe to run during light usage, but don't do it while users are running heavy reports.

Rebuild (for high fragmentation)

ALTER INDEX [IndexName] ON [Schema].[TableName] REBUILD;

If you're on SQL Server Standard Edition, this will take a schema modification lock. Schedule it during maintenance windows. I had a client try to rebuild a clustered index on a 200GB table at 2 PM—the app froze for 8 minutes. Don't be that person.

If you have Enterprise Edition, add WITH (ONLINE = ON) to avoid blocking:

ALTER INDEX [IndexName] ON [Schema].[TableName] REBUILD WITH (ONLINE = ON);

Step 3: Advanced Fix (15+ minutes) – Automate with Ola Hallengren's Scripts

Running manual ALTER INDEX on every table is a pain. In production environments, you need something smarter. Ola Hallengren's maintenance scripts are the gold standard—been using them for years, never had a failure.

1. Download MaintenanceSolution.sql from ola.hallengren.com.
2. Run it on your server—it creates stored procedures and jobs.
3. Create a SQL Agent job that calls sp_IndexOptimize with your thresholds.

Example call

EXEC dbo.sp_IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @LogToTable = 'Y';

This script handles edge cases: heaps, XML indexes, large lobs. It logs everything to a table so you can audit later. Set it to run weekly on a Saturday night.

One More Thing – Check Your Fill Factor

If fragmentation keeps coming back, your fill factor is likely set wrong. Default is 0 (100% full). That means every insert that doesn't fit causes a page split—fragmentation city. For tables with heavy inserts, set fill factor to 80 or 90.

ALTER INDEX [IndexName] ON [Schema].[TableName] REBUILD WITH (FILLFACTOR = 80);

Don't just rebuild—think about why it fragmented in the first place. Most of the time, it's poor fill factor or no maintenance schedule. Fix that, and you'll sleep better.

Quick Summary

• Check fragmentation with the DMV query.
• Rebuild if over 30%, reorganize under 30%.
• Automate with Ola Hallengren for recurring maintenance.
• Adjust fill factor if you see constant page splits.

I've walked through hundreds of servers that had index fragmentation ignored for years. A 15-minute rebuild can cut query times in half. Don't overthink it—just do it.