Reference · Understanding the optimizer · Chapter 1

One query, many plans.

Does the same procedure sometimes run in milliseconds and sometimes painfully slowly? Often the hardware is not the cause. What matters is which execution plan the optimizer chose, and which one happens to be in the cache. Try it yourself, with real plans from our test lab.

Last updated 6 Oct 2026Maturity outlineChapter 1 of 7 ↓

First: the model

Estimate first, then execute.

The optimizer decides on the plan before the query runs. At that moment it only has estimates: how many rows each step will return and what the possible routes would cost. What the query actually needs only shows during execution.

At compile time · estimated

Statistics and parameter valuewhat SQL Server knows about the data
Estimated rowsfor each step of the plan
Estimated costfor the possible routes
Chosen planthe cheapest route found

Plan cachethe plan stays for later calls

On every execution · measured

Current parameter valueoften a very different customer
Rows, reads, CPU, durationonly now actually known

Plan choice rests on estimates at compile time. Measurement only happens during execution, and a cached plan often runs for many more values.

Experiment 1 shows the left part: for different customers, the optimizer estimates different volumes and, beyond a certain volume, chooses a different plan.

Experiment 2 shows the right part: a cached plan also runs for customers it was never chosen for.

Experiment 1

Beyond a certain volume, the optimizer takes a different path.

Our test lab holds 1,510,967 orders with 4,535,871 order lines from 50,000 customers. The distribution is deliberately skewed, as in real life: some customers have a single order, one has 500,000. The procedure calculates the revenue per order for one customer. There is an index on the customer ID.

dbo.KundenUmsatz @CustomerID

SELECT o.OrderID, o.OrderDate, SUM(l.Amount) AS Umsatz
FROM dbo.Orders o
JOIN dbo.OrderLines l ON l.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID
GROUP BY o.OrderID, o.OrderDate;

For the curious: tables, indexes and data distribution

Test lab schema (compatibility level 170)

CREATE TABLE dbo.Customers (
  CustomerID int           NOT NULL PRIMARY KEY,
  Name       nvarchar(100) NOT NULL,
  Region     char(2)       NOT NULL);

CREATE TABLE dbo.Orders (
  OrderID    int IDENTITY  NOT NULL PRIMARY KEY CLUSTERED,
  CustomerID int           NOT NULL,
  OrderDate  date          NOT NULL,
  Status     tinyint       NOT NULL,
  Total      decimal(12,2) NOT NULL,
  Filler     char(200)     NOT NULL DEFAULT 'x');

CREATE TABLE dbo.OrderLines (
  OrderID    int           NOT NULL,
  [LineNo]   tinyint       NOT NULL,
  ProductID  int           NOT NULL,
  Qty        int           NOT NULL,
  Amount     decimal(12,2) NOT NULL,
  PRIMARY KEY CLUSTERED (OrderID, [LineNo]));

CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);

50,000 customers
1,510,967 orders, including seven step customers with 1, 10, 100, 1,000, 10,000, 100,000, 500,000 orders; all others have 1 to about 40
4,535,871 order lines, 1 to 5 per order
Statistics updated with FULLSCAN; the index on CustomerID deliberately does not cover the query, hence the key lookup

Customer with … orders

Move the slider. Each step shows the plan SQL Server compiles for exactly this customer.

Actual execution plan all operators as in SSMS…

A plan can be read in either direction. Read from right to left, you follow the data: it is read on the right, and the result appears on the left. Read from left to right, you follow the execution: each operator requests its rows from its right-hand neighbor. The arrow thickness shows how many rows flow.

Estimated rows: –
Logical reads: –
CPU: –
Duration: –

Experiment 2

And then one plan stays in the cache.

SQL Server compiles a procedure on its first call with whatever value is passed at that moment. This is called parameter sniffing. All later calls reuse that plan as long as it stays in the cache: plan reuse. The first customer thus shapes the strategy for everyone who comes after.

Background

Why the optimizer works this way

The SQL Server optimizer is cost-based. It estimates how many rows each step returns, weighs the plan alternatives by estimated cost and takes the cheapest one it finds in reasonable time. The estimates are based on statistics about the distribution of the data and, for parameters, on the value at compile time.

For a few rows, targeted access through an index is usually cheapest. For very many rows, it is cheaper to read the whole table once and join it with a hash. Neither plan is wrong. It only gets expensive when a plan is reused for data volumes it was not chosen for.

A side observation: For the customer with 10,000 orders, the optimizer chose a parallel plan. In our measurement it was not faster than the reused plan of the smallest customer (288 ms versus 242 ms), but it needed many times the CPU (1.1 s versus 242 ms). The optimizer compares estimated costs, not measured run times. Even a plan compiled for the value is only the one with the lowest estimated cost, not necessarily the fastest.

What helps, and what it costs

