See Large SQL query plan

  • 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.

  • Have a google for 'dm_exec_cached_plans'.



    Clear Sky SQL
    My Blog[/url]

  • do you have the query your app is running?

  • 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?

  • 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.



    Clear Sky SQL
    My Blog[/url]

  • 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?

  • 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



    Clear Sky SQL
    My Blog[/url]

  • 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