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?

10
  • Have you tried the -y argument to sqlcmd? Should let you choose the maximum size of returned fields. Sep 10 '12 at 19:44
  • @Simon No, I have not. Thanks, I'll go try that in a minute.
    – squillman
    Sep 10 '12 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 '12 at 20:05
  • How about retrieving the data using C# or PowerShell. Sep 10 '12 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 '12 at 20:29
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

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. Feb 19 '13 at 16:46
  • 6
    Restart of SSMS is not needed; You will have to open a new query window, tho.
    – edhubbell
    Jun 18 '13 at 15:03
  • @MartinSmith +1 - FYI I just had to restart SSMS to get this to work.
    – Curt
    Mar 17 '14 at 12:52
  • Restarting SSMS after the change mentioned above worked. Did not work without restarting SSMS.
    – user36459
    Mar 31 '14 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. Dec 12 '14 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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