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:

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

  • I have used the following to check long and short running queries on my dev & test systems. Clear the existing plans, buffers, caches then and execute the query(ies) with the trace on. Using dbcc drop/free functions to clear the session, cache, buffers, etc and setting the trace off or off. Unrelated but helpful, is to see what the deal is with the data in with the table. Please note that I saw that you were using S2K12 so these refs should match. The behavior may differ in earlier versions of Sql Server. Hope this helps. DBCC FREESYSTEMCACHE, DROPCLEANBUFFERS, Trace On, show_statistics, etc. Commented Feb 26, 2014 at 18:04

1 Answer 1



You need the USE PLAN hint.

In which you supply the XML from the first plan.

OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> ....')

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.

  • Thanks, exactly what I was asking for. (Unfortunately it leaves me with a new problem: "A query plan could not be found because optimizer exceeded number of allowed operations while searching for plan specified in USE PLAN hint.", but that's another issue to deal with I guess.)
    – Jeroen
    Commented Feb 26, 2014 at 12:26
  • @Jeroen - Hmmm. Not sure if there's any work around for that. Maybe there's some trace flag that would increase that limit but if there is I don't know it! Commented Feb 26, 2014 at 13:39
  • No problem, thx for your help in any case: learned something new today! :D
    – Jeroen
    Commented Feb 26, 2014 at 13:55
  • @Jeroen - Maybe undocumented TF 8780 that disables optimizer timeouts? Not sure if that would have the desired effect. Commented Feb 27, 2014 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.