sql server hub / indexing guide

SQL Serverindexing guide

A SQL Server index is an access path: a maintained copy of key values that helps the optimizer find the right rows without reading more of the table than necessary.

Good indexing starts with the workload. Use this guide to review inventory, Query Store, usage stats, missing-index DMVs, duplicate indexes, write cost, statistics, key order, INCLUDE columns, filtered indexes, and rollback planning.

Guide

Guide~10 min readUpdated 19 Apr 2026

LinkedIn X Email

01What SQL Server indexes are
02What SQL Server indexing can and cannot fix
03How to diagnose SQL Server indexing problems
04How to check SQL Server indexes
05How to read SQL Server index usage stats
06How to evaluate SQL Server missing index suggestions
07How to find duplicate and overlapping SQL Server indexes
08How write-heavy tables change SQL Server indexing decisions
09How statistics and query plans affect SQL Server indexing
10How to design key order, INCLUDE columns, and filtered indexes
11What not to change during SQL Server indexing work
12When to request a SQL Server performance review

What SQL Server indexes are

A SQL Server index is a maintained data structure that gives the optimizer another way to reach rows. Instead of reading a whole table every time, SQL Server may be able to seek into an ordered structure, scan a narrower copy of the data, enforce uniqueness, or read prearranged column groups for analytic work.

In everyday rowstore tuning, the two main structures are clustered and nonclustered indexes. Other indexing terms are rules, options, or separate storage families: a unique index enforces a key rule, INCLUDE columns add non-key payload, filtered indexes are nonclustered indexes over part of a table, columnstore is a different column-oriented index family, and a heap is a table with no clustered index.

Term	What it is
Clustered index	One of the two main rowstore index structures. The table data is stored in clustered-key order, and a table can have only one clustered index.
Nonclustered index	The other main rowstore index structure. It has its own key order and points back to the base row through the clustered key or a heap row identifier.
Unique index	A clustered or nonclustered index with a uniqueness rule on its key columns. It is an access path and a data rule, not a third rowstore structure.
INCLUDE columns	An option on a nonclustered index. INCLUDE columns are stored at the leaf level so a query may avoid extra lookups.
Filtered index	A nonclustered index with a filter predicate, so it only contains rows that match conditions such as active rows or non-null values.
Columnstore index	A separate column-oriented index family for analytic and reporting workloads. It is different from rowstore clustered and nonclustered indexes.
Heap	A table without a clustered index. It is not an index, but it matters because nonclustered indexes point back to heap rows differently.

What SQL Server indexing can and cannot fix

SQL Server indexing helps when a repeated workload reads too many rows, performs expensive lookups, sorts or groups more than needed, or holds locks longer because the access path is poor.

It does not fix every slow query. Bad estimates, parameter sensitivity, memory grants, blocking, tempdb spills, storage latency, chatty application behavior, and maintenance overlap can look like indexing problems from a distance.

The examples below are pattern recognition, not copy-paste fixes. Use them to understand the shape of a possible indexing problem, then use the review queries later in the guide to prove whether the change is worth making.

Lookup-heavy query

A plan that seeks into a narrow nonclustered index and then performs thousands of key lookups may need a better covering index. Check the actual plan, lookup count, row count, and columns returned before adding INCLUDE columns. Do not turn one narrow index into a wide copy of the table.

Example T-SQL pattern

-- Query shape: selective search, but returned columns cause lookups.
SELECT CustomerId, OrderDate, Status, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
  AND OrderDate >= @StartDate
ORDER BY OrderDate DESC;

-- Possible shape after plan review, not an automatic fix.
CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate DESC)
INCLUDE (Status, TotalAmount);

Missing-index suggestion that overlaps an existing index

The optimizer may ask for an index that looks new but shares the same leading columns as an existing one. Compare suggested equality, inequality, and include columns against current definitions before creating another structure.

Example T-SQL pattern

-- Existing index already starts with CustomerId, OrderDate.
CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (Status);

-- Missing-index DMV may suggest something close to this.
CREATE INDEX IX_Orders_CustomerId_OrderDate_TotalAmount
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (TotalAmount);

-- Usually review whether the existing index should be adjusted
-- instead of adding a near-duplicate index.

Over-indexed write-heavy table

