ShowPlan XML

Categories: News, Professional, SSC
Comments: No Comments
Published on: March 28, 2011

I just ran into something that I hadn’t noticed in SQL Server.  It struck me as somewhat interesting but is really not too big of a deal.

While demonstrating that the XML plan can be generated when you dump data into a temp table, I happened onto this little ditty.  First, let’s take a look at the simple demo script that I created for the temp table dilemma.

[codesyntax lang="tsql"]


It was thought, by the questioner, that inserting into a temp table just like I am demonstrating would cause an error to be thrown when trying to show the xml plan.  I can execute this query and it runs just fine.  Now, if I add the showplan_xml to it, like the next script, it will still run fine.

[codesyntax lang="tsql"]


Pretty straight forward and note that I have batched the showplan separately from the code I am testing.  This is required by the showplan command.  The curious part comes when looking at the results.  Well, not entirely the results – but more accurately the name of the results.

I looked at the column name for this output and thought: “wait, I know this is a SQL 2008 instance.”  I decided to verify the version and results.  Thus, I reran the query below to test.

[codesyntax lang="tsql"]


This query produces the following results.

This seems pretty conclusive to me that I am running SQL 2008 but the showplan still outputs as 2005 XML Showplan.  I also checked this on 2008 R2 and get the same results.  Microsoft has decided to continue to use the 2005 Showplan schema and did not update that name.  Would it be nice to have an updated label?  Certainly it would.  The flip-side is that the XML Schema does not seem to have changed, so it is merely aesthetic in nature.

If you would like, you can take a look at the schema here.  I only checked the last updated date on the current, SQL 2008, and SQL 2005 sp2 schemas and saw that the date was the same for all of them.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

March 2011
« Feb   Apr »


  • @billinkc: @Clem1029 #sqlhelp in my quick testing, before installing SSDT-BI, I had the AS folder in 12, not in 11. Installed and it showed up.
  • @billinkc: @Clem1029 #sqlhelp Does C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\ProjectTemplates\Analysis Services exist or 11.0
  • @tjaybelt: #sqlhelp any problems with installing SQL Server 2008R2 Web edition on Windows Server 2012?
  • @AskSQL: Anyone using Win2012R2 getting the SCSI VPD error when validating for WSFC? Says I "don't have inquiry data" #SQLHELP
  • @Clem1029: Is there a registry key or file that will indicate that SSDT BI is installed on a machine? #sqlhelp
  • @sqlstudent144: Can DTSRun be installed separately from a SQL Server instance? & on a win 2008 srvr? #sqlhelp
  • @toosuto: What's the impact of restarting the DTC? #sqlhelp
  • @SirSQL: @_dave705 sys.xp_sqlagent_enum_jobs to check running status #sqlhelp or enum method dir sqlserver:\sql\localhost\default\jobserver\jobs | gm
  • @SirSQL: @_dave705 you would have to call an sp_start_job and then poll the status of the job until it's complete and handle that in PS #sqlhelp
  • @_dave705: @SqlBrit Since the sql agent job is asynchronous it does not return any status even if the job fails unless you have idea #sqlhelp

Welcome , today is Tuesday, September 30, 2014