The Index Ledger

One table from a production audit, October 2026. The arithmetic is exact: 62 scans × 31,836 rows = 1,973,832 rows read, while the index sat unused.

Most indexing material explains what an index is: B-trees, clustered and nonclustered structures, leaf nodes. It explains it well. Production asks a different question. What does this index do for me, and what does it charge me for the privilege?

After reading one of those explainers, I opened a production database and checked whether its indexes were being used. One table of 31,836 rows had been read end to end 62 times. The index built specifically for that lookup had a usage counter of zero.

This article treats every index as an account with two columns. The cost column is charged on every insert, update and delete, and again in storage, buffer pool memory, transaction log volume and maintenance windows. The earnings column fills only when the optimizer chooses the index for a plan. An index problem is almost always a ledger where the second column is empty. The sections below explain how that happens, how to measure it in SQL Server, and which features in SQL Server 2022 change the balance.

01An index is a promise about order

An index is a sorted structure that lets the engine find rows without reading the table. SQL Server’s rowstore indexes are B+ trees built from 8 KB pages. Each page has a 96-byte header, which leaves 8,096 bytes for rows and the slot array. A root page points to intermediate pages, which point to leaf pages. Leaf pages are linked to their neighbours in both directions, so a range read can walk sideways once it has found its starting point.

What lives at the leaf level defines the index type. In a clustered index the leaf level is the table: rows are stored in key order, so there can be only one. In a nonclustered index the leaf holds the key plus a row locator that leads back to the full row. The locator’s form depends on the base table, and that detail matters more than most designs admit.

Order beats brute force because tree depth grows with the logarithm of the row count. A non-leaf page holding narrow keys points to hundreds of children, so each extra level multiplies capacity by that fan-out. Figure 2 plots that relationship with deliberately ordinary numbers.

SELECT  OBJECT_NAME(ips.object_id)  AS table_name,
        i.name                      AS index_name,
        ips.index_type_desc,
        ips.index_depth,            -- page reads for one seek
        ips.page_count,
        ips.avg_page_space_used_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, 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.page_count > 1000
ORDER BY ips.page_count DESC;

02The row locator has teeth

On a clustered table every nonclustered index row carries the entire clustering key as its locator. If the clustered index is not declared unique, SQL Server adds a hidden 4-byte uniquifier to duplicate keys as well. The clustering key is therefore not a local decision about one index. It is a width tax applied to every nonclustered index the table will ever have, at every leaf row.

The long-standing guidance for a clustering key still holds: narrow, static, unique and ever-increasing. Narrow keeps every nonclustered index small. Static avoids moving rows and rewriting every nonclustered entry when the key changes. Unique avoids the uniquifier. Ever-increasing avoids page splits in the middle of the tree. That last property concentrates inserts on the final page, and on very hot tables this produces PAGELATCH_EX contention. Since SQL Server 2019 the index option OPTIMIZE_FOR_SEQUENTIAL_KEY = ON mitigates it without changing the key.

03The optimizer decides, not you

You can create a perfect index and still get a scan. The optimizer is not being difficult. The query made the index unusable. A predicate that can use an index seek is called sargable (Search ARGument ABLE): the bare column is compared with a value whose type matches, in a form that maps to a contiguous key range.

Implicit conversion deserves its own picture, because it hides in application code rather than in the query text a DBA reviews. When two types meet in a comparison, SQL Server converts the one with lower data type precedence to the higher one. If the converted side is the column, every row is converted and the seek is lost.

An index that is never chosen is not neutral. It is maintained on every write and earns nothing. The plan cache already records the conversions that cost you seeks; the query below finds them.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
        st.text                          AS statement_text,
        qs.execution_count,
        qs.total_worker_time / 1000      AS total_cpu_ms,
        qp.query_plan
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE   qp.query_plan.exist('//PlanAffectingConvert[@ConvertIssue="Seek Plan"]') = 1
ORDER BY qs.total_worker_time DESC;

04Column order is the index

Ask ten developers what (TenantId, CreatedAt) indexes and most will say “TenantId and CreatedAt”. It indexes TenantId, and then CreatedAt within each tenant. The leaf level is one long list sorted by the first column, with the second column sorted only inside each run of equal first values. This is the leftmost-prefix property, and every rule about composite indexes follows from it. The explorer below lays out that leaf level so you can see which rows each query has to touch.

A composite index serves, in this order: equality on its leading columns, then either an ORDER BY or a range on the next column. It cannot give you both on the same column position. Once a range appears, the order of every later column is broken into separate runs. That is why “wider indexes serve more queries” is backwards: every extra key column makes the index larger and costlier to maintain, and serves only queries that also match the columns in front of it.

