SQL Server index optimization: a practical workflow

Read each table as a set. When the nonclustered indexes together are several times larger than the clustered index or heap, expect duplicates. Clustered vs. nonclustered indexes in SQL Server covers the structures themselves.

Find indexes that are never read

An index with a large user_updates value and no seeks, scans or lookups is maintained on every write and read by no query. Check what the numbers cover first:

• The counters are held in memory. They are emptied when the Database Engine starts, and a database's rows are removed when it is detached or shut down, which includes AUTO_CLOSE. Check sqlserver_start_time in sys.dm_os_sys_info and make sure the period includes month-end work.
• A row exists only after the first use. That is why the query uses a LEFT JOIN.
• Counts are per statement, not per row. A statement that deletes 1,000 rows adds 1 to user_updates.
• The view is local to the instance. Queries on a readable secondary replica are counted on that replica, so check every replica that serves reads.

A unique index enforces a data rule and tells the optimizer that a value matches at most one row, so judge it on correctness and not on reads. Check for indexes named in table hints too: a query that names a disabled index in a hint fails.

Find duplicate and overlapping indexes

Two indexes overlap when the key of one is a left-based prefix of the key of the other. This query (SQL Server 2017 and later, for STRING_AGG) lists exact duplicates, pairs with the same key and different included columns, and prefix pairs:

WITH idx AS (
    SELECT i.object_id, i.index_id, i.name, i.type_desc, i.is_unique,
           ISNULL(i.filter_definition, N'') AS filter_definition,
           (SELECT STRING_AGG(CONCAT(c.name, CASE WHEN ic.is_descending_key = 1 THEN N' DESC' END), N', ')
                     WITHIN GROUP (ORDER BY ic.key_ordinal)
            FROM sys.index_columns AS ic
            JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
              AND ic.key_ordinal > 0) AS key_cols,
           ISNULL((SELECT STRING_AGG(c.name, N', ') WITHIN GROUP (ORDER BY c.name)
                   FROM sys.index_columns AS ic
                   JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
                   WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                     AND ic.is_included_column = 1), N'') AS include_cols
    FROM sys.indexes AS i
    WHERE i.type IN (1, 2)                 -- clustered and nonclustered rowstore
      AND i.is_disabled = 0 AND i.is_hypothetical = 0
      AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
)
SELECT OBJECT_NAME(a.object_id) AS table_name,
       CASE WHEN a.key_cols = b.key_cols AND a.include_cols = b.include_cols THEN 'exact duplicate'
            WHEN a.key_cols = b.key_cols THEN 'same key, different includes'
            ELSE 'key is a left prefix' END AS relationship,
       a.name AS index_a, a.is_unique AS a_is_unique, a.key_cols AS a_key, a.include_cols AS a_include,
       b.name AS index_b, b.type_desc AS b_type, b.key_cols AS b_key, b.include_cols AS b_include
FROM idx AS a
JOIN idx AS b
     ON b.object_id = a.object_id
    AND b.index_id <> a.index_id
    AND b.filter_definition = a.filter_definition
    AND (   (b.key_cols = a.key_cols AND (a.index_id < b.index_id OR b.index_id = 1))
         OR LEFT(b.key_cols, LEN(a.key_cols) + 2) = a.key_cols + N', ')
WHERE a.type_desc = 'NONCLUSTERED'
ORDER BY table_name, a.key_cols;

• Exact duplicate. Keep one. If either is unique or backs a constraint, keep that one.
• Same key, different includes. Merge the included columns into one index and drop the other.
• Key is a left prefix. index_a on (CustomerID) is usually redundant next to index_b on (CustomerID, OrderDate), because any seek on CustomerID can use the wider index.

Two cases are not redundant. A unique index is not replaced by a wider non-unique one. When b_type is CLUSTERED, the nonclustered index can still be worth keeping, because it is far smaller than the table.

Measure what each index costs on writes

sys.dm_db_index_usage_stats counts statements. sys.dm_db_index_operational_stats counts row-level work and waiting:

