SQL Index Maintenance: 5 Critical Post-Creation Steps

Creating indexes is only the first step in optimizing database performance. Over time, as data is inserted, updated, and deleted, indexes can become fragmented, outdated, and unused, leading to degraded query performance and inflated storage costs. This guide covers the five essential maintenance tasks required to keep your SQL Server indexes healthy and your queries running at peak efficiency.

1. Why Index Maintenance Matters (The Car Analogy)

Many developers assume that once an index is created and a query is optimized, the job is complete. However, databases are dynamic environments. Without ongoing maintenance, database performance will inevitably degrade.

Real-Life Analogy: Maintaining a Car

Think of database indexes like owning a car. Purchasing or building a high-performance car is exciting, but it does not run forever on its own. To keep it running smoothly, you must perform regular maintenance: change the oil, rotate the tires, and check the engine diagnostics.

Similarly, database indexes require regular attention. If left unmaintained, they become fragmented, outdated, and bloated, turning a once-fast query engine into a bottleneck.

If you neglect index maintenance, you will face:

• Increased Storage Costs: Unused or bloated indexes consume valuable disk space.
• Degraded Write Performance: Every INSERT, UPDATE, and DELETE operation must also update all associated indexes. If those indexes are redundant or unused, you are paying a high write penalty for zero read benefit.
• Suboptimal Execution Plans: Outdated statistics force the database engine to make incorrect assumptions, leading to slow table scans instead of fast index seeks.

2. Task 1: Monitoring Index Usage & Identifying Unused Indexes

The first question to ask after an index has been in production for some time is: Are we actually using it?

An unused index is pure overhead. It slows down data modification operations and wastes storage. To determine if your indexes are active, you can query SQL Server's metadata and Dynamic Management Views (DMVs).

Step-by-Step Query Evolution

Step A: Inspecting Indexes with sp_helpindex

SQL Server provides a built-in system stored procedure to quickly view the indexes on a specific table.

-- Syntax: EXEC sp_helpindex 'table_name'
EXEC sp_helpindex 'sales.customers';

While useful for a quick check of index keys and types (e.g., nonclustered, columnstore), sp_helpindex does not provide any usage statistics.

Step B: Querying the sys.indexes Metadata Table

To build a comprehensive report, we start by querying the system catalog views to list all indexes and their properties.

SELECT 
    tbl.name AS table_name,
    idx.name AS index_name,
    idx.type_desc AS index_type,
    idx.is_primary_key,
    idx.is_unique,
    idx.is_disabled
FROM sys.indexes idx
INNER JOIN sys.tables tbl 
    ON idx.object_id = tbl.object_id
ORDER BY 
    tbl.name, 
    idx.name;

Step C: Integrating Usage Statistics via DMVs

To find out how often these indexes are used, we perform a LEFT JOIN with the Dynamic Management View sys.dm_db_index_usage_stats. We use a LEFT JOIN because we want to see all indexes, including those that have never been used (which will return NULL values for usage counters).

SELECT 
    tbl.name AS table_name,
    idx.name AS index_name,
    idx.type_desc AS index_type,
    idx.is_primary_key,
    idx.is_unique,
    idx.is_disabled,
    COALESCE(usage.user_seeks, 0) AS user_seeks,
    COALESCE(usage.user_scans, 0) AS user_scans,
    COALESCE(usage.user_lookups, 0) AS user_lookups,
    COALESCE(usage.user_updates, 0) AS user_updates,
    COALESCE(usage.last_user_seek, usage.last_user_scan) AS last_used_date
FROM sys.indexes idx
INNER JOIN sys.tables tbl 
    ON idx.object_id = tbl.object_id
LEFT JOIN sys.dm_db_index_usage_stats usage
    ON idx.object_id = usage.object_id
    AND idx.index_id = usage.index_id
    AND usage.database_id = DB_ID()
ORDER BY 
    tbl.name, 
    idx.name;

Understanding the Metrics

