Sqlserver Index Management

SQL Server Index Management

Index Health Overview

Run this query first to get a summary of index health in the current database.

-- Summary: fragmentation, usage, and size for all non-clustered indexes
SELECT
    OBJECT_NAME(i.object_id)          AS table_name,
    i.name                            AS index_name,
    i.type_desc                       AS index_type,
    ips.avg_fragmentation_in_percent  AS fragmentation_pct,
    ips.page_count,
    ISNULL(ius.user_seeks, 0)         AS user_seeks,
    ISNULL(ius.user_scans, 0)         AS user_scans,
    ISNULL(ius.user_lookups, 0)       AS user_lookups,
    ISNULL(ius.user_updates, 0)       AS user_updates,
    ius.last_user_seek
FROM sys.indexes i
JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    ON i.object_id = ips.object_id AND i.index_id = ips.index_id
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE ips.page_count > 100   -- Ignore tiny indexes (< 800 KB)
  AND i.type > 0             -- Exclude heaps
ORDER BY ips.avg_fragmentation_in_percent DESC;

Missing Index Analysis Workflow

The SQL Server optimizer records missed index opportunities in DMVs. Use this workflow to evaluate them.

Step 1 — Get top missing index recommendations with impact score

SELECT TOP 20
    DB_NAME(mid.database_id)                          AS database_name,
    OBJECT_NAME(mid.object_id, mid.database_id)       AS table_name,
    migs.user_seeks,
    migs.user_scans,
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact
          * (migs.user_seeks + migs.user_scans), 0)   AS impact_score,
    migs.avg_user_impact                              AS avg_pct_improvement,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.last_user_seek,
    'CREATE NONCLUSTERED INDEX IX_'
        + REPLACE(REPLACE(OBJECT_NAME(mid.object_id, mid.database_id), ' ', '_'), '.', '_')
        + '_' + CONVERT(VARCHAR(10), mig.index_group_handle)
        + ' ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.inequality_columns IS NOT NULL
               THEN CASE WHEN mid.equality_columns IS NOT NULL THEN ', ' ELSE '' END
                    + mid.inequality_columns
          ELSE '' END + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
        AS suggested_index
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY impact_score DESC;

Step 2 — Evaluation checklist before creating an index

• Impact score > 100,000? (Lower scores rarely justify the write overhead.)
• Does an existing index already cover the equality columns? If so, add INCLUDE columns to it instead.
• Are equality columns listed before inequality columns in the suggested statement? (The DMV always does this correctly — verify before copying.)
• Will this index be maintained during write-heavy operations? Consider FILLFACTOR.
• How many indexes does this table already have? OLTP tables: target < 10–15 non-clustered indexes.

Fragmentation Assessment

Check fragmentation (LIMITED mode for speed)

SELECT
    DB_NAME()                          AS database_name,
    OBJECT_NAME(i.object_id)           AS table_name,
    i.name                             AS index_name,
    i.type_desc                        AS index_type,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.partition_number
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.page_count > 100
  AND ips.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;

Fragmentation thresholds and recommended action:

Rebuild an index

-- Standard rebuild (offline — blocks reads and writes)
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD;

-- Online rebuild (Enterprise Edition only — minimal blocking)
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders
REBUILD WITH (ONLINE = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

-- Rebuild all indexes on a table
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);

Reorganize an index

-- Always online and interruptible
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REORGANIZE;

-- REORGANIZE does NOT update statistics — update manually afterward
UPDATE STATISTICS dbo.Orders WITH SAMPLE 30 PERCENT;

Unused Index Detection

Unused indexes consume write performance and disk space with no read benefit. Review periodically — note that DMVs reset on service restart.

