Extended Events - rpc_completed

Question

I still new to extended events, but thought I need to get into it, seeing as it is a lot more detailed. I am trying to setup a extended event, on the rpc_completed event, tracking proc calls that have a duration of 5 seconds or longer. I setup the event using the New Session (have scripted it out below) and to test it I have a SSMS connection open where I created a test proc which has a built in WAIT FOR DELAY of 10 seconds. So I expected that this appear in the watch live data but when I exec the test proc - it does not appear in the watch live data view, I have even removed the filtered on duration, and still no results in the "watch live data view" Any ideas what could be wrong I am on SQL 2014, local machine instance, here is the scripted out extended event.

CREATE EVENT SESSION [PRC_Completed_Test] 
ON SERVER ADD EVENT sqlserver.rpc_completed (SET collect_statement = (1)
ACTION(
 sqlserver.client_app_name
 , sqlserver.client_hostname
 , sqlserver.database_name
 , sqlserver.sql_text)
)
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

Any pointers would be great

Thank you.

score 1 · Answer 1 · answered Dec 08 '15 at 09:03

You would need to capture sql_batch_completed if you want to capture events when running queries from SSMS. Use rpc_completed when capturing events from application.

Here is an example of what I use:

CREATE EVENT SESSION [Monitor Queries running longer than 5 seconds] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.plan_handle)
    WHERE ([duration]>(5000000))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_id,sqlserver.plan_handle)
    WHERE ([duration]>(5000000))) 
ADD TARGET package0.event_file(SET filename=N'C:\ExtendedEvents\Queries running longer than 5 seconds 2015-12-08.xel')
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

Regards, Dinko Fabricni

score 0 · Answer 2 · answered Dec 05 '18 at 20:39

Here is an extended event session I use for looking at SPs being called from the app. You can find the duration by right-clicking in the watch-live-data view and add that column to what you see. This also saves to a file target so you can run queries against the file if you want. Also each time you run this the file target will be overwritten via the max_rollover_files = 0 parameter.

CREATE EVENT SESSION [sp_search] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'<enter database name>'))),
ADD EVENT sqlserver.rpc_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'<enter database name>')))
ADD TARGET package0.event_file(SET filename=N'c:\sp_capture.xel',max_file_size=(5),max_rollover_files=(0),metadatafile=N'c:\sp_capture.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

watch live data with duration

Extended Events - rpc_completed

2 Answers2