SQL Server index tuning: choosing the right index

In short

Start with the task and an actual execution plan. Look at filters, joins, sort order and how much data the query reads. That gives you a candidate for the key columns and, where needed, for included columns (INCLUDE). Then judge the candidate not only by the query it was meant for. Measure every query that uses the same table, and the data modifications too. A new index has to prove itself across the whole workload.

Which query should get faster?

Our example is an orders table with just over 2 million rows, about 700 MB. Ten large customers have 100,000 orders each, 20,000 small customers have between 1 and 99. This kind of skew is normal in many applications: a few key accounts, tenants or branches hold a large share of the data.

The application shows a customer's orders for one month:

SELECT COUNT(*) AS OrderCount, MAX(OrderId) AS LastOrderFROM dbo.OrdersWHERE CustomerId = @c  AND OrderDate >= @from AND OrderDate < @to;

The table has nothing but its clustered primary key on OrderId. We will call this query query A. On SQL Server 2022 at compatibility level 150 it takes about 70 ms, for small and large customers alike.

What the execution plan tells you about the access

The plan shows a parallel Clustered Index Scan with 87,661 logical reads per call. A small customer has at most a handful of orders in a month, a large one about 2,700. To find those few rows, the query reads every page of the table, every time.

That ratio is the real signal: pages read versus rows returned. SET STATISTICS IO ON or the actual plan shows the pages read; the actual plan also shows the actual number of rows. A scan is not bad in itself. It becomes a problem when it reads the whole table for a small result and the query runs often.

Before you create an index, rule out two other causes:

• Estimation errors. If estimated and actual rows are far apart, the optimizer may consider an existing index unsuitable. Update the statistics or rewrite the query first; do not add an index yet.
• Non-sargable predicates. A function on the column, such as YEAR(OrderDate) = 2026, or a mismatched data type can make a suitable index unusable. Then the fix belongs in the query, not in an index.

How to read plans and judge estimates is covered in our introduction to the optimizer.

Deriving the index key from the access pattern

A nonclustered index is a separate, sorted structure. The key columns determine the order in which SQL Server can search it. Derive them from what the query does: which columns are compared with =, which with a range, and which are sorted or joined?

Look at equality, range and sort order together

Query A compares CustomerId for equality and OrderDate against a range. That gives us the candidate:

CREATE INDEX IX_Customer_Date ON dbo.Orders (CustomerId, OrderDate);

With this order, SQL Server seeks straight to the customer and reads only that month. The other way round, with OrderDate first, it would have to read the whole month for all customers and filter for the customer there. "Equality before range" is a good place to start, but not a fixed rule. Sorting, joins and other queries on the same table can point to a different order. "Most selective column first" is likewise a sensible starting point among several equality columns, but not a law.

With the candidate in place, query A took 0.2 to 0.6 ms in the same run instead of about 70 ms, with 3 to 10 logical reads instead of 87,661. Across all versions and compatibility levels it ran in 0 to 2.3 ms with 3 to 13 reads.

Which columns belong in INCLUDE

Query A returns COUNT(*) and MAX(OrderId). OrderId is not in any index key, and yet SQL Server does not have to look anything up in the table: the clustered key is part of every nonclustered index, because it points to the row. On a table without a clustered index, a heap, a row locator takes its place. The candidate therefore covers query A completely.

If a query needs further columns that are not in the index, SQL Server fetches them from the table row by row. In the plan, that is a Key Lookup. Columns that are only returned or aggregated can be added to the leaf level of the index with INCLUDE. They make it wider without changing its order. That saves lookups, but costs space and write work. How much, the example further down shows.

When a filtered index is an option

For a small, clearly defined subset, such as open orders only, a filtered index (WHERE Status = 1) can be much smaller and cheaper to maintain. It comes with a restriction that is easy to miss: the query's predicate has to fall within the index filter. For a parameterized query, the cached plan has to be valid for every possible parameter value. If the parameter can also take values outside the filter, the optimizer will usually not use the filtered index, unless you add OPTION (RECOMPILE). In addition, changes to a table with a filtered index require certain SET options, such as QUOTED_IDENTIFIER ON. Older applications with different settings can then fail when they write. So test a filtered index with the application's actual call. We did not measure this case in the lab.

Checking existing indexes and missing index suggestions

Before a new index is created, look at the existing ones. Often there is already an index with the same leading column that can be extended, instead of adding a second, almost identical one. sys.dm_db_index_usage_stats shows how often an index was read and modified. The counters start from zero when the instance restarts, and also when the database is detached or closed. An index only appears there after it has been used for the first time. A rarely run month-end job can therefore be missing.

Missing index suggestions in the plan and in sys.dm_db_missing_index_details are a good starting point, but not a build order. Microsoft documents these limitations itself:

• They come from optimizing a single query and are based on estimates, not on measured runtime.
• They do not specify an order for the key columns. They never suggest filtered or unique indexes.
• The suggestions collected for a table disappear as soon as its definition changes, including after a CREATE INDEX, and when the instance restarts.
• Similar suggestions often overlap and should be merged.

The most important point is missing from that list: a suggestion cannot see the other queries on the table. It tells you what would help this one query. It does not tell you what the same index does to all the others. How we close that gap in our own analyses is described further down.

An index is not a local object

You may create an index for a single query and measure a clear improvement there. From that moment on, though, it is available to every query that uses the same table. The optimizer considers it as an alternative at every new compilation. For some queries that is a gain. Others get a plan that is only good for certain parameter values and costs more than before for most of their calls. Every additional index adds more of these decisions. That is why we never check an index change only against the query it was meant for.

This is easy to overlook, because the usual tools look at one query at a time: the missing index suggestion, the individual execution plan, the before-and-after test of the target query. So in the lab we measured it on a second query that had nothing to do with the new index.

Every query on the table sees the new index

Alongside query A, the application runs a revenue summary per customer, query B:

SELECT SUM(Amount) AS Total, COUNT(*) AS OrderCountFROM dbo.OrdersWHERE CustomerId = @c;

Without an additional index, B has exactly one way to run: a scan of the whole table. Not fast, but stable. Each call costs about 88,000 logical reads, no matter which customer.

After CREATE INDEX IX_Customer_Date for query A, B had a second way. It could find the customer's rows through the new index and fetch Amount row by row from the table with a Key Lookup. It did exactly that, immediately, in every run.

The switch happens immediately

Creating an index changes the table's definition. Cached plans that use the table are recompiled on their next execution. Microsoft lists changes to a referenced table as a reason for recompilation. In the lab, the sql_statement_recompile event reported Schema changed for query B after the index was created, on SQL Server 2019, 2022 and 2025. The very next execution already used the new index with a Key Lookup.

So the effect does not build up gradually. It shows up right after the deployment, and it hits queries nobody tested, because nobody connected them with the change.

New plan choices mean new tipping points

For a small customer with 50 orders, the path through the index is ideal: 50 lookups instead of 88,000 pages. For a large customer with 100,000 orders, it means 100,000 lookups, and those take longer than the scan. Somewhere in between lies a tipping point: the number of rows at which the scan becomes cheaper than a seek plus lookups.

Which plan ends up in the cache is decided by the first call after compilation. All later calls reuse it until it is recompiled again, for example after a restart, a statistics update, or when the plan has been evicted from the plan cache. Each time, the first call decides again. In the lab, after creating the index, we compiled B once with a small customer first and, in a second pass, with a large customer first, and then measured 100 calls each time: 80 for large and 20 for small customers.

-- query B, unchanged in every tabSELECT SUM(Amount) AS Total, COUNT(*) AS OrderCountFROM dbo.OrdersWHERE CustomerId = @c;-- plan for every customer: Clustered Index Scan

CREATE INDEX IX_Customer_Date    ON dbo.Orders (CustomerId, OrderDate); -- first call of B after the index: small customerEXEC sp_executesql @B, N'@c int', @c = 11;  -- seek + lookup, stays in cache-- every later call reuses this plan, large customers included

