Retrieve XML of large plan from plan cache I'm trying to view (graphically) a large query plan that is in the plan cache and am running into problems where the XML is truncated in the query results.  This is the query I'm using:

SELECT TOP 1
    size_in_bytes,
    query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
ORDER BY size_in_bytes DESC


I've tried it in SSMS and when I click on the XML in the results grid I get the Unable to show XML. Unexpected end of file when parsing Name has occurred.  Line 1, position 2097154. If I copy and paste the XML it is indeed truncated.

I've tried manually using SET TEXTSIZE 2147483647 just because, and that didn't do anything.  (I know that's the default value for the SQL Native Client providers, was just trying to force the issue).

I've tried running the statement via SQLCMD from a .sql file and outputting to a text file but that gives me even less.

I've tried using SQLCMD interactively, with :XML ON and got this error:

&lt;?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?&gt;


The plan is 157 MB.

Is there any way to get the full plan out so that I can see it?
For 157MB in Tools -&gt; Options -&gt; Query Results -&gt; SQL Server -&gt; Results to Grid screen you will need to set Maximum Characters Retrieved -&gt; XML data to unlimited as below.