November 2, 2009 at 3:11 am
Hi I have a 3rd Party reconcilliation application which is generating a huge query which is not completing. I want to look at the query plan to try to understand what the app is trying to do. I can see part of the plan via syscacheobjects via the following query:
select sqlbytes,sql from master..syscacheobjects
order by 1 desc
The plan is massive (389406 bytes!). Does anyone have any other ideas on how I can get the full text of the query plan? As its not completing I don't think I can get this via the profiler.
SQL Server 2005 Ent x64 (8cpu + 16GB).
Thanks in advance.
November 2, 2009 at 3:53 am
Have a google for 'dm_exec_cached_plans'.
November 2, 2009 at 3:53 am
do you have the query your app is running?
November 2, 2009 at 4:25 am
Hi dave. Thanks for the reply. I tried the following:
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
where text like 'Blah%'
ORDER BY usecounts DESC;
GO
however the text output is truncated at approx 8000 characters. Is there any way to get the full SQL text out?
November 2, 2009 at 4:50 am
First try
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
TEXT AS SQL ,
planxml = convert(xml,query_plan)
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
Cross Apply sys.dm_exec_text_query_plan(plan_handle,0,-1)
Which may fail with a 'maximum level error.'
If you push the results to grid , double click on the XML it should should the plan.
November 2, 2009 at 5:58 am
Dave, tried that one. Even when pushing results to Grid I get the same error:
Msg 6335, Level 16, State 102, Line 2
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
Any other ideas?
November 2, 2009 at 6:17 am
Bcp it out then, for example...
SELECT top 1 planxml = convert(text,query_plan)
into xmlout
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
Cross Apply sys.dm_exec_text_query_plan(plan_handle,0,-1)
order by datalength(TEXT) desc
Then
bcp yourdatabase..xmlout out c:\xmlout.sqlplan -c -Syourserver -T
You should now be able to load it in SSMS
November 2, 2009 at 6:48 am
Thats great. Thanks for you help. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply