September 15, 2011 at 6:09 pm
Hi Guys
I'm trying to build an automatic report that captures the data of the slowest performed queries on a daily basis and I've found that DMVs can help with this, I'm kinda stuck though beause I can't seem to capture the actual Stored Procedures execution statement and the info for it, here's what I have:
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,(cp.usecounts) execution_count
,(qs.total_elapsed_time) total_elapsed_time
,(qs.total_elapsed_time) / (cp.usecounts) avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
By handling both the cached plans DM and the function dm_exec_sql_text I seem to get in the object id columns stored procedures for the output, but in the "statement_text" column I only seem to get statements of queries that belong to those procedures and not the actual statement of the stored procedure call.
What I would like to get is something like "exec usp_test @param1,@param2" for example, and the rest of the info for whole SP call, is it possible? Because now the only idea I have is to group the executions by objecetid, but that doesn't gives me the parameters of the SP call either, in case I'd like to see a particular set of parameters that makes the SP slow.
Thanks in advance for your valuable help.
Stanly
September 19, 2011 at 4:32 am
did you use some the the code floating arround on the net. there is scripts called "SQL Server 2005 Diagnostic Information Queries" that you can search for. I use them on a regular basis.
Getting the actual parameter values used in a call to an SP...? I am not sure that you can get that in DMV's. It usualy just shows "exec spanswer @1,@2......" and if I under stand you correctly you need "exec spanswer 1,'G5R'......"
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 19, 2011 at 9:43 am
Speaking of code on the net, try Glenn Berry's Dr. DMV scripts.
http://sqlserverperformance.wordpress.com/tag/dmv-queries/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 9:48 am
I'll take a look and see what I can find, and yeah, I was trying to get something like "exec sp_test 'data1',2, etc..." like what you get in traces RPC Completed events.
Thanks guys for your input
September 19, 2011 at 9:50 am
DBCC INPUTBUFFER
That's iirc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2011 at 9:54 am
dsbolanos (9/19/2011)
I'll take a look and see what I can find, and yeah, I was trying to get something like "exec sp_test 'data1',2, etc..." like what you get in traces RPC Completed events.Thanks guys for your input
You are trying to capture the actual parameters passed to the procedures?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 9:56 am
dsbolanos (9/19/2011)
I'll take a look and see what I can find, and yeah, I was trying to get something like "exec sp_test 'data1',2, etc..." like what you get in traces RPC Completed events.Thanks guys for your input
It's a longshot, but try this (longshot because capturing the parameters passed via the dmvs is not guaranteed).
http://sqlblog.com/blogs/ben_nevarez/default.aspx?p=2
If you want to capture the parameters, try inputbuffer or a trace.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 10:03 am
Yeah, I wanted to see if there was a way through DMVs, other than traces or inputbuffer, I'll take a look and let you know if I can workout somethings.
Thanks for your help guys.
September 19, 2011 at 10:31 am
Based on what Jason pointed to, where you can harvest info from the execution plans, this seemed to work for me;
i ran a proc "sp_find that takes one parameter...it's basic, it's used to search teh metadata for tablenames/column names, but this returned the param name and the param value for me:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT p.query_plan,
TheCommand =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') ,
TheParamName =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:ParameterList/sp:ColumnReference/@Column)[1]', 'NVARCHAR(256)') ,
TheParamValue =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:ParameterList/sp:ColumnReference/@ParameterCompiledValue)[1]', 'NVARCHAR(256)')
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
WHERE p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') LIKE '%sp_find%'
Lowell
September 19, 2011 at 10:36 am
Lowell (9/19/2011)
Based on what Jason pointed to, where you can harvest info from the execution plans, this seemed to work for me;i ran a proc "sp_find that takes one parameter...it's basic, it's used to search teh metadata for tablenames/column names, but this returned the param name and the param value for me:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT p.query_plan,
TheCommand =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') ,
TheParamName =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:ParameterList/sp:ColumnReference/@Column)[1]', 'NVARCHAR(256)') ,
TheParamValue =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:ParameterList/sp:ColumnReference/@ParameterCompiledValue)[1]', 'NVARCHAR(256)')
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
WHERE p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') LIKE '%sp_find%'
I have had it work well, and I have had it provide no parameter information at all. It has been hit or miss for me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 10:55 am
Lowell (9/19/2011)
Based on what Jason pointed to, where you can harvest info from the execution plans, this seemed to work for me;i ran a proc "sp_find that takes one parameter...it's basic, it's used to search teh metadata for tablenames/column names, but this returned the param name and the param value for me:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT p.query_plan,
TheCommand =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') ,
TheParamName =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:ParameterList/sp:ColumnReference/@Column)[1]', 'NVARCHAR(256)') ,
TheParamValue =
p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:ParameterList/sp:ColumnReference/@ParameterCompiledValue)[1]', 'NVARCHAR(256)')
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
WHERE p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') LIKE '%sp_find%'
Be careful, that's the parameter value when the plan was initially compiled, not any execution since then.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2011 at 11:07 am
ahh nice Gail, you are right!
i ran it three more times after your post, and only when it recompiled with the first parameter i threw at it showed up in the query, adn not the two subsequent calls...
oh well that was my best guess.
Lowell
September 19, 2011 at 11:10 am
It was a very nice approach, it seems I'll just keep the SPs execution history withouth parameters for now.
Thanks for all your valuable help guys.
September 19, 2011 at 11:13 am
dsbolanos (9/19/2011)
It was a very nice approach, it seems I'll just keep the SPs execution history withouth parameters for now.Thanks for all your valuable help guys.
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply