Author: Christofer Lindholm
Course: DE25
Date: 2025-11-16
This project contains a SQL Server stored procedure: dbo.Index_Cleaner
The purpose of the procedure is to automatically scan all user tables in the current database and perform intelligent index maintenance based on fragmentation levels.
Maintenance Actions:
REORGANIZE→ Indexes with 5-30% fragmentationREBUILD→ Indexes with >30% fragmentation
dbo.Index_Cleaner
@ScanMode CHAR(8) = 'LIMITED' (Default)
Controls the scanning method for fragmentation analysis.
Available Modes:
| Mode | Description | Performance | Accuracy |
|---|---|---|---|
| LIMITED | Quick scan (default) | Faster | Approximate |
| DETAILED | Full detailed scan | Slower | Precise |
Note: DETAILED mode is not recommended for large databases due to performance impact.
- Automatically detects fragmented indexes
- Applies appropriate maintenance based on fragmentation level
- Skips indexes below 5% fragmentation (no action needed)
- Uses
TRY/CATCHblocks to prevent procedure failure - Continues processing even if individual index operations fail
- Tracks and reports all failures with error messages
The procedure automatically excludes:
- Microsoft-created system objects
- Heaps (tables without clustered indexes)
- Indexes with fragmentation below 5%
- Timestamps for start and end times
- Execution duration calculation
- Comprehensive statistics summary
The procedure monitors and reports:
- Tables Scanned – Total number of user tables analyzed
- Indexes Scanned – Total number of indexes in the database
- Indexes Reorganized – Count of indexes with 5-30% fragmentation
- Indexes Rebuilt – Count of indexes with >30% fragmentation
- Indexes Failed – Count of operations that encountered errors
Uses a cursor to iterate through all fragmented indexes systematically.
Generates and executes ALTER INDEX commands dynamically for each table and index.
Leverages sys.dm_db_index_physical_stats to analyze index fragmentation levels.
- Select your target database
- Run the CREATE OR ALTER script to install the procedure
Quick Scan (Default):
EXEC dbo.Index_Cleaner;Detailed Scan:
EXEC dbo.Index_Cleaner @ScanMode = 'DETAILED';Results are printed to the Messages window with a detailed summary:
==============================================================
Index Cleaner Summary, Database: YourDatabaseName
==============================================================
Start Time: 2025-11-16 14:30:00
End Time: 2025-11-16 14:32:15
Duration: 135 seconds
--------------------------------------------------------------
Tables scanned: 42
Indexes scanned: 156
Indexes reorganized: 23
Indexes rebuilt: 8
Indexes failed: 0
==============================================================
- Regular maintenance schedules
- Large databases (>100GB)
- Production environments during business hours
- Quick health checks
- Post-migration analysis
- After major data loads
- Troubleshooting specific performance issues
- Small to medium databases (<50GB)
- During maintenance windows
Consider scheduling this procedure:
- Weekly for active databases
- Monthly for read-heavy databases
- After large batch operations
- During off-peak hours
If an index operation fails:
- The error is logged to the Messages window
- The failure counter is incremented
- Processing continues with the next index
- No data loss or corruption occurs
Example Error Output:
FAILED: [dbo].[TableName].[IndexName] Error: Index is being used by another process
This assignment demonstrates:
- Stored procedure development in T-SQL
- Cursor implementation and management
- Dynamic SQL generation and execution
- Error handling with TRY/CATCH blocks
- System catalog queries (DMVs)
- Database maintenance automation
- Performance monitoring and reporting
Impact on Database:
- REORGANIZE is an online operation (minimal locking)
- REBUILD may require more resources and locking
- Monitor system resources during execution
- Consider transaction log size for large rebuilds
Optimization Tips:
- Run during maintenance windows when possible
- Use LIMITED mode for regular maintenance
- Monitor execution duration trends
- Adjust fragmentation thresholds if needed
- SQL Server 2016 or later (recommended)
- Appropriate permissions (ALTER on tables)
- Sufficient transaction log space
- Database not in read-only mode
Educational project for DE25 course.
This procedure provides automated index maintenance without manual intervention. Regular execution helps maintain optimal query performance and prevents performance degradation due to index fragmentation.
For production use, consider:
- Testing in a non-production environment first
- Monitoring resource consumption
- Adjusting the 5% fragmentation threshold if needed
- Implementing as a SQL Server Agent job for automation