• User Seeks: The number of times the index was used to find specific rows directly (highly efficient).
• User Scans: The number of times the index leaf pages were scanned sequentially.
• User Lookups: The number of times a clustered index was queried to fetch columns not included in a nonclustered index (bookmark lookups).
• User Updates: The number of times the index had to be modified due to write operations on the base table.

Important Caveat on Local Environments: Dynamic Management View statistics are stored in memory and are reset whenever the SQL Server service restarts. If you are running SQL Server Express locally on your PC, shutting down your computer or closing your client will clear these counters. In production environments with long uptimes, these numbers provide a highly accurate representation of real-world usage.

3. The "Day One Hero" Strategy

In real-world database consulting and engineering, there is a highly effective strategy to quickly demonstrate value when joining a new project.

In many legacy databases, developers create indexes for temporary tasks or single-use reports and forget to drop them. By running the usage query on day one, identifying indexes with high user_updates but zero user_seeks or user_scans, and safely dropping them, you can instantly optimize write performance and reclaim storage.

4. Task 2: Detecting Missing Indexes

While dropping unused indexes is vital, identifying missing indexes is equally important for accelerating slow-running queries. SQL Server's query optimizer automatically evaluates queries and logs recommendations for indexes that could improve performance.

You can query these recommendations using the sys.dm_db_missing_index_details DMV:

SELECT 
    statement AS table_name,
    equality_columns,
    inequality_columns,
    included_columns
FROM sys.dm_db_missing_index_details;

Analyzing Missing Index Recommendations

Suppose you run a complex join query filtering on specific columns:

SELECT * 
FROM AdventureWorksDW.dbo.FactInternetSales fis
INNER JOIN AdventureWorksDW.dbo.DimProduct dp 
    ON fis.ProductKey = dp.ProductKey
WHERE dp.Color = 'Black'
  AND fis.OrderDateKey BETWEEN 20230101 AND 20231231;

If the database lacks appropriate indexes, the DMV will return recommendations such as:

1. For DimProduct: A recommendation to create an index on the Color column (used in the equality WHERE clause).
2. For FactInternetSales: A recommendation to create an index on OrderDateKey (used in the inequality/range filter) and ProductKey (used in the join).

Warning: Do not blindly implement every index recommended by the database engine. Evaluate each suggestion carefully. Ask yourself: Is this query run frequently enough to justify the write overhead of a new index? Can we modify an existing index instead of creating a brand-new one?

5. Task 3: Identifying and Eliminating Duplicate Indexes

In collaborative environments with multiple developers, duplicate or overlapping indexes are common. This happens when different team members create separate indexes on the same columns of the same table without checking existing definitions.

To detect duplicate indexes, we can query the system catalogs to find columns that are indexed multiple times:

SELECT 
    tbl.name AS table_name,
    col.name AS column_name,
    idx.name AS index_name,
    idx.type_desc AS index_type,
    COUNT(*) OVER(PARTITION BY tbl.name, col.name) AS duplicate_count
FROM sys.indexes idx
INNER JOIN sys.tables tbl 
    ON idx.object_id = tbl.object_id
INNER JOIN sys.index_columns ic 
    ON idx.object_id = ic.object_id 
    AND idx.index_id = ic.index_id
INNER JOIN sys.columns col 
    ON ic.object_id = col.object_id 
    AND ic.column_id = col.column_id
ORDER BY 
    duplicate_count DESC, 
    tbl.name, 
    col.name;

Interpreting the Results

• duplicate_count = 1: The column is involved in only one index. This is healthy.
• duplicate_count > 1: The column is indexed multiple times across different index structures. For example, if Country is indexed in both a standard row-store nonclustered index and a columnstore index on the same table, you must evaluate which index type is appropriate for your workload and drop the duplicate.

6. Task 4: Understanding and Updating SQL Statistics

The SQL Server query optimizer relies on statistics to determine the most efficient execution plan for a query.

What are Statistics?

Statistics are system-generated metadata objects containing information about the distribution of values within a column or index. They include:

• The total number of rows in the table.
• The distribution and density of values (via histograms).
• The number of distinct values.

