Why’s This Query So Slow in Prod? Check sp_BlitzPlanCompare.

You’re testing a query, and you’re just not getting the same performance between the development and production servers.

There’s a huge list of common things that might be causing it:

• Parameter sniffing
• Different database-level settings like compatibility levels and cardinality estimators
• Different server-level settings like CTFP and MAXDOP
• Different hardware capabilities, like different numbers of cores available, memory grant sizes available, or TempDB speeds
• Different indexes or statistics on the two databases
• Different contents of the statistics or indexes, different sampling rates
• Different activity happening on the two servers, like blocking

And a whole list of esoteric issues, too. So how are you supposed to quickly narrow down the differences between them, identify which differences are the most relevant to your query performance differences, and prioritize them quickly?

You couldn’t – until now, with sp_BlitzPlanCompare.

How sp_BlitzPlanCompare Works

It takes 3 parameters: @QueryHash, @LinkedServer, and @CompareToXML.

Call it with a @QueryHash and a @LinkedServer, and sp_BlitzPlanCompare reads the query plans on the local & remote servers (including the last actual plan if available in sys.dm_exec_query_plan_stats), discovers what objects are referenced in the plans (tables, indexes, statistics, etc), and then digs into the configuration settings on both the local & remote side to understand what influenced the plan generation. It even looks at live activity to see if the same query is getting different wait stats on the two servers.

It returns a prioritized list of differences between the two plans:

The output is prioritized, so the stuff that’s most likely causing the issues floats up to the top. Let’s zoom in:

In this case, the most likely cause for differences is that the cached plans on dev and prod have two different sets of values. The “Reproducer” row, if you scroll over to the far right of the output, has a call stack that runs the dev params first, then the prod ones, and vice versa, so you can see what happens when one of the plans is put in cache and the other set of parameters is run.

However, continue scrolling down: in this case, the LiveWait section shows that the prod server has an instance of this query running right now, and it shows you the wait stats in that session. This is useful for live troubleshooting when production is in trouble – because perhaps prod is experiencing wait types on that query that you wouldn’t expect.

Don’t wanna use linked servers? No problem! Run sp_BlitzPlanCompare with the @QueryHash you’re investigating, and it’ll generate some XML for you to describe the situation on the server you’re on. You can also use @QueryPlanHash if you know the plan, but not the query:

Copy that data, then connect to the other server, paste it, and run. Voila, the same comparison diagnostic data without linked servers.

Getting the Best Results

To get the most accurate, granular advice, enable the last actual plan setting in SQL Server 2019 & newer:

You’ll get more granular performance data about what’s happening on both servers, like memory grant differences, TempDB spills, wait stats, and thread statistics. This does have overhead, since it causes SQL Server to cache the last actual plan for every query that runs (not just the one you’re focused on).

I recommend turning this option on when you’re actively performance tuning a server, and you wanna be able to see what’s happening without re-running the queries yourself to get their full actual plans – but only if your server does under 1,000 batch requests/sec. (Above that number, the CPU overhead can start to become impactful.)

How to Get It and Learn More

It’s in the Optional Scripts folder of the First Responder Kit 2026, and the documentation is here. If you hit a bug, or you’d like to see something changed, create a new issue, choosing either a bug report or a feature request, and answer the questions that show up in the issue template.

Wanna learn more about how to use it and how to react to the problems it solves? I’m doing an all-day training workshop called Dev-Prod Demon Hunters at the PASS Data Community Summit in Seattle in November. Register now!