Sqlserver Indexes

SQL Server Indexes

Indexes are the single biggest lever for SQL Server query performance. This skill covers finding missing indexes, maintaining existing ones, eliminating unused indexes, and designing effective covering indexes.

1. Missing Indexes

SQL Server tracks index opportunities it identified during query execution in DMVs. These are not perfect recommendations — treat them as leads.

Script: ../scripts/missing-indexes.sql

-- Top missing indexes by impact score
SELECT TOP 20
    ROUND(migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0)
                                            AS impact_score,
    DB_NAME(mid.database_id)               AS database_name,
    mid.statement                          AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_user_impact,
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_'
        + REPLACE(REPLACE(REPLACE(
            ISNULL(mid.equality_columns, '') + 
            ISNULL('_' + mid.inequality_columns, ''), 
            '[', ''), ']', ''), ', ', '_')
        + '] 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 + ')'
        + CASE WHEN mid.included_columns IS NOT NULL
               THEN ' INCLUDE (' + mid.included_columns + ')'
               ELSE '' END                 AS suggested_create_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;

Interpreting results:

• impact_score = avg_user_impact × (seeks + scans) — higher is more important
• equality_columns → these become the leading key columns
• inequality_columns → these follow equality columns in the key (range predicates)
• included_columns → INCLUDE these to cover the SELECT list (no key lookup needed)
• Don't blindly create every suggestion — consolidate similar indexes first

Column ordering rule: Always put equality predicates first, then inequality predicates, then included columns. See references/index-strategies.md.

2. Index Fragmentation

Fragmentation degrades performance by forcing SQL Server to read more pages for the same data. Run this regularly (monthly for most workloads).

Script: ../scripts/index-fragmentation.sql

SELECT
    s.name                              AS schema_name,
    o.name                              AS table_name,
    i.name                              AS index_name,
    ips.index_type_desc                 AS type_desc,
    ROUND(ips.avg_fragmentation_in_percent, 1) AS fragmentation_pct,
    ips.page_count,
    CASE
        WHEN ips.avg_fragmentation_in_percent > 30  THEN 'REBUILD'
        WHEN ips.avg_fragmentation_in_percent > 10  THEN 'REORGANIZE'
        ELSE 'SKIP'
    END                                 AS recommended_action,
    'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + o.name + '] '
        + CASE
            WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (ONLINE = ON);'
            ELSE 'REORGANIZE;'
          END                           AS maintenance_statement
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN sys.indexes i   ON ips.object_id = i.object_id AND ips.index_id = i.index_id
JOIN sys.objects o   ON i.object_id = o.object_id
JOIN sys.schemas s   ON o.schema_id = s.schema_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 100   -- Ignore tiny indexes (not worth maintaining)
  AND i.name IS NOT NULL
ORDER BY ips.avg_fragmentation_in_percent DESC;

Decision guide:

Notes:

• REBUILD updates statistics automatically; REORGANIZE does not (run UPDATE STATISTICS separately)
• ONLINE = ON requires Enterprise Edition — table stays accessible during rebuild
• Use SAMPLED mode (not DETAILED) for DMV query — much faster on large tables

3. Unused Indexes

Indexes that are never read but still incur write overhead on every INSERT/UPDATE/DELETE. These are pure waste.

Script: ../scripts/unused-indexes.sql

SELECT
    s.name                  AS schema_name,
    o.name                  AS table_name,
    i.name                  AS index_name,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,       -- Write cost — every DML pays this price
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_lookup,
    -- Commented out for safety — review before executing
    '-- DROP INDEX [' + i.name + '] ON [' + s.name + '].[' + o.name + '];'
                            AS drop_statement
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i  ON ius.object_id = i.object_id AND ius.index_id = i.index_id
JOIN sys.objects o  ON i.object_id = o.object_id
JOIN sys.schemas s  ON o.schema_id = s.schema_id
WHERE ius.database_id = DB_ID()
  AND ius.user_seeks   = 0
  AND ius.user_scans   = 0
  AND ius.user_lookups = 0
  AND ius.user_updates > 0
  AND i.type_desc != 'CLUSTERED'   -- Never drop the clustered index this way
  AND o.is_ms_shipped = 0
ORDER BY ius.user_updates DESC;

⚠️ Important caveats:

• Usage stats reset on SQL Server service restart — don't use this query after a recent restart
• An index may be used by infrequent batch jobs — verify against monthly patterns before dropping
• Some indexes exist for constraint enforcement (UNIQUE) — check i.is_unique_constraint
• Test dropping in dev/staging first

4. Index Usage Stats

Understand how an index is actually being used:

SELECT
    s.name                  AS schema_name,
    o.name                  AS table_name,
    i.name                  AS index_name,
    i.type_desc,
    ius.user_seeks,         -- Index seeks (best — specific rows via key)
    ius.user_scans,         -- Full or range scans (usually worse)
    ius.user_lookups,       -- Key lookups (seek + bookmark lookup — consider covering)
    ius.user_updates,       -- Write overhead
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i  ON ius.object_id = i.object_id AND ius.index_id = i.index_id
JOIN sys.objects o  ON i.object_id = o.object_id
JOIN sys.schemas s  ON o.schema_id = s.schema_id
WHERE ius.database_id = DB_ID()
  AND o.is_ms_shipped = 0
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC;

Seeks vs Scans vs Lookups:

• Seeks — Best. SQL Server navigated directly to specific rows via the B-tree key.
• Scans — SQL Server read the whole index (or a large range). May indicate missing predicate or wrong index.
• Lookups — SQL Server found a row in the non-clustered index but had to go back to the clustered index for additional columns. Fix with a covering index.

5. Covering Indexes

A covering index satisfies a query entirely from the index — no lookup to the base table needed.

Problem pattern (Key Lookup):

-- Table: Orders (OrderID PK, CustomerID, OrderDate, Status, TotalAmount, Notes)
-- Query:
SELECT OrderDate, Status, TotalAmount
FROM Orders
WHERE CustomerID = 42;

-- If you have: CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID)
-- SQL Server seeks to CustomerID = 42 but then does a KEY LOOKUP for OrderDate, Status, TotalAmount

Fix — covering index:

CREATE INDEX IX_Orders_CustomerID_Covering
ON Orders (CustomerID)
INCLUDE (OrderDate, Status, TotalAmount);
-- CustomerID is the key (searchable); OrderDate/Status/TotalAmount are carried along
-- No key lookup needed

Key vs INCLUDE columns:

• Key columns — used in WHERE, JOIN, ORDER BY. Added to the B-tree structure.
• INCLUDE columns — only carried in the leaf level. Can be any data type. Not searchable. Used to satisfy SELECT list.

See references/index-strategies.md for detailed design patterns.

Reference

• Index Strategies Reference — Composite key ordering, filtered indexes, columnstore, maintenance scheduling
• Scripts: ../scripts/missing-indexes.sql, ../scripts/index-fragmentation.sql, ../scripts/unused-indexes.sql