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 29 Sep 2026Maturity outlineChapter 1 of 7 ↓

Experiment 1

The optimizer takes a different path for each value.

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 is a separate call of the procedure, freshly compiled each time.

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, and then reuses that plan for all later calls. Which customer comes first therefore decides for everyone else. This is called parameter sniffing.

Background

Why the optimizer works this way

The SQL Server optimizer is cost-based. It estimates how many rows each step returns and picks, from many possible plans, the one with the lowest estimated cost. 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 unbeatable. For very many rows, it is cheaper to read the whole table once and join it with a hash. Neither plan is wrong. A plan becomes wrong only when it is used for data it was not built 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 the “matching” plan is only the one with the lowest estimated cost.

What helps, and what it costs

Approach	Effect	Price
`OPTION (RECOMPILE)`	a matching plan 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, up to three plan variants per query	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 up to three plan variants for 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.

Measurements

All numbers from the experiment

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

Customer's orders	Matching plan	Matching	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; 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 shape of the effect stays the same. We are happy to share the scripts to reproduce this on request.

Change log

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