5

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?

Improve this question
10
  • Have you tried the -y argument to sqlcmd? Should let you choose the maximum size of returned fields.
    – Simon Righarts
    Sep 10, 2012 at 19:44
  • @Simon No, I have not. Thanks, I'll go try that in a minute.
    – squillman
    Sep 10, 2012 at 19:45
  • @Simon No luck. Max value for column length is 8000 and that's not enough. XML is still truncated.
    – squillman
    Sep 10, 2012 at 20:05
  • How about retrieving the data using C# or PowerShell.
    – Aaron Bertrand
    Sep 10, 2012 at 20:12
  • @Aaron I can give that a try. I'd expect to run into the same thing, though, as the providers would be the same. Unless there's a different provider I could find that doesn't have that limitation.
    – squillman
    Sep 10, 2012 at 20:29

1 Answer 1

Reset to default
9

For 157MB in Tools -> Options -> Query Results -> SQL Server -> Results to Grid screen you will need to set Maximum Characters Retrieved -> XML data to unlimited as below.

Screenshot

Improve this answer
6
  • 1
    An anonymous editor just suggested that restarting SSMS was neccesary after changing this setting. I don't believe this to be the case so rejected the edit but mention it here in case it was valid.
    – Martin Smith
    Feb 19, 2013 at 16:46
  • 7
    Restart of SSMS is not needed; You will have to open a new query window, tho.
    – edhubbell
    Jun 18, 2013 at 15:03
  • @MartinSmith +1 - FYI I just had to restart SSMS to get this to work.
    – Curtis
    Mar 17, 2014 at 12:52
  • Restarting SSMS after the change mentioned above worked. Did not work without restarting SSMS.
    – user36459
    Mar 31, 2014 at 21:01
  • 2
    IIRC, you may have to create a new query window. You shouldn't have to restart SSMS, though that should also work.
    – RBarryYoung
    Dec 12, 2014 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.