-- Indexes never read since last SQL Server restart (high write cost, zero read benefit)
SELECT
    OBJECT_NAME(i.object_id)  AS table_name,
    i.name                    AS index_name,
    i.type_desc,
    ISNULL(ius.user_seeks, 0)   AS user_seeks,
    ISNULL(ius.user_scans, 0)   AS user_scans,
    ISNULL(ius.user_lookups, 0) AS user_lookups,
    ISNULL(ius.user_updates, 0) AS user_updates,
    ius.last_user_seek,
    ius.last_user_scan,
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON '
        + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.'
        + QUOTENAME(OBJECT_NAME(i.object_id)) AS drop_statement
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE i.type > 0
  AND i.is_unique = 0
  AND i.is_primary_key = 0
  AND OBJECT_NAME(i.object_id) IS NOT NULL
  AND ISNULL(ius.user_seeks, 0) = 0
  AND ISNULL(ius.user_scans, 0) = 0
  AND ISNULL(ius.user_lookups, 0) = 0
ORDER BY ISNULL(ius.user_updates, 0) DESC;
-- Never drop unique indexes or primary keys without analysis
-- Verify the server has been running long enough to capture all workloads (> 1 week ideally)

Index Maintenance Workflow

Adaptive maintenance script (5–30% reorganize, > 30% rebuild)

DECLARE @TableName NVARCHAR(300), @IndexName NVARCHAR(300),
        @Fragmentation FLOAT, @SQL NVARCHAR(600);

DECLARE idx_cursor CURSOR FOR
    SELECT
        QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.'
            + QUOTENAME(OBJECT_NAME(i.object_id)),
        QUOTENAME(i.name),
        ips.avg_fragmentation_in_percent
    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.page_count > 100
      AND ips.avg_fragmentation_in_percent > 5
      AND i.type > 0;

OPEN idx_cursor;
FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName, @Fragmentation;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Fragmentation > 30
        SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName
                 + ' REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON)';
    ELSE
        SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE';

    EXEC sp_executesql @SQL;
    FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName, @Fragmentation;
END;
CLOSE idx_cursor;
DEALLOCATE idx_cursor;

Industry-standard alternative: Ola Hallengren's IndexOptimize stored procedure. Source: https://ola.hallengren.com

Index Design Principles

Covering index pattern (eliminate key lookups)

-- Query: SELECT name, email FROM dbo.Customers WHERE country = 'US' AND status = 'Active'
CREATE NONCLUSTERED INDEX IX_Customers_Country_Status
ON dbo.Customers (country, status)   -- Seek predicates in key
INCLUDE (name, email);               -- Projected columns as included (not in key)

Filtered index (partial index for sparse queries)

-- Only index pending orders; smaller, faster for this specific query pattern
CREATE NONCLUSTERED INDEX IX_Orders_Pending
ON dbo.Orders (customer_id, order_date)
INCLUDE (total_amount)
WHERE status = 'Pending';

Columnstore for analytical queries on OLTP tables

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales_Analytics
ON dbo.Sales (sale_date, product_id, customer_id, amount, quantity);
-- Batch mode processing; 10:1 typical compression

FILLFACTOR guidelines:

Columnstore Index Health Check

SELECT
    OBJECT_NAME(i.object_id)  AS table_name,
    i.name                    AS index_name,
    rg.state_desc             AS rowgroup_state,
    COUNT(*)                  AS rowgroup_count,
    SUM(rg.total_rows)        AS total_rows,
    SUM(rg.deleted_rows)      AS deleted_rows,
    CAST(SUM(rg.deleted_rows) * 100.0
         / NULLIF(SUM(rg.total_rows), 0) AS DECIMAL(5,2)) AS deleted_pct,
    SUM(rg.size_in_bytes) / 1024.0 / 1024.0 AS size_mb
FROM sys.column_store_row_groups rg
JOIN sys.indexes i ON rg.object_id = i.object_id AND rg.index_id = i.index_id
GROUP BY i.object_id, i.name, rg.state_desc
ORDER BY deleted_pct DESC;
-- Rebuild when deleted_pct > 10%
ALTER INDEX CCI_FactSales ON dbo.FactSales REBUILD;

Key Metrics and Thresholds

References

• Index types — all index types with use cases and creation syntax
• Maintenance scripts — complete fragmentation analysis and maintenance T-SQL
• Design patterns — index design patterns and anti-patterns
• Examples — fragmentation assessment, evaluating missing index recommendations, finding redundant indexes, designing indexes for a query