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.
Sql-server – Monitor stored procedure statistics with extended events
extended-eventsmonitoringsql-server-2008-r2
Related Question
- Sql-server – Expensive Query not showing in extended events trace
- Sql-server – Read system_health event_file instead of ring_buffer for deadlocks extended events
- Sql-server – module_end extended event enough for stored procedure logging
- Sql-server – Capture Extended Events Histogram only during time window
- Sql-server – Problem tracing Error: 8623 with extended events
Best 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: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