| 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 GOIn 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..>GOSET SHOWPLAN_XML OFF GO[/code]The error message is self-explanatory, you can't force plan usage in those circumstances. |
 |
|
|
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? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-12-18 : 06:05:19
|
| How about http://msdn.microsoft.com/en-us/library/ms174283.aspxJust 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 |
|
|
|
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. |
|
|
|
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 here3. Review the execution plan with the hint and identify the bottlenecks again and repeat the process. |
|
|
|
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? |
|
|
|
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. |
|
|
|
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? |
|
|
|
|
SET SHOWPLAN_XML and USE PLAN problems