SELECT i.name AS index_name,
       SUM(os.leaf_insert_count) AS leaf_inserts,
       SUM(os.leaf_update_count) AS leaf_updates,
       SUM(os.leaf_delete_count + os.leaf_ghost_count) AS leaf_deletes,
       SUM(os.leaf_allocation_count) AS leaf_page_allocations,
       SUM(os.range_scan_count + os.singleton_lookup_count) AS reads,
       SUM(os.row_lock_wait_in_ms + os.page_lock_wait_in_ms) AS lock_wait_ms,
       SUM(os.page_latch_wait_in_ms) AS page_latch_wait_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'Sales.Orders'), NULL, NULL) AS os
JOIN sys.indexes AS i ON i.object_id = os.object_id AND i.index_id = os.index_id
GROUP BY i.name
ORDER BY SUM(os.leaf_insert_count + os.leaf_update_count + os.leaf_delete_count + os.leaf_ghost_count) DESC;

An index with millions of leaf-level changes and a handful of reads is a cost with little return. High lock_wait_ms or page_latch_wait_ms on one index shows where writers queue. leaf_allocation_count counts leaf page allocations, which for an index means page splits, including the new page added at the end of an ever-increasing key.

The counters last only while the object's metadata stays in the metadata cache, and Microsoft's documentation says not to use this function to decide whether an index is used. Use it for write cost and contention, and the usage DMV for the drop decision.

Fix key column order, then merge

An index supports a seek only on a left-based prefix of its key:

1. Columns compared with = go first, led by the one that the most queries filter on.
2. One range column (>, <, BETWEEN) or the ORDER BY column goes after the equality columns. Key columns after a range predicate no longer narrow the seek.
3. Columns that are only returned belong in INCLUDE.

When indexes share a leading column, change one of them in a single step with DROP_EXISTING, then drop the others:

-- Before: IX_Orders_Status (Status)
--         IX_Orders_Status_OrderDate (Status, OrderDate) INCLUDE (TotalDue)
--         IX_Orders_Status_Customer (Status) INCLUDE (CustomerID)
CREATE NONCLUSTERED INDEX IX_Orders_Status_OrderDate
ON Sales.Orders (Status, OrderDate)
INCLUDE (TotalDue, CustomerID)
WITH (DROP_EXISTING = ON, ONLINE = ON);   -- ONLINE: Enterprise edition and Azure SQL only

DROP INDEX IX_Orders_Status ON Sales.Orders;
DROP INDEX IX_Orders_Status_Customer ON Sales.Orders;

The merged index is wider, so a query that scanned a narrow index now reads more pages. Don't merge indexes with different leading columns, a filtered index with an unfiltered one, or a unique index with a non-unique one.

Keep INCLUDE lists short

Included columns are stored only at the leaf level. They remove key lookups, and they also widen every leaf row and add maintenance whenever one of them is updated.

• Include a column when a frequent query needs it and the lookup it removes is visible in logical reads. A lookup for five rows is not worth an include.
• Never include the clustered index key. Every nonclustered index already contains it.
• Be suspicious of an index whose used_mb approaches the size of the clustered index. It is a second copy of the table.

Filtered indexes and the parameterization problem

A filtered index stores only the rows that match a fixed predicate, so it is smaller, cheaper to maintain and has its own more precise statistics:

CREATE NONCLUSTERED INDEX IX_Orders_Open_Customer
ON Sales.Orders (CustomerID, OrderDate)
INCLUDE (TotalDue)
WHERE Status = 'Open';

Three things go wrong in practice:

• Parameterized predicates. A query with WHERE Status = @Status cannot use this index in a cached plan, because the plan must be valid for every value of @Status. State the predicate as a literal (Status = 'Open') or compile per execution with OPTION (RECOMPILE). Forced parameterization at the database level causes the same problem by replacing your literal with a parameter. The plan XML reports the skipped index in an UnmatchedIndexes element.
• SET options. Creating the index, modifying its rows and using it in a plan all require ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER to be ON and NUMERIC_ROUNDABORT to be OFF. A session with the wrong settings gets an error on any statement that modifies the indexed rows, so check old drivers and scripts before adding the first filtered index to a table.
• The filter column. If a query returns the filter column, or compares it differently from the filter expression, add it as a key or included column.