A table with many nonclustered indexes can make inserts, updates, deletes, ETL loads, and maintenance slower. Check usage stats and operational stats together before dropping anything. Rare reporting and month-end work may not appear in a short sample.

Example T-SQL pattern

-- Look for indexes with write activity but little read activity.
SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    COALESCE(us.user_seeks, 0) + COALESCE(us.user_scans, 0) + COALESCE(us.user_lookups, 0) AS reads,
    COALESCE(us.user_updates, 0) AS writes
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.object_id = i.object_id
   AND us.index_id = i.index_id
   AND us.database_id = DB_ID()
WHERE i.type_desc = N'NONCLUSTERED'
ORDER BY writes DESC, reads ASC;

Stale statistics making the wrong index look bad

A poor estimate can make SQL Server scan, spill, or pick a lookup-heavy path even when a useful index exists. Check statistics freshness and the actual execution plan before assuming the index design is wrong.

Example T-SQL pattern

-- Check whether statistics changed heavily since the last update.
SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY sp.modification_counter DESC;

-- If stale stats caused the bad plan, update stats deliberately,
-- then retest the same query shape and parameters.

Filtered index that only works for matching predicates

A filtered index can be excellent when the query predicate matches the filter and parameter behavior is predictable. It can also be ignored when the query shape does not line up. Check plans under realistic parameters.

Example T-SQL pattern

-- Filtered index for a common narrow slice.
CREATE INDEX IX_Orders_Open_OrderDate
ON dbo.Orders (OrderDate)
INCLUDE (CustomerId, TotalAmount)
WHERE Status = N'Open';

-- This query can match the filter.
SELECT OrderId, CustomerId, TotalAmount
FROM dbo.Orders
WHERE Status = N'Open'
  AND OrderDate >= @StartDate;

-- A parameterized Status predicate may not use the filtered index
-- unless the optimizer can prove the parameter matches the filter.

Month-end index that looks unused

Usage DMVs reset after restart, failover, detach, and other events. An index used by monthly finance reports or emergency workflows can look unused during a short review window. Confirm timing before removal.

Example T-SQL pattern

-- Usage stats need the collection window, not just zero reads.
SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT
    i.name AS index_name,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.user_seeks,
    us.user_scans,
    us.user_lookups
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.object_id = i.object_id
   AND us.index_id = i.index_id
   AND us.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID(N'dbo.MonthEndLedger');

How to diagnose SQL Server indexing problems

Start from the workload, not from a missing-index list. Use Query Store, execution plans, waits, blocking, job timing, application timing, and recent changes to decide whether the problem is really an access-path problem.

Run these checks from an account with enough metadata access. Some DMVs require `VIEW SERVER STATE` or `VIEW DATABASE STATE`; SQL Server 2022 and newer can require `VIEW SERVER PERFORMANCE STATE` or `VIEW DATABASE PERFORMANCE STATE`.

How to check database context for indexing review

Reads database name, version context, uptime, compatibility level, Query Store state, and recovery model.

SELECT
    DB_NAME() AS database_name,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel') AS product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY('Edition') AS edition,
    osi.sqlserver_start_time,
    d.compatibility_level,
    d.is_query_store_on,
    qso.actual_state_desc AS query_store_actual_state_desc,
    qso.readonly_reason AS query_store_readonly_reason,
    d.recovery_model_desc
FROM sys.databases AS d
CROSS JOIN sys.dm_os_sys_info AS osi
LEFT JOIN sys.database_query_store_options AS qso
    ON 1 = 1
WHERE d.database_id = DB_ID();

sqlserver_start_time tells you how long usage and wait counters may have been collecting. compatibility_level affects optimizer behavior and Query Store options. is_query_store_on, query_store_actual_state_desc, and query_store_readonly_reason tell you whether richer workload history may be available. Run database-scoped checks inside the database being reviewed.

How to find Query Store queries for indexing review

Finds recent high-read or high-duration Query Store entries that may deserve index review.

