Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Ray Porrata Feb 26 at 18:04

1 Answer 1

up vote 11 down vote accepted

Yes.

You need the USE PLAN hint.

In which you supply the XML from the first plan.

SELECT *
FROM T
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.

share|improve this answer
    
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 Feb 26 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! –  Martin Smith Feb 26 at 13:39
    
No problem, thx for your help in any case: learned something new today! :D –  Jeroen Feb 26 at 13:55
    
@Jeroen - Maybe undocumented TF 8780 that disables optimizer timeouts? Not sure if that would have the desired effect. –  Martin Smith Feb 27 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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