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:
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>
The plan is 157 MB.
Is there any way to get the full plan out so that I can see it?