Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SET SHOWPLAN_XML and USE PLAN problems

Author  Topic 

Analyzer
Posting Yak Master

115 Posts

Posted - 2012-12-13 : 11:23:40
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

15732 Posts

Posted - 2012-12-13 : 11:45:56
[code]SET SHOWPLAN_XML ON
GO
<select..>
GO
SET SHOWPLAN_XML OFF
GO[/code]The error message is self-explanatory, you can't force plan usage in those circumstances.
Go to Top of Page

Analyzer
Posting Yak Master

115 Posts

Posted - 2012-12-18 : 05:45:54
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

15732 Posts

Posted - 2012-12-18 : 06:05:19
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

115 Posts

Posted - 2012-12-18 : 06:37:45
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-18 : 07:42:39
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

115 Posts

Posted - 2012-12-18 : 10:38:06
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-18 : 14:06:16
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

15732 Posts

Posted - 2012-12-18 : 15:14:49
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
   

- Advertisement -