May 24, 2012 at 12:05 pm
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))
May 24, 2012 at 5:24 pm
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]
May 25, 2012 at 1:34 am
Brilliant! Thank you very much indeed!
May 25, 2012 at 10:09 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply