13 - Execution Plans

101 min read

Execution Plans

Quote

“The query optimizer is the most sophisticated piece of software in any database system.”

— Michael Stonebraker, ACM interview


flowchart TD
    A["Query is slow"] --> B["Get actual execution plan<br/>Ctrl+M or SET STATISTICS XML ON"]
    B --> C{"Estimated vs actual<br/>rows differ > 10x?"}
    C --> Y1([YES])
    Y1 --> D["Cardinality Estimation issue<br/>UPDATE STATISTICS<br/>Create multi-column stats<br/>CE Feedback (SS 2022)"]
    C --> N1([NO])
    N1 --> E{"Check per-query<br/>wait stats"}
    E --> F{"PAGEIOLATCH?"}
    F --> Y2([YES])
    Y2 --> G["I/O bottleneck<br/>Add indexes<br/>Increase RAM"]
    F --> N2([NO])
    N2 --> H{"LCK_M waits?"}
    H --> Y3([YES])
    Y3 --> I["Lock contention<br/>Enable RCSI<br/>Shorten transactions"]
    H --> N3([NO])
    N3 --> J{"High-cost<br/>operator?"}
    J --> Y4([YES])
    Y4 --> K{"Which operator?"}
    K --> L["Table or index scan<br/>Add index or rewrite predicate<br/>to be SARGable"]
    K --> M["Key Lookup<br/>Add INCLUDE columns<br/>to the nonclustered index"]
    K --> N["Sort with spill<br/>Use memory grant feedback<br/>Add a pre-sorted index"]
    K --> O["Hash Match spill<br/>Increase memory grant<br/>Reduce input rows"]
    J --> N4([NO])
    N4 --> P{"Parameter sniffing?<br/>High variance ratio"}
    P --> Y5([YES])
    Y5 --> Q["OPTIMIZE FOR UNKNOWN<br/>RECOMPILE<br/>PSP (SS 2022)"]
    P --> N5([NO])
    N5 --> R["Check implicit conversions<br/>Fix type mismatches<br/>Fix client parameter types"]

    classDef yesNode fill:#1f3b2d,stroke:#73d13d,stroke-width:2px,color:#c0caf5,font-weight:bold;
    classDef noNode fill:#4a1f24,stroke:#db4b4b,stroke-width:2px,color:#c0caf5,font-weight:bold;
    class Y1,Y2,Y3,Y4,Y5 yesNode;
    class N1,N2,N3,N4,N5 noNode;

Reproducible Setup

This page mixes read-only DMV queries, session-level instrumentation, and database-scoped configuration changes. Run the setup below once in SSMS before executing the later examples so the plan cache, Query Store, and last-plan DMVs all have real stoxx data to inspect.

SQL Server | lab setup | database context and capture flags

Every later example in the note assumes the same baseline: SQL Server 2022 at compatibility level 160, the local stoxx lab database, Query Store in READ_WRITE mode with broad capture, LAST_QUERY_PLAN_STATS on, and the advanced optimizer feedback switches enabled. The four components below establish that baseline, seed a reproducible demo query into the plan cache and Query Store, expose the session_id lookup you need for live-plan capture, and finally walk back the capture settings when the lab session is over.

Use the stoxx lab database

This sets the database context and verifies the database-level features that the rest of the page depends on.

Set the database context and confirm the lab instance state before running the later DMV queries.

USE stoxx;
GO
 
SELECT
    @@VERSION AS sql_server_version,
    DB_NAME() AS current_database,
    d.compatibility_level,
    d.is_query_store_on,
    d.is_read_committed_snapshot_on
FROM sys.databases d
WHERE d.name = DB_NAME();
GO
sql_server_versioncurrent_databasecompatibility_levelis_query_store_onis_read_committed_snapshot_on
Microsoft SQL Server 2022 (RTM-CU23) (KB5078297) - 16.0.4236.2 (X64), Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS)stoxx16010

This setup row is the first gate for the entire note. It confirms the session is in stoxx, the engine is SQL Server 2022, Query Store is already on, and READ_COMMITTED_SNAPSHOT is still off at capture time. That combination means the Query Store and IQP sections are expected to work, while the later RCSI section is still demonstrating a change that has not yet been applied.

ColumnValueWatchMeaningImplication
current_databasestoxx✅The session is connected to the intended lab database.Later outputs belong to the same database as the note text.
current_databaseAnything else❌The session is running in the wrong database context.Almost every later query becomes misleading until USE stoxx; is rerun.
compatibility_level160✅SQL Server 2022 optimizer behavior is active.PSP, CE Feedback, DOP Feedback, and the expected SQL Server 2022 plan behavior are available.
compatibility_level150DependsSQL Server 2019 optimizer behavior is active.Batch mode on rowstore can still work, but several SQL Server 2022-only sections no longer match the note.
compatibility_level140 or lower❌Older optimizer behavior is active.Modern IQP features in this page are unavailable or behave differently.
is_query_store_on1✅Query Store is enabled.Query Store plan and runtime-stat queries can return persisted rows.
is_query_store_on0❌Query Store is disabled.Query Store sections will be empty or incomplete until it is enabled.
is_read_committed_snapshot_on0DependsRead committed still uses locking semantics.Good if you want to demonstrate the pre-RCSI state; readers should still expect reader-writer blocking to be possible.
is_read_committed_snapshot_on1✅Read committed uses row-versioned snapshot scans.The later RCSI enablement command becomes a verification step rather than a state change.

Enable the capture features used later in this page

Several later sections assume Query Store is writable, one-off queries are captured, and the server retains the last actual plan for completed queries.

Enable the database-scoped capture and feedback features referenced throughout the note.

Later examples depend on these features being enabled

Several later examples in this note will either return incomplete data or not work at all if these features are not enabled first.

  • The Query Store queries later in the page need Query Store to be ON and in READ_WRITE mode. If Query Store is off, those catalog views will be empty or misleading for this walkthrough.
  • The ad-hoc demo query used throughout the note is easiest to find when QUERY_CAPTURE_MODE = ALL. With the default AUTO mode, small one-off lab queries may not be captured.
  • The sys.dm_exec_query_plan_stats example depends on LAST_QUERY_PLAN_STATS = ON. Without it, that section will not return the last actual plan for completed statements.
  • The CE Feedback, PSP, Memory Grant Feedback persistence, and DOP Feedback sections describe SQL Server 2022 optimizer behaviors that only appear when those database-scoped features are enabled.
  • In production, do not enable everything blindly just because the lab note does. QUERY_CAPTURE_MODE = ALL increases Query Store write volume and storage use, and LAST_QUERY_PLAN_STATS = ON adds lightweight runtime-plan capture overhead. The optimizer-feedback features are usually appropriate for modern production databases, but they should still be enabled intentionally, monitored, and validated against your workload.
  • For a lab or troubleshooting session, enabling these settings up front is the simplest way to guarantee that every later command in this page produces observable output.

Enable broadly in lab, enable narrowly in production

Safe pattern:

  • In a lab or one-off investigation, enable the full batch below before running the walkthrough, then revert the extra capture settings afterward with the cleanup batch already included in this section.
  • In production, prefer enabling Query Store in READ_WRITE mode first, keep QUERY_CAPTURE_MODE = AUTO unless you specifically need ad-hoc capture, and turn on LAST_QUERY_PLAN_STATS only when you need last-actual-plan visibility badly enough to justify the extra overhead.
  • If you skip this batch, expect the later sections on Query Store, sys.dm_exec_query_plan_stats, CE Feedback, PSP, persisted Memory Grant Feedback, and DOP Feedback to be partially or completely unavailable.
ALTER DATABASE stoxx SET QUERY_STORE = ON;
ALTER DATABASE stoxx SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = ALL,
    WAIT_STATS_CAPTURE_MODE = ON
);
GO
 
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = ON;
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = ON;
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = ON;
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
GO

Seed the plan cache and Query Store with one repeatable demo query

Many later examples retrieve plans from cache or Query Store. This query gives those sections a stable statement to target.

Run one tagged query three times so the plan-cache, Query Store, and LAST_QUERY_PLAN_STATS examples all have a known statement to inspect.

SELECT
    symbol,
    [date],
    [close],
    volume
FROM silver.eurostoxx50_ohlcv
WHERE symbol = 'ASML.AS'
  AND [date] >= '2025-04-01'
  AND [date] < '2025-05-01'
/* execution-plans-demo */;
GO 3
symboldateclosevolume
ASML.AS2025-04-01619.7678551
ASML.AS2025-04-02616.3678930
ASML.AS2025-04-03578.71145270
ASML.AS2025-04-04564.11994082
ASML.AS2025-04-07550.02619138

This rowset is only a reproducibility check. It proves the tagged demo query is valid in stoxx, returns real April 2025 ASML.AS data, and therefore has something concrete to seed into both the plan cache and Query Store. By itself, this output is not performance evidence yet.

Find the session id for in-flight plan capture

The sys.dm_exec_query_statistics_xml example later in the page needs the session_id of a currently running or waiting user query from another SSMS window.

Use the example below to generate active blocking and capture a live session_id together with wait and blocker context.

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.cpu_time AS cpu_time_ms,
    r.total_elapsed_time AS elapsed_time_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.blocking_session_id,
    LEFT(REPLACE(REPLACE(LTRIM(SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1 THEN (DATALENGTH(st.text) - r.statement_start_offset) / 2 + 1
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    )), CHAR(13), ' '), CHAR(10), ' '), 160) AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC, r.session_id;
session_iddatabase_namelogin_namehost_nameprogram_namestatuscommandwait_typewait_time_mscpu_time_mselapsed_time_mslogical_readsreadswritesblocking_session_idrunning_statement
53stoxxsaELYSIUMMicrosoft SQL Server Management Studio - QuerysuspendedUPDATELCK_M_IX2173702173700056UPDATE [dbo].[dm_exec_requests_demo] SET [payload] = [payload] WHERE [id]=@1
62stoxxsaELYSIUMMicrosoft SQL Server Management Studio - QuerysuspendedSELECTLCK_M_SCH_S1158401158400056SELECT payload FROM dbo.dm_exec_requests_demo WITH (UPDLOCK, HOLDLOCK) WHERE id = 1

Both visible requests are victims, not the root blocker. Session 53 is an UPDATE waiting on LCK_M_IX, and session 62 is a SELECT waiting on LCK_M_SCH_S; both point to blocking_session_id = 56. Session 56 does not appear in sys.dm_exec_requests because that DMV only shows currently active requests, and a sleeping session with an open transaction can still hold incompatible locks without having a current request row. The timing columns show that both victims have been stalled for seconds, while cpu_time_ms = 0, logical_reads = 0, reads = 0, and writes = 0 make it clear that the current bottleneck is lock waiting, not CPU work or I/O. running_statement shows exactly which statements are blocked, which lets you distinguish a blocked writer from a blocked reader before tracing the root blocker in session and lock DMVs.

ColumnValueWatchMeaningImplication
database_nameExpected target database such as stoxx✅The request is running where you expect it to run.Focus the investigation on that database’s objects, indexes, and workload.
database_nameUnexpected database such as master, tempdb, or another user DB❌The request is not running in the database you assumed.Re-scope the investigation before tuning the wrong database.
login_nameExpected service or application login✅The session identity matches the workload you are investigating.Helps separate application traffic from ad hoc or administrative activity.
login_namesa or an unexpected privileged loginDependsThe request is being executed under a broad administrative identity.Check whether this is an emergency change, administrative action, or unsafe production practice.
host_nameExpected application host, jump box, or job runner✅The request came from a known origin.Useful for routing the issue to the right team or node.
host_nameUnknown workstation or unexpected server❌The request origin is not what you expected.Investigate ad hoc activity, rogue tooling, or a misrouted workload.
program_nameExpected application, agent, or client tool✅The client program matches the workload path you are tracing.Helps separate application traffic from SSMS, ETL, or scripts.
program_nameSQLCMD, Microsoft SQL Server Management Studio, or other ad hoc tool in productionDependsThe request is coming from a manual or script-driven client, not necessarily from the application tier.Check whether the issue is operational, not application-driven.
statusrunning✅The request is currently executing on a worker.Best candidate when you want to capture an actively consuming statement.
statusrunnableDependsThe request is ready to run but waiting for scheduler time.Look for CPU pressure or scheduler contention.
statussuspendedDependsThe request is waiting on a resource.Use wait_type and blocking_session_id to determine whether the wait is benign or actionable.
statussleeping❌The session is idle and not actively executing a request.Not useful for in-flight plan capture or live request triage.
commandSELECT✅A read query is the active statement.Investigate plan shape, blocking, row goals, and read volume.
commandUPDATEDependsA write statement is active or waiting.Consider lock acquisition, transaction scope, and whether the write itself is the blocker or another victim.
commandINSERT, UPDATE, or DELETEDependsA write statement is active.Consider locking impact, transaction length, log pressure, and index maintenance cost.
commandWAITFORDependsThe session is intentionally paused but the batch is still active.Acceptable only when deliberate; otherwise it can indicate an agent loop, delay logic, or a held transaction.
commandBackup, restore, DBCC, or maintenance commandDependsAdministrative work is active.Do not treat it like normal OLTP traffic; coordinate with operations first.
wait_typeBlank / NULLDependsNo current wait is exposed for the request.Often means the request is actively running or has just transitioned between waits.
wait_typeWAITFOR✅The request is sleeping inside a WAITFOR statement.Acceptable only when deliberate; otherwise investigate why the session is intentionally delayed.
wait_typeLCK_M_IX❌The request is waiting to acquire an intent exclusive lock.Usually indicates write-side blocking before SQL Server can proceed to finer-grained exclusive locking.
wait_typeLCK_M_SCH_S❌The request is waiting for a schema stability lock.Often points to blocking from DDL, recompilation-sensitive activity, or a session holding an incompatible schema-level lock.
wait_typeLCK_M_* such as LCK_M_U❌The request is waiting on a lock.Follow the blocker before tuning the victim query.
wait_typePAGEIOLATCH_*❌The request is waiting for data pages to be read into memory.Investigate storage latency, missing indexes, and cache residency.
wait_typeCXPACKET or CXCONSUMERDependsThe request is participating in a parallel plan.Check whether parallelism is helping or masking skew and grant issues.
wait_typeASYNC_NETWORK_IODependsSQL Server is waiting for the client to consume rows.The bottleneck may be client-side rather than query-side.
wait_time_ms0-1000✅The current wait is short.Usually not enough by itself to justify urgent action.
wait_time_ms1000-10000DependsThe request has been waiting for seconds, not milliseconds.Worth watching, especially on hot paths or high-frequency queries.
wait_time_ms>10000❌The current wait is long-lived.Escalate blocking, I/O, or scheduler investigation based on wait_type.
cpu_time_msNear 0 while wait_type shows a resource wait✅The request is mostly waiting, not burning CPU.Focus on the wait reason rather than CPU tuning first.
cpu_time_msClose to elapsed_time_ms❌The request is spending most of its lifetime on CPU.Investigate plan inefficiency, row volume, scalar work, or poor parallelism choices.
elapsed_time_msLow and stable✅The request is recent or transient.A short-lived issue may be acceptable if it is not frequent.
elapsed_time_msContinuously growing❌The request is staying alive for a long time.Prioritize it if it is blocking others or consuming resources.
logical_reads0-100 for point lookups or blocked waits✅The request has touched few buffer-pool pages so far.Not a sign of read amplification by itself.
logical_readsLarge and rapidly increasing❌The request is scanning or repeatedly touching many pages.Investigate index design, predicate shape, and row estimates.
reads0 while the request is blocked✅The session is not performing physical I/O during the wait.Confirms the current bottleneck is not disk access.
readsNonzero and growing with PAGEIOLATCH_* waits❌Physical page reads are occurring.Correlate with I/O waits and storage/cache conditions.
writes0 for a blocked reader✅The victim is not generating write I/O.Supports the interpretation that it is waiting on a lock rather than changing data.
writesNonzero on a write-heavy requestDependsThe request is dirtying pages.Consider transaction log pressure and downstream blocking impact.
blocking_session_id0 or NULL✅The request is not currently blocked, or a blocker is not identified.Look elsewhere for the bottleneck.
blocking_session_idPositive session id❌Another session is the blocker.Trace the blocker first; if that session does not appear in sys.dm_exec_requests, it may be sleeping with an open transaction and must be investigated through session and lock metadata.
blocking_session_id-2, -3, -4, or -5❌SQL Server is indicating a nonstandard blocker case rather than a normal user session.Investigate distributed transactions, recovery, or latch ownership details before drawing conclusions.
running_statementPrecise current statement text✅The query isolates the exact statement currently executing inside the batch.You can tune or trace the right statement instead of guessing from the full batch.
running_statementTruncated or unexpectedly generic textDependsThe current statement is long, dynamic, or parameterized.Pull the full batch text or the full plan XML if you need more context.

Optional cleanup after you finish collecting outputs

Return the capture settings to lighter defaults after collecting the required plans.

Return Query Store and last-plan capture to their usual lab defaults after you finish the walkthrough.

Run cleanup only after you have captured the outputs you need

This batch reduces future capture detail. If you run it too early, later sections that rely on broad Query Store capture or last actual plan retention may stop returning the evidence you expect. It does not delete existing Query Store rows, but it does make the environment less observant for subsequent demos.

Use it as an end-of-lab reset

Keep the richer settings on while you are collecting plans, XML, waits, and feedback metadata. Run the cleanup batch only when you are done with the walkthrough or want to return the lab to a lighter baseline.

ALTER DATABASE stoxx SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = OFF;
GO

Estimated vs. Actual Plans

SQL Server exposes three different plan views depending on whether the query has already executed, is still running, or is being inspected before execution. Getting the right one for your diagnostic goal matters: estimated plans can mislead when statistics are stale, actual plans carry the runtime counters you actually need to reason about performance, and live plans animate data flow while the query is still in progress.

SQL Server | plan variants | estimated, actual, and live capture

Choosing between estimated, actual, and live plans is almost always the first question when you start a performance investigation. The H4 below is a single comparison table that maps each plan variant to the SSMS shortcut or T-SQL flag that produces it and to the information it actually carries.

Compare estimated, actual, and live plan capture options

Map each plan variant to how you produce it and what diagnostic information it contains.

Plan typeHow to get itWhat it shows
EstimatedSSMS: Ctrl+L, or SET SHOWPLAN_XML ONWhat the optimizer predicts will happen — row counts, costs, operator choices. No actual execution.
ActualSSMS: Ctrl+M then run, or SET STATISTICS XML ONEverything above PLUS what actually happened — real row counts, real memory, spills, elapsed time.
LiveSSMS: Include Live Query StatisticsReal-time animation showing rows flowing through operators as the query runs.

Always Use Actual Plans for Diagnosis

Estimated plans can mislead when statistics are stale. The estimated cost percentages are computed from optimizer predictions — when those predictions are wrong, the cost distribution is wrong too. Always cross-reference with actual row counts.

Reading the Visual Tree in SSMS

SQL Server execution plans are read right-to-left, bottom-to-top. The rightmost operators are the data sources (table/index scans and seeks). Data flows left through transformations (joins, sorts, aggregations) until it reaches the leftmost operator — the final SELECT, INSERT, or UPDATE result.


flowchart RL
    Select["SELECT<br/>Final result"]
    Join["Hash Match<br/>Inner Join<br/>Cost: 12%"]
    Scan["Clustered Index Scan<br/>bronze source<br/>Cost: 60%"]
    CSeek["Clustered Index Seek<br/>silver source<br/>Cost: 28%"]
    NCSeek["Nonclustered Index Seek<br/>Cost: 0%"]
    Lookup["Key Lookup<br/>Cost: 0%"]

    Scan -->|"many rows"| Join
    CSeek -->|"few rows"| Join
    NCSeek --> Lookup
    Lookup --> CSeek
    Join --> Select

SQL Server | plan tree | operators, arrows, and cost tooltips

Reading a SQL Server plan is a mechanical process once you know the order and the three properties that matter most on each operator. The H4 below walks that sequence end-to-end for the common shapes you will see on pipeline and dashboard queries.

Walk the plan from data sources to final SELECT

  1. Start at the far right. These are the data access operators — where SQL Server touches tables/indexes. Look at their type:
    • Index Seek (good) — B-tree navigation to specific rows, O(log n). Requires SARGable predicates in the WHERE clause.
    • Index Scan (check context) — reads all leaf pages of an index
    • Table Scan (usually bad) — full heap scan, reads every page
    • Key Lookup (expensive if frequent) — bookmark lookup from NC index to clustered index. Fix by adding INCLUDE columns to the nonclustered index.
  2. Follow the arrows left. Data flows through intermediate operators:
    • Hash Match — builds a hash table for joins or aggregations
    • Merge Join — merges two pre-sorted inputs (efficient if both are already sorted)
    • Nested Loops — for each row in outer input, seeks into inner input
    • Sort — sorts rows (watch for spill warnings)
    • Filter — applies WHERE conditions that couldn’t be pushed to the seek
  3. End at the far left. The result operator: SELECT, INSERT, UPDATE, or DELETE.
  4. Check arrow thickness. A sudden thick-to-thin transition (or vice versa) reveals where filtering or explosion happens. A thick arrow into a Nested Loops operator with a thin inner input means many iterations — potential performance issue.
  5. Hover over each operator for the tooltip. The critical properties are:
    • Estimated/Actual Number of Rows — are they close? (see Cardinality Estimation below)
    • Estimated Operator Cost — where is time being spent? (see Cost Analysis below)
    • Number of Executions — how many times was this operator invoked?
    • Warnings — yellow triangle icons indicate problems (spills, implicit conversions, missing indexes)

Getting Plans from the Pipeline (Non-SSMS)

SSMS is the standard tool for interactive plan analysis, but data pipelines run unattended. You need programmatic methods to capture and store plans for post-execution review — from the volatile plan cache, from live sessions, or from Query Store for persistent history.

Query Store

Query Store is a built-in flight recorder for query performance data, introduced in SQL Server 2016. When enabled (ALTER DATABASE db SET QUERY_STORE = ON), it persists execution plans, runtime statistics, and wait stats to disk — surviving plan cache eviction and server restarts. Query Store is required for several SQL Server 2022 Intelligent Query Processing features (CE Feedback, Memory Grant Feedback Persistence, DOP Feedback).

SQL Server | DMV capture | programmatic plan retrieval

Pipelines run unattended, so you need ways to recover a plan after the query has finished. The three H4s below cover the three main sources: the volatile plan cache for recently executed statements, inline capture during execution, and the durable Query Store history that survives cache eviction and restarts.

Retrieve a cached plan from the plan cache

The plan cache holds compiled plans in memory. This query retrieves the plan for a specific query after it has executed. The plan cache is volatile — plans are evicted under memory pressure or after DDL changes.

SELECT
    qp.query_plan,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_reads,
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%execution-plans-demo%'
ORDER BY qs.last_execution_time DESC;
execution_countavg_readsavg_cpu_ms
2860
query_plan_hashroot_opoperatorsindexes
0x0B9E9C3019B25F40Nested LoopsNested Loops → Index Seek → Clustered Index SeekIX_silver_eurostoxx50_ohlcv_symbol_date, PK__eurostox__3213E83FDF67D274

