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 Answer
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