SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SET SHOWPLAN_XML and USE PLAN problems
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Analyzer
Posting Yak Master

United Kingdom
101 Posts

Posted - 12/13/2012 :  11:23:40  Show Profile  Reply with Quote
Hi - trying to capture .xml plans but keep getting message directly below.Is there a workaround for this. Syntax seems correct.

Error: "The SET SHOWPLAN statements must be the only state"

SET SHOWPLAN_XML ON
GO
<select..>
SET SHOWPLAN_XML OFF
GO

In addition the .xml is sometimes generated and then I try to use the USE PLAN option I get another error.

OPTION (USE PLAN N'<..xml string>')

Cannot execute query because USE PLAN hint conflicts with use of distributed query or full-text operations. Consider removing USE PLAN hint.

Any ideas. Using SS2005 BTW. Thanks

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 12/13/2012 :  11:45:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
SET SHOWPLAN_XML ON 
GO
<select..>
GO
SET SHOWPLAN_XML OFF 
GO
The error message is self-explanatory, you can't force plan usage in those circumstances.
Go to Top of Page

Analyzer
Posting Yak Master

United Kingdom
101 Posts

Posted - 12/18/2012 :  05:45:54  Show Profile  Reply with Quote
Any recommendations to the best technique to (a) capture the .xml execution plan then (b) force the plan. Going around in circles with OPTION (USE PLAN N'.. or OPTION (USE xml_plan n'. Can never get the syntax to parse.

Problem: Complicated SELECT which uses #tbls has developed a corrupt plan and now takes 3 hours when use to take 10 mins. Restore DB from time before corrupt plan and takes 10 mins. So need to rip the .xml plan and use in PROD stored proc copy. Any ideas?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 12/18/2012 :  06:05:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
How about http://msdn.microsoft.com/en-us/library/ms174283.aspx

Just clear the specific plans from cache and let them recompile normally. Restoring a database is a bit drastic just to recompile a plan. There's also DBCC FLUSHPROCINDB but it's undocumented:

http://beyondrelational.com/modules/2/blogs/77/posts/11390/sql-server-performance-tuning-dbcc-flushprocindb-flush-procedures-of-a-particular-database-from-cach.aspx
Go to Top of Page

Analyzer
Posting Yak Master

United Kingdom
101 Posts

Posted - 12/18/2012 :  06:37:45  Show Profile  Reply with Quote
Thanks. Problem is more complex. (Client already paid for MS engineer to visit site for 2 weeks earlier this year and apart from new index recommendations they cannot explained why execution plan switches between 10 min to 4 hours,.. then back again after may weeks) Already updated STATS with SAMPLE 100%, WITH RECOMPILE, Cleared the CACHE, Rebooted. However when we restored environment to last known good execution it works in 10 mins. Something is skewing the plan and only options I have left are hints or redesign query (which is huge).

My latest attempt was capture the good .xml plan and use it within a USE PLAN option - however it always complains about the syntax although it looks correct.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/18/2012 :  07:42:39  Show Profile  Reply with Quote
Not even remotely to suggest that I can add anything to what an MS Engineer (and Rob) have reviewed. One thought that comes to mind though is the following:

1. Look at the execution plan to see what is taking up the time and resources.

2. Add a statement level recompile hint (OPTION RECOMPILE) to force recompilation of those statements. There is a good article by Itzik Ben-Gan here

3. Review the execution plan with the hint and identify the bottlenecks again and repeat the process.
Go to Top of Page

Analyzer
Posting Yak Master

United Kingdom
101 Posts

Posted - 12/18/2012 :  10:38:06  Show Profile  Reply with Quote
Thanks Sunita (and Rob). Been a DBA for 12 years now and this is the most challenging tuning problem I've dealt with.

Stored Proc suddenly went from 10 minutes to 7 hours+, planned skewed and we don't know why. When activating 'Actual Estimation Plan' the plan is different virtually every time with different contentious areas such as a RID Lookup. However restore DB to the day before the change it runs in 10 minutes again. RECOMPILE, MAXDOP, Drop/Create, UPDATE STATs - tried most things and only left with the lengthy range of hints choices or start hacking away at the stored proc, replacing indexes and so on.

Has anyone actually successfully used OPTION (PLAN 'N... and its worked?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/18/2012 :  14:06:16  Show Profile  Reply with Quote
Call me a masochist, but this would be the type of problem I would LOVE to wrestle with (if I am not under the gun, of course). If you do find the root cause and solution, please post back so all of us can learn.

Is it possible for you to run the same query on a different version of the server? Also, is it SQL 2005 SP3 or SP4, or is it an earlier version? I recall reading about a number of issues that were fixed in SP3, although none that I can relate directly to the problems that you are experiencing.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 12/18/2012 :  15:14:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
This almost sounds like it's not even a plan issue. I'm assuming you've ruled out server activity, I/O problems, disk or database corruption. Antivirus running? How about index fragmentation? Have you checked wait statistics while it runs? If you've cleared the procedure cache and the problem comes back there's something else going on.

If those are all clear, are there multiple plans cached for this query? Is there any kind of dynamic SQL or sp_executesql code being used? Any plan guides in use? Are the parameters (if any) exactly the same for each initial execution? (Can you post any of these plans? Would need a good and bad one. I'd suggest using Pastebin rather than posting them here)

Since the error mentions distributed queries and full-text, do you have those in there? Can they be removed? What was the "change" you mention that you restored to prior? The only reason I can see a restore improving performance is that the outdated statistics were somehow better, and that auto-update FUBAR'd them on subsequent executions. Have you tried disabling auto-update stats on that database after you restore it?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000