The first table is cache-level aggregate telemetry from sys.dm_exec_query_stats. It says the cached statement had executed twice, averaged 86 logical reads per execution, and consumed less than 1 ms of average worker time once rounded to milliseconds. Because SQL Server and Query Store both express these counters as aggregated plan metrics, the right reading is “cheap and stable so far,” not “guaranteed fast forever.” The second table is the XML-plan summary: the same statement shape is identified by one query_plan_hash, and the operator chain shows a nonclustered seek on (symbol, date) followed by clustered lookups for the non-covered columns close and volume.

ColumnValueWatchMeaningImplication
execution_count1DependsOnly one execution contributed to the cache row.Enough to inspect plan shape, but too little history for trend analysis.
execution_count2-10DependsLight execution history.Still a small sample; avoid broad conclusions until the query has executed more times.
execution_count>10✅Repeated plan reuse is happening.Average metrics become more representative of normal behavior.
avg_reads0-10✅Extremely selective access, usually a point lookup or tiny seek.Usually not an I/O concern unless executed at very high frequency.
avg_reads10-100✅Modest buffer-pool work per execution.Common and often acceptable for selective transactional queries.
avg_reads100-1000DependsModerate memory traffic per execution.Acceptable for wider predicates, but worth checking on hot paths.
avg_reads>1000❌Heavy page access per execution.Investigate scans, key lookups, or missing indexes.
avg_cpu_ms<1 ms✅CPU cost is trivial or rounded below 1 ms.CPU is not the pressure point for this statement in the captured sample.
avg_cpu_ms1-10 ms✅Low CPU consumption.Usually acceptable unless the statement runs constantly.
avg_cpu_ms10-50 msDependsModerate CPU usage.Fine for some workloads, but monitor if frequency is high.
avg_cpu_ms>50 ms❌CPU cost is materially noticeable.Investigate expensive expressions, joins, sorts, or poor row estimates.
root_opNested Loops✅Loop join chosen for relatively selective probing.A good sign when outer-row counts are low.
operatorsIndex Seek -> Clustered Index SeekDependsThe plan is selective but not fully covered.Fine for small result sets; can degrade as qualifying rows grow because each row triggers lookups.
query_plan_hashSame hash across cache, Query Store, and last-plan sections✅Multiple telemetry sources are pointing at the same physical plan.Cross-section comparisons in the note are valid.

Click the XML result in SSMS to open the graphical plan viewer.

Capture the live actual plan inline with a session flag

Wrapping a query with SET STATISTICS XML ON/OFF adds the full execution plan as an additional XML result set column. This is the standard method for capturing actual plans from pipeline scripts during development.

Extra XML result set and session-scoped instrumentation

SET STATISTICS XML ON changes the shape of what the session returns: every subsequent statement emits an extra XML plan result set until you turn it off. That is usually fine in SSMS, but it can confuse application code, automation, or notebooks that expect only the normal query result. It also adds overhead, so do not leave it on in busy production troubleshooting loops.

Use it in an isolated SSMS session and turn it off immediately after the target query

This pattern is appropriate for labs, one-off investigations, and scripted captures where you explicitly want the actual plan XML inline with the query output.

SET STATISTICS XML ON;
 
SELECT
    symbol,
    [date],
    [close],
    volume
FROM silver.eurostoxx50_ohlcv
WHERE symbol = 'ASML.AS'
  AND [date] >= '2025-04-01'
  AND [date] < '2025-05-01'
/* execution-plans-demo */;
 
SET STATISTICS XML OFF;
symboldateclosevolume
ASML.AS2025-04-01619.7678551
ASML.AS2025-04-02616.3678930
ASML.AS2025-04-03578.71145270
ASML.AS2025-04-04564.11994082
ASML.AS2025-04-07550.02619138
query_plan_hashroot_opoperatorsroot_actual_rowsseek_logical_readskey_lookup_logical_reads
0x0B9E9C3019B25F40Nested LoopsNested Loops → Index Seek → Clustered Index Seek20244

The first table is the business rowset, and the second table is the actual-plan summary derived from the XML result set produced by SET STATISTICS XML ON. The important point is not just that 20 ASML rows were returned, but that the actual plan shows where the work happened: only 2 logical reads on the seek itself, versus 44 logical reads on the clustered key lookups. That is the textbook signature of a selective access path that still pays extra work for non-covered columns.

ColumnValueWatchMeaningImplication
root_actual_rowsExact match to returned row count✅The plan summary is aligned with the visible query result.You can safely tie the operator work to the business rowset.
root_actual_rowsMuch higher than expected❌More rows flowed through the plan than the reader may assume from the preview.Recheck filters, row goals, or hidden branches in the plan.
seek_logical_reads0-10✅The access method itself is cheap.The index key is selective and doing its job.
seek_logical_reads>100❌Even the seek phase is touching many pages.Predicate selectivity or index design may be poor.
key_lookup_logical_readsLower than seek reads✅Lookup overhead is minor.The plan is close to being efficient enough already.
key_lookup_logical_readsSimilar to or higher than seek readsDependsLookup overhead is material.Consider a covering index if this query is important or frequent.
operatorsIndex Seek -> Clustered Index SeekDependsGood selectivity, but the index does not cover all requested columns.Fine for small row counts; risky if row count grows.

Retrieve persisted plan history from Query Store

Query Store captures plans across restarts, making it the preferred source for historical plan analysis and regression detection. Unlike the plan cache, plans in Query Store are durable.

SELECT TOP 20
    qsqt.query_sql_text,
    TRY_CAST(qsp.query_plan AS XML) AS plan_xml,
    qsrs.avg_duration / 1000 AS avg_ms,
    qsrs.avg_logical_io_reads
FROM sys.query_store_runtime_stats qsrs
JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%silver.eurostoxx50_ohlcv%'
  AND qsqt.query_sql_text LIKE '%ASML.AS%'
ORDER BY qsp.last_execution_time DESC;
query_sql_textavg_msavg_logical_io_readsquery_plan_hashroot_opoperatorsindexes
SELECT symbol, [date], [close], volume FROM silver.eurostoxx50_ohlcv WHERE symbol = 'ASML.AS' AND [date] >= '2025-04-01' AND [date] < '2025-05-01'0.217860x0B9E9C3019B25F40Nested LoopsNested Loops → Index Seek → Clustered Index SeekIX_silver_eurostoxx50_ohlcv_symbol_date, PK__eurostox__3213E83FDF67D274

This row is the durable Query Store version of the same query shape seen in the volatile plan cache. Microsoft documents avg_duration as microseconds and avg_logical_io_reads as 8 KB pages, so after the conversion in this query the output says: this plan averaged 0.217 ms per execution and about 86 logical page reads, or roughly 688 KB of buffer-pool page access per execution. That is a fast query. The query_plan_hash matching the cache section matters because it proves Query Store and the plan cache were describing the same physical plan, not two different plans that only happened to query the same table.

ColumnValueWatchMeaningImplication
avg_ms<1 ms✅Usually trivial elapsed time for one Query Store runtime-stats row.The query is not a tuning priority unless it runs extremely often.
avg_ms1-10 ms✅Generally healthy for short lookup-style work.Usually acceptable unless the statement is on a hot path.
avg_ms10-50 msDependsStill often acceptable, but no longer negligible.Review frequency and business criticality before ignoring it.
avg_ms50-200 msDependsNoticeable latency.Worth checking if the statement executes frequently.
avg_ms200-1000 ms❌Materially expensive for most interactive workloads.Investigate plan quality, row estimates, and indexing.
avg_ms>1000 ms❌Clear tuning target unless the query is intentionally batch/reporting work.Expect deeper plan analysis.
avg_logical_io_reads0-10✅Tiny page-touch footprint.Typical of very selective point lookups.
avg_logical_io_reads10-100✅Modest logical I/O footprint.Common and usually acceptable for selective seeks.
avg_logical_io_reads100-1000DependsModerate buffer-pool work.Fine for medium-range queries, but watch hot-path frequency.
avg_logical_io_reads>1000❌Large page-touch footprint per execution.Investigate scans, lookups, or wider-than-expected predicates.
avg_logical_io_reads86 in this capture✅About 86 x 8 KB = 688 KB of logical page access.Not alarming on its own. The query is fast and the I/O footprint is still modest.
root_opNested Loops✅Query Store preserved the same join strategy as the cache capture.The plan is still a seek-plus-lookup plan, not a regression to a scan-heavy shape.
query_plan_hashSame hash as the cache and last-actual-plan sections✅Same physical plan across telemetry sources.Cross-source comparisons in the note are trustworthy.

One Query Store row is one plan in one aggregation interval

avg_ms and avg_logical_io_reads do not represent the query for all time. They represent one Query Store runtime-stats row for one plan in one interval. If the same query has multiple plans or multiple intervals, you must aggregate or compare those rows explicitly before making historical claims.

SQL Server | lightweight profiling | in-flight and last-actual plans

SQL Server 2019+ enables lightweight profiling (v3) by default — collecting per-operator row counts for every query execution with minimal overhead (~2%). This replaces the need for SET STATISTICS XML ON in many production scenarios, because you can retrieve the last actual execution plan for any session without adding instrumentation to the query itself. The two H4s below use its two main access points: a live in-flight lookup by session_id and a retroactive lookup for the last actual plan of a statement that already finished.

Capture the live in-flight actual plan of a running query

This DMV returns the actual execution plan (with runtime statistics) for a currently running query. Call it from a separate session, passing the target session’s session_id. No prior setup is needed on SQL Server 2019+.

DECLARE @session_id smallint = 0;
 
SELECT *
FROM sys.dm_exec_query_statistics_xml(@session_id);

No embedded row here, because this DMV only returns a result while a different session is still executing.

Finding the session_id

Reuse the production sys.dm_exec_requests query and pick the session_id for the live user request you want to inspect.

Retrieve the last actual plan of a completed statement

When enabled, SQL Server retains the last actual execution plan statistics for completed queries, accessible via sys.dm_exec_query_plan_stats. This gives you actual plans for queries that have already finished, without requiring SET STATISTICS XML ON during execution.

SELECT
    qp.query_plan,
    qs.execution_count,
    qs.last_elapsed_time / 1000 AS last_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%execution-plans-demo%'
ORDER BY qs.last_execution_time DESC;
execution_countlast_ms
20
query_plan_hashroot_opoperatorsindexes
0x0B9E9C3019B25F40Nested LoopsNested Loops → Index Seek → Clustered Index SeekIX_silver_eurostoxx50_ohlcv_symbol_date, PK__eurostox__3213E83FDF67D274

This DMV gives you the last known actual plan after execution has already finished. The first table tells you the cached statement had been executed twice and that the most recent elapsed time rounded down below 1 ms. The second table matters more: it proves the preserved last actual plan was still the same seek-plus-key-lookup shape seen in the plan cache and Query Store. In practice, this DMV is most useful when the query is already gone by the time you start troubleshooting.

ColumnValueWatchMeaningImplication
execution_count1DependsYou only have one completed execution in the cache row.Fine for confirming the last plan, weak for trend analysis.
execution_count>1✅The statement has reused the same cached entry.Useful for comparing “last execution” against broader averages elsewhere.
last_ms<1 ms✅The last completed execution was trivial once rounded to milliseconds.The query is not currently a latency concern.
last_ms1-10 ms✅Low elapsed time for the most recent execution.Usually healthy for a selective lookup.
last_ms10-100 msDependsThe last execution is no longer negligible.Compare against cache averages to see whether this was a one-off.
last_ms>100 ms❌The latest execution was noticeably slow.Inspect actual rows, waits, and parameter values.
query_plan_hashSame as other sections✅The last actual plan matches the other captured sources.The note is showing one stable plan, not conflicting telemetry.

Permissions Change in SQL Server 2022

sys.dm_exec_query_statistics_xml requires VIEW SERVER STATE on SQL Server 2019 and earlier, but VIEW SERVER PERFORMANCE STATE on SQL Server 2022+.

Grant the new permission on SQL Server 2022+ instances

GRANT VIEW SERVER PERFORMANCE STATE TO [pipeline_user];

