SQL Server Index Design: Clustered, Non-Clustered and Covering Indexes (2026)

Why Indexes Exist: The Heap Scan Problem

Without an index, SQL Server stores table rows in a heap — an unordered collection of 8 KB data pages. To find any row, the engine must read every single page from first to last: a full table scan. On a table with 50 million rows spread across 200,000 pages, that means reading roughly 1.5 GB of data from disk for every query, even if you only want one row.

Indexes solve this by building a separate B-tree (balanced tree) structure. A B-tree organises key values in sorted order across a hierarchy of pages: one root page at the top, zero or more intermediate levels in the middle, and leaf pages at the bottom. A seek on a 50-million-row table typically traverses only 4–5 levels (root → 2–3 intermediate → leaf), reading a handful of pages instead of hundreds of thousands.

Leaf pages in a clustered index contain the actual data rows. Leaf pages in a non-clustered index contain the key columns plus a pointer (the clustered key or a RID for heaps) back to the full row. Both structures keep leaf pages doubly-linked so range scans can follow the chain without returning to the root.

Clustered Index: Physical Row Order

A clustered index determines the physical storage order of rows on disk. Because rows can only be in one order, each table can have exactly one clustered index. When you create a PRIMARY KEY on a column in SQL Server, a clustered index is created on that column by default (unless you explicitly specify NONCLUSTERED).

-- Create a table with a clustered index on the identity column (default PK behaviour)
CREATE TABLE dbo.Orders
(
    order_id    INT           NOT NULL IDENTITY(1,1),
    customer_id INT           NOT NULL,
    order_date  DATE          NOT NULL,
    status      NVARCHAR(20)  NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (order_id)
);

Choosing the right clustered index key is one of the most consequential decisions in schema design. The ideal clustered key is:

• Narrow — 4–8 bytes. Every non-clustered index stores a copy of the clustered key as a row locator, so a wide clustered key bloats all non-clustered indexes.
• Unique — SQL Server silently appends a 4-byte uniquifier to duplicate clustered keys, adding overhead.
• Ever-increasing — an incrementing integer or sequential GUID means new rows always insert at the right end of the leaf level, avoiding mid-page insertions and the page splits they cause.
• Static — updating the clustered key value moves the entire row to a new physical location and cascades the change to all non-clustered indexes.

-- Identity integer: ideal clustered key (narrow, unique, ever-increasing)
CREATE TABLE dbo.Employees
(
    employee_id INT NOT NULL IDENTITY(1,1),
    department  NVARCHAR(50) NOT NULL,
    hire_date   DATE         NOT NULL,
    salary      DECIMAL(10,2) NOT NULL,
    CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED (employee_id)
);

-- Range query benefits from clustered index on order_date when most queries filter by date
-- (only appropriate if date is the dominant access pattern AND you have another unique key)
CREATE TABLE dbo.SalesLog
(
    log_id      BIGINT       NOT NULL IDENTITY(1,1),
    logged_at   DATETIME2(3) NOT NULL,
    event_type  NVARCHAR(50) NOT NULL,
    payload     NVARCHAR(MAX) NULL,
    CONSTRAINT PK_SalesLog PRIMARY KEY NONCLUSTERED (log_id)
);
CREATE CLUSTERED INDEX CIX_SalesLog_LoggedAt ON dbo.SalesLog (logged_at);

Clustered Index Gotchas: GUIDs and Wide Keys

Two anti-patterns appear repeatedly in real-world schemas and cause significant, sometimes invisible, performance problems.

GUID Clustered Keys Cause Fragmentation

A UNIQUEIDENTIFIER populated with NEWID() generates a random 16-byte value. Inserting random values into a B-tree means new rows land in the middle of existing leaf pages rather than at the end. When a page is full, SQL Server splits it: half the rows move to a new page, leaving both pages ~50% full. Heavy GUID insert workloads drive logical fragmentation to 90%+ within hours.

-- Anti-pattern: random GUID clustered key
CREATE TABLE dbo.Sessions
(
    session_id  UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),  -- random, causes splits
    user_id     INT              NOT NULL,
    created_at  DATETIME2(3)     NOT NULL,
    CONSTRAINT PK_Sessions PRIMARY KEY CLUSTERED (session_id)
);

-- Better: sequential GUID avoids random inserts
CREATE TABLE dbo.Sessions
(
    session_id  UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),  -- monotone
    user_id     INT              NOT NULL,
    created_at  DATETIME2(3)     NOT NULL,
    CONSTRAINT PK_Sessions PRIMARY KEY CLUSTERED (session_id)
);

-- Best for most OLTP: integer identity (4 bytes vs 16)
CREATE TABLE dbo.Sessions
(
    session_id  BIGINT           NOT NULL IDENTITY(1,1),
    session_guid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),  -- still available for external references
    user_id     INT              NOT NULL,
    created_at  DATETIME2(3)     NOT NULL,
    CONSTRAINT PK_Sessions PRIMARY KEY CLUSTERED (session_id)
);

Wide Clustered Keys Bloat All Non-Clustered Indexes

Every non-clustered index leaf row stores the clustered key as a row locator. If your clustered key is a composite of three columns totalling 80 bytes, and you have 10 non-clustered indexes on a 100-million-row table, you are storing 80 bytes × 100 M rows × 10 indexes = 80 GB of duplicated key data. A 4-byte integer clustered key reduces that to 4 GB.

Non-Clustered Index: Separate B-Tree

A non-clustered index is an independent B-tree whose leaf pages contain the indexed key columns plus the clustered key (or heap RID) as a row locator. SQL Server can create up to 999 non-clustered indexes per table, though you will rarely want more than a handful.

-- Simple non-clustered index on a frequently filtered column
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (customer_id);

-- Composite non-clustered index for a specific query pattern
-- Query: WHERE customer_id = ? AND order_date >= ? ORDER BY order_date
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON dbo.Orders (customer_id, order_date);

When SQL Server uses a non-clustered index to satisfy a query, the leaf page provides the key columns and the clustered key. If the query needs additional columns that are not in the index, SQL Server performs a Key Lookup (or RID Lookup for heaps): a second B-tree traversal into the clustered index to fetch those columns. Key Lookups are cheap for a handful of rows but become expensive when applied thousands of times in a loop join. This is what covering indexes solve (see Section 6).

Index Seeks vs Scans and SARGable Predicates

SQL Server can access an index in two ways. An Index Seek traverses the B-tree from root to leaf, landing on exactly the rows that satisfy the predicate — efficient. An Index Scan reads every leaf page sequentially from first to last — equivalent to a heap scan for a large table. The optimizer chooses based on selectivity and the shape of the predicate.

Whether the optimizer can perform a seek depends on whether the predicate is SARGable (Search ARGument able). A SARGable predicate can be directly matched against the B-tree's sorted key values. Common patterns that break SARGability:

-- SARGable: optimizer can seek into the index
SELECT order_id FROM dbo.Orders WHERE customer_id = 4201;
SELECT order_id FROM dbo.Orders WHERE order_date >= '2026-01-01';
SELECT product_name FROM dbo.Products WHERE product_name LIKE 'SQL%';   -- leading literal

-- NOT SARGable: index cannot be used for a seek
-- Wrapping the column in a function prevents the engine from matching sorted key values
SELECT order_id FROM dbo.Orders WHERE YEAR(order_date) = 2026;
-- Fix:
SELECT order_id FROM dbo.Orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

-- Leading wildcard: engine cannot seek because the matching prefix is unknown
SELECT product_name FROM dbo.Products WHERE product_name LIKE '%SQL%';

-- Implicit type conversion: column is INT, literal is a string
SELECT order_id FROM dbo.Orders WHERE customer_id = '4201';
-- Fix: match literal type to column type
SELECT order_id FROM dbo.Orders WHERE customer_id = 4201;

-- Arithmetic on the column side
SELECT order_id FROM dbo.OrderItems WHERE unit_price * quantity > 500;
-- Fix: isolate column
-- (use a computed column + index if this pattern is frequent — see Section 10)

The optimizer will also choose a scan over a seek when the estimated selectivity is low — for example, fetching 80% of rows. Reading the leaf pages sequentially in order is faster than thousands of random seeks followed by Key Lookups. This is expected behaviour and not a bug.

Covering Index: Eliminating Key Lookups with INCLUDE

A covering index is one that satisfies the entire query from the index alone, without a Key Lookup into the clustered index. You make an index covering by adding the extra columns the query needs to the index's INCLUDE list. INCLUDE columns live only at the leaf level — they do not participate in B-tree navigation and they do not affect seek order — but they are available once the leaf is reached.

Before: Key Lookup Required

-- Table and initial index
CREATE TABLE dbo.Orders
(
    order_id     INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
    customer_id  INT           NOT NULL,
    order_date   DATE          NOT NULL,
    status       NVARCHAR(20)  NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
);

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (customer_id);   -- only customer_id in the index

-- Query that forces a Key Lookup to fetch order_date, status, total_amount
SELECT order_id, order_date, status, total_amount
FROM   dbo.Orders
WHERE  customer_id = 4201;

-- Execution plan shows: Index Seek (IX_Orders_CustomerID) → Key Lookup (PK_Orders)
-- For customers with thousands of orders this Key Lookup loop is expensive

After: Covering Index Eliminates the Key Lookup

-- Drop the old index and create a covering version
DROP INDEX IF EXISTS IX_Orders_CustomerID ON dbo.Orders;

CREATE NONCLUSTERED INDEX IX_Orders_Customer_Covering
ON dbo.Orders (customer_id)
INCLUDE (order_date, status, total_amount);
-- customer_id: seek key (B-tree navigation)
-- order_date, status, total_amount: stored at leaf only, no Key Lookup needed

-- Same query now reads only the non-clustered index — no clustered index touch
SELECT order_id, order_date, status, total_amount
FROM   dbo.Orders
WHERE  customer_id = 4201;
-- Execution plan: Index Seek (IX_Orders_Customer_Covering) only — no Key Lookup

The order_id column is not listed in INCLUDE because it is the clustered key — it is automatically present in every non-clustered index leaf row as the row locator.

Composite Index Column Order: The EER Rule

When an index spans multiple columns, their order critically determines which queries benefit. The EER rule summarises optimal ordering: Equality predicates first, then Equality-range transition, then Range predicates. More practically: put columns used in equality filters (=) before columns used in range filters (>, <, BETWEEN, LIKE 'x%').

SQL Server can seek on a composite index only for a contiguous prefix of its key. Once a range predicate is encountered, navigation stops and a range scan begins from that point. Columns after the range column cannot be used for seek navigation.

-- Query pattern: exact match on status, range on order_date, read total_amount
SELECT order_id, total_amount
FROM   dbo.Orders
WHERE  status = 'Shipped'          -- equality
  AND  order_date >= '2026-01-01'; -- range

-- Wrong order: range column first means SQL Server can only seek to order_date >= 2026-01-01
-- then must scan all status values within that range
CREATE NONCLUSTERED INDEX IX_Wrong
ON dbo.Orders (order_date, status)
INCLUDE (total_amount);

-- Correct order: equality column first, then range — seeks on status='Shipped' first,
-- then range-scans only the matching order_date rows within the Shipped partition
CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
ON dbo.Orders (status, order_date)
INCLUDE (total_amount);

Selectivity Tie-Breaking

When two or more columns are all equality predicates, put the most selective column (fewest duplicate values) first. This prunes the B-tree more aggressively at the top levels and reduces intermediate pages read. For example, if region has 5 distinct values and rep_id has 5,000 distinct values, lead with rep_id.

-- Query: WHERE rep_id = 88 AND region = 'APAC'
-- rep_id is more selective (5000 values vs 5) → put it first
CREATE NONCLUSTERED INDEX IX_Sales_Rep_Region
ON dbo.SalesOrders (rep_id, region)
INCLUDE (order_total);

See also: Reading Execution Plans to confirm the optimizer is using the leading column for seek predicates.

Filtered Index: Partial Indexes with WHERE

A filtered index includes only a subset of rows that match a static WHERE clause. Because it covers a smaller rowset, a filtered index is smaller, cheaper to maintain, and often more selective than a full-table index on the same column.

Sparse Column Optimisation

-- Table stores documents; only ~5% have an expiry_date (non-NULL)
CREATE TABLE dbo.Documents
(
    doc_id      BIGINT        NOT NULL IDENTITY(1,1) PRIMARY KEY,
    title       NVARCHAR(200) NOT NULL,
    expiry_date DATE          NULL,      -- sparse: mostly NULL
    status      NVARCHAR(20)  NOT NULL
);

-- Full index on expiry_date: 95% of leaf rows are NULL — wasteful
-- Filtered index: only indexes the rows that actually have an expiry_date
CREATE NONCLUSTERED INDEX IX_Documents_ExpiryDate_Filtered
ON dbo.Documents (expiry_date)
WHERE expiry_date IS NOT NULL;

-- This query now seeks a tiny, dense index instead of a large sparse one
SELECT doc_id, title
FROM   dbo.Documents
WHERE  expiry_date < GETDATE()
  AND  expiry_date IS NOT NULL;

Soft-Delete Pattern

