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
Best Answer
Out of the box, no.
You'd need to hook into the event stream for extended events and then take action based on that. Tom Stringer has a good overview and sample code to do this!
See also Introducing the Extended Events Reader by Mike Wachal.
Jonathan Kehayias also describes it in detail in his Pluralsight course.