SELECT TOP (25)
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS executions,
    CONVERT(decimal(18,2), SUM(rs.avg_duration * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0)) AS avg_duration_microseconds,
    CONVERT(decimal(18,2), SUM(rs.avg_logical_io_reads * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0)) AS avg_logical_reads,
    CONVERT(decimal(18,2), SUM(rs.avg_cpu_time * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0)) AS avg_cpu_microseconds,
    MAX(rsi.end_time) AS last_interval_end
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.end_time >= DATEADD(day, -7, SYSUTCDATETIME())
GROUP BY qt.query_sql_text, q.query_id, p.plan_id
ORDER BY avg_logical_reads DESC, avg_duration_microseconds DESC;

avg_logical_reads helps find queries reading more data than expected. executions separates repeated workload from a one-time expensive query. avg_duration_microseconds shows user-facing cost, not just read volume. If Query Store is off or read-only, use monitoring data, plans, or cached request history instead.

How to check SQL Server indexes

Check the indexes that already exist before adding anything new. You need to know the current keys, INCLUDE columns, uniqueness, filters, and constraints before deciding whether a missing-index suggestion is new, overlapping, or unsafe.

How to check SQL Server indexes

Lists indexes with key columns, included columns, uniqueness, constraints, and filter definitions using SQL Server 2016-compatible aggregation.