-- Table uses a soft-delete flag; most queries only care about active rows
CREATE TABLE dbo.Customers
(
    customer_id  INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
    email        NVARCHAR(254) NOT NULL,
    is_deleted   BIT           NOT NULL DEFAULT 0
);

-- Filtered unique index: enforces unique email only among active customers
-- (deleted customers may share an email if the address is reused)
CREATE UNIQUE NONCLUSTERED INDEX UX_Customers_Email_Active
ON dbo.Customers (email)
WHERE is_deleted = 0;

-- Most application queries only see active customers — this tiny index is perfect
SELECT customer_id FROM dbo.Customers
WHERE  email = 'alice@example.com'
  AND  is_deleted = 0;

Unique Index: Enforcing Uniqueness

A unique index enforces that no two rows share the same key value. It is both a constraint mechanism and a query optimisation tool — the optimizer knows a unique index returns at most one row for an equality predicate, enabling more efficient plan choices.

-- Unique index as an alternate key (email uniqueness aside from the PK)
CREATE UNIQUE NONCLUSTERED INDEX UX_Employees_Email
ON dbo.Employees (email);

-- Composite unique index: pair of columns must be unique together
CREATE UNIQUE NONCLUSTERED INDEX UX_ProjectAssignment
ON dbo.ProjectAssignments (project_id, employee_id);

-- Check current unique constraints/indexes on a table
SELECT i.name, i.is_unique, i.is_primary_key,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns
FROM   sys.indexes i
JOIN   sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN   sys.columns       c  ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE  i.object_id = OBJECT_ID('dbo.Employees')
  AND  i.is_unique = 1
GROUP BY i.name, i.is_unique, i.is_primary_key;

NULL Behaviour in Unique Indexes

SQL Server treats NULL values in unique indexes differently from a UNIQUE CONSTRAINT in the SQL standard. A UNIQUE CONSTRAINT allows only one NULL per indexed column (for single-column indexes). A UNIQUE INDEX behaves identically. However, a filtered unique index with WHERE column IS NOT NULL allows unlimited NULLs while still enforcing uniqueness among non-NULL values — useful for optional unique identifiers.

-- Allow multiple NULLs but enforce uniqueness among non-NULL external_id values
CREATE UNIQUE NONCLUSTERED INDEX UX_Employees_ExternalID
ON dbo.Employees (external_id)
WHERE external_id IS NOT NULL;

Index on Computed Columns

A function applied to a column in a WHERE clause breaks SARGability. The solution is to materialise the expression as a persisted computed column and then index that column. SQL Server stores the computed value on disk and keeps it updated automatically on writes.

-- Problem: query filters by the year of order_date — non-SARGable
SELECT order_id FROM dbo.Orders WHERE YEAR(order_date) = 2026;
-- Full scan even with an index on order_date

-- Solution: persisted computed column + index
ALTER TABLE dbo.Orders
ADD order_year AS YEAR(order_date) PERSISTED;

CREATE NONCLUSTERED INDEX IX_Orders_OrderYear
ON dbo.Orders (order_year);

-- Now this query performs an index seek on order_year = 2026
SELECT order_id FROM dbo.Orders WHERE YEAR(order_date) = 2026;
-- The optimizer automatically maps YEAR(order_date) → order_year

-- Another example: case-insensitive search via computed column
ALTER TABLE dbo.Customers
ADD email_upper AS UPPER(email) PERSISTED;

CREATE NONCLUSTERED INDEX IX_Customers_EmailUpper
ON dbo.Customers (email_upper);

-- Seek: filters by UPPER(email) = UPPER('Alice@EXAMPLE.COM')
SELECT customer_id FROM dbo.Customers
WHERE  email_upper = UPPER('Alice@EXAMPLE.COM');

See T-SQL SELECT Internals for background on why the optimizer can substitute computed column expressions for their definitions.

Measuring Index Usage: sys.dm_db_index_usage_stats

sys.dm_db_index_usage_stats tracks cumulative seek, scan, lookup, and update counts for every index since the last SQL Server restart (or since the index was rebuilt). It is the primary tool for identifying unused indexes — those that cost write overhead but are never used for reads.

