Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Have you tried the -y argument to sqlcmd? Should let you choose the maximum size of returned fields. –  Simon Righarts 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. –  Aaron Bertrand 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

1 Answer 1

up vote 7 down vote accepted

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

share|improve this answer
    
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 '13 at 16:46
2  
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 at 12:52
    
Restarting SSMS after the change mentioned above worked. Did not work without restarting SSMS. –  user36459 Mar 31 at 21:01
1  
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 at 22:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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