SELECT
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.has_filter,
    i.filter_definition,
    STUFF((
        SELECT
            N', ' + QUOTENAME(c.name)
            + CASE WHEN ic2.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END
        FROM sys.index_columns AS ic2
        JOIN sys.columns AS c
            ON c.object_id = ic2.object_id
           AND c.column_id = ic2.column_id
        WHERE ic2.object_id = i.object_id
          AND ic2.index_id = i.index_id
          AND ic2.is_included_column = 0
        ORDER BY ic2.key_ordinal
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, N'') AS key_columns,
    STUFF((
        SELECT N', ' + QUOTENAME(c.name)
        FROM sys.index_columns AS ic2
        JOIN sys.columns AS c
            ON c.object_id = ic2.object_id
           AND c.column_id = ic2.column_id
        WHERE ic2.object_id = i.object_id
          AND ic2.index_id = i.index_id
          AND ic2.is_included_column = 1
        ORDER BY ic2.index_column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, N'') AS included_columns
FROM sys.indexes AS i
JOIN sys.objects AS o
    ON o.object_id = i.object_id
WHERE o.type = N'U'
  AND i.index_id > 0
ORDER BY schema_name, table_name, i.index_id;

key_columns show the ordered access path SQL Server can seek or scan. included_columns show extra payload used to avoid lookups. has_filter and filter_definition matter for targeted indexes. Unique and constraint-backed indexes need separate review before any removal.

How to read SQL Server index usage stats

Index usage stats show seeks, scans, lookups, and updates since the counters were last reset. They are useful for review, but they are not permanent history and they do not explain whether a plan was good.

Column	How to read it
user_seeks	Times SQL Server used the index to navigate into a key range. Seeks are often good, but a seek plus many lookups can still be expensive.
user_scans	Times SQL Server scanned the index. Scans are not automatically bad; they can be normal for reports, small tables, or broad range reads.
user_lookups	Times SQL Server used the clustered index or heap to fetch columns missing from a nonclustered index. High lookups can point to a covering-index question.
user_updates	Times inserts, updates, or deletes maintained the index. High updates with little read use can show write cost, but it is not a drop decision by itself.
last_user_seek / scan / lookup / update	The last recorded time for each access type in the current counter window. Always compare this with sqlserver_start_time and the workload calendar.

How to read SQL Server index usage stats

Reads seeks, scans, lookups, updates, last-use times, and SQL Server start time.

SELECT
    DB_NAME() AS database_name,
    osi.sqlserver_start_time,
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
FROM sys.indexes AS i
JOIN sys.objects AS o
    ON o.object_id = i.object_id
CROSS JOIN sys.dm_os_sys_info AS osi
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE o.type = N'U'
  AND i.index_id > 0
ORDER BY
    COALESCE(us.user_seeks, 0) + COALESCE(us.user_scans, 0) + COALESCE(us.user_lookups, 0) DESC,
    COALESCE(us.user_updates, 0) DESC;

user_seeks and user_scans show read access, but not whether the plan was efficient. user_lookups can point to lookup-heavy plans that may need covering review. user_updates shows write maintenance pressure. Read these counters beside sqlserver_start_time and workload calendar.

How to find rarely read nonclustered indexes

Shows nonclustered indexes with no recorded reads in the current usage-stat window.

SELECT
    DB_NAME() AS database_name,
    osi.sqlserver_start_time,
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    us.user_updates,
    us.last_user_update
FROM sys.indexes AS i
JOIN sys.objects AS o
    ON o.object_id = i.object_id
CROSS JOIN sys.dm_os_sys_info AS osi
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE o.type = N'U'
  AND i.type_desc = N'NONCLUSTERED'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND i.is_unique = 0
  AND COALESCE(us.user_seeks, 0) = 0
  AND COALESCE(us.user_scans, 0) = 0
  AND COALESCE(us.user_lookups, 0) = 0
ORDER BY COALESCE(us.user_updates, 0) DESC, schema_name, table_name, index_name;

This is a candidate list, not a drop list. Usage stats reset after restart, detach, failover, and some database events. Rare reports, month-end jobs, and emergency workflows may not appear in a short window. Constraints, uniqueness, foreign key support, and application assumptions need separate review.

How to evaluate SQL Server missing index suggestions

Missing-index DMVs record optimizer suggestions. They are clues, not approval to create indexes. They do not understand your whole index set, write cost, storage budget, deployment timing, or whether several suggestions should be merged.

How to read SQL Server missing index suggestions

Reads missing-index requests with seek/scan counts, impact estimates, and suggested columns.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    CONVERT(decimal(18,2),
        migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)
        * (migs.user_seeks + migs.user_scans)
    ) AS rough_improvement_score,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.last_user_seek,
    migs.last_user_scan
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
WHERE mid.database_id = DB_ID()
ORDER BY rough_improvement_score DESC;

rough_improvement_score is a ranking aid, not a production decision. equality_columns and inequality_columns still need key-order review. included_columns can become too wide if copied without judgment. Missing-index DMV data is cleared when SQL Server restarts.

How to compare missing indexes with existing indexes

Shows missing-index suggestions beside current indexes on the same table so overlap is harder to miss.

WITH ExistingIndexes AS (
    SELECT
        i.object_id,
        i.index_id,
        SCHEMA_NAME(o.schema_id) AS schema_name,
        o.name AS table_name,
        i.name AS index_name,
        STUFF((
            SELECT
                N', ' + QUOTENAME(c.name)
                + CASE WHEN ic2.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END
            FROM sys.index_columns AS ic2
            JOIN sys.columns AS c
                ON c.object_id = ic2.object_id
               AND c.column_id = ic2.column_id
            WHERE ic2.object_id = i.object_id
              AND ic2.index_id = i.index_id
              AND ic2.is_included_column = 0
            ORDER BY ic2.key_ordinal
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, 2, N'') AS key_columns,
        STUFF((
            SELECT N', ' + QUOTENAME(c.name)
            FROM sys.index_columns AS ic2
            JOIN sys.columns AS c
                ON c.object_id = ic2.object_id
               AND c.column_id = ic2.column_id
            WHERE ic2.object_id = i.object_id
              AND ic2.index_id = i.index_id
              AND ic2.is_included_column = 1
            ORDER BY ic2.index_column_id
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, 2, N'') AS included_columns
    FROM sys.indexes AS i
    JOIN sys.objects AS o
        ON o.object_id = i.object_id
    WHERE o.type = N'U'
      AND i.index_id > 0
),
MissingSuggestions AS (
    SELECT TOP (50)
        mid.object_id,
        OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
        OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
        mid.equality_columns,
        mid.inequality_columns,
        mid.included_columns,
        migs.user_seeks,
        migs.user_scans,
        migs.avg_user_impact
    FROM sys.dm_db_missing_index_details AS mid
    JOIN sys.dm_db_missing_index_groups AS mig
        ON mig.index_handle = mid.index_handle
    JOIN sys.dm_db_missing_index_group_stats AS migs
        ON migs.group_handle = mig.index_group_handle
    WHERE mid.database_id = DB_ID()
    ORDER BY (migs.user_seeks + migs.user_scans) DESC
)
SELECT
    ms.schema_name,
    ms.table_name,
    ms.equality_columns,
    ms.inequality_columns,
    ms.included_columns,
    ms.user_seeks,
    ms.user_scans,
    ms.avg_user_impact,
    ei.index_name AS existing_index_name,
    ei.key_columns AS existing_key_columns,
    ei.included_columns AS existing_included_columns
FROM MissingSuggestions AS ms
LEFT JOIN ExistingIndexes AS ei
    ON ei.object_id = ms.object_id
   AND ms.equality_columns IS NOT NULL
   AND (
       ei.key_columns LIKE COALESCE(ms.equality_columns, N'') + N'%'
       OR COALESCE(ms.equality_columns, N'') LIKE ei.key_columns + N'%'
   )
ORDER BY ms.schema_name, ms.table_name, ms.user_seeks DESC, existing_index_name;

This comparison is intentionally conservative; it points to likely overlap, not final design. A suggestion can often be handled by adjusting an existing index rather than adding a new one. Filters, uniqueness, INCLUDE columns, and key order still need plan review. Do not generate CREATE INDEX scripts directly from this output.

How to find duplicate and overlapping SQL Server indexes

Overlapping indexes are common after years of tuning one incident at a time. Two indexes can have different names while sharing the same leading keys, similar included columns, and the same write burden.

How to find duplicate and overlapping indexes

Compares nonclustered key definitions on the same table to find exact or leading-key overlap.

WITH IndexDefinitions AS (
    SELECT
        i.object_id,
        i.index_id,
        SCHEMA_NAME(o.schema_id) AS schema_name,
        o.name AS table_name,
        i.name AS index_name,
        i.is_unique,
        i.has_filter,
        i.filter_definition,
        STUFF((
            SELECT N', ' + QUOTENAME(c.name)
            FROM sys.index_columns AS ic2
            JOIN sys.columns AS c
                ON c.object_id = ic2.object_id
               AND c.column_id = ic2.column_id
            WHERE ic2.object_id = i.object_id
              AND ic2.index_id = i.index_id
              AND ic2.is_included_column = 0
            ORDER BY ic2.key_ordinal
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, 2, N'') AS key_columns,
        STUFF((
            SELECT N', ' + QUOTENAME(c.name)
            FROM sys.index_columns AS ic2
            JOIN sys.columns AS c
                ON c.object_id = ic2.object_id
               AND c.column_id = ic2.column_id
            WHERE ic2.object_id = i.object_id
              AND ic2.index_id = i.index_id
              AND ic2.is_included_column = 1
            ORDER BY ic2.index_column_id
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, 2, N'') AS included_columns
    FROM sys.indexes AS i
    JOIN sys.objects AS o
        ON o.object_id = i.object_id
    WHERE o.type = N'U'
      AND i.type_desc = N'NONCLUSTERED'
      AND i.is_primary_key = 0
      AND i.is_unique_constraint = 0
)
SELECT
    a.schema_name,
    a.table_name,
    a.index_name AS index_a,
    b.index_name AS index_b,
    a.key_columns AS index_a_keys,
    b.key_columns AS index_b_keys,
    a.included_columns AS index_a_includes,
    b.included_columns AS index_b_includes,
    a.has_filter AS index_a_has_filter,
    b.has_filter AS index_b_has_filter
FROM IndexDefinitions AS a
JOIN IndexDefinitions AS b
    ON b.object_id = a.object_id
   AND b.index_id > a.index_id
   AND (
       a.key_columns = b.key_columns
       OR b.key_columns LIKE a.key_columns + N',%'
       OR a.key_columns LIKE b.key_columns + N',%'
   )
ORDER BY a.schema_name, a.table_name, a.key_columns;

Exact matching key columns are stronger duplicate candidates. Leading-key overlap can still be valid if include columns, filters, or uniqueness differ. Check usage stats and actual plans before merging or dropping. This query finds candidates; it does not confirm an index is redundant.

How write-heavy tables change SQL Server indexing decisions

Every nonclustered index must be maintained when matching table data changes. On write-heavy tables, a helpful read index can still be a bad production choice if it creates too much update, lock, latch, storage, or maintenance cost.

How to measure index write cost

Reads write activity, lock waits, and page-latch waits from sys.dm_db_index_operational_stats.

SELECT TOP (50)
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    SUM(ios.leaf_insert_count) AS leaf_inserts,
    SUM(ios.leaf_update_count) AS leaf_updates,
    SUM(ios.leaf_delete_count) AS leaf_deletes,
    SUM(ios.row_lock_wait_count) AS row_lock_waits,
    SUM(ios.row_lock_wait_in_ms) AS row_lock_wait_ms,
    SUM(ios.page_latch_wait_count) AS page_latch_waits,
    SUM(ios.page_latch_wait_in_ms) AS page_latch_wait_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
JOIN sys.objects AS o
    ON o.object_id = ios.object_id
JOIN sys.indexes AS i
    ON i.object_id = ios.object_id
   AND i.index_id = ios.index_id
WHERE o.type = N'U'
GROUP BY o.schema_id, o.name, i.name, i.type_desc
ORDER BY
    SUM(ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count) DESC,
    SUM(ios.row_lock_wait_in_ms + ios.page_latch_wait_in_ms) DESC;

leaf_inserts, leaf_updates, and leaf_deletes show write maintenance activity. row_lock_wait_ms can point to lock pressure around a table or index. page_latch_wait_ms can point to page-level contention or hot spots. Operational stats are current observations, not a substitute for plan and workload review.

How statistics and query plans affect SQL Server indexing

A stale or missing statistic can make a good index look bad. A poor estimate can make SQL Server scan, spill, or choose a lookup-heavy route that looks like an indexing problem.

Fragmentation and fill factor belong in the review, but they should not become the default explanation for slow queries. Check estimates, actual rows, reads, lookups, spills, waits, and maintenance timing first.

How to check statistics freshness

Reads statistics update time, sampled rows, row count, and modification counter.

SELECT
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    s.name AS stats_name,
    s.auto_created,
    s.user_created,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter,
    CONVERT(decimal(10,2),
        100.0 * sp.modification_counter / NULLIF(sp.rows, 0)
    ) AS pct_rows_modified_since_update
FROM sys.stats AS s
JOIN sys.objects AS o
    ON o.object_id = s.object_id
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.type = N'U'
ORDER BY pct_rows_modified_since_update DESC, sp.modification_counter DESC;

last_updated can be null when a statistics blob has not been created. modification_counter shows changes since the last statistics update. High modification percentage can explain poor cardinality estimates. Use this with the actual execution plan, not as a standalone rule.

How to design key order, INCLUDE columns, and filtered indexes

Key order matters for seeks, range predicates, joins, sorting, grouping, and how much of the index SQL Server can use efficiently. INCLUDE columns can remove lookups, but they also widen the index.

A filtered index can be excellent for a stable subset of data. It can also sit unused if parameter behavior or query predicates do not match the filter.

Design choice	Check before changing it
Leading key columns	Equality predicates, joins, selectivity, and whether the plan can seek into the useful range.
Inequality and range columns	Whether the range ends the useful seek portion and changes how later columns help.
INCLUDE columns	Lookup reduction against storage, write-maintenance cost, and memory pressure.
Filtered indexes	Predicate stability, parameter behavior, plan use, and whether the filter matches the workload.
Columnstore indexes	Analytic workload fit, rowgroup health, load pattern, and coexistence with rowstore access paths.
Fill factor	Whether page splits or hot updates justify extra free space, and what maintenance cost it creates.

What not to change during SQL Server indexing work

Do not create every missing-index suggestion.

Do not drop rarely read indexes from one short sample window.

Do not judge index value without knowing when SQL Server last restarted.

Do not add wide covering indexes without checking write cost and storage.

Do not rebuild or reorganize indexes as a substitute for query and index design.

Do not assume fragmentation is the main cause of slow queries.

Do not change key order from guesswork; check predicates, joins, sorting, grouping, and selectivity.

Do not remove indexes that support constraints, foreign keys, rare reports, month-end jobs, or emergency workflows.

When to request a SQL Server performance review

A performance review makes sense when missing-index suggestions, slow plans, blocking, high reads, or write pressure point in different directions. The work is to decide the smallest index change that improves the workload without creating a new cost elsewhere.

Send query text, execution plans, Query Store data, index usage stats, current index definitions, waits, job timing, and what changed recently. That is enough to start separating useful index work from cleanup that should wait.

Request performance review Read the waits guide

Next step

If index changes need to be checked against plans, writes, waits, and production timing, use the SQL Server performance review page or request the review above.

Next useful reads: the SQL Server waits guide for resource pressure, the SQL Server blocking guide for lock chains, and the SQL Server monitoring guide for recurring capture.