1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.