The Outdated Statistics Problem

If you create a table with 50 rows, the database engine creates statistics reflecting a tiny table. If you subsequently insert 1,000,000 rows, the statistics may remain outdated, still reporting only 50 rows to the optimizer.

When a query is executed, the optimizer reads the outdated statistics, assumes the table is tiny, and decides to perform a full table scan instead of using an index. This leads to severe performance degradation.

Monitoring Statistics Health

You can monitor the age and modification state of your statistics using the following query:

SELECT 
    tbl.name AS table_name,
    st.name AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter
FROM sys.stats st
INNER JOIN sys.tables tbl 
    ON st.object_id = tbl.object_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
ORDER BY 
    sp.modification_counter DESC;

• modification_counter: Tracks the number of row modifications (INSERT, UPDATE, DELETE) since the statistics were last updated. A high number relative to the total rows indicates that the statistics are stale.

How to Update Statistics

Option A: Update a Specific Statistic on a Table

UPDATE STATISTICS sales.customers IX_customers_last_name;

Option B: Update All Statistics on a Specific Table

UPDATE STATISTICS sales.customers;

Option C: Update All Statistics in the Entire Database

EXEC sp_updatestats;

Note: sp_updatestats is intelligent. It loops through all tables in the database and only updates statistics if there have been modifications, skipping unchanged tables to save resources.

Production Best Practices

• Weekly Maintenance Jobs: Schedule a recurring SQL Agent job over the weekend to run sp_updatestats or a custom statistics update script.
• Post-Migration Updates: Always manually update statistics immediately following large-scale data migrations or bulk inserts to ensure the optimizer is aware of the new data distribution.

7. Task 5: Managing Index Fragmentation

As data is modified, pages in an index can split and leave empty space, or logical page ordering can fall out of alignment with physical disk ordering. This is known as fragmentation.

Reorganize vs. Rebuild

When an index becomes fragmented, you have two primary methods to resolve it:

Monitoring Fragmentation

To check the fragmentation levels of your indexes, query the sys.dm_db_index_physical_stats system function:

SELECT 
    tbl.name AS table_name,
    idx.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.tables tbl 
    ON ips.object_id = tbl.object_id
INNER JOIN sys.indexes idx 
    ON ips.object_id = idx.object_id 
    AND ips.index_id = idx.index_id
ORDER BY 
    ips.avg_fragmentation_in_percent DESC;

Action Thresholds & Syntax

Use the following industry-standard thresholds to decide how to handle fragmented indexes:

Executing a Reorganize (10% to 30% Fragmentation)

ALTER INDEX IX_customers_country 
ON sales.customers 
REORGANIZE;

Executing a Rebuild (> 30% Fragmentation)

ALTER INDEX IX_customers_country 
ON sales.customers 
REBUILD;

8. Complete Index Maintenance Workflow

To maintain a healthy database, combine these five tasks into a structured, proactive workflow.

Summary and Key Takeaways

• Indexes require active maintenance: Just like a car requires oil changes, indexes require continuous monitoring and maintenance to prevent performance degradation over time.
• Unused indexes are costly: They consume storage and slow down write operations. Use sys.dm_db_index_usage_stats to identify and safely drop them.
• The "Day One Hero" strategy works: Running an unused index audit on a legacy database is one of the fastest ways to reclaim storage and improve write performance on a new project.
• Do not blindly trust missing index recommendations: Evaluate suggestions from sys.dm_db_missing_index_details against query frequency and write overhead before implementing them.
• Eliminate duplicate indexes: Use window functions over system metadata to find and remove duplicate indexes created on the same columns.
• Keep statistics fresh: Outdated statistics lead to bad execution plans. Monitor modifications with sys.dm_db_stats_properties and run sp_updatestats regularly.
• Address fragmentation systematically: Monitor fragmentation with sys.dm_db_index_physical_stats. Reorganize indexes with 10% to 30% fragmentation (online, lightweight), and Rebuild indexes with more than 30% fragmentation (offline, heavyweight).