Columns that a query only needs to return belong in INCLUDE. Included columns live only at the leaf level. They do not widen the intermediate pages, do not count toward the 1,700-byte nonclustered key limit, and do not affect ordering. They turn a seek plus key lookups into a covering seek. Two cautions apply. Every included column is still written on every insert and on every update of that column. And the missing-index DMVs suggest equality columns followed by inequality columns, with no awareness of ORDER BY or of the indexes you already have, so treat their output as raw material for a design rather than as a design.

05Every index is a write tax

Each nonclustered index is a separate B+ tree that must be kept in step with the table. An INSERT modifies the base table and every nonclustered index; a filtered index is touched only when the row qualifies. A DELETE removes the entry from every index. An UPDATE touches the indexes whose key or included columns changed. All of it is logged, all of it takes locks and latches, and all of it competes for the same buffer pool.

The tax has a second form: space that holds nothing. I once looked at a hot counter table with ten live rows occupying 6.8 MB. That measurement was taken on PostgreSQL, where every update leaves a dead row version behind until vacuum. SQL Server reaches a similar state by other routes: ghost records left by deletes, page splits that leave pages half empty, and row versions kept for snapshot isolation or accelerated database recovery.

SELECT  i.name AS index_name,
        ips.index_level,
        ips.page_count,
        ips.record_count,
        ips.ghost_record_count,
        ips.avg_page_space_used_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Counters'), NULL, NULL, 'DETAILED') AS ips
JOIN    sys.indexes AS i
        ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.index_level = 0;   -- leaf level only

06Keeping the ledger

sys.dm_db_index_usage_stats records both columns of the ledger for every index the engine has touched since startup. user_seeks, user_scans and user_lookups are the earnings: the number of times a query plan used the index. user_updates is the cost: the number of statements that had to maintain it. Note that both are counted per operation, not per row; one UPDATE that changes a million rows adds one to user_updates.

One caveat protects careers. These counters live in memory and reset when the instance restarts, when the database is taken offline or detached, and when AUTO_CLOSE closes it. Some older builds of SQL Server 2012 and 2014 also reset an index’s row when it was rebuilt. Always read sqlserver_start_time before you read the counters.

-- 1. How long is the evidence?
SELECT sqlserver_start_time,
       DATEDIFF(DAY, sqlserver_start_time, SYSDATETIME()) AS days_of_evidence
FROM   sys.dm_os_sys_info;

-- 2. Nonclustered indexes ranked by cost with no earnings
SELECT  OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
        OBJECT_NAME(i.object_id)        AS table_name,
        i.name                          AS index_name,
        ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0)
          + ISNULL(us.user_lookups, 0)  AS reads_earned,
        ISNULL(us.user_updates, 0)      AS writes_paid,
        ps.used_pages * 8 / 1024.0      AS used_mb
FROM    sys.indexes AS i
JOIN    sys.objects AS o
        ON o.object_id = i.object_id AND o.type = 'U'
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
CROSS APPLY (SELECT SUM(p.used_page_count) AS used_pages
             FROM sys.dm_db_partition_stats AS p
             WHERE p.object_id = i.object_id AND p.index_id = i.index_id) AS ps
WHERE   i.type_desc = 'NONCLUSTERED'
  AND   i.is_primary_key = 0
  AND   i.is_unique_constraint = 0
ORDER BY reads_earned ASC, writes_paid DESC;

Before you drop anything

• Readable secondaries. The DMV is per instance. Reports running on an Availability Group secondary never appear on the primary, so an index can look idle on the primary and be essential on the replica.
• Uniqueness. A unique index can be doing its job with zero reads. The query above excludes constraints, but not unique indexes created with CREATE UNIQUE INDEX.
• Index hints and forced plans. Code using WITH (INDEX(IX_name)) fails when the index disappears, and a Query Store forced plan that references it stops being forceable. Search the code base and sys.query_store_plan where is_forced_plan = 1.
• Reversibility. Disable before you drop. ALTER INDEX … DISABLE keeps the definition and stops maintenance; a rebuild restores it. Never disable a clustered index this way, because that makes the table unreadable.

07One plan for all parameters

SQL Server caches a plan per statement, not per parameter value. The first execution compiles the plan using the parameter values it was given, and every later execution reuses it. When data is evenly spread this is a great bargain. When data is skewed it is a trap called parameter sniffing.

For a rare value the best plan is a nonclustered seek followed by key lookups. For a common value it is a scan. The crossover between them is the tipping point, and Figure 12 shows why reusing the wrong plan is not a small mistake.

For twenty years the fixes were manual, and each has a price:

08SQL Server 2022 changes the economics

