Execution Plan Problem

fromeo

unread,

Sep 7, 2010, 9:43:09 AM9/7/10

to

Hi,

I'm trying to performance tune a query that I can't run in an ad-hoc
manner (SS2K5 SP2).
I've managed to get back an XML version of the execution plan that has
been cached by using a DMV.
I can right-click on the XML link in the results tab and save the plan
with an extension of *.sqlplan.
The plan is large, so I want to display it graphically rather than
dissect it as XML.
However, when I drag the .sqlplan file back into SSMS I get the
following error:

"Error loading execution plan XML file C:\test.sqlplan (SQLEditors)
There is an error in XML document (1,1).(System.Xml)
Data at the root level is invalid. Line 1, position 1. (System.Xml)"

The saved .sqlplan file contains the following (abridged) text:

dbid,objectid,number,encrypted,query_plan
NULL,NULL,NULL,0,<ShowPlanXML xmlns="http://schemas.microsoft.com/
sqlserver/2004/07/showplan" Version="1.0"
Build="9.00.3080.00"><BatchSequence>...</BatchSequence></ShowPlanXML>

The first bit of text before the start of the XML shows the column
names from the query used to retrieve the execution plan.
SQL Server has put this in for some reason. And I thought that this
might be causing the problem.
However, removing it does not fix the problem. And looking at the XSD
at http://schemas.microsoft.com/sqlserver/2004/07/showplan/sql2005sp2/showplanxml.xsd
it would seem that I might be missing some other XML elements.
I've googled for the answer but can't find anyone else with the same
problem.
Does anyone know what the problem is caused by or how to rectify it?
Or can someone run a query plan as XML that works and post the top and
tail of the XML that I'm missing please so that I can add it to my
plan to allow it to display?

Many thanks,
F.

Erland Sommarskog

unread,

Sep 7, 2010, 5:04:35 PM9/7/10

to

fromeo (franci...@gmail.com) writes:
> I'm trying to performance tune a query that I can't run in an ad-hoc
> manner (SS2K5 SP2).
> I've managed to get back an XML version of the execution plan that has
> been cached by using a DMV.
> I can right-click on the XML link in the results tab and save the plan
> with an extension of *.sqlplan.
> The plan is large, so I want to display it graphically rather than
> dissect it as XML.
> However, when I drag the .sqlplan file back into SSMS I get the
> following error:

Hm, do I understand you right that you right-clicked and select
Save Results as? First single-click the plan, so that the XML document
opens and then save as *.sqlplan.

Or get a copy of SSMS 2008 - it will open the graphical plan directly from
the results grid. My absolute favourite feature in SSMS 2008!

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

John Bell

unread,

Sep 7, 2010, 5:42:38 PM9/7/10

to

Hi

Looks like you saved the whole row and not just the plan
The XML execution plan file should start

or similar.

If you had returned your result into a table, then you could click on
the link in the query_plan to open a new window. If you then chose to
save that the file should be open-able with SSMS

John

fromeo

unread,

Sep 8, 2010, 3:30:21 AM9/8/10

to

Hi,

Thanks John and Erland.
Of course I was not opening the plan first.
Now that I do that it works correctly :-)

Cheers,
F.