Automating SQL Server Index Maintenance Using Dynamic T‑SQL

Index fragmentation is one of the most common performance bottlenecks in SQL Server environments. As data grows and workloads increase, regular index maintenance becomes essential to ensure optimal query performance, minimize I/O overhead, and keep execution plans efficient.

In this blog, we’ll walk through a dynamic T‑SQL script that automatically identifies fragmented indexes and rebuilds them online. This script is especially helpful for DBAs who want to automate maintenance tasks or integrate them into SQL Agent jobs.

📌 Why Index Fragmentation Matters

Fragmentation occurs when logical ordering of index pages no longer matches the physical ordering on disk. High fragmentation typically results in:

• Increased I/O operations
• Slower range scans
• Inefficient buffer usage
• Poor query performance

SQL Server provides sys.dm_db_index_physical_stats to examine fragmentation levels, and that’s the view we leverage in our script.

🧱 Script Overview

The script performs the following steps:

1. Scans all indexes using sys.dm_db_index_physical_stats (sampled mode)
2. Filters indexes with:
   • Fragmentation > 25%
   • Page count > 100 (to avoid wasting time on small indexes)
3. Generates dynamic SQL to rebuild each fragmented index
4. Executes the generated ALTER INDEX commands

Let’s break down the script.

🧩 T‑SQL Script

DECLARE @sql NVARCHAR(MAX) = N'';
  
;WITH Frag AS (
    SELECT 
        OBJECT_SCHEMA_NAME(ips.object_id) AS SchemaName,
        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, 'SAMPLED') 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 > 25  
      AND ips.page_count > 100
)
SELECT @sql = @sql +
    'ALTER INDEX [' + IndexName + '] ON [' + SchemaName + '].[' + TableName + '] REBUILD WITH (ONLINE = ON);' + CHAR(10)
FROM Frag;
  
PRINT @sql;
EXEC(@sql);

DECLARE @sql NVARCHAR(MAX) = N'';
  
;WITH Frag AS (
    SELECT 
        OBJECT_SCHEMA_NAME(ips.object_id) AS SchemaName,
        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, 'SAMPLED') 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 > 25  
      AND ips.page_count > 100
)
SELECT @sql = @sql +
    'ALTER INDEX [' + IndexName + '] ON [' + SchemaName + '].[' + TableName + '] REBUILD WITH (ONLINE = ON);' + CHAR(10)
FROM Frag;
  
PRINT @sql;
EXEC(@sql);

🧠 How the Script Works

1. Capturing Fragmentation Data

sys.dm_db_index_physical_stats returns fragmentation levels for all indexes.
Using SAMPLED mode keeps the process lightweight while maintaining accuracy.

2. Filtering Fragmented Indexes

We rebuild only when:

• Fragmentation > 25% → best practice threshold for rebuild
• Page count > 100 → avoids micro‑indexes that don’t need maintenance

3. Building Dynamic SQL

Instead of manually writing dozens of ALTER INDEX commands, the script constructs them:

ALTER INDEX [IX_Sample] ON [dbo].[Sales] REBUILD WITH (ONLINE

The ONLINE = ON option ensures minimal locking and business disruption — a key requirement for production OLTP systems.

4. Executing the Maintenance Commands

After printing the generated statements (for verification), the script executes them.

⚙️ Why Use Dynamic SQL?

Using dynamic SQL here offers flexibility:

• Automates index maintenance across all schemas
• Easily customizable thresholds
• Minimizes manual intervention
• Ensures consistent maintenance across the entire database

You can also plug this script directly into a SQL Agent job to run nightly or weekly.

🏎️ Performance Tips & Best Practices

1. Use REORGANIZE for 10–25% fragmentation
   Rebuilds are heavier; reorganizes are lighter and online by default.
2. Avoid ONLINE = ON for Standard Edition
   SQL Server Standard Edition doesn’t support online rebuild for all index types.
3. Monitor index usage
   Avoid rebuilding unused indexes using sys.dm_db_index_usage_stats.
4. Create a job schedule
   Running during off‑peak hours reduces impact on user workloads.

📈 Final Thoughts

This script provides a clean, automated way to handle fragmented indexes dynamically, ensuring your SQL Server databases continue performing efficiently with minimal administrative overhead. As your data grows, having automated index maintenance isn’t just a “nice to have”—it becomes a foundational aspect of database health.

If you’d like, I can help you:

✅ Convert this into a SQL Agent job
✅ Add REORGANIZE logic and thresholds
✅ Build a full index maintenance framework
✅ Publish this as a formatted LinkedIn blog post

Just let me know!