How can I replicate the functionality of sp_trace with extended events from C#?

Question

I am working on a C# application which uses the various sp_trace procedures to view events from a remote SQL Server database. The particular events I'm most interested in are when queries and stored procedures start and finish. I need data for each event like timing, SQL text, hostname, etc.

MSFT's documentation says that the sp_trace functionality is deprecated and is being replaced by extended events. I've also discovered several articles stating that extended events incur much less overhead on the database. However, I'm struggling to find a tutorial or examples of using extended events to replicate trace functionality.

Thus, my question is, what is the sequence of stored procedures I need to execute from my C# app to effectively trace the database?

Answer 1

Instead of using stored procedures, Extended Event traces are created using DDL. The example below shows a trace with both ring buffer and trace file targets, and a select from the 'sys.fn_xe_file_target_read_file' TVF to return XE trace data.

A ring-buffer target is useful if you don't need persistence and just want to keep the most recent events in memory. A file target allows you to specify a max file size and number of rollover files. I suggest a file target if you capture many events.

You can use SSMS Object Explorer to create a trace and then generate a script for your application needs.

CREATE EVENT SESSION [batch_and_rpc] ON SERVER 
ADD EVENT sqlserver.rpc_starting(
    WHERE ([sqlserver].[client_hostname]=N'ServerName')),
ADD EVENT sqlserver.rpc_completed(
    WHERE ([sqlserver].[client_hostname]=N'ServerName')),
ADD EVENT sqlserver.sql_batch_starting(
    WHERE ([sqlserver].[client_hostname]=N'ServerName')),
ADD EVENT sqlserver.sql_batch_completed(
    WHERE ([sqlserver].[client_hostname]=N'ServerName'))
ADD TARGET package0.event_file(SET filename=N'C:\TraceFiles\batch_and_rpc.xel'),
ADD TARGET package0.ring_buffer
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

ALTER EVENT SESSION [batch_and_rpc] ON SERVER STATE = START;
GO

SELECT * 
FROM sys.fn_xe_file_target_read_file('C:\TraceFiles\batch_and_rpc*.xel', null, null, null);
GO

To delete the XE session:

DROP EVENT SESSION [batch_and_rpc] ON SERVER; 

You can also read XE data using objects in the Microsoft.SqlServer.XEvent namespace. Be aware that method is not well-documented, though. See http://blogs.msdn.com/b/extended_events/archive/2011/07/20/introducing-the-extended-events-reader.aspx.