Fragmentation, page density and what maintenance buys

Fragmentation (avg_fragmentation_in_percent) means the logical order of the leaf pages differs from their physical order. It affects read-ahead during large scans. Page density (avg_page_space_used_in_percent) is how full the pages are. Low density means more pages to read for every scan and seek range, more buffer pool memory and larger backups.

Microsoft's current guidance gives no fragmentation thresholds. Its index maintenance article says decisions "shouldn't be based on fixed fragmentation or page density thresholds alone", that in many workloads raising page density helps more than reducing fragmentation, and that on most storage used by Azure SQL there is no performance difference between sequential and random I/O. The sys.dm_db_index_physical_stats reference adds that removing fragmentation has no effect if the workload doesn't run large scans, and its sample script calls the familiar 30 percent cut-off "an arbitrary decision point".

SELECT i.name AS index_name, ips.page_count,
       ips.avg_fragmentation_in_percent, ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'Sales.Orders'), NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.alloc_unit_type_desc = 'IN_ROW_DATA';

LIMITED mode is faster and returns NULL for page density, so use SAMPLED or DETAILED.

REORGANIZE is always online, works on the leaf level only, compacts pages to the fill factor and does not update statistics. REBUILD builds a new copy of the index, so it needs space for two copies, and it is offline unless you use ONLINE = ON, which is limited to Enterprise edition and Azure SQL. An online rebuild is fully logged.

REORGANIZE fails on an index with ALLOW_PAGE_LOCKS = OFF. Maintain a specific index when you have measured low page density or a scan-heavy query that improves afterwards. Blanket nightly rebuilds mostly generate log and replica lag; see Why does my SQL Server transaction log keep growing?

Leave fill factor at the default unless you measured page splits

Fill factor is the percentage to which leaf pages are filled when an index is created or rebuilt, and to which REORGANIZE compacts them. The server default is 0, which is the same as 100. It is not maintained as rows are inserted later.

A fill factor of 80 makes the index 25% larger on disk and in memory from the moment it is rebuilt, for every query that reads it. Microsoft recommends leaving it at 100 or 0 except for indexes with a high number of page splits, such as a frequently modified index led by a nonsequential GUID. Find the exceptions someone set years ago with SELECT name, fill_factor FROM sys.indexes WHERE fill_factor NOT IN (0, 100);.

Update statistics instead of rebuilding

When a rebuild makes queries faster, the cause is often the statistics. A rebuild updates the statistics on the index with the equivalent of FULLSCAN, and the plans that depend on them recompile. Microsoft's guidance is to try a statistics update in place of the rebuild. It takes minutes where a rebuild can take hours.

SELECT s.name AS stats_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'Sales.Orders')
ORDER BY sp.modification_counter DESC;

UPDATE STATISTICS Sales.Orders IX_Orders_Status_OrderDate WITH FULLSCAN;

• A rebuild updates only the statistics of that index, with a full scan unless the index is partitioned or the operation is resumable. Column statistics are untouched.
• A sampled UPDATE STATISTICS after a rebuild replaces the full-scan statistics with sampled ones. Maintenance jobs that rebuild and then update all statistics do this every night.
• At database compatibility level 130 and later, automatic updates on a table with more than 500 rows trigger after MIN(500 + 0.20 * n, SQRT(1000 * n)) modifications. Below level 130 the threshold is 500 + 20% of the rows, which is too slow for large tables.

Compress large, read-mostly indexes

Row and page compression store the same rows in fewer pages, which reduces logical reads and buffer pool use at the cost of extra CPU when rows are read and written. Data compression is available in every edition from SQL Server 2016 SP1. Estimate the saving first:

