PL/SQL & T-SQL (Oracle and SQL Server) Module 7 – Execution plans and performance in SQL Server
Parameter sniffing, PSP and Query Store
See why one cached plan fits only some parameter values, reproduce parameter sniffing on skewed data, and fix it with PSP, hints and Query Store.
What you will learn
- Explain parameter sniffing and reproduce it on skewed data with a model and a script
- Apply a fix for a sniffed plan from OPTION (RECOMPILE), OPTIMIZE FOR, Query Store hints and plan forcing
- Describe parameter sensitive plan optimization and optional parameter plan optimization
- Find regressed queries in Query Store and compare their plans by logical reads
Before you start
What you’ll learn · Before you start
What you will learn
- Explain parameter sniffing and reproduce it on skewed data with a model and a script
- Apply a fix for a sniffed plan from OPTION (RECOMPILE), OPTIMIZE FOR, Query Store hints and plan forcing
- Describe parameter sensitive plan optimization and optional parameter plan optimization
- Find regressed queries in Query Store and compare their plans by logical reads
Before you start
On this page
In the page-count model of this lesson, one procedure reads 1,695 pages for the 99,000 ‘shipped’ orders of a 100,000-row table when it gets the plan built for them, and 297,418 pages when it reuses the plan built for the 10 ‘new’ orders: about 175 times as many. The plan, not the data, made the call expensive.
This is parameter sniffing: SQL Server reads the parameter values of the first call while it compiles a procedure or parameterized query, so the cached plan fits those values and may fit others poorly.: SQL Server reads the parameter values of the first call while it compiles, and the plan cache keeps that plan for every later call. parameter sensitive plan optimization: PSP optimization, from compatibility level 160: one parameterized statement can keep a dispatcher plan and several query variants, each with its own plan, chosen by the rows a value matches. optimization lets one statement keep several plans, and Query Store: A feature of each database that records queries, their execution plans and runtime statistics per time interval, so plan changes can be found and compared. records each plan with its reads, so a regression can be found and fixed without editing the code.
One cached plan is built for the first value it sees
A stored procedure’s plan goes into the plan cache: The memory area where SQL Server keeps compiled execution plans, so later calls of the same procedure or statement can reuse a plan instead of compiling again. the first time it compiles. While
compiling, the optimizer reads each parameter’s value and estimates from the statistics: What SQL Server records about the values in a column or index, such as how many rows hold each range of values, so the optimizer can estimate how many rows a step of a query returns. More in “T-SQL temporary tables: #local and ##global” how
many rows match. Microsoft calls this parameter sensitivity, also known as parameter sniffing, and applies it to stored
procedures, sp_executesql and prepared queries. It usually helps. The trouble starts when values differ a lot. In the lab table status = N'new' matches 10 rows and
status = N'shipped' matches 99,000, and the first call decides the plan until it leaves the cache or is recompiled:
The drawing shows the whole scene; its 5 steps are listed below.
Every step as text
- Call 1 asks for 'new'. The application runs the procedure with the status new, 10 of the 100,000 orders. Nothing is in the plan cache for the procedure yet.
- SQL Server sniffs the value. The optimizer reads the parameter value while it compiles and estimates 10 rows. In the model a seek on the status index plus 10 key lookups is cheapest, 33 reads against 1,695 for a scan.
- The plan is cached. The compiled plan, a seek with key lookups, is stored in the plan cache. The cache keeps the plan, not the value it was compiled for.
- Call 2 asks for 'shipped'. The application runs the same procedure with the status shipped, 99,000 of the 100,000 orders. The text of the procedure is the same, so SQL Server looks for its plan in the cache.
- The cached plan is reused. The cache has a plan, so nothing is compiled. The seek plan runs for 99,000 rows with one key lookup each, 297,418 reads in the model, while a scan would need 1,695.
Key idea
A cached plan is right for the value it was compiled with and a guess for every other value.
Checkpoint
Check: One cached plan, built for the first value
Whichever call comes first decides what the second one costs
The model computes the data pages of a scan and the reads of a key lookup for the lab table from Microsoft’s index
sizing formulas, with the same functions as the
lesson on reading execution plans, and lets the optimizer pick the
plan with the fewest expected logical reads: The number of 8 KB pages a statement read from SQL Server's data cache, reported per table by SET STATISTICS IO; pages read from disk are counted separately as physical reads. More in “T-SQL cursors and set-based rewrites”. Its numbers are estimates, not measurements
of a server. Press Edit and set FIRST and SECOND to "cancelled" and "paid":
"""Parameter sniffing on dbo.orders_big: a model of the page reads of the two plans a procedure can cache.
The table is the lab table of the tsql-performance module: 100,000 rows, a clustered primary key on
order_id and an index on status. The page counts come from Microsoft's index sizing formulas (the
same functions as plan_cost.py of the lesson on reading execution plans, so both print the same
numbers). The model assumes the optimizer picks the plan with the fewest logical reads it expects;
real SQL Server also weighs CPU, parallelism and random against sequential I/O, so these are the
model's estimates, not measurements. Edit FIRST and SECOND to try other calls.
"""
import math
FIRST, SECOND = "new", "shipped" # the status values of two calls; the model runs both orders
ROWS = {"new": 10, "cancelled": 90, "paid": 900, "shipped": 99_000}
N = sum(ROWS.values())
# --- page counts from the sizing formulas (average lengths of the variable columns, full pages) ---
row_size = 20 + (2 + 3 * 2 + (14 + 6 + 80)) + (2 + (7 + 7) // 8) + 4
rows_per_page = 8096 // (row_size + 2)
leaf_pages = math.ceil(N / rows_per_page) # a clustered index scan reads every one of them
ci_rows = 8096 // (4 + 2 + 1 + 6 + 2)
ci_levels = math.ceil(1 + math.log(leaf_pages / ci_rows, ci_rows)) # levels above the leaf level
nc_row = (2 + 2 + 14) + (2 + (1 + 7) // 8) + 1 + 6 + 4
nc_rows = 8096 // (nc_row + 2)
nc_pages = math.ceil(N / nc_rows)
nc_levels = math.ceil(1 + math.log(nc_pages / nc_rows, nc_rows))
def seek_lookup(k): # seek on ix_orders_big_status, then one clustered lookup per row
return nc_levels + max(1, math.ceil(k / nc_rows)) + k * (ci_levels + 1)
def scan(k): # clustered index scan: every data page, whatever k is
return leaf_pages
PLANS = {"seek+lookup": seek_lookup, "scan": scan}
def best(k):
"""The plan with the fewest expected reads for k rows."""
return min(PLANS, key=lambda plan: PLANS[plan](k))
print(f"dbo.orders_big: {N:,} rows, {leaf_pages:,} data pages; a key lookup costs {ci_levels + 1} reads")
print(f"{'status':<10}{'rows':>7}{'seek+lookup':>13}{'scan':>7} best plan")
for name, k in ROWS.items():
print(f"{name:<10}{k:>7,}{seek_lookup(k):>13,}{scan(k):>7,} {best(k)}")
tip = next(k for k in range(1, N) if seek_lookup(k) > scan(k))
print(f"Lookups cost more than the scan from {tip} rows on.")
print()
print("Call 1 compiles for its own value; call 2 reuses that plan from the cache.")
for first, second in ((FIRST, SECOND), (SECOND, FIRST)):
cached = best(ROWS[first])
r1, r2 = PLANS[cached](ROWS[first]), PLANS[cached](ROWS[second])
ideal = PLANS[best(ROWS[second])](ROWS[second])
print(f"{first} then {second}: cached plan {cached}; reads {r1:,} then {r2:,}; call 2 needs {ideal:,} at best")Output
dbo.orders_big: 100,000 rows, 1,695 data pages; a key lookup costs 3 reads status rows seek+lookup scan best plan new 10 33 1,695 seek+lookup cancelled 90 273 1,695 seek+lookup paid 900 2,706 1,695 scan shipped 99,000 297,418 1,695 scan Lookups cost more than the scan from 564 rows on. Call 1 compiles for its own value; call 2 reuses that plan from the cache. new then shipped: cached plan seek+lookup; reads 33 then 297,418; call 2 needs 1,695 at best shipped then new: cached plan scan; reads 1,695 then 1,695; call 2 needs 33 at best
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 sniffing_model.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
Key lookups cost more than the scan from 564 rows on. In the order new, shipped, the second call reads 297,418 pages where 1,695 would do; in the reverse order it reads 1,695 where 33 would do, about 51 times as many.
Checkpoint
Check: The reads of the two plans, in both orders
Eight ways to stop one plan from serving every value
Each fix gives every value its own plan, gives all values one acceptable plan, or removes the lookups that made the plans differ:
| Fix | What it does |
|---|---|
| Covering index and statistics | A covering index holds every column the query reads, so no lookups remain; current statistics keep estimates honest. |
OPTION (RECOMPILE) |
Compiles the statement on every call with that call’s values and discards the plan afterwards. |
OPTIMIZE FOR a value |
Compiles as if the parameter held a value you choose, whatever the call brings. |
OPTIMIZE FOR UNKNOWN and local variables |
Both estimate from the column’s average density instead of a sniffed value, so every call gets one plan. |
| PSP optimization | From compatibility level 160, one dispatcher plan can send each call to a query variant chosen by the rows its value matches. |
| optional parameter plan optimization: OPPO, from compatibility level 170: a search such as column = @p OR @p IS NULL can get separate query variants for a NULL parameter and for a value. | From compatibility level 170, a search like column = @p OR @p IS NULL gets one variant for NULL and one for a value. |
| Query Store hint: A query hint attached to a query by its query_id in Query Store, so it applies without editing the code; available from SQL Server 2022. | Attach a hint such as RECOMPILE to a query by its query_id, without editing the code, from SQL Server 2022. |
| plan forcing: Pinning a plan that Query Store recorded for a query_id, so the optimizer tries to use that plan whenever the query compiles. | Pins a plan that Query Store recorded for a query_id, so the optimizer tries to use it at every compile. |
Statistics and indexes come first, since Microsoft advises completing them before any hint.
Checkpoint
Check: The eight fixes, by name
Query Store records every plan, and PSP lets one statement keep several
Query Store keeps each query, its plans and their runtime statistics per interval inside the user database. It arrived in SQL Server 2016 and is on by default for databases created on SQL Server 2022 and later. PSP targets the skew of this lesson: at compatibility level 160 the first compile of a skewed predicate can store a dispatcher plan and query variants with plans of their own. Microsoft publishes no skew values for PSP, so check the server rather than assume. The timeline shows when each tool arrived.
- SQL Server 2016
Query Store and plan forcing Query Store
Query Store arrives. It keeps queries, their plans and runtime statistics, and a plan can be forced for a query. It is off until someone turns it on.
- SQL Server 2017
Automatic plan correction automatic tuning
Automatic tuning can force the last known good plan when a plan change makes a query slower, and checks that the forced plan is better.
- SQL Server 2022
PSP, Query Store hints and Query Store on PSP
PSP optimization arrives at compatibility level 160, Query Store hints let a hint apply without a code change, and Query Store is on by default for new databases.
- SQL Server 2025
Optional parameter plan optimization OPPO
OPPO arrives at compatibility level 170 and is on by default there. It targets searches written as a column equal to a parameter or the parameter is NULL.
The timeline has 4 events, from SQL Server 2016 to SQL Server 2025.
Checkpoint
Check: Query Store and PSP, by version
Reproducing sniffing and comparing the fixes, with numbers Pro: opens with a Pro, Premium or Ultimate pass
The model estimated the reads; this part reproduces the problem on a server, works out the estimate behind local
variables and OPTIMIZE FOR UNKNOWN, and compares every fix by the reads it leaves. Run the scripts on the lab
instance and put the server’s counts beside the model’s.
In this part:
- Reproduce sniffing on a server and read its counts
- What a local variable and OPTIMIZE FOR UNKNOWN estimate, with lab numbers
- Compare every fix by its reads and its cost
5 runnable examples (Python, SQL) · about 9 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Query Store, PSP and OPPO on a server, step by step Pro: opens with a Pro, Premium or Ultimate pass
Query Store keeps the history that turns “the query got slow” into a plan and a number. This part finds a plan change in its catalog views, forces and unforces a plan, sets a hint, and reads the dispatcher plans of PSP and OPPO.
In this part:
- Find the plan change in the Query Store views
- Force a plan, read the failure columns, and unforce it
- Set a Query Store hint without touching the code
- See PSP and OPPO variants as the server reports them
1 diagram · 4 runnable examples (Python, SQL) · about 8 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Practise: place each fix before you run it
Decide which plan is cached and what it costs, then read why each answer is right or wrong:
Practice that adapts to you
Practise this lesson
11 questions about 8 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Warm-up (fresher to 2–5 years): when does SQL Server sniff a parameter?
While it compiles or recompiles a stored procedure, an sp_executesql query or a prepared query, it reads the current
parameter values to estimate rows, and the cached plan serves later calls. That helps when values look alike and hurts
when one plan cannot suit a small and a large value.
Three longer questions follow; decide your own first step before you open each one:
-
Mid to senior
An orders search procedure takes five optional filters, each written as (col = @p OR @p IS NULL). After every restart its speed depends on which filters the first caller sends. Explain why one plan struggles, then compare three fixes on SQL Server 2025 and say which you would ship.
-
Senior to staff
After a weekend that rebuilt indexes and updated statistics, a dashboard query went from about 80 ms to about 4 s. Query Store is on. Show how you find what changed, decide between forcing the earlier plan and adding a hint, and say what you watch afterwards and when you undo it.
-
Mid to senior
A colleague stops an intermittent slowdown by copying each parameter into a local variable at the top of the procedure. The runtime is now steady but slower for the most common calls. On 100,000 rows where the filter column has 4 distinct values, work out the estimate the optimizer now uses and say what you would do instead.
Flashcards · 7 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
What is parameter sniffing?
While compiling, the optimizer reads the parameter values of the first call and estimates from the statistics how many rows match; the plan cache then keeps that plan for every later call. Microsoft calls it parameter sensitivity and applies it to stored procedures, sp_executesql and prepared queries.
-
When does one cached plan become expensive?
When the values differ a lot. In the lesson's 100,000-row table status = 'new' matches 10 rows and 'shipped' 99,000, and whichever call comes first decides the plan: the model reads 297,418 pages for the big value on the small value's plan, where a scan reads 1,695.
-
Which fix for a sniffed plan comes before any hint?
Statistics and indexes: a covering index holds every column the query reads, so no key lookups remain and the plans stop differing, and current statistics keep the estimates honest. Microsoft advises completing both before any hint.
-
What do OPTION (RECOMPILE), OPTIMIZE FOR a value and OPTIMIZE FOR UNKNOWN each do?
RECOMPILE compiles the statement on every call with that call's values and discards the plan afterwards. OPTIMIZE FOR a value compiles as if the parameter held the value you choose. OPTIMIZE FOR UNKNOWN, like a local variable, estimates from the column's average density, so every call gets one plan.
-
What are PSP optimization and OPPO, and which levels do they need?
From compatibility level 160, parameter sensitive plan optimization can store a dispatcher plan that sends each call to a query variant chosen by the rows its value matches. From level 170, optional parameter plan optimization gives a search like column = @p OR @p IS NULL one variant for NULL and one for a value.
-
What does Query Store keep, and where?
Each query, its plans and their runtime statistics per interval, inside the user database. It arrived in SQL Server 2016 and is on by default for databases created on SQL Server 2022 and later.
-
Which two fixes change a query's plan without editing its code?
A Query Store hint, such as RECOMPILE, attached to a query by its query_id, from SQL Server 2022; and plan forcing, which pins a plan Query Store recorded for a query_id so the optimizer tries to use it at every compile.
Key takeaways
- Parameter sniffing compiles a plan with the first call’s values and reuses it for every later call.
- In the model one call reads 33 or 297,418 pages depending on which value came first; a scan reads 1,695.
- Eight fixes exist, and statistics and indexes come before any hint.
- PSP and OPPO let one statement keep several plans, and Query Store finds regressions by their reads.
Terms in this lesson
- optional parameter plan optimization
- OPPO, from compatibility level 170: a search such as column = @p OR @p IS NULL can get separate query variants for a NULL parameter and for a value.
- parameter sensitive plan optimization
- PSP optimization, from compatibility level 160: one parameterized statement can keep a dispatcher plan and several query variants, each with its own plan, chosen by the rows a value matches.
- parameter sniffing
- SQL Server reads the parameter values of the first call while it compiles a procedure or parameterized query, so the cached plan fits those values and may fit others poorly.
- plan cache
- The memory area where SQL Server keeps compiled execution plans, so later calls of the same procedure or statement can reuse a plan instead of compiling again.
- plan forcing
- Pinning a plan that Query Store recorded for a query_id, so the optimizer tries to use that plan whenever the query compiles.
- Query Store
- A feature of each database that records queries, their execution plans and runtime statistics per time interval, so plan changes can be found and compared.
- Query Store hint
- A query hint attached to a query by its query_id in Query Store, so it applies without editing the code; available from SQL Server 2022.
References
- Query processing architecture guide (parameter sensitivity) (Microsoft)
- Parameter Sensitive Plan optimization (Microsoft)
- Optional parameter plan optimization (OPPO) (Microsoft)
- Monitor performance by using the Query Store (Microsoft)
- How Query Store collects data (Microsoft)
- Query Store hints (Microsoft)
- sys.sp_query_store_set_hints (Transact-SQL) (Microsoft)
- sp_query_store_force_plan (Transact-SQL) (Microsoft)
- sp_query_store_unforce_plan (Transact-SQL) (Microsoft)
- sys.query_store_plan (Transact-SQL) (Microsoft)
- sys.query_store_runtime_stats (Transact-SQL) (Microsoft)
- sys.query_store_query_variant (Microsoft)
- Automatic tuning (Microsoft)
- Hints (Transact-SQL) - Query (Microsoft)
- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) (Microsoft)
- DBCC SHOW_STATISTICS (Transact-SQL) (Microsoft)
- sp_recompile (Transact-SQL) (Microsoft)
- Estimate the size of a clustered index (Microsoft)
- Estimate the size of a nonclustered index (Microsoft)
- What's new in SQL Server 2022 (Microsoft)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress