Re-run a specific actual query plan

I have captured an actual query plan for a specific query.

After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan is different (which makes sense).

The query now runs a lot faster. I'm curious if the new execution plan has anything to do with this, because other changes (changes to the IO setup, VM settings, sql instance restart, etc) may also be causing the improvement in performance. To test this I'd like to run the query once more, and try to force SQL Server to use the old execution plan.

Question: is there a way to re-run a query with a user-provided execution plan, or even run a query directly from such a plan?

Here's what I've tried to figure this one out:

• I've searched in the books we have available at the office (Professional SQL Server 2012 Internals and Troubleshooting, Querying Microsoft SQL Server 2012);

• Google searches, for example "run query based on specific query plan"

• DBA.SE searches, for example "execute query plan" and "re run execution plan"

• And finally, one that has answered my questions many time before: carefully check "Questions that may already have your answer" before hitting "Post your question" :-)

So bottom line: is this possible? If so: how?

Yes.

You need the USE PLAN hint.

In which you supply the XML from the first plan.

SELECT *
FROM T
OPTION (USE PLAN N' ....')

Whilst it doesn't guarantee that the plan will be exactly the same (e.g. compute scalar operators can move around for example) it will likely be pretty close.

StackExchange Database Administrators Q#59689, answer score: 10