August 11, 2010 at 7:17 am
Can I use a saved execution plan in SQL Server 2008? I know I can save an execution plan but since it appears to be portable (and MS says it is) what is the syntax or method for using it after I have saved it to a file? I have searched all over for this.
Thanks! 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
August 11, 2010 at 7:26 am
Use it for what?
To view, if it's saved as a .sqlplan file, just open it in management studio.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2010 at 7:46 am
To use it in conjunction with a T-SQL statement... could I use it with an OPTION (USE PLAN...) clause? The examples I have found are calling .xml... It looks like I can just copy the xml out of the saved plan and put it in the OPTION clause at the end of my statement...?
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
August 11, 2010 at 8:05 am
Yes you can.
I would like to add a couple words of caution - by using that hint you're essentially telling the optimiser to mind its own business, that you know best in all circumstances. If that plan is not optimal (or becomes not optimal due to data changes), the optimiser has no way of changing the plan and query performance will suffer.
Also, the plan specified must be one that the optimiser could have come up with by itself. When you specify that hint, you're not bypassing the optimiser, it will still get called and will have to generate plans until it generates the one that matches what you specified. If it doesn't (the plan you specified isn't one it could come up with) the query will fail with an error. I believe (but have not tested) that it is error 8698
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2010 at 8:15 am
Yes, I tried it and it works. And I could even modify it carefully and it still works.
Thanks for the info!
BTW, and I am aware that using a plan is normally not recommended. But some students want the option!! 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
August 11, 2010 at 8:21 am
Peter Trast (8/11/2010)
But some students want the option!! 🙂
Yeah, I know. Please just make sure if you're teaching this, also teach them why this is not something that one should typically do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2010 at 5:53 pm
hehe.... the first words out of my mouth today were, " I would never recommend doing this but IF you wanted to..."
🙂 I have always known it could be done but I had never figured out how or bothered to demo it. Just had a need to know and show... even the instructor needs to learn every day!!!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
August 11, 2010 at 11:43 pm
Peter Trast (8/11/2010)
hehe.... the first words out of my mouth today were, " I would never recommend doing this but IF you wanted to..."
😀 I did a TechEd session last year on query hints and plan guides. I think 3/4 of the slides had a disclaimer of 'This isn't something you'd do normally, but there are times when....'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2010 at 11:28 am
I missed it!!! I was in New Orleans this year...
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
August 12, 2010 at 11:35 am
Peter Trast (8/12/2010)
I missed it!!! I was in New Orleans this year...
TechEd Africa, not TechEd North America.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2010 at 2:58 pm
Wow, I really missed it 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
August 13, 2010 at 12:50 pm
I wish I could go to some conferences...... !!!!!!!!!!!!
August 13, 2010 at 4:57 pm
Twinsoft SME (8/13/2010)
I wish I could go to some conferences...... !!!!!!!!!!!!
Get a great job in a great company like Centriq Training of Kansas City! 🙂 We are always on the lookout for great talent...
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
August 14, 2010 at 3:27 am
Twinsoft SME (8/13/2010)
I wish I could go to some conferences...... !!!!!!!!!!!!
Start saving up. I've been paying for myself the last 2 years.
By speaking at the conference, the conference fee is wavered, but the flight and accommodation is not (typically)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2010 at 2:23 pm
GilaMonster (8/14/2010)
Twinsoft SME (8/13/2010)
I wish I could go to some conferences...... !!!!!!!!!!!!Start saving up. I've been paying for myself the last 2 years.
By speaking at the conference, the conference fee is wavered, but the flight and accommodation is not (typically)
I am hoping to work Hands On Labs next year so that I can become known by the regulars and then I will apply to speak the next year. One of my coworkers has worked the conference for several years. The company really likes not having to pay that big fee to attend 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply