XML Showplans
In Microsoft SQL Server, Showplan execution plan output can be generated in XML format by various methods. Showplan output in XML format can be moved from one computer to another and thus rendered on any computer, even on computers where SQL Server is not installed. Showplan output in XML format can also be programmatically processed using XML technologies, such as XPath, XQuery, XSLT, SAX, DOM, and so on. XML Showplan processing is supported in SQL Server, which contains a built-in query evaluation engine for XPath and XQuery.
You can generate XML Showplan output by using the following methods:
Selecting Display Estimated Execution Plan or Include Actual Execution Plan from the query editor toolbar in SQL Server Management Studio
Using the Transact-SQL Showplan SET statement options SHOWPLAN_XML and STATISTICS XML
Selecting the SQL Server Profiler event classes Showplan XML, Showplan XML for Query Compile, and Showplan XML Statistics Profile for tracing
Using the sys.dm_exec_query_plan dynamic management function
XML Showplans are returned in the nvarchar(max) data type for all of these methods, except when you use sys.dm_exec_query_plan. XML Showplans are returned in the xml data type when you use this dynamic management view.
The XML schema for Showplan is available with the SQL Server installation files at the following location:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd
Note
If the query optimizer prematurely terminates query optimization, the StatementOptmEarlyAbortReason attribute is returned for the StmtSimple element in XML Showplan output. The possible values that can display for this attribute are TimeOut, GoodEnoughPlanFound, and MemoryLimitExceeded. If TimeOut or GoodEnoughPlanFound are returned for this attribute, no action is necessary. The Showplan returned contains correct results.
If MemoryLimitExceeded is returned for the StatementOptmEarlyAbortReason attribute, the XML Showplan produced will still be correct, but it may not be optimal. Try one of the following methods to increase available memory: 1) Reduce the load on the server. 2) Increase memory available to SQL Server. For more information, see Managing Memory for Large Databases. 3) Check the max server memory option that is set with sp_configure, and increase the value if it is too low. For more information, see Server Memory Options.
About the Showplan XML Schema
Version designation of the Showplan XML Schema consists of two parts, such as m.n, where m is the major version number and n is the minor version number. For example, "Version 2.5." This version number appears in the Showplan XML Schema document root element. For example: version="0.5"
Backward Compatibility of Showplan XML Schemas
When the major version number increments, new XML Showplan output may not validate against the old schema. However, if the minor version number increments, there is no effect. For example, if you have generated XML Showplan output with schema version 0.5, that output validates against a Showplan XML schema version 0.6.
Encoding of XML Showplans
SQL Server sends the XML Showplan output to the client in Unicode format using two bytes for each character sent. The encoding rules are as follows:
If (char >= 0x0020 && char <= 0xD7FF) or (char == 0x0009) or
(char == 0x000A) or (char == 0x000D) or (char >= 0xE000 && char <= 0xFFFD)
then send the char as 2 bytes, else send '?'
Tab, newline, and linefeed characters are encoded as follows:
\t is encoded as '	'
\n is encoded as '
'
\r is encoded as '
'
Saving XML Showplan Output to a File
After Showplan output has been generated by using either the SHOWPLAN_XML or the STATISTICS XML Transact-SQL SET statements, you can save the output to a file with the extension .sqlplan. For example, MyXMLShowplan.sqlplan. These .sqlplan files can then be opened and viewed in SQL Server Management Studio. For more information, see How to: Save an Execution Plan in XML Format.