DaveWentzel.com            All Things Data

XML Query Plans


Newly available in 2005. 
If you aren't an XML guru a tool like XML Notepad can be invaluable in reading XML plans.  Available here.  XML plans show a little more detail than the graphical or text based plans. 
SET STATISTICS XML ON;  --this is the equivalent of Include Actual Execution Plan
SET SHOWPLAN_XML ON;  --this is the equivalent of Display Estimated Execution Plan
You can also right click a graphical plan and choose Save Execution Plan As
You also use XML Query plans to construct Plan Guides
The full schema for the XML output is available here:  http://schemas.microsoft.com/sqlserver/2004/07/showplan/
Extracting Query Plans From Cache
sys.dm_exec_query_plan DMF returns the showplan in XML format for any query in the procedure cache.  sys.dm_exec_query_stats has the required plan_handle that needs to be passed in.  Using CROSS APPLY we can we can cycle through available plan_handles and output the plans. 
Select qplan.query_plan as QueryPlan
FROM sys.dm_exec_query_stats qstats
CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) as qplan;
Unfortunately the query text is buried deep inside the XML so if we want to see the statement text we need to "parse" the XML a little bit.  Here is the code to do this