Cost Analysis — Finding the Most Expensive Operator

Every operator in the execution plan shows an Estimated Operator Cost as a percentage of the total query cost. This tells you where to focus optimization effort. The cost is a dimensionless number derived from the optimizer’s internal model — it combines estimated I/O and CPU work but does not represent seconds, milliseconds, or any real-time unit.


flowchart RL
    Select["SELECT<br/>Cost: 0%"]
    Sort["Sort<br/>Cost: 5%"]
    Join["Hash Match Join<br/>Cost: 15%"]
    Seek["Clustered Index Seek<br/>Cost: 8%"]
    Scan["Clustered Index Scan<br/>Cost: 72%<br/>Bottleneck"]

    Seek --> Join
    Scan --> Join
    Join --> Sort
    Sort --> Select

    style Scan fill:#3b1f2b,stroke:#f7768e,stroke-width:3px,color:#c0caf5

SQL Server | plan cost | interpretation and extraction

Estimated Operator Cost percentages come from the optimizer’s internal model, not from real execution. The four H4s below first translate the percentage ranges into operational thresholds, then show how to extract per-operator cost data from plan XML, how to split that cost into I/O and CPU components, and finally how to cross-check estimated cost against real STATISTICS TIME/IO numbers for the same query.

Interpret Estimated Operator Cost percentage ranges

Cost rangeWhat it meansAction
0-5%NegligibleIgnore — not worth optimizing
5-20%NormalCheck only if query is slow overall
20-50%SignificantInvestigate — might benefit from an index or query rewrite
50-100%DominantThis operator is the bottleneck. Fix this first.

Shred operator costs from cached plan XML

DECLARE @sql_handle varbinary(64);
 
SELECT TOP (1)
    @sql_handle = qs.sql_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%execution-plans-demo%'
ORDER BY qs.last_execution_time DESC;
 
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    node.value('@PhysicalOp', 'varchar(50)') AS operator_name,
    node.value('@EstimatedTotalSubtreeCost', 'float') AS subtree_cost,
    node.value('@EstimateRows', 'float') AS estimated_rows,
    node.value('@EstimateIO', 'float') AS io_cost,
    node.value('@EstimateCPU', 'float') AS cpu_cost
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY qp.query_plan.nodes('//RelOp') AS T(node)
WHERE qs.sql_handle = @sql_handle
ORDER BY subtree_cost DESC;
operator_namesubtree_costestimated_rowsio_costcpu_cost
Sort0.03137981350.01126130.00159444
Compute Scalar0.018524113500.0000135
Filter0.018510613500.00132
Nested Loops0.0171906150000.00627
Nested Loops0.00942047150000.00627

These numbers come from the optimizer’s cost model, not from measured runtime. In this capture, Sort has the highest subtree_cost, which means SQL Server estimated that ordering work would dominate the plan branch more than the join or scalar computation. That does not prove the sort was the real runtime bottleneck. It only tells you where the optimizer believed the most combined I/O-plus-CPU work would be, which is why you should confirm it against actual rows and STATISTICS IO/TIME.

ColumnValueWatchMeaningImplication
subtree_costHighest row in the result set✅Highest estimated branch cost in the plan.Start your cost-focused inspection here.
estimated_rowsClose to actual rows later in the note✅The optimizer’s row model is behaving reasonably.Cost ranking is more trustworthy.
estimated_rowsFar from actual rows❌Cost ranking is being driven by bad assumptions.Fix statistics or cardinality issues before trusting the cost order.
io_cost greater than cpu_costDependsSQL Server expects page access to dominate.Investigate selectivity, scans, and indexing first.
cpu_cost greater than io_costDependsSQL Server expects computation to dominate.Investigate sorts, hashes, and expressions first.

Break down per-operator cost into I/O versus CPU

Each operator’s cost is split into I/O cost and CPU cost:

  • High IO cost → the operator is reading many pages from disk/buffer pool. Solution: add indexes to reduce pages read, or add RAM for better buffer pool hit ratio.
  • High CPU cost → the operator is doing heavy computation (sorting, hashing, string comparisons). Solution: reduce the number of rows reaching this operator, or simplify the expression.

Cost Percentages Are Based on Estimates

Cost percentages are based on the optimizer’s estimates, not actual execution. If statistics are stale, the cost distribution can be completely wrong. A scan showing “5%” might actually dominate execution time if the optimizer underestimated the row count. Always cross-reference costs with actual row counts and SET STATISTICS TIME/IO output.

Always verify cost percentages against actual row counts and SET STATISTICS TIME/IO

Run with SET STATISTICS TIME ON; SET STATISTICS IO ON; alongside the actual execution plan (Ctrl+M). Compare the reported elapsed time per statement against the plan’s cost percentages — a mismatch signals stale statistics. Run UPDATE STATISTICS table WITH FULLSCAN to correct estimates.

Capture actual timing and logical reads per statement

These session-level settings report actual I/O and CPU measurements per statement — not per operator, but they validate total query-level performance against the plan’s cost distribution.

Session-scoped diagnostics with noisy output

SET STATISTICS TIME ON and SET STATISTICS IO ON keep emitting Messages-pane diagnostics for every later statement in the same session until they are turned off. That is safe for manual troubleshooting, but it is noisy in shared scripts and can break parsers that expect clean output. The numbers are statement-level totals, not operator-level timings, so do not over-interpret them as a substitute for the actual plan.

Use them to validate the plan, not replace it

Run them in SSMS or another manual session alongside the actual execution plan, capture the Messages output you care about, then turn both settings back off immediately.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
 
SELECT
    symbol,
    [date],
    [close],
    volume
FROM silver.eurostoxx50_ohlcv
WHERE symbol = 'ASML.AS'
  AND [date] >= '2025-04-01'
  AND [date] < '2025-05-01'
/* execution-plans-demo */;
 
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
metricvalue
row_count20
tablesilver.eurostoxx50_ohlcv
scan_count1
logical_reads70
physical_reads0
CPU_ms1
elapsed_ms0

This is statement-level runtime evidence rather than plan estimates. The query returned 20 rows, touched 70 logical 8 KB pages, performed no physical reads, used 1 ms of CPU, and rounded down to 0 ms elapsed time in the captured message output. No physical reads means the needed pages were already in memory for this execution, so storage latency was not part of the observed cost. 70 logical reads is about 560 KB of page access, which is still modest.

MetricValue or rangeWatchMeaningImplication
row_countMatches expected result size✅The statement returned the rows you expected.Good baseline before comparing plan metrics.
logical_reads0-10✅Tiny page-touch footprint.Usually a point lookup or very selective seek.
logical_reads10-100✅Modest buffer-pool work.Common and often acceptable for selective queries.
logical_reads100-1000DependsModerate page-touch volume.Check frequency and whether lookups are inflating reads.
logical_reads>1000❌Heavy page access per execution.Investigate scans, broad predicates, or non-covering indexes.
physical_reads0✅All needed pages came from memory.Storage is not the pressure point for this execution.
physical_reads>0DependsSome pages had to be read from storage.Could be normal for a cold cache or a sign of memory pressure.
CPU_ms<1-5 ms✅Low CPU cost.CPU is not the main issue here.
elapsed_ms much larger than CPU_ms❌The statement spent time waiting rather than burning CPU.Investigate blocking, I/O waits, or client consumption.

Cardinality Estimation — Detecting Bad Row Count Guesses

The cardinality estimator predicts how many rows each operator will process. When these predictions are wrong, the optimizer chooses bad strategies — wrong join types, insufficient memory grants, unnecessary sorts.

The golden rule: Compare Estimated Number of Rows vs. Actual Number of Rows for every operator in the actual execution plan. A ratio > 10x in either direction signals a problem.

SQL Server | cardinality | detect estimation errors

Cardinality estimation errors are the single most common root cause of bad plans. Detection starts in the SSMS actual plan, then moves to Query Store for historical ranking, then to plan XML for per-operator comparison of estimated and actual row counts. The three H4s below cover those three detection surfaces in that order.

Spot bad row-count estimates in the SSMS actual plan


flowchart LR
    subgraph Good["Good estimate"]
        GPAD[" "]
        G1["Estimated Rows: 1,200"]
        G2["Actual Rows: 1,350"]
        G3["Ratio: 1.1x"]
        G4["Hash Join selected"]
        G5["Memory grant: 2 MB"]
        G6["No spills"]
        GPAD ~~~ G1
        G1 --> G2 --> G3 --> G4 --> G5 --> G6
    end

    subgraph Bad["Bad estimate"]
        BPAD[" "]
        B1["Estimated Rows: 50"]
        B2["Actual Rows: 48,000"]
        B3["Ratio: 960x"]
        B4["Nested Loops selected"]
        B5["Memory grant too small"]
        B6["48,000 loop iterations<br/>TempDB spill"]
        BPAD ~~~ B1
        B1 --> B2 --> B3 --> B4 --> B5 --> B6
    end
    style GPAD fill:transparent,stroke:transparent,color:transparent
    style BPAD fill:transparent,stroke:transparent,color:transparent
  1. Run the query with Include Actual Execution Plan (Ctrl+M)
  2. Hover over each operator — the tooltip shows both Estimated and Actual rows
  3. Look for thick arrows where you expect thin ones (or vice versa)
  4. SSMS 18+ shows a warning icon (yellow triangle) when estimates are off by > 10x

Rank the worst cardinality regressions in Query Store

SELECT TOP 20
    qsqt.query_sql_text,
    qsp.query_plan,
    qsrs.avg_rowcount AS actual_avg_rows,
    qsrs.avg_logical_io_reads,
    qsrs.count_executions,
    qsrs.avg_duration / 1000 AS avg_ms
FROM sys.query_store_runtime_stats qsrs
JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsrs.avg_duration > 1000000
ORDER BY qsrs.avg_duration DESC;
rows_returnednote
0No Query Store runtime-stat row matched avg_duration > 1000000 on 2026-04-08.

This zero-row result is informative, not a failure. Query Store did have runtime rows, but none of them had an average duration above one second for the captured intervals. In other words, the filter is stricter than the current lab workload. If you want this section to produce examples, lower the threshold or run a deliberately slower query.

ColumnValueWatchMeaningImplication
rows_returned0DependsThe query ran successfully, but no row met the filter.Either the workload is healthy, the interval is quiet, or the threshold is too high.
rows_returned>0✅At least one Query Store runtime row crossed the threshold.The returned statements are valid tuning candidates.

Shred estimated versus actual rows from cached plan XML

DECLARE @sql_handle varbinary(64);
 
SELECT TOP (1)
    @sql_handle = qs.sql_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%execution-plans-demo%'
ORDER BY qs.last_execution_time DESC;
 
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    node.value('@PhysicalOp', 'varchar(50)') AS operator_name,
    node.value('@EstimateRows', 'float') AS estimated_rows,
    runtime.value('@ActualRows', 'int') AS actual_rows,
    CASE
        WHEN node.value('@EstimateRows', 'float') = 0 THEN 'N/A'
        WHEN runtime.value('@ActualRows', 'int') = 0 THEN 'N/A'
        ELSE CAST(
            runtime.value('@ActualRows', 'float') /
            NULLIF(node.value('@EstimateRows', 'float'), 0)
            AS VARCHAR(20))
    END AS actual_to_estimated_ratio
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY qp.query_plan.nodes('//RelOp') AS T(node)
OUTER APPLY node.nodes('RunTimeInformation/RunTimeCountersPerThread') AS RT(runtime)
WHERE qs.sql_handle = @sql_handle
ORDER BY runtime.value('@ActualRows', 'int') DESC;
operator_nameestimated_rowsactual_rowsactual_to_estimated_ratio
Sort1
Filter135.36
Nested Loops1504
Concatenation1504
Table-valued function504