Approach	Effect	Price
`OPTION (RECOMPILE)`	a new plan choice with the current value on every call	A compilation on every call; for frequently executed queries this costs considerable CPU, so there has to be budget for it
`OPTIMIZE FOR`	one deliberately chosen plan for all values	A compromise; it fits some of the values less well
Dynamic SQL per query shape	separate, reusable plans per shape	Maintenance effort; much lower with our open-source project T-Lift
Parameter Sensitive Plan Optimization	from SQL Server 2022, separate plan variants for different value ranges	applies only under certain conditions, see below
Query Store: force a plan or set a hint	targeted correction without a code change	has to be monitored when the data or the version changes

And Parameter Sensitive Plan optimization?

Since SQL Server 2022 (compatibility level 160), SQL Server can keep several plan variants for different value ranges of a query with unevenly distributed parameter values. That targets exactly the problem from experiment 2.

With PSP optimization enabled, our test lab created a dispatcher plan and 3 variants for the procedure:

Variant 1 for customers with one order: Nested Loops, serial
Variant 2 for customers with 10 to 10,000 orders: Nested Loops, serial
Variant 3 for customers with 100,000 to 500,000 orders: Hash Match, parallel

That defuses experiment 2 considerably, but not completely: customers with 10 and with 10,000 orders share one variant. Which variant applies is decided by the estimate, not the actual row count. SQL Server estimates the customers with 10, 100 and 1,000 orders all at 370 rows here, because they fall into the same step of the statistics histogram. That is exactly what chapter 2 is about.

How we find cases like this

The SQL Server Query Store records which plans a query had over time and how expensive each one was. PSG QX analyzes exactly this history: which query had several plans, which one was more expensive, and since when. If Query Store is not yet enabled, we turn it on at the start and let it fill.

Part of our SQL Server performance guide: compare plan and parameters when the same query is sometimes fast and sometimes slow →

Measurements

All numbers from the experiment

Logical reads and duration per customer, with the plan compiled for its value and with a plan reused from the cache. The dashed line marks where the plan compiled for the value changes.

Customer's orders	Compiled for this value	With own plan	Smallest customer's plan	Largest customer's plan
1	Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek	9 < 1 ms	9 < 1 ms	54,094 1.8 s
10	Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek	53 < 1 ms	63 < 1 ms	47,424 915 ms
100	Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek	574 2 ms	604 2 ms	58,224 2.9 s
1,000	Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek	5,879 43 ms	6,021 41 ms	51,677 6.6 s
10,000	Plan B Hash Match · Index Seek · Key Lookup · Clustered Index Scan · parallel	46,475 288 ms	60,165 242 ms	46,570 794 ms
100,000	Plan C Hash Match · Clustered Index Scan · parallel	61,686 626 ms	601,649 1.4 s	60,782 2.7 s
500,000	Plan D Merge Join · Clustered Index Scan	60,782 4.0 s	3,008,513 6.0 s	60,782 3.7 s

Reference

Understanding the optimizer: the chapters

This topic is growing. Chapters that are still in progress are already listed here, so you can see where it is heading.

01
One query, many plansThis chapter
02
How the optimizer estimatesCardinality, statistics, histograms · in progress
03
Parameter sniffing in detailRecognizing it, and the remedies compared · in progress
04
Plan cache and plan reuseWhy a restart seems to help · in progress
05
Compatibility level and the cardinality estimatorWhat changes on upgrade, see SQL Server versions · in progress
06
Newer optimizer featuresIntelligent Query Processing from SQL Server 2022 · in progress
07
Making plans visibleQuery Store and PSG QX · in progress

Maintenance

Method and change log

Measured on 29 Sep 2026 on Microsoft SQL Server 2025 (RTM-CU7) (KB5096981) - 17.0.4065.4 (X64) in a container with 8 CPUs and 6,144 MB of memory, compatibility level 170, MAXDOP 0. Warm cache, duration and CPU as the median of ten runs (two rounds of five runs, each after an uncounted compile run); with the same plan, times vary by up to a factor of 2, and values below one millisecond are orders of magnitude only. Duration includes sending the result rows to the client. For experiments 1 and 2, Parameter Sensitive Plan optimization was turned off so the classic behavior is visible.

Your numbers will differ: different hardware, different data. The mechanism stays; how strongly it shows and where the plan changes depends on your data. We are happy to share the scripts to reproduce this on request.

Change log

6 Oct 2026Added the model before experiment 1 (estimate first, then execute); sharpened terms: “compiled for each customer” instead of “matching plan”, parameter sniffing and plan reuse separated; measurement method described more precisely.
29 Sep 2026Chapter 1 created: two experiments with real plans from SQL Server 2025 CU7, measurements, a look at the remedies and the result for Parameter Sensitive Plan optimization.

Back to Knowledge