-- Find indexes that have never been used for reads since last restart
-- High update count + zero read counts = candidate for removal
SELECT
    OBJECT_SCHEMA_NAME(i.object_id)          AS schema_name,
    OBJECT_NAME(i.object_id)                 AS table_name,
    i.name                                   AS index_name,
    i.type_desc,
    ISNULL(us.user_seeks,  0)                AS user_seeks,
    ISNULL(us.user_scans,  0)                AS user_scans,
    ISNULL(us.user_lookups,0)                AS user_lookups,
    ISNULL(us.user_updates,0)                AS user_updates,
    ISNULL(us.last_user_seek,  NULL)         AS last_seek,
    ISNULL(us.last_user_scan,  NULL)         AS last_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats 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 > 100                        -- skip system objects
  AND i.type_desc <> 'HEAP'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND ISNULL(us.user_seeks + us.user_scans + us.user_lookups, 0) = 0
ORDER BY ISNULL(us.user_updates, 0) DESC;

-- Summary: read-to-write ratio per index — low ratio = likely over-indexed
SELECT
    OBJECT_NAME(i.object_id)                          AS table_name,
    i.name                                            AS index_name,
    us.user_seeks + us.user_scans + us.user_lookups   AS total_reads,
    us.user_updates                                   AS total_writes,
    CAST(
        (us.user_seeks + us.user_scans + us.user_lookups) * 1.0
        / NULLIF(us.user_updates, 0)
    AS DECIMAL(10,2))                                 AS read_write_ratio
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats 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 = 'NONCLUSTERED'
ORDER BY read_write_ratio ASC;

Measuring Index Effectiveness: sys.dm_db_index_operational_stats

sys.dm_db_index_operational_stats provides lower-level I/O and locking metrics per index partition. It is particularly useful for diagnosing row lock waits (contention hot spots) and page split counts (fragmentation caused by mid-page inserts).

-- Find indexes with high page split counts — sign of bad clustered key or fill factor
SELECT
    OBJECT_SCHEMA_NAME(ios.object_id)    AS schema_name,
    OBJECT_NAME(ios.object_id)           AS table_name,
    i.name                               AS index_name,
    ios.leaf_insert_count,
    ios.leaf_update_count,
    ios.leaf_delete_count,
    ios.leaf_page_merge_count,
    ios.page_latch_wait_count,
    ios.page_latch_wait_in_ms,
    ios.row_lock_wait_count,
    ios.row_lock_wait_in_ms,
    ios.page_lock_wait_count,
    ios.page_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
JOIN sys.indexes i
    ON  i.object_id = ios.object_id
    AND i.index_id  = ios.index_id
WHERE ios.object_id > 100
  AND i.type_desc <> 'HEAP'
ORDER BY ios.page_latch_wait_in_ms DESC;

-- Combined view: fragmentation + usage + operational stats in one query
SELECT
    OBJECT_NAME(ps.object_id)             AS table_name,
    i.name                                AS index_name,
    ps.index_type_desc,
    CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS frag_pct,
    ps.page_count,
    ISNULL(us.user_seeks + us.user_scans + us.user_lookups, 0) AS total_reads,
    ISNULL(us.user_updates, 0)            AS total_writes,
    ios.row_lock_wait_count,
    ios.page_latch_wait_in_ms
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i
    ON  i.object_id = ps.object_id
    AND i.index_id  = ps.index_id
LEFT JOIN sys.dm_db_index_usage_stats us
    ON  us.object_id   = ps.object_id
    AND us.index_id    = ps.index_id
    AND us.database_id = DB_ID()
LEFT JOIN sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
    ON  ios.object_id = ps.object_id
    AND ios.index_id  = ps.index_id
WHERE ps.object_id > 100
  AND i.type_desc <> 'HEAP'
  AND ps.page_count > 100
ORDER BY ps.avg_fragmentation_in_percent DESC;

Index Maintenance: Rebuild vs Reorganize

Index fragmentation accumulates as pages split and rows move. Fragmentation increases I/O by causing sequential reads to hop between non-contiguous pages. SQL Server provides two repair operations: ALTER INDEX … REORGANIZE (online, incremental defrag) and ALTER INDEX … REBUILD (offline by default, drops and recreates the B-tree).

The widely-used Microsoft guidance is: fragmentation below 10% — do nothing; 10–30% — reorganize; above 30% — rebuild. However, page count matters too: small indexes (under 1,000 pages) have negligible fragmentation impact regardless of percentage.

-- Adaptive maintenance script: reorganize or rebuild based on fragmentation %
DECLARE @schema_name  NVARCHAR(128);
DECLARE @table_name   NVARCHAR(128);
DECLARE @index_name   NVARCHAR(128);
DECLARE @frag_pct     FLOAT;
DECLARE @sql          NVARCHAR(MAX);

