Extended Events Capture a Specific Proc Parameter

Question

Post reply

Extended Events Capture a Specific Proc Parameter

lmarkum

SSCertifiable

Points: 7979
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: 323482 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

lmarkum SSCertifiable Points: 7979 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software