The important signal here is the absence of actual_rows, not the operator names themselves. This query shredded cached plan XML that did not include runtime counters, so SQL Server could still expose EstimateRows but not the actual per-operator row counts. That is why the ratio column is blank. The operational lesson is simple: cached plan XML is often enough for shape analysis, but not always enough for actual-vs-estimated row analysis.

ColumnValueWatchMeaningImplication
actual_rowsNumeric value present✅Runtime row counters were captured.You can compare actual vs estimated rows directly.
actual_rowsBlank / NULL❌Runtime counters were not present in the XML source.This output cannot prove cardinality accuracy. Use an actual-plan source instead.
actual_to_estimated_ratioClose to 1x✅Estimate quality is good.The optimizer had a reasonable row-count model.
actual_to_estimated_ratio>10x or <0.1x❌Large estimation error.Expect poorer join choices or memory grants.

SQL Server | CE model | causes and fixes

Once an estimation error is identified, the next question is which CE model the database is running and whether switching back to the legacy estimator (or fixing statistics) produces a better plan. The three H4s below list the common root causes, expose the current compatibility level, and show how to force the legacy CE per statement when the default 160 model is producing a worse plan.

Map common causes of bad estimates to their fixes

SymptomCauseFix
Estimated = 1, Actual = 50,000Statistics not updated after bulk loadUPDATE STATISTICS table WITH FULLSCAN
Estimated = 100,000, Actual = 5Statistics were built when table was full, then data was deletedUPDATE STATISTICS or OPTION (RECOMPILE)
Estimates wrong on joined columnsMulti-column correlation not captured by single-column statisticsCreate multi-column statistics: CREATE STATISTICS stat_idx_sym ON silver.signals_daily (_index, symbol)
Estimates wrong with local variablesOptimizer can’t sniff variable values (unlike parameters)Use OPTION (RECOMPILE) or convert to parameterized query
Estimates wrong on filtered dataStatistics histogram has insufficient granularityUPDATE STATISTICS ... WITH FULLSCAN or filtered statistics
Consistently bad on complex predicatesCE model limitation (e.g., WHERE a = 1 OR b = 2)Break into UNION ALL, or use plan guides

Confirm which CE model the database is running

SQL Server has two CE models. The legacy CE (introduced in SQL Server 7.0) assumes full independence between predicates. The new CE (introduced in SQL Server 2014, compatibility level 120+) uses a partial correlation model and handles ascending keys and multi-statement TVFs better. SQL Server 2022 uses CE 160. If you’re seeing bizarre estimates on upgraded databases, check which model is active:

SELECT name, compatibility_level FROM sys.databases WHERE name = 'stoxx';
namecompatibility_level
stoxx160

This output confirms the database-level optimizer generation for stoxx. 160 means SQL Server 2022 behavior, which is why the SQL Server 2022 IQP features in this note are expected to appear. Compatibility level is not just a syntax flag. It changes which optimizer behaviors SQL Server may use for new compilations.

ColumnValueWatchMeaningImplication
compatibility_level160✅SQL Server 2022 behavior.The note’s PSP, CE Feedback, and newest IQP sections match the database setting.
compatibility_level150DependsSQL Server 2019 behavior.Some newer SQL Server 2022 sections will no longer match observed behavior.
compatibility_level140 or lower❌Older optimizer behavior.Modern sections in this note become partially inapplicable.
Compatibility levelCE model
------
70Legacy CE (pre-2014)
120New CE (SQL Server 2014)
150New CE (SQL Server 2019)
160New CE (SQL Server 2022, recommended)

Override the CE model for one statement with a query hint

If the new CE gives worse estimates for a specific query, you can force the legacy model without changing the database compatibility level. You can also toggle the CE model at the database level using ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON.

Last-resort hint, not a first fix

FORCE_LEGACY_CARDINALITY_ESTIMATION changes compilation behavior for that statement and can make the plan look better for one workload slice while making it worse for others. Do not jump to this hint before checking stale statistics, non-SARGable predicates, skewed data, and missing indexes. If you keep the hint permanently, document why, because it becomes a long-lived optimizer override.

Compare both plans side by side before deciding

Run the same query with and without the hint, capture the actual plans plus STATISTICS IO/TIME, and keep the hint only if the measured outcome is consistently better on the real workload.

SELECT * FROM silver.signals_daily
WHERE _index = 'euro_stoxx_50'
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
id_indexsymbolsignal_datecurrent_pricetarget_median_priceupside_potential
1euro_stoxx_50ASML.AS2026-03-041199.814500.2085
2euro_stoxx_50MC.PA2026-03-04507.46400.2613
3euro_stoxx_50RMS.PA2026-03-04193023550.2202
4euro_stoxx_50OR.PA2026-03-04374.34100.0954
5euro_stoxx_50SAP.DE2026-03-04167.382550.5235

This grid only confirms that the hinted query returned the expected euro_stoxx_50 rows. The point of the hint is not the row data but the compiled plan behind it, so the real comparison is whether row estimates, join choices, or memory grants change when you run the same statement with and without the legacy CE hint.

SQL Server | CE Feedback | 2022 optimizer feedback loop

SQL Server 2022 introduces Cardinality Estimation Feedback, an Intelligent Query Processing feature that automatically detects and corrects significant CE errors at runtime. Instead of manually diagnosing estimation mismatches and applying hints, CE Feedback runs a three-phase cycle:

  1. Identify — during execution, SQL Server detects operators where actual rows diverge significantly from estimated rows
  2. Verify — on subsequent executions, the optimizer recompiles the query with alternate CE assumptions (e.g., switching from full independence to partial correlation for multi-predicate filters) and validates whether the alternate plan is faster
  3. Replace — if the alternate plan is confirmed faster, the correction is persisted as a Query Store hint, automatically applied to future compilations

CE Feedback targets three specific CE model assumptions:

  • Correlation — adjusts predicate selectivity between full independence, partial correlation, and full correlation
  • Join containment — switches between simple containment and base containment assumptions
  • Row goal — modifies the row goal optimization for TOP, EXISTS, and IN subqueries

Enable CE Feedback at the database scope

This single database-scoped switch is the only command you need to turn CE Feedback on. The enablement is silent — no rows are returned — and the resulting feedback loop only becomes visible after repeated executions of eligible queries. The prerequisites and the telemetry surfaces you use to confirm that SQL Server is applying feedback are explained in the callouts immediately below.

Enable the CE Feedback Intelligent Query Processing feature for the current database.

CE Feedback Requirements

CE Feedback requires compatibility level 160 and Query Store enabled in READ_WRITE mode. If a forced plan already exists in Query Store for a query, CE Feedback is skipped for that query.

Enable CE Feedback and verify it is active

Run the configuration batch below after confirming that the database is already at compatibility level 160 and Query Store is writable.

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = ON;

Wait Stats Inside Execution Plans

SQL Server 2016+ embeds query-level wait statistics directly into the actual execution plan XML. Instead of correlating server-wide wait stats with specific queries, you can see exactly what each query waited on.

SQL Server | plan wait stats | per-query wait extraction

SQL Server records per-query waits inside the actual plan itself, not only in the server-wide sys.dm_os_wait_stats view. The two H4s below cover the two ways you extract those waits: interactively through the SSMS plan properties panel, and programmatically by shredding the embedded WaitStats XML node out of cached plans.

Read per-query waits from the SSMS plan properties panel

  1. Run query with Include Actual Execution Plan (Ctrl+M)
  2. Right-click on the root operator (leftmost — SELECT, INSERT, etc.)
  3. Click Properties (or press F4)
  4. Expand WaitStats node in the Properties panel

flowchart TD
    Root["Root operator properties<br/>Actual Number of Rows: 50<br/>Estimated Operator Cost: 0.234<br/>Number of Executions: 1"]
    Waits["WaitStats"]
    P["PAGEIOLATCH_SH<br/>WaitCount: 23<br/>WaitTimeMs: 142"]
    C["CXPACKET<br/>WaitCount: 4<br/>WaitTimeMs: 38"]
    N["ASYNC_NETWORK_IO<br/>WaitCount: 1<br/>WaitTimeMs: 5"]
    I1["Disk I/O wait<br/>pages not in buffer pool"]
    I2["Parallelism coordination"]
    I3["Client consumed rows slowly"]

    Root --> Waits
    Waits --> P --> I1
    Waits --> C --> I2
    Waits --> N --> I3

Shred per-query waits from cached plan XML

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    SUBSTRING(st.text, 1, 200) AS query_text,
    ws.value('@WaitType', 'varchar(100)') AS wait_type,
    ws.value('@WaitTimeMs', 'bigint') AS wait_time_ms,
    ws.value('@WaitCount', 'bigint') AS wait_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY qp.query_plan.nodes('//WaitStats/Wait') AS W(ws)
WHERE st.text LIKE '%execution-plans-demo%'
ORDER BY ws.value('@WaitTimeMs', 'bigint') DESC;
rows_returnednote
0No per-query wait rows were returned from cached plan XML for the tagged demo query.

A zero-row result here does not mean the query never waited; it means the matched cached plan XML did not contain embedded per-query wait nodes. This often happens on very fast statements or when the plan source is not an actual-plan capture that recorded waits.

SQL Server | wait types | per-query interpretation reference

Once you have per-query waits, you need to translate wait type names into operational causes. The two H4s below first map the wait types you will actually encounter on pipeline queries to their causes and fixes, then explain how to correlate per-query waits with the server-wide cumulative counters in sys.dm_os_wait_stats.

Interpret the common wait types seen on pipeline queries

Wait type in planMeaningAction
PAGEIOLATCH_SH / PAGEIOLATCH_EXQuery waited for pages to be read from disk into buffer poolNot enough RAM (buffer pool too small) or missing indexes causing unnecessary scans
WRITELOGQuery waited for transaction log flush to diskSlow log disk, or too many individual COMMITs (batch them)
CXPACKET / CXCONSUMERParallelism coordination waitsUsually harmless. If excessive: check for skewed thread distribution or lower MAXDOP
ASYNC_NETWORK_IOSQL Server produced rows faster than the client consumed themClient (dashboard/pipeline) is slow processing results, or network latency
LCK_M_S / LCK_M_XQuery was blocked by another session’s lockContention — check for long-running transactions, consider RCSI
MEMORY_GRANT_QUEUEQuery waited in the memory grant queue before it could startToo many concurrent queries requesting sort/hash memory — reduce parallelism or add RAM
SOS_SCHEDULER_YIELDCPU was overloaded, query had to yield its time sliceCPU pressure — optimize the query or add vCPUs

Correlate per-query waits with server-wide cumulative waits

Per-query waits tell you “this specific query waited on X.” Server-wide waits (from sys.dm_os_wait_stats) tell you “the entire workload is bottlenecked on X.” Use both:

  1. Check server-wide wait stats → identify the category (I/O? locks? CPU?)
  2. Find the specific queries contributing → per-query wait stats in execution plans
  3. Fix the worst offenders

Critical Plan Operators for Batch Workloads

When reviewing execution plans for data pipeline queries (bronze→silver→gold MERGE operations, bulk INSERTs, aggregation jobs), certain operators have specific expectations. The same operator can be healthy on one query shape and a strong red flag on another, so the reader needs a quick reference that pairs each operator with its expected pipeline context.

SQL Server | pipeline operators | expected and red-flag reference

The H4 below is a single reference table that maps the operators you will encounter most often on stoxx-style pipeline workloads to the query shape where they are expected and to the scenarios where their presence should trigger further investigation.

Map plan operators to pipeline expectations and red flags

Reference table for interpreting each operator in the context of bronze/silver/gold pipeline queries.

OperatorExpected in PipelineRed Flag
Clustered Index InsertNormal for bulk INSERT—
Clustered Index ScanExpected for full-table MERGE sourceRed flag if appearing in WHERE-filtered queries
Table ScanNever acceptable on silver/gold tablesAdd clustered index
Hash Match (Inner Join)Normal for large MERGE joinsCheck memory grant — spills to TempDB are costly
SortOften needed for MERGEWatch for sort spills (yellow warning in plan)
Nested LoopsGood for small lookupsRed flag if outer input is large (> 1000 rows)
Key LookupCovering index missing columnsAdd INCLUDE columns to index
Parallelism (Gather Streams)Normal for large operationsCheck for skewed thread distribution

