Monitor stored procedure statistics with extended events

Question

I want to log the duration and time of user defined stored procedures each time they are invoked. I believe using extended events would be the way to go to achieve this.
Can you please help me in defining the session and in querying the results? I am not quite sure about which event to add (sqlserver.rpc_completed?) and which target to choose (synchronous_event_counter,asynchronous_file_target or ring_buffer?).
I also need help in querying the results (For example: grouping the result set so that each sp will show from top execution duration and execution time.) We use SQL Server 2008 R2.

Answer 1

Your options are a bit limited in SQL Server 2008 R2 and Extended Events. You could try rpc_completed but remember it will only register for remote procedure calls. Here's some sample code tested in SQL 2008 R2 which shows how to create a session then read it. Work through it, make sure you understand it and try it out:

-- xe_rpc_completed
IF EXISTS ( SELECT * from sys.server_event_sessions WHERE name = 'xe_rpc_completed' )
DROP EVENT SESSION [xe_rpc_completed] ON SERVER
GO

-- rpc_completed
CREATE EVENT SESSION [xe_rpc_completed] ON SERVER 
ADD EVENT sqlserver.rpc_completed
    (
    ACTION ( package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack )
    )
ADD TARGET package0.asynchronous_file_target (
     SET filename = 'd:\temp\xe_rpc_completed.etl', metadatafile = 'd:\temp\xe_rpc_completed.mta' )
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


-- Start the session, collect some data
ALTER EVENT SESSION [xe_rpc_completed]
ON SERVER
STATE = START;
GO


IF OBJECT_ID('tempdb..#tmp') IS NOT NULL drop table #tmp

SELECT IDENTITY(INT,1,1) rowId, object_name, CAST( event_data AS XML ) event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file('d:\temp\xe_rpc_completed*etl', 'd:\temp\xe_rpc_completed*mta', NULL, NULL )
GO


-- Optionally add primary XML index; 
--!!NB this will cause table to grow up to 5x!
ALTER TABLE #tmp ADD CONSTRAINT pk_tmp PRIMARY KEY (rowId)
GO
CREATE PRIMARY XML INDEX xmlidx_event_data ON #tmp ( event_data )
GO


SELECT 
    e.c.value('@timestamp', 'DATETIME2') [timestamp],
    e.c.value('(data[@name = "cpu"]/value/text())[1]', 'INT') cpu,
    e.c.value('(data[@name = "duration"]/value/text())[1]', 'INT') duration_s,
    e.c.value('(data[@name = "reads"]/value/text())[1]', 'INT') reads,
    e.c.value('(data[@name = "writes"]/value/text())[1]', 'INT') writes,
    e.c.query('(action[@name = "sql_text"]/value/text())[1]') sql_text,
    e.c.query('.') event_data

FROM #tmp t
    CROSS APPLY t.event_data.nodes('event') e(c)
ORDER BY [timestamp]
GO

-- Collect some data
-- ...

-- When you're ready stop the session
ALTER EVENT SESSION [xe_rpc_completed]
ON SERVER
STATE = STOP;
GO

synchronous_event_counter probably won't help you as you just get an event and a count. It's good for say, counting logins or deadlocks in a day, something like that.

ring_buffer probably won't help you because it's a circular area with only limited space allowed. Basically it could run out of room and cycle over.

HTH