EXEC sys.sp_estimate_data_compression_savings
     @schema_name = N'Sales', @object_name = N'Orders',
     @index_id = NULL, @partition_number = NULL, @data_compression = N'PAGE';

Apply it with ALTER INDEX ... REBUILD WITH (DATA_COMPRESSION = PAGE).

Test the CPU cost on a write-heavy index first, and consider ROW compression there instead.

When a nonclustered columnstore index is the answer

If a transactional table has collected several wide rowstore indexes that exist only for reporting queries (aggregates over many rows, grouped by date, customer or status), one nonclustered columnstore index can replace them. The table stays updatable in SQL Server 2016 and later, and a table can have only one columnstore index.

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Reporting
ON Sales.Orders (OrderDate, CustomerID, Status, TotalDue)
WHERE Status = 'Closed';

The filter keeps frequently changing rows out of the columnstore, which lowers its maintenance cost on the transactional workload. Columnstore indexes exist in every edition from SQL Server 2016 SP1, with lower memory and parallelism limits below Enterprise edition. Keep the rowstore indexes that serve single-row lookups.

A note on missing index suggestions

The missing index DMVs and the green hint in execution plans show which index the optimizer wanted for one query. They ignore existing indexes and write cost, and never suggest removing or merging anything. Treat each as a prompt to widen an index you already have, after this clean-up is done.

Disable before you drop

Disable an index before dropping it:

ALTER INDEX IX_Orders_Status_Customer ON Sales.Orders DISABLE;
ALTER INDEX IX_Orders_Status_Customer ON Sales.Orders REBUILD;   -- brings it back

A disabled nonclustered index keeps its definition in metadata, but its data is deleted, so re-enabling costs a full build. Disabling a clustered index makes the table inaccessible, and disabling a unique index also disables the primary key or unique constraint and every foreign key that references it. Leave the index disabled for a full business cycle, then drop it.

Measure the result

• Reads. Compare logical reads and CPU for the affected queries in Query Store before and after. Reading SQL Server execution plans shows how to confirm which index a plan uses.
• Writes. Compare logical reads and duration of the table's main INSERT, UPDATE and DELETE statements. This is where dropped indexes pay back.
• Plan forcing. Check sys.query_store_plan for rows where is_forced_plan = 1 and last_force_failure_reason_desc is not NONE. A forced plan that referenced a dropped index stops being applied.

If one procedure becomes erratic after an index change, see Why is my stored procedure fast one day and slow the next?

Common mistakes

• Dropping on usage numbers from a recent restart. The counters cover only the time since startup.
• Rebuilding every index above a fragmentation percentage. Most of the benefit usually comes from the statistics update.
• Creating a filtered index for a parameterized query. The cached plan cannot use it.

Checklist

1. Run the inventory and confirm how much history the usage counters cover.
2. List indexes with writes and no reads. Exclude unique indexes and anything named in hints or forced plans.
3. Run the overlap query and resolve exact duplicates, same-key pairs and left-prefix pairs.
4. Check row-level write cost and lock or latch waits with sys.dm_db_index_operational_stats.
5. Merge indexes that share a leading column, with equality columns first and a short INCLUDE list.
6. Measure page density, reset fill factors that have no page split evidence, and replace blanket rebuilds with statistics updates.
7. Evaluate filtered indexes, compression and a nonclustered columnstore index where they fit.
8. Disable, wait a full business cycle, drop, then compare reads, writes and size.

References

• Maintain indexes optimally to improve performance and reduce resource utilization (Microsoft Learn), the source for the fragmentation, page density and statistics guidance
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• Create filtered indexes
• Statistics
• Data compression
• Columnstore indexes: design guidance
• Disable indexes and constraints

Why is my stored procedure fast one day and slow the next?

Same proc, same code, suddenly 100× slower, and fast again when you run it in SSMS. Here's how parameter sniffing causes it, how to prove it and the fixes that actually hold.

#execution-plans#performance#troubleshooting