SQL Server 2022 is the release where the engine starts keeping its own books. The common thread is persistence: the engine records what happened at runtime and uses it on the next compilation, instead of relying on a DBA to notice.

Query Store is the foundation, and it is on by default only for new databases

Query Store persists query text, plans and runtime statistics across restarts, which makes plan regressions visible and provable. In SQL Server 2022 it is enabled in READ_WRITE mode for newly created databases. Databases that were restored or upgraded in place keep the settings they arrived with, so on an upgraded server it may still be off. Check before relying on anything below.

SELECT name, compatibility_level, is_query_store_on
FROM   sys.databases
WHERE  database_id > 4;

-- run inside each database
SELECT actual_state_desc, desired_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb
FROM   sys.database_query_store_options;

ALTER DATABASE [Sales] SET QUERY_STORE = ON
      (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);

Parameter Sensitive Plan optimization

PSP optimization attacks parameter sniffing at the root. At compatibility level 160, when the statistics histogram shows skew on a parameterized equality predicate, the optimizer compiles a dispatcher plan. At runtime the dispatcher places the parameter value in a low, medium or high cardinality bucket and routes execution to a query variant with its own plan. Up to three predicates per query are considered, a deliberate cap so the plan cache cannot explode.

Feedback that survives restarts

Three feedback features learn from repeated executions and store what they learned in Query Store. Cardinality estimation feedback spots queries whose estimates are consistently wrong because of a model assumption, such as how correlated two predicates are, and adjusts that assumption for the next compilation. Degree of parallelism feedback lowers the DOP of queries that waste threads. Memory grant feedback in percentile mode sizes grants from recent history rather than the last run, which stops queries with oscillating needs from alternating between spills and wasted memory.

ALTER DATABASE [Sales] SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;

-- PSP variants created so far
SELECT query_variant_query_id, parent_query_id, dispatcher_plan_id
FROM   sys.query_store_query_variant;

-- Query Store hint: recompile one query without touching the application
EXEC sys.sp_query_store_set_hints
     @query_id    = 4217,
     @query_hints = N'OPTION (RECOMPILE)';

-- Approximate P99 instead of sorting every row
SELECT APPROX_PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_ms) AS p99_ms
FROM   dbo.RequestLog;

09The workflow that actually works

Index tuning goes wrong when it starts from the slowest query someone remembers. Start from where the server spends its time. In Query Store, rank by total CPU and total duration, not by average.

SELECT TOP (20)
        q.query_id,
        SUM(rs.count_executions)                              AS executions,
        SUM(rs.avg_cpu_time  * rs.count_executions) / 1000.0  AS total_cpu_ms,
        SUM(rs.avg_duration * rs.count_executions) / 1000.0  AS total_duration_ms,
        MAX(qt.query_sql_text)                                AS query_text
FROM    sys.query_store_runtime_stats          AS rs
JOIN    sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN    sys.query_store_plan       AS p  ON p.plan_id = rs.plan_id
JOIN    sys.query_store_query      AS q  ON q.query_id = p.query_id
JOIN    sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE   rsi.start_time >= DATEADD(DAY, -7, SYSUTCDATETIME())
GROUP BY q.query_id
ORDER BY total_cpu_ms DESC;

For the top offenders, open the actual execution plan and look for three signatures: a scan where you expected a seek, CONVERT_IMPLICIT on a column, and a fat arrow where the estimate says 1 row and the actual count says 100,000. Then work in a loop.

10Governance for architects

Unused indexes are not a tuning failure. They are a governance failure: an index is easy to add, nobody owns its removal, and its cost is spread so thinly across every write that no single incident points at it. A few standing rules change that.

• Every index has a reason on record. The query or Query Store query_id it was created for, and an owner, kept in an extended property or the change ticket.
• Hot OLTP tables have an index budget. Set a ceiling per table, for example five nonclustered indexes, and require a measured justification to exceed it.
• The ledger is reviewed monthly. DMV snapshots are persisted to a table so restarts do not erase evidence, and the review spans month-end.
• Removal is staged. Disable, observe through a full business cycle, then drop.
• Compatibility level changes are projects. Baseline, switch, compare, force where needed, fix the cause, then unforce.
• Query Store health is monitored. Alert when actual_state_desc differs from desired_state_desc, because a Query Store that silently flipped to read-only disables the 2022 feedback loop.

11The lesson

The explainer taught me what an index is. Production taught me what an index costs.

An index is a contract with the optimizer. It says this predicate is selective, this column is sargable, and this key order matches the query. Break any clause of that contract and you still pay the maintenance. You simply get nothing back.

So the question worth asking about your own database is not which index is biggest. It is which index charges you writes every second and has never earned a single read.

That one is not an asset. It is a subscription.