CREATE INDEX IX_Customer_Date    ON dbo.Orders (CustomerId, OrderDate); -- first call of B after the index: large customerEXEC sp_executesql @B, N'@c int', @c = 1;   -- scan, stays in cache-- every later call reuses this plan, small customers included

CREATE INDEX IX_Customer_Date_Amount    ON dbo.Orders (CustomerId, OrderDate)    INCLUDE (Amount);                        -- no more lookups -- plan for every customer, in either order: Index Seek

Across all versions up to compatibility level 150, the picture was the same:

• Small customer first: large customers read 3.6 times as many pages and ran 2.4 to 3.3 times as long as without the index. CPU time was actually slightly lower than for the parallel scan, but the lookup plan ran serially and read far more pages. The total over all 100 calls rose to 2.0 to 2.8 times the original. An index that took query A from 70 ms to under 1 ms at least doubled the cost of query B at the same moment.
• Large customer first: B stayed with the scan, for the small customers too. That was no worse than without the index, but small customers never got any benefit from it. If B is called mostly for small customers, this plan is the wrong one for most calls. Calculated for 95% small calls, the average would be 61 to 77 ms instead of 11 to 13 ms in the other cache state.

Which of the two happens does not depend on the index. It depends on which parameter value the plan is compiled for (parameter sniffing): which user clicks first after the deployment, and after every later recompilation. Without the index, B had no tipping point. The index for A created it. How to recognize such parameter-sensitive plans, the classic parameter sniffing problem, is covered in the guide Sometimes fast, sometimes slow.

New statistics, different estimates

Every index comes with a statistics object on its key columns, built from all rows when the index is created. The optimizer uses it for queries that never read the index, too. In the lab, a third query read only the clustered index through a hint. We made visible which statistics the optimizer loads with the undocumented trace flag 2363, which belongs in test environments only. Before the new index, it used the histogram of the auto-created, sampled statistics for CustomerId; afterwards, in every run, the histogram of the index statistics.

Whether the estimate changes noticeably depends on the state of both statistics. In a pilot run with fresh, full-scan index statistics, their histogram gave 34 to 50 rows for four small customers, the sampled statistics 102 to 118; the actual numbers were 10 to 52. Neither was consistently more accurate, but the estimate rested on a different basis. Once enough rows had been modified, the index statistics were also updated automatically, this time from a sample. After that, both estimated almost the same. So even queries that never use an index can feel it through its statistics.

Readers and writers taking different paths

A lookup plan reads the new index first and then the table. A modification often goes the other way: table first, then the index. When both run at the same time, a well-known deadlock pattern can emerge. We did not measure this in the lab. It is one more reason to watch for deadlocks after an index change. A covering index like the one in the next section removes the lookup and with it the basis for this pattern. When blocking only waits and when it turns into a deadlock is explained in Blocking and timeouts.

When an index stabilizes plans

The fourth tab above shows the opposite direction. With INCLUDE (Amount), B no longer needs a lookup. The seek is the best path for small and large customers alike, and the tipping point disappears. B ran stably in either order and on every version, at about a quarter of the cost without the index.

That does not mean "always include every column". Here, exactly one column was missing. With wide result sets the index grows accordingly, and even with INCLUDE a seek is not guaranteed. In the lab, the price showed up on the write side:

Every change to Amount now has to maintain the index as well: three times as many log records and several times the runtime. Inserts also write extra log records per row for either index. We saw these costs but did not measure them reliably enough to give a number. Whether it pays off depends on how often Amount changes and how often B is read. That trade-off is part of every index decision. An index is good when it makes the whole workload better, not one query.

Measuring the gain and the side effects across the workload

For the decision you need the same numbers as in the lab, but from your own system: every query on the table, before and after, weighted by how often it runs. Query Store provides this if it is enabled for the database.

Look for the affected queries in the plan, not in the query text. If a query reaches the table through a view, a function or a synonym, the table name is not in its text, but it is in its plan. Query 1 lists every query in Query Store whose plan uses the table, and flags plans that are new since the index change:

/* PSG · Index tuning · Query 1   Which queries use this table, and which of them have a new plan since an index change?   Read-only. Reads the Query Store of the current database.   Searches the plans rather than the query text, so access through views, procedures and functions counts.   On very large Query Stores, searching the plans can take a while.   Permission: VIEW DATABASE STATE, from SQL Server 2022 VIEW DATABASE PERFORMANCE STATE.   Times in UTC. Averages include aborted executions (timeouts).   The search checks schema and table, not the database: tables with the same name in other   databases that are read through three-part names are counted as well.   MAXDOP 1: the query runs serially and does not take further cores away from the application. */DECLARE @schema sysname        = N'dbo',        @table  sysname        = N'Orders',        @since  datetimeoffset = DATEADD(DAY, -1, SYSDATETIMEOFFSET());  -- index change, e.g. '2026-11-05 14:00 +01:00' DECLARE @pattern nvarchar(600) = N'Schema="' + QUOTENAME(@schema) + N'" Table="' + QUOTENAME(@table) + N'"'; WITH plans AS (    SELECT p.query_id, p.plan_id, p.initial_compile_start_time, p.last_execution_time, p.is_forced_plan    FROM sys.query_store_plan AS p    WHERE CHARINDEX(@pattern, p.query_plan) > 0),runtime AS (    SELECT rs.plan_id,           SUM(rs.count_executions) AS executions,           SUM(rs.avg_duration * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS avg_ms,           SUM(rs.avg_logical_io_reads * rs.count_executions) / NULLIF(SUM(rs.count_executions), 0) AS avg_reads    FROM sys.query_store_runtime_stats AS rs    WHERE rs.plan_id IN (SELECT plan_id FROM plans)    GROUP BY rs.plan_id)SELECT pl.query_id,       pl.plan_id,       CASE WHEN pl.initial_compile_start_time >= @since THEN N'new plan' ELSE N'' END AS since_change,       pl.is_forced_plan,       CAST(pl.initial_compile_start_time AS datetime2(0)) AS first_plan,       CAST(pl.last_execution_time AS datetime2(0))        AS last_run,       r.executions,       CAST(r.avg_ms AS decimal(12,2))    AS avg_ms,       CAST(r.avg_reads AS decimal(14,0)) AS avg_reads,       OBJECT_NAME(q.object_id) AS object_name,   -- procedure or trigger; NULL for ad hoc, view and inline function       LEFT(REPLACE(REPLACE(qt.query_sql_text, CHAR(13), N' '), CHAR(10), N' '), 120) AS query_textFROM plans AS plJOIN sys.query_store_query      AS q  ON q.query_id = pl.query_idJOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_idLEFT JOIN runtime               AS r  ON r.plan_id = pl.plan_idORDER BY pl.query_id, pl.initial_compile_start_timeOPTION (MAXDOP 1);

In a test run, we queried the same table directly, through a view, from a procedure and through an inline table-valued function, then created an index and repeated everything. This is what the result looked like on SQL Server 2022, with a few columns left out:

query_id plan_id since_change executions avg_ms avg_reads object_name query_text
3        4                    5          14.84  2870      NULL        (@c int)SELECT SUM(Amount) … FROM dbo.Orders WHERE CustomerId = @c
3        11      new plan     5          .15    62        NULL        (@c int)SELECT SUM(Amount) … FROM dbo.Orders WHERE CustomerId = @c
4        5                    5          14.76  2870      NULL        (@c int)SELECT SUM(Amount) … FROM dbo.v_Orders WHERE CustomerId = @c
4        12      new plan     5          .13    62        NULL        (@c int)SELECT SUM(Amount) … FROM dbo.v_Orders WHERE CustomerId = @c
5        6                    5          16.29  2870      p_Revenue   (@c int)SELECT CustomerId, SUM(Amount) … GROUP BY CustomerId
5        13      new plan     5          .12    62        p_Revenue   (@c int)SELECT CustomerId, SUM(Amount) … GROUP BY CustomerId
6        7                    5          14.71  2870      NULL        (@c int)SELECT SUM(Amount) … FROM dbo.f_Orders(@c)
6        14      new plan     5          .12    62        NULL        (@c int)SELECT SUM(Amount) … FROM dbo.f_Orders(@c)
9        10      new plan     1          118.81 4661      NULL        CREATE INDEX IX_Customer_Date ON dbo.Orders (CustomerId, OrderDate)

The query ran without errors and with the same result on SQL Server 2017, 2019, 2022 and 2025. A query that only reads a different table did not appear.

How to read the result:

• A query marked "new plan" got a plan shape after the given time that did not exist before. That can be the index change, but also a statistics update or an entirely new query. Compare executions, average duration and logical reads of its old and new plans.
• Several plans for the same query after the change point to a tipping point like the one in query B. Check which parameter values produced which plan.
• From SQL Server 2022 at compatibility level 160, a query can appear as several plan variants, each with its own query ID. That is Parameter Sensitive Plan Optimization, covered in the next section. sys.query_store_query_variant shows which variants belong together.
• The object_name column names the procedure or trigger a query comes from. SQL Server expands views and inline functions into the calling query, which then appears with its own text, in the example with dbo.v_Orders and dbo.f_Orders.
• The CREATE INDEX itself shows up too, because its plan reads the table. You can skip that row.
• If the plan shape stays the same, Query Store shows no new plan, even though the query was recompiled. That only means the first call took the same path this time. As query B in the "large first" case shows, the tipping point can still be there and tip at the next recompilation. For queries with skewed parameters, test with a small and a large value.

Query 1 only sees what Query Store has captured. In the default AUTO capture mode, rare and very cheap queries can be missing, and older data is removed after the configured retention period. It shows times in UTC. For a clean comparison, record the exact time of the change, let a representative time window run before and after, and compare windows of equal length. A weekly close that only runs on Fridays needs a window that contains a Friday.

The whole workload in view: PSG QX

Query 1 answers the question for one table and one point in time. In real operations, it is rarely just one index and one table that change. The same deployment brings new code, updated statistics and sometimes a new version. The real questions then are: does this table need the index at all? And what has changed since then for which query, and what is the evidence for it?

For both questions we use PSG QX in our analyses. QX analyzes the existing execution history in Query Store, locally and without any additional recording. QX does not create indexes itself; it supplies evidence and narrows down the candidates, and we make the decision together with you.

Before the change: think from the table's point of view. The access profile of a table in QX turns the usual perspective around. Instead of one query and its plan, it shows every query that uses the table, grouped by access pattern: equality search, equality plus range, access without any search predicate. Each pattern is weighted by the CPU time of the queries involved and backed by the index and plan accesses that actually appear in the plan. Alongside that, QX compares the roles the columns play in filters, joins and output with the existing indexes and the clustered key, and consolidates overlapping missing index suggestions. That way you see, before you create anything, whether an existing index already serves the patterns, whether it can be extended, or whether a new index really closes a gap. For the example in this article, that is exactly the question: A and B both filter on CustomerId, and B also needs Amount. An index designed with only A in mind misses that.

After the change: compare before and after. QX compares the period before and after a change across the whole workload: new queries, new and regressed plans, queries that run more often or become more expensive. A query with several plans, like query B, stands out as such, together with the line in the procedure's source code where those plans originated. Every comparison is classified as comparable, limited or not possible, each with a reason. That keeps a short time window after a deployment from turning into premature evidence, in either direction.

Re-check your index design with every new version

An index design is not fixed in time either. With every new SQL Server version and every higher compatibility level, the optimizer evaluates your indexes differently. It estimates differently, uses new execution techniques and therefore picks different plans. An index that used to be essential may afterwards cost nothing but write work. Another one is suddenly chosen by queries it was never meant for. We know both cases from practice; we did not reproduce them in the lab.

What the lab did show is something different and just as important, using exactly the same case:

From compatibility level 160, Parameter Sensitive Plan Optimization recognized the skewed distribution of customers and kept a separate plan variant for small and for large customers. The regression from the previous section no longer occurred. The same index was a risk for query B at compatibility level 150 and an improvement at 160.

Do not rely on it, though. The feature only applies to certain equality predicates with skewed values, and not to every form of query. In a preliminary run in which query B returned its result through output parameters, it created no variants. Whether it applies to your queries, only measurement will tell.

Two things help with planning:

• The trigger is usually the compatibility level, not the installed version alone. Many optimizer changes are tied to the database's compatibility level. A database keeps its previous level on upgrade or restore; a newly created one gets the version's default, 160 on SQL Server 2022. If you stay at the old level after the upgrade, you only postpone the check to the day you raise it. Some optimizer fixes also arrive with cumulative updates and only take effect through a separate opt-in setting, the query optimizer hotfixes option. Microsoft recommends upgrading at the existing level, collecting a baseline in Query Store and only then raising the level (Query Store usage scenarios). That is also the right moment to review the indexes. In our projects, PSG QX does the before-and-after comparison when the level is raised.
• "Re-check" does not mean "redesign". It means an orderly review after a representative period: which queries have different plans, which indexes are no longer used or used differently, which new missing index suggestions appear, and which indexes now cost nothing but write work? Then change one index at a time, each with a rollback path.

What else a version upgrade changes is covered in our upgrade decision guide.

Choosing a candidate and introducing it traceably

The result should be a decision you can justify. This table sums up the questions:

Introduce an index change on its own, not together with other changes. Otherwise a regression cannot be attributed. The way back is an index change too: dropping the index triggers recompilations again. Replacing an existing index is a separate change: check beforehand whether it enforces uniqueness, whether queries reference it by name in a hint, and whether forced plans in Query Store depend on it. A hint naming a dropped index raises an error; a forced plan that depends on it can no longer be applied, and the query is compiled normally instead.

Forcing a plan in Query Store can serve as a stopgap when a query regresses after the change. It does not replace the decision whether the index stays.

How index selection and index maintenance fit together

This article covers the question of which index makes sense. How an index is maintained afterwards, with fill factor, reorganize and rebuild, is a separate task. A rebuild rebuilds the existing definition. It does not turn an unsuitable key into a suitable one. Conversely, every new index needs a maintenance decision that fits its change pattern. Our series Index maintenance explained covers that part.

Frequently asked questions

Should I create every suggested missing index? No. The suggestions are based on estimates for individual queries. Check existing and overlapping indexes, the effect on the other queries on the table, and the additional write costs.

Why is another query slower after a new index? Because the index does not belong only to the query it was created for. Queries on the table are recompiled on their next execution and can use it. That can produce a plan that only suits some of the parameter values. Query 1 shows which queries are affected.

Is an index seek always faster than a scan? No. A seek with 100,000 lookups took 2.4 to 3.3 times as long as a scan of the whole table in the lab. What matters is the amount of data read and the complete plan, not the name of the operator.

Does an index rebuild help with an unsuitable index key? No. A rebuild rebuilds the existing definition. A different key order or different included columns do not come out of it.

Do I have to redesign all indexes after an upgrade? No, but you should review them. After raising the compatibility level, a comparison in Query Store shows which queries have different plans. That tells you which indexes to change, drop or keep.

Sources

• Microsoft: SQL Server index architecture and design guide
• Microsoft: Create indexes with included columns
• Microsoft: Create filtered indexes
• Microsoft: Tune nonclustered indexes with missing index suggestions
• Microsoft: Parameter Sensitive Plan Optimization
• Microsoft: Query Store usage scenarios
• Microsoft: ALTER INDEX, section on disabling indexes
• Microsoft: Change the database compatibility level and use the Query Store
• Microsoft: Query processing architecture guide, section on recompiling execution plans
• Microsoft: sys.query_store_plan
• Microsoft: sys.dm_db_index_usage_stats

Read on: Index maintenance explained · Sometimes fast, sometimes slow · SQL Server performance optimization