SQL Server Extended Events

SQL Server extended events were introduced in SQL Server 2008 as a more flexible method for event monitoring to replace the more resource intensive SQL Server trace. Server side trace or SQL Profiler trace is still in the product as of SQL Server 2016, but is expected to be removed at some point.

Extended event sessions can be created via the SSMS GUI, or via T-SQL. Here is an example script of the latter to monitor for SQL batch start and stop events for a specific user: –

/*
Description: -

Create an extended events session to monitor batch start and stop for a specific username.

Query SQL Server actions via: -

SELECT [Action] = p.name + N'.' + o.name + N',',*
FROM sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p ON o.package_guid = p.guid
WHERE o.object_type = N'action' AND
p.name = N'sqlserver'
ORDER BY [Action]

Change History: -
Who When Why
Andy Jones 04/11/2015 Created.
*/
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'Stored procedure execution')
 BEGIN;
 DROP EVENT SESSION [Stored procedure execution] ON SERVER;
 END;

CREATE EVENT SESSION [Stored procedure execution] ON SERVER

ADD EVENT sqlserver.sql_batch_starting (
 SET collect_batch_text = (1)
 ACTION(
 sqlserver.client_app_name,
 sqlserver.client_hostname,
 sqlserver.database_id,
 sqlserver.database_name,
 sqlserver.nt_username,
 sqlserver.plan_handle,
 sqlserver.server_instance_name,
 sqlserver.server_principal_name,
 sqlserver.server_principal_sid,
 sqlserver.session_id,
 sqlserver.session_nt_username,
 sqlserver.session_server_principal_name,
 sqlserver.sql_text,
 sqlserver.username
 )
 WHERE (username = (N'ajones'))
 ),

ADD EVENT sqlserver.sql_batch_completed (
 SET collect_batch_text = (1)
 ACTION(
 sqlserver.client_app_name,
 sqlserver.client_hostname,
 sqlserver.database_id,
 sqlserver.database_name,
 sqlserver.nt_username,
 sqlserver.plan_handle,
 sqlserver.server_instance_name,
 sqlserver.server_principal_name,
 sqlserver.server_principal_sid,
 sqlserver.session_id,
 sqlserver.session_nt_username,
 sqlserver.session_server_principal_name,
 sqlserver.sql_text,
 sqlserver.username
 )
 WHERE (username = (N'ajones'))
 )

ADD TARGET package0.ring_buffer(SET max_memory = (10240))

WITH (
 MAX_MEMORY = 4096 KB,
 EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY = 5 SECONDS,
 MAX_EVENT_SIZE = 0 KB,
 MEMORY_PARTITION_MODE = NONE,
 TRACK_CAUSALITY = ON,
 STARTUP_STATE = OFF
 );

IF NOT EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = N'Stored procedure execution')
 BEGIN;
 ALTER EVENT SESSION [Stored procedure execution] ON SERVER STATE = START;
 END;

This example is writing the output to memory. Live session data can be viewed by SSMS | Management | Extended Events | Sessions | Stored procedure execution | Right Click | Watch Live Data. Alternatively, the session data can be written to a file and queried via T-SQL and XQuery. That method and other extended event options will be described in a future blog post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s