In short
- The tool saves legwork. sp_BlitzPlanCompare brings parameters, indexes, statistics, settings, plans and wait states from two servers together in one prioritized list.
- In the lab, it found the right leads: different compile values, changed statistics headers, the first operator with a different row count (500 instead of 3,000) and a current lock.
- A lead is not yet a cause. An unused index got priority 1, and so did a forced plan of an entirely different procedure.
- Only the cross-check proves the cause. At the same runtime value, the plan compiled for a rare customer read about 270,000 pages, the other one about 2,800.
TakeawayA difference is a lead, not a root cause. Only a cross-check turns it into one.
Same query, different speed
The call usually goes like this: “In test this runs in one second, in production it takes a minute.” The SQL text is the same. Where do you start? With the indexes, the statistics, the parameters, the server settings, or a lock that someone is holding right now?
If you investigate this by hand, you collect information in many places: plan cache, index definitions, statistics headers, database options, wait states. And then you lay it all out side by side. That legwork is exactly what sp_BlitzPlanCompare takes off your hands. Brent Ozar introduced the tool in October 2026 as part of the free First Responder Kit (Brent’s introduction). Thanks to Brent and everyone who contributes to the First Responder Kit.
We wanted to know more than “it returns a list”. So in our own lab we created specific differences on purpose and checked which ones the tool finds and how to read them. Along the way, we run into three common misconceptions:
“Whatever is at the top is the cause.” The priority sorts leads. It does not check whether a difference explains the slow execution.
resolved in: Priority is not a diagnosis ↓“Same query, same plan, same work.” The same plan can read very different amounts depending on the parameter.
resolved in: Case 1 ↓“Slower means a worse plan.” A good plan can still wait, for example on a lock.
resolved in: Case 4 ↓
Each of the following cases clears up one of these assumptions.
The model: four levels of a fair comparison
Before we read any findings, we need a framework. An execution plan is the route SQL Server picks for a query: for example, find rows through an index, join tables, add up values. The optimizer picks this route at compile time, with the information it has at that moment. Later the plan is executed, under whatever conditions apply at that point. These are two different points in time.
This gives us four levels at which a fast side and a slow side can differ:
- 1Context Are we really comparing the same statement, in the same database, with the same session settings?
- 2Compilation Did the optimizer have the same ingredients on both sides, that is, parameter values, indexes, statistics, estimation model and options?
- 3Plan Which route did it pick on each side, and how many rows actually flowed through the individual steps?
- 4Runtime conditions Under which circumstances did the plan run: locks, memory grants, CPU, I/O?
The levels build on each other: compilation determines the plan, and the plan determines the work. Runtime conditions act on the duration from the side, without changing the plan. sp_BlitzPlanCompare collects information from all four levels. Our job is still to assign each finding to its level and to ask whether it can explain the slowness we observe. Microsoft’s guide to comparing two servers follows the same logic.
Four levels, two sides
Each level is compared horizontally. Tap a level for examples and the matching cases.
faste.g. test
slowe.g. production
WorkDuration
WorkDuration
1Context Same statement?
Examples: the same SQL text, the same database, the same session settings such as ARITHABORT.
In this article: Different database names · Two cache copies
2Compilation Same ingredients?
Examples: parameter values at compile time, indexes, statistics, estimation model (cardinality estimator), database options.
In this article: Case 1 · Parameter · Case 2 · Statistics · Unused index
3Plan Same route, same row counts?
Examples: index seek with key lookup, or a scan; estimated and actual rows per step.
In this article: Case 3 · Row counts · Lookups or scan
4Runtime conditions Same circumstances?
Examples: locks, memory grant, CPU, I/O.
In this article: Case 4 · Blocking · Three clocks
The first run: take a snapshot, compare on the other side
The procedure lives in the First Responder Kit repository under OptionalScripts. The simplest way is to install it on both instances. On the first side, we name the query in question, here a stored procedure:
-- Side 1 (e.g. production): create the snapshot
-- Before running this in production: read the code and agree on it with operations (see note below)
EXEC master.dbo.sp_BlitzPlanCompare
@StoredProcName = N'dbo.usp_Orders',
@DatabaseName = N'PlanCompareLab';The result column CallStack contains a ready-made call for the other side. It carries an XML snapshot with the plan and metadata. We run this call on the second instance. It compares the snapshot it receives (“Remote”) with its own state (“Local”) and returns two results: the prioritized list of differences and the two plans that were compared. No connection between the servers is needed for this. If you already have a linked server with RPC OUT, you can also compare both sides in a single call; both ways worked in the lab.
One practical pitfall: the call is a long piece of text. SSMS truncates long text cells in the results view. A truncated snapshot is a transfer error, not a finding. So before you copy it, check the limit for text results and follow the approach described in the tool’s help.
The snapshot travels
slow Side 1 · e.g. production
Step 1 Call sp_BlitzPlanCompare for dbo.usp_Orders
Column CallStack: ready-made call with the snapshot
Read directly through the linked server
Truncated in SSMS? Then that is a transfer error, not a finding.
fast Side 2 · e.g. test
Step 2 Run the call from CallStack hereOne call compares both sides
Case 1 · 2CompilationThe first parameter shapes the plan
Setup A table with 100,000 orders. 90,000 of them belong to one big customer; a rare customer has ten. An index on the customer ID helps to find the rows, but SQL Server has to look up the amount being summed in the table for every row it finds (key lookup).
Ten lookups or ninety thousand
Index
Customer ID
Key Lookup
once per match
Table
with amount
Seek + Key Lookup90,000individual lookups
Scan1 ×every page of the table once, however many matches
Work grows with the matches.
For ten matches that is ideal: seek ten times, look up ten times, done. For 90,000 matches, the same route means 90,000 individual lookups. At that point it is cheaper to read the whole table once. So “Index Seek” is not a verdict on quality; what matters is how many rows are processed afterward.
SQL Server compiles a parameterized procedure with the value that arrives with the first call and then reuses the plan. This is called parameter sniffing, and most of the time it is useful: the optimizer plans for a concrete need instead of an average. It becomes tricky when a plan for ten rows is later reused for 90,000 (Microsoft on parameter sensitivity).
Expectation We compile the procedure once for the rare customer and once for the big customer, and then run each plan with both values. Four combinations. Which one reads the most?
Prediction: which combination reads the most?
Your guess: pick the cell that reads the most.
compiled forrare customer@CustomerID = 42 · lookup plan
compiled forbig customer@CustomerID = 1 · scan plan
executed forrare customer10 matches
32logical reads
10 lookups2,790logical reads
one scanexecuted forbig customer90,000 matches
270,158logical reads
90,000 lookups2,790logical reads
one scanObservation The big customer, run with a plan compiled for the rare one, needs about 270,000 logical page accesses. With the plan for the big customer it is about 2,800, almost a hundred times fewer, with an identical result. Conversely, for the rare customer the lookup plan is the cheap one: 32 instead of 2,790 accesses. SQL Server 2022 and 2025 returned exactly the same numbers.
| Compiled for | Executed for | Logical reads (median, 5 warm runs) |
|---|---|---|
| rare customer | rare customer | 32 |
| rare customer | big customer | 270,158 |
| big customer | rare customer | 2,790 |
| big customer | big customer | 2,790 |
Logical reads count the engine’s page accesses, including pages that are already in memory. They are not disk reads. But for comparing the same query on the same data, they are a very stable measure of work.
What the tool showed sp_BlitzPlanCompare reported the different compile values (@CustomerID = 1 versus 42) and added a reproducer with both calls. That is exactly the right lead.
Conclusion Only the cross-matrix test proves the cause: same runtime value, only the compile value changed, work measured. What does not follow is a recipe. OPTION (RECOMPILE), a covering index or another approach has to prove itself against the real workload, the write load and the operational effort.
Two details for practice. The reproducer the tool outputs is a template: it does not restore the original compile state, and running it again may simply reuse the existing plan. On SQL Server 2025 it also wrote the numeric values as (1) and (42); written that way, the call failed with error 102, without the parentheses it ran. And we deliberately used compatibility level 140. Starting with SQL Server 2022, parameter sensitive plan optimization can intentionally keep several plans for different value ranges (Microsoft on PSP); there, multiple plans are not a defect.
Case 2 · 2CompilationUp to date does not mean distributed alike
The optimizer does not know the number of matches in advance; it estimates it with statistics. A statistics object has a header (when it was updated, how many rows, what sample size, how many modifications since then) and a histogram that describes the distribution of the values in at most 200 steps (Microsoft on the statistics header and histogram).
Expectation If we delete data on one side and rebuild the statistics, the tool should notice.
Change On one side, we removed 5,000 rows and then updated the statistics with FULLSCAN.
Observation sp_BlitzPlanCompare showed the new row count (95,000 instead of 100,000) and the changed statistics headers.
Conclusion and limit The tool compares the headers, not the histograms. This is a known limitation of this early version: Brent names it himself in the script header, and the histogram comparison has been deferred to a later version. Two statistics can be equally fresh, describe the same number of rows and still show a completely different distribution. And two different timestamps are not a problem in themselves. The real question is: does the estimate for the affected predicate match the actual number of matches? The header comparison leads you to that question; you answer it in the plan (estimated versus actual rows) or in the histogram.
Illustration: same number of rows, different distribution
fast
- Rows
- 100,000
- Sample
- 100,000
- Updated
- same time
evenly distributed
slow
- Rows
- 100,000
- Sample
- 100,000
- Updated
- same time
heavily skewed: one customer with 90,000 rowsevenly distributed
Lab measurement What the tool actually reported
- 100,000 → 95,000 rows5,000 removed on one side
- FULLSCANstatistics rebuilt
- Header changedrow count and timestamp; sp_BlitzPlanCompare reports it (priority 15)
The estimation model sits on the same level. Compatibility level and cardinality estimator are two different things: with LEGACY_CARDINALITY_ESTIMATION = ON, a database at level 150 ran with CE version 70 in the plan, while the comparison side ran with 140. The tool showed both values. So you cannot tell the active estimation model from the engine version alone (Microsoft on the cardinality estimator).
Case 3 · 3PlanWhere the row counts diverge
Two plans with the same shape can process very different amounts of data. For a diagnosis, what matters is where the row flow first differs, because the steps above it keep working with that row count.
Change Both sides use the same parameterized plan; the range limit is 3,000 on one side and 500 on the other.
Observation sp_BlitzPlanCompare flagged the clustered index seek (node 3), with 500 versus 3,000 actual rows, as the first notable spot, identically on SQL Server 2022 and 2025. The figure shows that the second seek (node 4) also returns 500 instead of 3,000 rows, because the same parameter limits both accesses, and the nested loops above it carries that row count forward. The tool names the first deviation in its traversal order, not every one.
Row flow in two identical plans
Local · @N = 500instance where the comparison runs
Remote · @N = 3000snapshot passed in
Outlined in orange: node 3, the first row count deviation flagged by the tool (500 versus 3,000).
Conclusion This is a real shortcut: instead of comparing two plans node by node, you look at the flagged spot. It tells you where the row counts diverge, not why. A different parameter, different data, a different filter? That is the next question. And the first deviation is not automatically the most expensive step.
For the comparison to hold up, the plans have to match. If they have the same shape, the tool compares the corresponding nodes; if their shapes differ, only the top node. It uses actual row counts only when both sides have runtime values, otherwise estimates. For procedures with several statements, it is therefore worth checking which plans were actually compared.
Case 4 · 4Runtime conditionsSame plan, longer wait
Expectation When a query suddenly takes seconds instead of milliseconds, the obvious suspect is a worse plan.
Change A writing transaction holds a lock on exactly the row our query wants to read.
Observation The reader waits, and the plan stays the same. sp_BlitzPlanCompare showed a blocked session and the current wait type LCK_M_S. The tool’s own finding for LiveState · BlockedSessions makes the point: The slow server may be losing time to blocking, not the plan. Once the writer was rolled back, the reader finished immediately.
One plan, two runs
Plan · the same in both runsClustered Index ScanStream Aggregate
Run A without a lock
Run B a writer holds a lock
LCK_M_S · waits ≈ 2.4 sdoneTime (schematic)
Conclusion No new index will help here. The right next question is about the blocker: who holds the lock, for how long, and why is the transaction so long? (More on this in the guide’s diagnostic path Blocking and timeouts.)
It is similar with other resources. A memory grant is a memory reservation for sorting or hashing. If too little memory is free, the query waits or spills to tempdb; if the grant is far too large, that memory is not available to others (Microsoft on memory grants). CPU time and duration also belong in separate views: with parallel plans, the summed CPU time can be greater than the elapsed time.
Priority is not a diagnosis
The result list sorts differences by an importance that is built into the tool. That is a good place to start, as long as you read every row with two questions: What does the row say? and What does it prove for my query? Four cross-checks from our lab show why the second question is necessary.
Finding cards to turn over
Front: the result row in the original, exactly as the tool outputs it. Turn it over:Back: what it proves and how the cross-check turned out.
-
2CompilationTool output (original)
Priority 1 Category Index Setting Present
Object
PlanCompareLab.dbo.Orders.IX_Orders_Amount_UnusedLocalValue present RemoteValue NULL
Finding An index exists on one server but not the other. The optimizer on the side without it couldn't pick the same plan. Create the missing index there to compare apples to apples.
Unused index
What it provesThe table’s index lists differ. Not that the query uses the index.
Cross-checkThe query does not use the index at all; the finding still came with priority 1.
-
2CompilationTool output (original)
Priority 1 Category Index Setting Present
Object
PlanCompareLab.dbo.Orders.IX_Orders_CustomerLocalValue NULL RemoteValue present
Finding An index exists on one server but not the other. The optimizer on the side without it couldn't pick the same plan. Create the missing index there to compare apples to apples.
Priority 1 Category Index Setting Present
Object
PlanCompareLab.dbo.Orders.IX_Orders_Customer_RenamedLocalValue present RemoteValue NULL
Finding An index exists on one server but not the other. The optimizer on the side without it couldn't pick the same plan. Create the missing index there to compare apples to apples.
Only renamed
What it provesThe index names differ.
Cross-checkTechnically the same index under a new name. Names are compared, not definitions.
-
1ContextTool output (original)
Priority 1 Category Object Setting Present
Object
PlanCompareLab.dbo.OrdersLocalValue NULL RemoteValue present
Finding A table referenced by the plan is missing on one server. This will break the query entirely.
Priority 1 Category Object Setting Present
Object
PlanCompareOther.dbo.OrdersLocalValue present RemoteValue NULL
Finding A table referenced by the plan is missing on one server. This will break the query entirely.
Different database names
What it provesUnder this fully qualified name, the objects do not exist on one side.
Cross-checkThe database had a different name on the other side. This creates apparent gaps for all objects.
-
2CompilationTool output (original)
Priority 1 Category ForcedPlan Setting QueryStore
Object
PlanCompareLabLocalValue plan_id=35 hash=0x01B30C54B38BF8D7 RemoteValue NULL
Finding A forced plan exists on one side. Forced plans override the optimizer and almost always cause cross-server divergence.
Unrelated forced plan
What it provesSome plan is forced in this database.
Cross-checkThe forced plan belonged to an unrelated procedure. Nothing was forced for our query.
- Unused index, priority 1. We created an index that the query does not use at all. The tool reported it with the highest priority. It looks at all indexes of the tables involved.
- Only renamed. A technically identical index under a new name produced two
Index · Presentrows, one for the old name and one for the new one, each present on one side only. Names are compared, not definitions. - Different database names. If the database has a different name on each side, apparent gaps appear for all objects.
- Unrelated forced plan. A plan forced in Query Store for an unrelated procedure in the same database also produced a priority 1 finding. Nothing was forced for our query.
None of this is a criticism of the tool; it is a reading aid. The index finding suggests creating the index on the other side so that both sides are truly comparable. That is advice for the comparison, not a finding about the cause. Before it turns into a CREATE INDEX in production, we check the definition, the usage and the actual workload. And a missing row does not mean that both sides are the same (see the 25% rule in the deep dive).
Deep dive for regular use
Deep dive
For anyone who wants to use the tool regularly. You do not need these sections for the verification path below. Skip to the verification path ↓
Three clocks, three measurement scopes
The tool shows times from three sources, and all of them are in milliseconds. Even so, they measure different things:
- Last actual plan: a single execution, the most recent one available. It can be retrieved from SQL Server 2019 on through
LAST_QUERY_PLAN_STATSwhen the option is turned on. If it is off, the tool uses the estimated plan; the absence of runtime warnings then proves nothing (Microsoft on sys.dm_exec_query_plan_stats). - Query Store: totals over stored time intervals. The tool adds up the plan’s wait times across all available intervals. A period with many executions then has more total wait time, even if each individual execution was faster. The comparison only becomes fair with the same time window and the same number of executions; the wait categories are also coarser than wait types (Microsoft on Query Store waits).
- Session: the connection’s cumulative wait times. In the lab, a session first waited 1.2 seconds with
WAITFORand then started the query, which got stuck on a lock. The tool showed the 1,200 ms of the earlierWAITFOR. They belong to the session’s history, not to the query.
Three clocks
Last actual planone execution
last runQuery Storetotals per interval
Sessioncumulative since login
WAITFOR 1,200 msQuery waits for lockSession wait total: includes the WAITFORA finding with an open cause. For a deliberately blocked run of about 2.4 seconds, the sources on our builds returned the following:
| Source | SQL Server 2022 CU27 (16.0.4295.3) | SQL Server 2025 CU9 (17.0.5005.3) |
|---|---|---|
| DMV duration of the last execution | 2,424 ms | 2,421 ms |
| Explicitly captured actual plan (duration / CPU) | 2,423 / 24 ms | 2,420 / 23 ms |
| Last actual plan (duration / CPU) | 1 / 1 ms | 24 / 2,420 ms |
| Tool output (duration / CPU) | 1 / 1 ms | 2,420 / 24 ms |
On SQL Server 2025, duration and CPU are swapped in the last actual plan. Brent had described exactly this bug in April (Brent’s bug report), and his tool swaps the values back when it reads them; the result is correct. On our 2022 build, by contrast, it shows 1 ms where 2.4 seconds were measured; the swap does not explain that. We have not determined the cause. The practical consequence: record times from the last actual plan together with the build number, and check them against a second source.
Same query, two cache copies
The query hash is a fingerprint of the query logic, the plan hash one of the plan shape. So the questions “same query?” and “same route?” have different identifiers (Microsoft on sys.dm_exec_query_stats). Neither is always enough to identify a cache entry uniquely: with a different ARITHABORT setting, two copies of the same procedure were created, with the same query hash and the same plan hash. The call remained ambiguous; the tested version has no parameter for the plan handle (Microsoft on cache attributes). Incidentally, this is also the mechanism behind the classic “fast in SSMS, slow in the application” (Microsoft on this).
Also: the tool looks for the source plan in the plan cache. After we cleared our database’s cache, the plan was still in Query Store but was no longer found. In the tested version, Query Store is no substitute for a plan that has been evicted from the cache. The script itself lists this, too, as a known limitation of this version.
The 25% rule
For certain numeric values, the tool hides small differences. It uses the larger of the two values as the base: 100 versus 125 is 25/125 = 20% and is suppressed; 100 versus 150 is 50/150 ≈ 33% and is reported. Exactly 25% is suppressed as well. So no finding does not mean “equal”.
25% calculator
Difference 50 ÷ larger value 150 ≈ 33% · reported
| A versus B | Difference ÷ larger value | Result |
|---|---|---|
| 100 versus 125 | 25/125 = 20% | suppressed |
| 100 versus 150 | 50/150 ≈ 33% | reported |
| 100 versus 75 | 25/100 = 25% | suppressed (exactly 25%) |
From finding to effect: the verification path
What remains is a simple procedure that also works without the tool:
- Record the symptom. Which execution is slow, with which parameter, when, from which application? Do not confuse the server-side duration with the time until the result appears in the client.
- Collect leads. This is where sp_BlitzPlanCompare saves the most time.
- Formulate a hypothesis. The row
Parameter · CompiledValue(1 versus 42) becomes: The plan compiled for few matches does too many lookups for the big customer. And “LCK_M_S” becomes: The reader is waiting for a specific transaction. - Cross-check. Change one well-founded variable, then measure whether the result stays the same and how much work is done. Refuted? Back to the next lead.
- Effect and cost. Does the change also help for other parameters, under write load, in day-to-day operation?
The verification path, using the example from Case 1
- 1SymptomThe call for the big customer is slow in production.
- 2Leadssp_BlitzPlanCompare reports
Parameter · CompiledValue:@CustomerID1 versus 42. - 3HypothesisThe plan compiled for few matches does too many lookups for the big customer.
- 4Cross-checkSame runtime value, only the compile value changed: about 270,000 instead of 2,800 page accesses, same result.
- 5EffectMeasure the remedy against real load, write load and operational effort, for other parameters too.
Cross-check refuted? Back to the leads, on to the next one.
The snapshot is part of the evidence: keep it with its timestamp and check its contents before you pass it on.
Origins and thanks
sp_BlitzPlanCompare comes from Brent Ozar and is part of the SQL Server First Responder Kit (MIT license). The tool makes a tedious task manageable: the investigation starts with a specific query and lays out parameters, configuration, table metadata and current states side by side, in a targeted way. Thanks to Brent and the community around the First Responder Kit.
This article is our own account, with our own examples and measurements. Brent has neither reviewed nor approved it; the conclusions and any errors are ours. For the tool itself, Brent’s introduction and the documentation in the repository are the right places to start. Newer versions may change features and limitations.
Method and limits
Tested version: OptionalScripts/sp_BlitzPlanCompare.sql, commit 53c380ed of Oct 21, 2026, unmodified.
Setup and measurement in detail
Setup: two dedicated instances each of SQL Server 2022 CU27 (16.0.4295.3) and 2025 CU9 (17.0.5005.3) on Linux in containers, each with 2 CPUs and 2 GB max server memory; additional functional checks on 2017 and 2019. Starting configuration: compatibility level 140, MAXDOP 2, cost threshold 50, Query Store on, LAST_QUERY_PLAN_STATS deliberately turned on from 2019 on. Purely synthetic data: 100,000 orders, 90,000 of them for one customer, ten for each rare customer.
Measurement: parameter cross-matrix test with a targeted recompile per cell, followed by five warm runs; reads stable in every cell. CPU and duration values are only illustrative because of shared VM resources; the read counts carry the argument. 51 data-based checks passed across all tests.
Not tested: Azure SQL Database, Managed Instance, SQL Server on Windows, restricted permissions, a dedicated histogram example (in this version the tool only compares statistics headers), large plan caches, overhead under real load, PSP at compatibility level 160/170, spills and memory grants as a positive case of their own, many special cases with multiple statements. The numbers belong to our synthetic setup and are not expected values for other databases.
Are you reading the list correctly?
Each answer is explained right away, with a link to the relevant part of the article. No cookies.