Extended Events Capture a Specific Proc Parameter

Question

Extended Events Capture a Specific Proc Parameter

lmarkum

SSCertifiable

Points: 7989
More actions
February 19, 2015 at 12:57 pm

#301414

I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

wow neat question.

i head to search this, and this link seems to say if you create an extended event session watching rpc_completed, it';s all available:

http://dba.stackexchange.com/questions/50122/collecting-parameter-values-from-extended-event-session

https://www.google.com/search?sourceid=navclient&aq=&oq=extended+events+paramter+values&ie=UTF-8&q=extended+events+paramter+values&gs_l=hp....0.0.0.4715...........0.

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!

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

I'm with Lowell, rpc_complete would be the way to do this. But, just for an example, I did one that captures sql_batch_complete so that I can test it from SSMS:

CREATE EVENT SESSION [Filtered Event] ON SERVER

ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)

WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2014') AND [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%uspGetBillOfMaterials%') AND [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%@StartProductID = 723%')))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

rpc_complete will let you get a specific object by name instead of the "like" comparison I had to run. But then you'll have to do the same thing I did for the parameter value. It should work fine. Worked well enough in my tests.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

lmarkum SSCertifiable Points: 7989 More actions · Answer 3

Lowell/Grant,

I am trying the code form the dba.stackexchange site and getting the following error.

Msg 25623, Level 16, State 3, Line 1

The event action name, "package0.event_sequence", is invalid, or the object could not be found

I try Grant's code and I get:

Msg 25623, Level 16, State 1, Line 1

The event name, "sqlserver.sql_batch_completed", is invalid, or the object could not be found

The code worked fine on 2012. However, the instance I need to do this against is 2008R2. I'm sure there is a solution hidden in a little research on my part. I will do some searching and learning to sort this out. I know there are some DMVs for XE that show the available objects and columns, etc. It does seem that the answer to my question is that I should be able to do this with XE.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

I'm not surprised the event_sequence isn't available in 2008, but I could have sworn they supported sql_batch_completed. Take a look at the events available by running the query from here. Microsoft didn't have great documentation on the events in 2008.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning