Shredding the query plan XML and then joining that to the individual query

  • Jimmy M

    Default port

    Points: 1481

    Hi all

    I want to add something to a stored procedure plan/stats query I am working on. I want to list the parameters which were used to compile each statement in a stored procedure.

    So i am running the below query to examine the XML in the query plan. Where a stored proc i am examining has more than one statement, i only want the parameters for that statement.

    The only way i can see of doing this is to join the sys.dm_exec_query_stats column "query_plan_hash" to the shredded equivalent in the query plan: there is an attribute called @QueryPlanHash (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/ParameterList/ColumnReference)

    I was quite pleased with this approach! But the join doesn't work because i cannot cast the query plan XML's representation of @QueryPlanHash to binary(8), which is the data type of sys.dm_exec_query_stats column.query_plan_hash. This returns a NULL value from the SQLXML Value function. And I can't work out if i can do anything with the varchar value that i am able to get back from Value, in terms of casting that to a binary(8) on the TSQL side. That doesn't seem to work.

    Needless to say if i have no join at all, i get a cartesian product - each statement is repeated N times, where N is the number of statements in the query plan. Each statement's parameter list is repeated for each statement.

    Please see query below, i hope this will explain better what i am trying to do.

    If anyone knows how i can cast @QueryPlanHash from the XML in such a way that it can join to sys.dm_exec_query_stats column.query_plan_hash, i'd be grateful to know it. Whether that's a CAST in the XML query or a CAST on the TSQL side, either way that would be great!

    Or perhaps someone has found a more appropriate way to tie an individual sql statement's parameters (or other part of ITS own portion of the overall plan for the batch or SP) to the query's row in sys.dm_exec_query_stats? If so, again i'd be very grateful to hear about it.

    Many thanks for reading and any help you are able to provide.

    James

    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

    SELECT

    DB_NAME(qp.dbid) as 'database_name'

    , REPLACE(SUBSTRING(st.text, PATINDEX('%CREATE %PROCEDURE%', st.text), 100), CHAR(13) + CHAR(10), ' ') 'Stored Procedure Name'

    , REPLACE(

    SUBSTRING(

    st.text

    , (qs.statement_start_offset/2) + 1

    , ((case qs.statement_end_offset when -1 then datalength(text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1

    )

    , CHAR(13) + CHAR(10)

    , ' ') as 'Statement_text'

    , qs.creation_time

    , qs.last_execution_time

    , qs.execution_count

    -- THESE ARE THE 2 COLUMNS I AM TRYING TO JOIN

    , N.c.value('@QueryPlanHash', 'varchar(128)') 'QueryPlanHash'

    , qs.query_plan_hash

    -- THIS IS WHAT THIS PROBLEM IS ALL ABOUT - GETTING A SPECIFIC PARAMETER OR A LIST OF THEM (IN XML)

    , N.c.value('QueryPlan[1]/ParameterList[1]/ColumnReference[attribute::Column="@ip_FirmCode"][1]/@ParameterCompiledValue', 'varchar(128)') 'Firm_code_compiled_for'

    , N.c.query('QueryPlan/ParameterList/ColumnReference') 'All_params'

    , qs.total_worker_time/1000 AS [Total CPU Time milliseconds]

    , qs.total_worker_time/execution_count/1000 AS [Avg CPU Time milliseconds]

    , qs.total_elapsed_time/1000 [Total duration milliseconds]

    , qs.total_elapsed_time/execution_count/1000 [Avg duration milliseconds]

    , qs.plan_handle

    , qp.query_plan

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY

    sys.dm_exec_sql_text(qs.sql_handle) AS st

    CROSS APPLY

    sys.dm_exec_query_plan(qs.plan_handle) as qp

    OUTER APPLY

    qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as N(c)

    WHERE

    st.text like '%create%procedure%_jm_p_DBA_QueryPlanParamsTest_s%'

    and not (st.text like '%sys.dm_exec_query_stats%' or st.text like '%dm_exec_cached_plans%' or st.text like '%s_OptimiseExecutionPlans_u%')

    -- HERE IS THE ATTEMPTED JOIN, NONE OF THE 3 ATTEMPTED JOINS WORK (NO ROWS RETURNED) AND NO JOIN MEANS CARTESIAN PRODUCT, I.E. I DON'T GET THE PARAMS JUST FOR THE

    -- INDIVIDUAL STATEMENT

    --and qs.query_plan_hash = N.c.value('@QueryPlanHash', 'VARCHAR(128)')

    --and qs.query_plan_hash = N.c.value('@QueryPlanHash', 'BINARY(8)')

    and qs.query_plan_hash = CAST(N.c.value('@QueryPlanHash', 'varchar(128)') AS BINARY(8))

  • RBarryYoung

    SSC Guru

    Points: 143327

    Try it like this:

    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

    SELECT

    DB_NAME(qp.dbid) as 'database_name'

    , REPLACE(SUBSTRING(st.text, PATINDEX('%CREATE %PROCEDURE%', st.text), 100), CHAR(13) + CHAR(10), ' ') 'Stored Procedure Name'

    , REPLACE(

    SUBSTRING(

    st.text

    , (qs.statement_start_offset/2) + 1

    , ((case qs.statement_end_offset when -1 then datalength(text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1

    )

    , CHAR(13) + CHAR(10)

    , ' ') as 'Statement_text'

    , qs.creation_time

    , qs.last_execution_time

    , qs.execution_count

    -- THESE ARE THE 2 COLUMNS I AM TRYING TO JOIN

    , N.c.value('@QueryPlanHash', 'varchar(128)') 'QueryPlanHash'

    , qs.query_plan_hash

    -- THIS IS WHAT THIS PROBLEM IS ALL ABOUT - GETTING A SPECIFIC PARAMETER OR A LIST OF THEM (IN XML)

    , N.c.value('QueryPlan[1]/ParameterList[1]/ColumnReference[attribute::Column="@ip_FirmCode"][1]/@ParameterCompiledValue', 'varchar(128)') 'Firm_code_compiled_for'

    , N.c.query('QueryPlan/ParameterList/ColumnReference') 'All_params'

    , qs.total_worker_time/1000 AS [Total CPU Time milliseconds]

    , qs.total_worker_time/execution_count/1000 AS [Avg CPU Time milliseconds]

    , qs.total_elapsed_time/1000 [Total duration milliseconds]

    , qs.total_elapsed_time/execution_count/1000 [Avg duration milliseconds]

    , qs.plan_handle

    , qp.query_plan

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY

    sys.dm_exec_sql_text(qs.sql_handle) AS st

    CROSS APPLY

    sys.dm_exec_query_plan(qs.plan_handle) as qp

    OUTER APPLY

    qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as N(c)

    WHERE

    st.text like '%create%procedure%_jm_p_DBA_QueryPlanParamsTest_s%'

    and not (st.text like '%sys.dm_exec_query_stats%' or st.text like '%dm_exec_cached_plans%' or st.text like '%s_OptimiseExecutionPlans_u%')

    -- HERE IS THE ATTEMPTED JOIN, NONE OF THE 3 ATTEMPTED JOINS WORK (NO ROWS RETURNED) AND NO JOIN MEANS CARTESIAN PRODUCT, I.E. I DON'T GET THE PARAMS JUST FOR THE

    -- INDIVIDUAL STATEMENT

    --and qs.query_plan_hash = N.c.value('@QueryPlanHash', 'VARCHAR(128)')

    --and qs.query_plan_hash = N.c.value('@QueryPlanHash', 'BINARY(8)')

    --and qs.query_plan_hash = CAST(N.c.value('@QueryPlanHash', 'varchar(128)') AS BINARY(8))

    and sys.fn_varbintohexstr(qs.query_plan_hash) = N.c.value('@QueryPlanHash', 'VARCHAR(128)')

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Jimmy M

    Default port

    Points: 1481

    Brilliant! Thank you very much indeed!

  • RBarryYoung

    SSC Guru

    Points: 143327

    Heh, glad I could help. That Binary/Hex string thing has been a bugaboo in SQL Server for a long time ...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply