Capturing Stored Procedures statements with DMVs

Question

Capturing Stored Procedures statements with DMVs

dsbolanos

SSC Eights!

Points: 829
More actions
September 15, 2011 at 6:09 pm

#245064

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

Viewing 14 posts - 1 through 13 (of 13 total)

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

OomBoom Hall of Fame Points: 3481 More actions · Answer 1

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

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

dsbolanos SSC Eights! Points: 829 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

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

dsbolanos SSC Eights! Points: 829 More actions · Answer 7

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.

Lowell SSC Guru Points: 323482 More actions · Answer 8

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323482 More actions · Answer 11

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dsbolanos SSC Eights! Points: 829 More actions · Answer 12

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.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 13

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