sp_IndexCleanup | Darling Data

sp_IndexCleanup – SQL Server Index Housekeeping Tool

Welcome to the home of sp_IndexCleanup, a flexible SQL Server stored procedure for identifying and removing duplicate, redundant, and unused indexes.

As an added bonus, it will also find uncompressed indexes and give you compression scripts for them.

This page explains the functionality and usage of the tool to help you maintain cleaner, more efficient SQL Server databases.

View the full README on GitHub

Overview

sp_IndexCleanup finds duplicate, redundant, and unused indexes in your databases.

It thoroughly analyzes index usage, considers included columns, checks unique constraints, and provides helpful removal scripts when it’s safe to drop an index.

It also looks at overlapping (superset/subset) index keys, and compares includes for merging.

Unique Features

What makes sp_IndexCleanup different from other index analysis tools:

Properly handles included columns when determining duplication
Considers uniqueness, keys, and constraints
Analyzes both duplicate and overlapping (subset) indexes
Provides drop scripts and estimated space savings
Allows focusing on specific databases or tables
Offers configurable thresholds for unused index identification

Parameters

sp_IndexCleanup offers various parameters to customize its behavior:

@database_name – Target a specific database (default: current database)
@schema_name – Filter to a specific schema
@table_name – Focus on a particular table
@index_name – Analyze a specific index
@index_min_size_mb – Minimum index size to consider (default: 0)
@index_min_writes – Minimum number of writes to consider an index “used” (default: 0)
@index_min_reads – Minimum number of reads to consider an index “used” (default: 0)
@index_min_age_days – Minimum age in days to consider an index “unused” (default: 30)
@dedupe_only – Don’t bother with unused indexes, just consider everything for deduplication
@show_include_columns – Show INCLUDE columns in output (default: 1)
@cleanup_mode – Various modes for analyzing or cleaning up indexes
@output_database_name – Database to store results (optional)
@output_schema_name – Schema for output tables (default: dbo)
@output_table_name – Table to store results (default: IndexCleanupResults)

Usage Examples

Here are some common usage examples for sp_IndexCleanup:

-- Basic usage to analyze all indexes in a database
EXECUTE dbo.sp_IndexCleanup
    @database_name = 'YourDatabase';

-- Analyze a specific table with debug information
EXECUTE dbo.sp_IndexCleanup
    @database_name = 'YourDatabase',
    @table_name = 'YourTable',
    @debug = 1;

-- Only perform deduplication without marking unused indexes for removal
EXECUTE dbo.sp_IndexCleanup
    @database_name = 'YourDatabase',
    @dedupe_only = 1;

-- Analyze tables in a specific schema only
EXECUTE dbo.sp_IndexCleanup
    @database_name = 'YourDatabase',
    @schema_name = 'YourSchema';

-- Filter indexes by minimum usage thresholds
EXECUTE dbo.sp_IndexCleanup
    @database_name = 'YourDatabase',
    @min_reads = 100,
    @min_writes = 10;

-- Analyze all user databases on the server
EXECUTE dbo.sp_IndexCleanup
    @get_all_databases = 1,
    @debug = 1;

-- Analyze only specific databases
EXECUTE dbo.sp_IndexCleanup
    @get_all_databases = 1,
    @include_databases = 'Database1,Database2,Database3';

-- Analyze all databases except specific ones
EXECUTE dbo.sp_IndexCleanup
    @get_all_databases = 1,
    @exclude_databases = 'ReportServer,TempDB2';

-- Show help information
EXECUTE dbo.sp_IndexCleanup
    @help = 1;

Duplicate Key Indexes

Duplicate indexes have identical key columns (in the same order) and identical included columns (order doesn’t matter). They waste space and add overhead to write operations.

sp_IndexCleanup helps you identify which index to keep by showing:

Space consumed by each duplicate
Usage statistics (reads and writes)
Index definition details
Whether the index supports constraints

Overlapping Key Indexes

Overlapping indexes share some or all key columns with another index, in the same order. This is when one index is a subset of another, which makes it redundant.

sp_IndexCleanup identifies overlapping index relationships by analyzing:

Leading edge column overlap
Full subset relationships
Included columns
Usage statistics to identify valuable overlaps

Unused Indexes

Unused indexes consume space and slow down write operations while providing no benefit. sp_IndexCleanup identifies indexes that haven’t been used based on configurable thresholds.

The procedure considers:

Index age
Minimum read/write thresholds
Index size
Constraint support

Heaps

Heaps (tables without clustered indexes) can cause performance issues in SQL Server. sp_IndexCleanup will deduplicate non-clustered indexes on Heaps, but the decision to add a clustered index is a lot more complicated than what a script can decide.

If the table is important to the workload
If it has a lot of forwarded fetches
If it has a lot of deletes
How big it is
How many non-clustered indexes it has

The last point is especially important, because it may be a lot faster to drop them all, add a clustered index, and then add them back after.

When you add a clustered index to a heap, all of the non-clustered indexes need to be rewritten to remove the RID and add the clustering keys to them.

For large heaps, that can be quite a process.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.