Implicit Conversions — The Silent Performance Killer

The most common silent performance killer in Python-to-SQL pipelines. Python’s pyodbc sends parameters as NVARCHAR by default, but SQL columns may be VARCHAR. This forces a per-row conversion and prevents index seeks.

SQL Server | implicit conversions | detect and fix

Implicit conversions silently break index usability and are common on pipelines that send string parameters from a Python client. The two H4s below first detect the problem server-side by scanning cached plans for the PlanAffectingConvert warning, then fix the root cause client-side in the pyodbc driver so the conversion never reaches SQL Server in the first place.

Detect implicit conversions in cached plan XML

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 20
    st.text AS query_text,
    qp.query_plan,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_reads
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qp.query_plan.exist('//Warnings/PlanAffectingConvert') = 1
ORDER BY qs.total_logical_reads DESC;
query_textexecution_countavg_reads
SET NOCOUNT ON; SELECT TOP 1 CAST(qp.query_plan AS nvarchar(max)) AS query_plan, qs.execution_count, CAST(qs.total_logical_reads / NULLIF(qs.execution_count,0) AS bigint) AS avg_reads, CAST(qs.total_worker_time / NULLIF(qs.execution_count,0 ...113787
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT SUBSTRING(st.text, 1, 200) AS query_text, ws.value('@WaitType', 'varchar(100)') AS wait_type, ws.value('@WaitTimeMs', 'bigint') AS wai ...113579
(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000))SELECT clmns.column_id AS [ID], clmns.name AS [Name], ISNULL(dc.Name, N'') AS [DefaultConstraintName], clmns.is_nullable AS [Nullable], CAST(ISNULL(cik.index_ ...2919
DECLARE @msticks bigint, @mstickstime datetime, @LastHour datetime SELECT @mstickstime = GETDATE(), @msticks = ms_ticks from sys.dm_os_sys_info SELECT @LastHour = DATEADD(HOUR, -1, @mstickstime); ...3481
SELECT TOP 20 qsqt.query_sql_text, qsp.query_plan, qsrs.avg_rowcount AS actual_avg_rows, qsrs.avg_logical_io_reads, qsrs.count_executions, qsrs.avg_duration / 1000 AS avg_ms FROM sys.query_store_runtime_stats qsrs JO ...11395

This output is useful mostly as a caution about scope. The XML warning filter is broad enough to surface internal and diagnostic statements, not just your application queries. The avg_reads values are also large in the first rows, which means these warning-bearing statements touched many pages on average. That does not automatically mean the conversion warning caused all of the cost, but it does tell you these are expensive enough rows to justify inspection once you narrow the text filter to your real workload.

ColumnValueWatchMeaningImplication
execution_count1DependsOnly one execution contributed to the row.Enough to inspect the statement, weak for trend analysis.
execution_count>10✅Repeated executions are amplifying the effect of the conversion issue.Fixing one bad conversion can pay off many times.
avg_reads481 to 1395DependsModerate to high logical I/O in this sample.Worth inspecting if these are application queries rather than tooling queries.
avg_reads>10000❌Very heavy page-touch footprint.If the query is real workload SQL, the conversion warning deserves urgent review.

Fix NVARCHAR→VARCHAR conversion at the pyodbc driver

The detection query above finds implicit conversions after they have already reached the server. The root fix is to stop them ever reaching the server by telling pyodbc to send VARCHAR bytes when the target column is VARCHAR, instead of defaulting to NVARCHAR. The two short snippets below cover the two places this matters: the connection-level encoding setup, which affects every statement on the connection, and the per-cursor fast_executemany path used during bulk INSERT.

Configure the pyodbc connection encoding and enable fast_executemany to eliminate implicit NVARCHAR→VARCHAR conversion at the driver.

conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
conn.setencoding(encoding='utf-8')
 
cursor.fast_executemany = True
cursor.executemany("INSERT INTO ...", rows)

Full SARGability Reference

For the complete list of SARGable vs. non-SARGable patterns, the detection query, and the data pipeline quick-reference table, see sargable-queries.

Parameter Sniffing

Parameter sniffing is less common in pipelines (queries use literal values, not stored procedures), but it affects parameterized queries from pyodbc.

SQL Server | parameter sniffing | CPU-variance detection

Parameter sniffing shows up as the same cached statement consuming wildly different amounts of CPU across executions. The H4 below uses sys.dm_exec_query_stats to rank cached statements by CPU variance, which is a reliable first-pass heuristic for finding parameter-sensitive statements before deciding which mitigation to apply.

Rank cached statements by CPU variance across executions

SELECT
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
    qs.min_worker_time / 1000 AS min_cpu_ms,
    qs.max_worker_time / 1000 AS max_cpu_ms,
    CAST((qs.max_worker_time - qs.min_worker_time) * 1.0 /
         NULLIF(qs.min_worker_time, 0) AS DECIMAL(10,1)) AS variance_ratio,
    SUBSTRING(st.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 10
  AND qs.max_worker_time > qs.min_worker_time * 10
ORDER BY variance_ratio DESC;
execution_countavg_cpu_msmin_cpu_msmax_cpu_msvariance_ratioquery_text
2900136.8(@planId bigint, @queryId bigint, @replicaGroupId bigint, @startTime datetimeoffset, @APRC_SUM_SQUARE_CPU_SCALE int, @accountAbortedFlag bit,@sumCountExecutions bigint OUTPUT,@sumCountAborted bigint O
2900136.2(@planId bigint, @queryId bigint, @replicaGroupId bigint, @startTime datetimeoffset, @APRC_SUM_SQUARE_CPU_SCALE int, @accountAbortedFlag bit,@sumCountExecutions bigint OUTPUT,@sumCountAborted bigint O

This output is a heuristic shortlist, not a verdict. A variance_ratio above 30x means the worst observed CPU time was more than thirty times the best observed CPU time for the same cached statement, which is exactly the kind of spread that makes parameter sensitivity plausible. But the sample rows here are internal Query Store procedures, not business SQL, so the correct conclusion is “the heuristic works” rather than “these two rows prove your application has parameter sniffing.”

ColumnValueWatchMeaningImplication
execution_count<=10DependsVery little history.Variance can be noise, which is why the query excludes these rows.
execution_count>10✅Enough executions to make variance more meaningful.Better candidate set for investigation.
variance_ratio<3x✅Low CPU spread across executions.Usually not a strong parameter-sensitivity signal.
variance_ratio3x-10xDependsNoticeable variability.Worth watching if the query is business-critical.
variance_ratio>10x❌Strong variability.Good candidate for plan inspection, parameter review, or PSP analysis.
query_textInternal system or tooling SQLDependsThe heuristic found a variable statement, but not your app workload.Narrow the text filter before drawing application conclusions.

SQL Server | query hints | traditional PS mitigations

Before SQL Server 2022 introduced PSP, the two standard mitigations were statement-level query hints: OPTIMIZE FOR UNKNOWN compiles a single generic plan based on average statistics, and OPTION (RECOMPILE) rebuilds the plan on every execution. The H4 below shows the exact syntax for both hints and explains when each one is the right trade-off.

Apply OPTIMIZE FOR UNKNOWN and OPTION (RECOMPILE)

Both hints trade plan quality for predictability in different ways

OPTIMIZE FOR UNKNOWN can protect you from a bad sniffed parameter, but it does so by asking for a generic plan that may be mediocre for every parameter value. OPTION (RECOMPILE) does the opposite: it can produce an excellent plan for the current value, but it adds compile CPU every time the statement runs and prevents normal plan reuse for that statement.

Choose the narrowest mitigation that fits the workload

Use OPTIMIZE FOR UNKNOWN when one stable reusable plan is good enough across the parameter range. Use RECOMPILE when executions are infrequent and per-execution plan quality matters more than compile overhead. Measure both against the unhinted version before keeping either.

DECLARE @index varchar(20) = 'euro_stoxx_50';
DECLARE @from date = '2026-04-01';
 
SELECT * FROM silver.signals_daily
WHERE _index = @index AND signal_date >= @from
OPTION (OPTIMIZE FOR UNKNOWN);
 
DECLARE @idx varchar(20) = 'euro_stoxx_50';
 
SELECT * FROM gold.scores_daily
WHERE _index = @idx
OPTION (RECOMPILE);
id_indexsymbolsignal_datecurrent_pricetarget_median_priceupside_potential
3002euro_stoxx_50ASML.AS2026-04-081113.814500.3018
3003euro_stoxx_50MC.PA2026-04-08466.856100.3066
3004euro_stoxx_50RMS.PA2026-04-081648.522950.3922
3005euro_stoxx_50OR.PA2026-04-08350.8407.50.1616
3006euro_stoxx_50SAP.DE2026-04-08145.222280.57
id_indexsymbolscore_datesectorcomposite_scorecomposite_rankcurrent_price
149euro_stoxx_50ABI.BR2026-03-04Consumer Defensive0.4069564.48
150euro_stoxx_50AD.AS2026-03-04Consumer Defensive0.27551241.42
151euro_stoxx_50ADS.DE2026-03-04Consumer Cyclical0.031524141.8
152euro_stoxx_50ADYEN.AS2026-03-04Technology0.049423957.6
153euro_stoxx_50AI.PA2026-03-04Basic Materials0.097222172.36

These two tables are only rowset previews. The first table is the statement compiled with OPTIMIZE FOR UNKNOWN; the second is the statement compiled with OPTION (RECOMPILE). Neither table proves anything about plan quality on its own. Their role is to show that both statements executed successfully against real stoxx data. The actual lesson is the compilation policy behind them: OPTIMIZE FOR UNKNOWN asks for a generic reusable plan, while RECOMPILE asks SQL Server to build a fresh plan for the current parameter value each time.

When to Use RECOMPILE

Use OPTION (RECOMPILE) sparingly — only on queries that run a few times per pipeline (not thousands of times in a loop). Recompilation has CPU overhead.

SQL Server | PSP | 2022 Parameter Sensitive Plan Optimization

SQL Server 2022 ships Parameter Sensitive Plan Optimization (PSP), an Intelligent Query Processing feature that lets the optimizer keep multiple cached plan variants for the same parameterized statement and select the right one at execution time based on the input parameter value. The two H4s below cover the full PSP operational lifecycle: enabling it at the database scope so eligible statements start getting multiple plan variants, and then narrowly disabling it on one specific regressing statement while leaving the database-wide setting on.

SQL Server 2022 introduces Parameter Sensitive Plan (PSP) Optimization, a built-in solution to parameter sniffing for queries over non-uniform data distributions. Instead of caching a single plan per parameterized query, PSP creates a dispatcher plan that selects among multiple plan variants at runtime based on the actual parameter value.

The dispatcher evaluates the parameter against boundary values derived from the statistics histogram, then routes execution to the cached variant optimized for that value range. For example, a query filtering on _index might have one variant with a Nested Loops plan for selective values (few rows) and another with a Hash Join plan for non-selective values (many rows).

Enable PSP at the database scope

PSP is enabled by default at compatibility level 160. It applies automatically to eligible parameterized queries — no query hints needed. Query Store is recommended (not required) for full observability.

Database-scoped feature with workload-wide effects

This setting affects future compilations across the database, not just one query. PSP is usually helpful on skewed data distributions, but it can increase the number of cached plans for one statement and change how you troubleshoot plan cache behavior. If you are testing regressions, compare before and after under the same workload.

Make the prerequisite explicit, then validate on real skewed queries

In this note, the command is mainly there to make the lab reproducible. In practice, keep PSP on when compatibility level 160 and parameter sniffing are both intended, then validate its effect on the parameterized statements that actually show skew.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

Opt one statement out of PSP with a query hint

If PSP causes plan cache bloating or unexpected regressions, disable it at the query level without affecting other queries:

PSP changes plan-cache behavior for the whole database

Enabling PSP is usually beneficial on skewed parameterized workloads, but it allows SQL Server to keep multiple plan variants for qualifying statements. That can change memory use, troubleshooting patterns, and the number of plans you see for one query. Do not assume PSP is active just because this setting is on: PARAMETER_SNIFFING must also be on, compatibility level must support it, and the statement must qualify.

Enable globally, disable narrowly

The preferred pattern is to keep PSP enabled at the database scope, observe whether the workload improves, and use DISABLE_PARAMETER_SENSITIVE_PLAN only on the specific statement that regresses.

DECLARE @index varchar(20) = 'euro_stoxx_50';
 
SELECT * FROM silver.signals_daily
WHERE _index = @index
OPTION (USE HINT('DISABLE_PARAMETER_SENSITIVE_PLAN'));
id_indexsymbolsignal_datecurrent_pricetarget_median_priceupside_potential
1euro_stoxx_50ASML.AS2026-03-041199.814500.2085
2euro_stoxx_50MC.PA2026-03-04507.46400.2613
3euro_stoxx_50RMS.PA2026-03-04193023550.2202
4euro_stoxx_50OR.PA2026-03-04374.34100.0954
5euro_stoxx_50SAP.DE2026-03-04167.382550.5235

This rowset again only proves that the statement ran and returned the intended euro_stoxx_50 rows. The meaning of DISABLE_PARAMETER_SENSITIVE_PLAN is not in the row values. It is in the plan-cache behavior: after applying the hint, the statement should stop participating in PSP multi-variant plan selection even if PSP remains enabled at the database level.

PSP Interactions

PSP is automatically disabled when trace flag 4136 is active or when PARAMETER_SNIFFING = OFF is set at the database level. If you have either of these legacy mitigations in place, PSP will not activate even at compatibility level 160.

Remove legacy parameter sniffing workarounds to let PSP run

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;

Batch Mode Execution

SQL Server 2022 supports batch mode on rowstore (no columnstore index required). This dramatically accelerates analytical queries (groupby, window functions).

SQL Server | batch mode | check and enable on rowstore

Batch mode on rowstore processes approximately 900 rows at a time in a columnar format rather than one row at a time, dramatically accelerating window functions, aggregations, and analytical scans. The two H4s below first check which of your existing cached plans actually ran in batch mode, then show how to explicitly request batch mode on a single statement with the ENABLE_BATCH_MODE_ON_ROWSTORE hint.

Check whether cached plans ran in batch mode

This query identifies your most CPU-intensive gold-layer queries and retrieves their plans. In the XML plan output, look for ActualExecutionMode="Batch" vs "Row" on each operator — batch mode processes ~900 rows at a time in a columnar format, while row mode processes one row at a time.

SELECT
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    SUBSTRING(st.text, 1, 200) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE st.text LIKE '%gold.%'
ORDER BY qs.total_worker_time DESC;
execution_counttotal_cpu_msquery_text
1381DROP TABLE IF EXISTS dbo.demo_signals_daily; SELECT * INTO dbo.demo_signals_daily FROM silver.signals_daily; DROP TABLE IF EXISTS dbo.demo_eurostoxx50_ohlcv; SELECT * INTO dbo.demo_eurostoxx50_ohlcv F
145DROP TABLE IF EXISTS dbo.demo_signals_daily; SELECT * INTO dbo.demo_signals_daily FROM silver.signals_daily; DROP TABLE IF EXISTS dbo.demo_eurostoxx50_ohlcv; SELECT * INTO dbo.demo_eurostoxx50_ohlcv F
112DROP TABLE IF EXISTS dbo.demo_signals_daily; SELECT * INTO dbo.demo_signals_daily FROM silver.signals_daily; DROP TABLE IF EXISTS dbo.demo_eurostoxx50_ohlcv; SELECT * INTO dbo.demo_eurostoxx50_ohlcv F
19DROP TABLE IF EXISTS dbo.demo_signals_daily; SELECT * INTO dbo.demo_signals_daily FROM silver.signals_daily; DROP TABLE IF EXISTS dbo.demo_eurostoxx50_ohlcv; SELECT * INTO dbo.demo_eurostoxx50_ohlcv F
12DROP TABLE IF EXISTS dbo.demo_signals_daily; SELECT * INTO dbo.demo_signals_daily FROM silver.signals_daily; DROP TABLE IF EXISTS dbo.demo_eurostoxx50_ohlcv; SELECT * INTO dbo.demo_eurostoxx50_ohlcv F

This output is useful mainly as a gotcha. The text filter is broad enough to surface demo DDL that references gold tables, not just analytical SELECT statements. The sample rows therefore tell you more about the weakness of the text filter than about batch mode itself. total_cpu_ms is total accumulated CPU time since compilation, not average per execution, and execution_count = 1 on every displayed row means each row is currently just a single-use cached statement.

ColumnValueWatchMeaningImplication
execution_count1DependsOnly one execution contributed to the CPU total.The row is a weak candidate for trend analysis.
execution_count>1✅Repeated cached use.Better candidate set for identifying true high-CPU analytical queries.
total_cpu_msHigh but tied to DDL textDependsCPU was consumed, but maybe by setup commands rather than analytics.Tighten the text filter before drawing batch-mode conclusions.

Request batch mode on one statement with a hint

Database compatibility changes are broad, and the hint is only a request

ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 160 affects the optimizer behavior of the entire database, not just this query. Test compatibility-level changes carefully because they can change many plans at once. Also, ENABLE_BATCH_MODE_ON_ROWSTORE does not guarantee batch mode; it only encourages the optimizer to consider it when the statement is eligible.

Test the query hint before committing to database-wide changes

In a lab, setting compatibility level explicitly makes the prerequisite reproducible. In production, validate compatibility-level changes separately, and use the actual plan to confirm whether operators really ran in batch mode.

ALTER DATABASE stoxx SET COMPATIBILITY_LEVEL = 160;
 
DECLARE @date date = '2026-04-08';
 
SELECT _index, score_date,
    AVG(momentum_score) OVER (PARTITION BY _index) AS avg_momentum
FROM gold.scores_daily
WHERE score_date = @date
OPTION (USE HINT('ENABLE_BATCH_MODE_ON_ROWSTORE'));
statusmessage
error'ENABLE_BATCH_MODE_ON_ROWSTORE' is not a valid hint.

This means the hint was not accepted by the current engine or syntax combination, so the statement did not produce a batch-mode demo plan here. Treat that as real environment evidence rather than as a documentation typo: not every hint that appears in blog posts or older builds will be valid on the target instance.

ColumnValueWatchMeaningImplication
statuserror❌SQL Server rejected the statement or hint.The example did not produce a batch-mode plan and needs an alternate method.
statussuccess or row output✅The statement executed.You can then inspect the returned plan for ActualExecutionMode.

Intelligent Query Processing

Intelligent Query Processing (IQP) is a family of automatic optimization features introduced incrementally across SQL Server 2017, 2019, and 2022. These features allow the query processor to adapt its decisions at runtime — correcting bad cardinality estimates, adjusting memory grants, and selecting better join strategies without manual intervention. Batch Mode on Rowstore and Parameter Sensitive Plan Optimization (covered in dedicated sections above) are part of this family.

The features below are the remaining IQP capabilities that directly affect how execution plans are generated and adapted. Each requires a minimum compatibility level and some require Query Store enabled in READ_WRITE mode.

SQL Server | IQP | feature matrix and compatibility requirements

Intelligent Query Processing spans four SQL Server releases, and each feature has its own minimum compatibility level and Query Store prerequisite. The single H4 below maps every current IQP feature to the version and settings that enable it so you can tell at a glance whether a feature is available on the target database.

Map every IQP feature to version, compat level, and QS requirement

FeatureVersionCompat levelQS requiredWhat it does
Adaptive JoinsSS 2017+140NoSelects Hash or Nested Loops at runtime based on actual input rows
Interleaved Execution (MSTVFs)SS 2017+140NoUses actual multi-statement TVF cardinality instead of fixed guess of 100
Memory Grant Feedback (batch mode)SS 2017+140NoAdjusts memory grant up/down based on spill/waste history
Memory Grant Feedback (row mode)SS 2019+150NoExtends batch-mode MGF to all rowstore queries
Scalar UDF InliningSS 2019+150NoInlines scalar UDFs as relational expressions — UDF logic visible in plan
Table Variable Deferred CompilationSS 2019+150NoUses actual table variable cardinality at first compilation (not fixed estimate of 1)
MGF Percentile + PersistenceSS 2022+160Yes90th-percentile algorithm over recent history; persisted across cache evictions
DOP FeedbackSS 2022+160YesAuto-tunes degree of parallelism per query
CE FeedbackSS 2022+160YesCorrects CE model assumptions; persisted via QS hints
PSP OptimizationSS 2022+160RecommendedMultiple cached plan variants per parameterized statement
Optimized Plan ForcingSS 2022+160YesStores compilation replay hints in QS to speed up forced plan recompilation

SQL Server | Adaptive Joins | runtime join type selection

Adaptive Joins are the SQL Server 2017+ IQP feature that defers the join-type decision from compile time to runtime. The H4 below explains how to read the adaptive operator in a plan and how to confirm which join strategy was actually used for a given execution via the ActualJoinType property in the plan XML.

Read adaptive join behavior from the execution plan

Adaptive Joins dynamically choose between Hash Match and Nested Loops at runtime. The optimizer sets an adaptive threshold — a row count boundary — during compilation. During execution, if the actual row count from the build input exceeds the threshold, the join executes as a Hash Match; if below, it switches to Nested Loops. Rows already read by the Hash build phase are reused, so there is no duplicate I/O.

In the execution plan, an Adaptive Join appears as a single operator with three child branches: the Hash probe phase, the Nested Loops seek, and the adaptive threshold node. The ActualJoinType property in the plan XML shows which strategy was actually used at runtime.

SQL Server | Memory Grant Feedback | runtime grant adjustment

Memory Grant Feedback (MGF) is the IQP feature that tunes the per-query memory grant based on execution history rather than relying on the static compile-time estimate. The H4 below enables the SQL Server 2022 refinements that make MGF more stable and durable: percentile-based grant calculation and persistence through Query Store.

Enable percentile-based and persistent memory grant feedback

When the optimizer compiles a plan, it estimates how much memory the query needs for sort and hash operations (the memory grant). If the estimate is too low, data spills to TempDB — visible as yellow warning icons on Sort and Hash Match operators. If too high, memory is wasted and concurrent queries may queue in the RESOURCE_SEMAPHORE wait.

Memory Grant Feedback automatically adjusts the grant based on execution history:

  • Under-grant detected (spill occurred) → next execution gets a larger grant
  • Over-grant detected (< 50% used) → next execution gets a smaller grant
  • Self-disabling → if adjustments cause oscillation (alternating spill/waste), MGF stops adjusting and falls back to the optimizer’s estimate

SQL Server 2022 adds percentile mode (uses the 90th percentile of recent grant history plus a buffer, instead of just the last execution) and persistence (feedback survives plan cache eviction and server restarts via Query Store).

Enable percentile-grant and persistence for Memory Grant Feedback at the database scope.

Adaptive behavior appears only after repeated executions

These settings do not speed up the next single execution by themselves. Memory Grant Feedback needs qualifying repeated executions before you see adjusted grants, and persistence depends on Query Store being enabled and writable. On volatile or one-off queries, you may not observe any visible effect.

Enable it where repeated analytical queries matter

This is most useful on workloads where the same statements run many times with different parameter values or changing row counts. Verify the behavior with actual plans and Query Store metadata, not by assuming the feature fired.

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = ON;
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = ON;

SQL Server | DOP Feedback | runtime parallelism adjustment

DOP Feedback is the SQL Server 2022 IQP feature that auto-tunes the effective degree of parallelism for individual queries when the optimizer’s compile-time DOP produces skewed thread distribution or excessive CXPACKET waits. The H4 below enables the feature at the database scope; the actual effect becomes visible only after repeated executions of qualifying parallel statements.

Enable DOP Feedback at the database scope

DOP (Degree of Parallelism) Feedback automatically tunes the parallelism degree for individual queries based on runtime feedback. If a query’s parallel execution wastes CPU due to skewed thread distribution or excessive CXPACKET waits, DOP Feedback reduces the DOP for subsequent executions. Feedback is persisted in Query Store.

Parallelism may change across executions

Once DOP Feedback is enabled, the same query can receive a different effective DOP on later executions. That is the point of the feature, but it also means troubleshooting becomes more dynamic: one execution might not match the next. As with other SQL Server 2022 feedback features, you need repeated qualifying executions before you will see an effect.

Observe it on stable, repeatable workloads

Use this on workloads where the same expensive parallel statements run often enough for feedback to converge. Confirm changes in actual plans or Query Store feedback metadata instead of assuming the database-scoped setting alone changed performance.

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;

IQP and Execution Plan Visibility

All IQP adaptations are visible in the actual execution plan. Adaptive Joins show AdaptiveJoinType, Memory Grant Feedback shows IsMemoryGrantFeedbackAdjusted, and DOP Feedback adjustments appear in the plan’s MemoryGrantInfo and RuntimeCountersPerThread nodes. Always use actual plans (not estimated) to observe IQP behavior — estimated plans show only the pre-adaptation compile-time decisions.

Missing Indexes for Pipeline Queries

SQL Server surfaces missing index recommendations directly in the execution plan (yellow warning icon) and stores them in DMVs. Because the real stoxx tables already have constraints and indexes, the lab approach is to create disposable heap copies in the dbo schema first, then apply representative clustered and nonclustered index DDL to those lab copies so you can observe the plan differences safely without touching the production-shaped source tables.

SQL Server | demo tables | build disposable heap copies and add indexes

The two H4s below form the full missing-index lab workflow: first build a set of disposable heap copies of the bronze/silver/gold tables using SELECT INTO, then add representative clustered and covering nonclustered indexes so you can re-run the same queries and observe how the execution plans change.

Build disposable heap copies of bronze, silver, and gold tables

This setup batch creates five dbo.demo_* heap copies you can experiment on freely. It is intentionally destructive on rerun: it drops and recreates the demo tables every time so the lab starts from a clean baseline. It never touches the original source tables, which stay read-only for the duration of the batch.

Create five disposable heap copies in the dbo schema from the real bronze, silver, and gold tables.

Rerunnable setup that deletes previous demo copies

This batch drops and recreates the dbo.demo_* tables each time it runs. That is intentional for a clean lab, but it also means any indexes, constraints, or data changes you previously added to those demo tables will be lost. Do not point this pattern at real business tables.

Safe lab pattern because it leaves source tables untouched

The original bronze, silver, and gold tables are read only in this batch. Use the disposable dbo.demo_* copies for plan experiments, then drop or recreate them freely as you iterate.

DROP TABLE IF EXISTS dbo.demo_signals_daily;
SELECT * INTO dbo.demo_signals_daily FROM silver.signals_daily;
 
DROP TABLE IF EXISTS dbo.demo_eurostoxx50_ohlcv;
SELECT * INTO dbo.demo_eurostoxx50_ohlcv FROM silver.eurostoxx50_ohlcv;
 
DROP TABLE IF EXISTS dbo.demo_scores_daily;
SELECT * INTO dbo.demo_scores_daily FROM gold.scores_daily;
 
DROP TABLE IF EXISTS dbo.demo_index_performance;
SELECT * INTO dbo.demo_index_performance FROM gold.index_performance;
 
DROP TABLE IF EXISTS dbo.demo_pulse_tickers;
SELECT * INTO dbo.demo_pulse_tickers FROM bronze.pulse_tickers;

Add representative clustered and covering nonclustered indexes

With the heap copies in place, the next step is to add the specific clustered and nonclustered indexes that mirror the common lookup patterns for each table. This is the step you re-run while iterating on index designs: run the heap-copy batch above to reset, then run this batch with whichever index shape you want to test next.

Add representative clustered and covering nonclustered indexes to the dbo.demo_* tables.

Index creation changes the demo tables and can fail if the copied data is not unique

These CREATE UNIQUE CLUSTERED INDEX statements assume the copied demo data is unique on the chosen key columns. If the source data contains duplicates, SQL Server will reject the index creation. Even on demo tables, index builds consume I/O and log space, so treat them as real DDL rather than as a harmless display command.

Good lab step because the write scope is isolated

This is the right place to demonstrate heap-to-index plan changes: the DDL touches only the disposable dbo.demo_* tables, and the selected keys mirror common lookup patterns from the real stoxx tables.

CREATE UNIQUE CLUSTERED INDEX CIX_demo_signals_daily
ON dbo.demo_signals_daily (_index, symbol, signal_date);
 
CREATE UNIQUE CLUSTERED INDEX CIX_demo_eurostoxx50_ohlcv
ON dbo.demo_eurostoxx50_ohlcv (symbol, [date]);
 
CREATE UNIQUE CLUSTERED INDEX CIX_demo_scores_daily
ON dbo.demo_scores_daily (_index, score_date, symbol);
 
CREATE UNIQUE CLUSTERED INDEX CIX_demo_index_performance
ON dbo.demo_index_performance (_index, perf_date);
 
CREATE NONCLUSTERED INDEX IX_demo_pulse_tickers_index
ON dbo.demo_pulse_tickers (_index) INCLUDE (symbol, rank, activity_score, volume_surge);

Missing Index DMVs

For the systematic missing index detection query using sys.dm_db_missing_index_details, see index-types-and-strategy > Missing Index DMV Queries.

Quick Wins for Query Performance (Ordered by Impact)

These are the highest-impact, lowest-effort optimizations to apply after reading execution plans. Each addresses a common pattern seen in pipeline and dashboard workloads.

SQL Server | high-impact fixes | detection and implementation

The four H4s below are the highest-impact, lowest-effort optimizations you can apply after reading execution plans for a pipeline workload. Each one pairs a detection query — something you run to find out whether the fix is needed — with the corresponding implementation command. The order follows the usual impact ranking on stoxx-style workloads: heap-to-clustered conversion, enabling row-versioned read committed, page compression, and finally statistics refresh after bulk loads.

OptimizationEffortImpactWhen to Apply
Clustered indexes on all tablesLowHighImmediately if any heaps exist
OPTION (RECOMPILE) on pipeline queriesLowMediumIf you see parameter sniffing issues
Enable RCSILowHighImmediately — eliminates reader/writer blocking
Covering indexesMediumMediumWhen dashboard queries show Key Lookups
Page compression on gold tablesMediumMediumWhen buffer pool starts filling up
Statistics update after loadsLowHighAdd to pipeline post-load step

Detect populated heap tables with no clustered index

SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS table_name,
    p.rows
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id = 0
WHERE p.rows > 0
ORDER BY p.rows DESC;
table_namerows
dbo.demo_pulse_tickers40

This means only one populated heap was found in the current database snapshot: dbo.demo_pulse_tickers, with about 40 rows. Because the row count is tiny, the immediate cost is small, but it is still a useful reminder that index_id = 0 identifies heap storage and that larger heaps would usually be early clustered-index candidates.

Enable row-versioned read committed isolation (RCSI)

Database-wide concurrency change that needs exclusive access

READ_COMMITTED_SNAPSHOT ON changes how all future read-committed statements in the database behave. Enabling it requires SQL Server to obtain exclusive access during the transition, so the command can fail while other sessions are connected. It also shifts read consistency to row versioning in tempdb, which increases tempdb usage and should be evaluated deliberately before changing a production database.

Enable it during a planned change window after checking tempdb

Use the first SELECT to verify the current state, close or drain other sessions before changing the setting, and treat this as a database-level operational decision rather than a casual troubleshooting toggle.

SELECT name, is_read_committed_snapshot_on
FROM sys.databases WHERE name = 'stoxx';
 
ALTER DATABASE stoxx SET READ_COMMITTED_SNAPSHOT ON;
nameis_read_committed_snapshot_on
stoxx0

This is the prechange state check. name = stoxx confirms you are inspecting the intended database, and is_read_committed_snapshot_on = 0 means row-versioned read committed isolation was still off when the output was captured. In that state, normal read-committed readers still use shared locks and can participate in reader-writer blocking.

ColumnValueWatchMeaningImplication
is_read_committed_snapshot_on0DependsRCSI is off.Good if you are demonstrating the default locking behavior before a change.
is_read_committed_snapshot_on1✅RCSI is on.Read committed readers use row versions instead of shared locks.

Estimate and apply page compression on a large table

Estimation is lightweight; rebuild is not

sp_estimate_data_compression_savings is a planning step, but ALTER INDEX ... REBUILD WITH (DATA_COMPRESSION = PAGE) is a real maintenance operation that consumes CPU, I/O, log space, and potentially long-running locks depending on edition and options. Do not treat the estimate and the rebuild as equally safe.

Separate the decision from the maintenance window

Run the estimate first, review the savings, then schedule the rebuild when the logging, blocking, and elapsed-time impact are acceptable for that table and environment.

EXEC sp_estimate_data_compression_savings
    @schema_name = 'gold',
    @object_name = 'index_performance',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = 'PAGE';
 
ALTER INDEX ALL ON gold.index_performance
REBUILD WITH (DATA_COMPRESSION = PAGE);
object_nameschema_nameindex_idpartition_numbersize_with_current_compression_setting(KB)size_with_requested_compression_setting(KB)sample_size_with_current_compression_setting(KB)sample_size_with_requested_compression_setting(KB)
index_performancegold11672384728416
index_performancegold21200120232144

This procedure estimates compression savings without changing the index. PAGE compression would likely shrink gold.index_performance materially: for index_id = 1, estimated size drops from 672 KB to 384 KB, and for index_id = 2, from 200 KB to 120 KB. That is a meaningful percentage reduction, but the decision still depends on whether the CPU overhead of compressed access is acceptable for this workload.

ColumnValueWatchMeaningImplication
index_id0DependsHeap.Compression behavior and maintenance patterns differ from indexed storage.
index_id1✅Clustered index.This is usually the most important row because it covers the table’s base storage.
index_id>1DependsNonclustered index.Savings matter, but usually after the clustered structure.
partition_number1✅First partition, or the only partition on a nonpartitioned object.The estimate is easy to interpret because no partition spread is involved.
size_with_requested_compression_setting(KB) lower than current size✅Compression is likely to save space.Consider whether the saved memory and I/O justify the CPU tradeoff.
size_with_requested_compression_setting(KB) higher than current size❌Compression would make the object larger.Do not enable compression for that structure without a very specific reason.

Refresh statistics broadly and with full scan after bulk loads

Statistics refreshes can trigger recompiles and heavy reads

sp_updatestats and manual UPDATE STATISTICS change optimizer metadata, which can invalidate cached plans and trigger recompilation on later executions. FULLSCAN in particular can be expensive on large tables because SQL Server reads the whole object to build the histogram. Do not run fullscan updates blindly on large production tables during busy periods.

Use the broad tool broadly, and reserve FULLSCAN for targeted cases

sp_updatestats is the lower-effort maintenance option after general data change. Keep UPDATE STATISTICS ... WITH FULLSCAN for tables where estimates are materially wrong and the extra read cost is justified.

EXEC sp_updatestats;
 
UPDATE STATISTICS gold.scores_daily WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS gold.index_performance WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Graph View

Backlinks