DECLARE idx_cursor CURSOR FOR
    SELECT
        OBJECT_SCHEMA_NAME(ps.object_id),
        OBJECT_NAME(ps.object_id),
        i.name,
        ps.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
    JOIN sys.indexes i
        ON  i.object_id = ps.object_id
        AND i.index_id  = ps.index_id
    WHERE ps.page_count > 1000          -- ignore tiny indexes
      AND ps.index_id  > 0              -- skip heaps
      AND i.type_desc <> 'HEAP'
      AND ps.avg_fragmentation_in_percent > 10;

OPEN idx_cursor;
FETCH NEXT FROM idx_cursor INTO @schema_name, @table_name, @index_name, @frag_pct;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @frag_pct > 30
        SET @sql = 'ALTER INDEX [' + @index_name + '] ON ['
                   + @schema_name + '].[' + @table_name + '] REBUILD WITH (ONLINE = ON);';
    ELSE
        SET @sql = 'ALTER INDEX [' + @index_name + '] ON ['
                   + @schema_name + '].[' + @table_name + '] REORGANIZE;';

    PRINT @sql;
    EXEC sp_executesql @sql;

    FETCH NEXT FROM idx_cursor INTO @schema_name, @table_name, @index_name, @frag_pct;
END;

CLOSE idx_cursor;
DEALLOCATE idx_cursor;

After a REBUILD, sys.dm_db_index_usage_stats resets for that index. Factor this into your unused-index analysis — an index that shows zero reads after a recent rebuild may still be in active use.

How Many Indexes Is Too Many?

Every non-clustered index on a table adds write overhead. For every INSERT, SQL Server must insert a new row into every non-clustered index. For every UPDATE that touches an indexed column, SQL Server must update the corresponding index rows. For every DELETE, SQL Server must remove rows from every index. This overhead compounds with the number of indexes and the write rate of the table.

Write Cost per Index

-- Estimate write amplification on a heavily-indexed table
-- Suppose a table has 12 non-clustered indexes and receives 10,000 INSERTs/second
-- Each INSERT → 1 clustered index write + 12 non-clustered index writes = 13 writes
-- At 10,000 TPS: 130,000 index page writes per second — substantial I/O and latch pressure

-- Measure actual write cost: check user_updates vs user_seeks ratio
SELECT
    OBJECT_NAME(i.object_id)                                     AS table_name,
    i.name                                                       AS index_name,
    us.user_updates                                              AS write_ops,
    us.user_seeks + us.user_scans + us.user_lookups              AS read_ops,
    CASE
        WHEN us.user_seeks + us.user_scans + us.user_lookups = 0
            THEN 'NEVER READ — DROP CANDIDATE'
        WHEN us.user_updates > 10 * (us.user_seeks + us.user_scans + us.user_lookups)
            THEN 'HIGH WRITE / LOW READ — REVIEW'
        ELSE 'OK'
    END AS assessment
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats 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 = 'NONCLUSTERED'
  AND i.is_primary_key = 0
ORDER BY us.user_updates DESC;

General heuristics for OLTP tables:

• 1–3 indexes per high-write table (orders, transactions, events) is usually sufficient.
• 5–8 indexes per moderate-write table is reasonable if each index serves a distinct, frequently-executed query pattern.
• 10+ indexes is a warning sign. Audit with sys.dm_db_index_usage_stats before adding more.
• Read-heavy or read-only tables (dimension tables, reference data) can sustain more indexes since write overhead is minimal.

When deciding whether to add an index, weigh the read benefit against the write cost. Use execution plan analysis to confirm the new index is actually chosen by the optimizer before permanently committing to it in production. See also Set-Based Thinking for writing queries that are intrinsically more index-friendly, and SQL Server JOINs for join-specific index strategies.

-- Top missing index suggestions ordered by estimated improvement impact
SELECT TOP 20
    mig.avg_total_user_cost * mig.avg_user_impact * (mig.user_seeks + mig.user_scans)
                                                        AS improvement_measure,
    mid.statement                                       AS affected_table,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    mig.user_seeks,
    mig.user_scans,
    CAST(mig.avg_user_impact AS DECIMAL(5,1))           AS avg_impact_pct
FROM sys.dm_db_missing_index_group_stats  mig
JOIN sys.dm_db_missing_index_groups       mig2 ON mig.group_handle = mig2.index_group_handle
JOIN sys.dm_db_missing_index_details      mid  ON mig2.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Read Next