MSSQL Extended Events

  • Before SQL Server 2008 in order to trace we had to use the SQL Server Profiler tool. It is still available in higher versions.

  • Starting with SQL Server 2008, we have a new tracing and diagnostic capability known as Extended Events.

  • There is an Event called "system_health" started automatically when MS SQL starts.

"The system_health session is an Extended Events session that is included by default with SQL Server. This session starts automatically when the SQL Server Database Engine starts, and runs without any noticeable performance effects. The session collects system data that you can use to help troubleshoot performance issues in the Database Engine."

  • In MS SQL 2008/2008R2 Extended Events can be managed only using TSQL and by default the system_health session collects to a RING BUFFER.

  • Since MS SQL 2012 Extended Events are available in MS SQL Management Studio and the system_health session are PERSISTED TO DISK.... E:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\system_health_0_131187516603310000.xel (4 files 5 MB each)

Example Usage

The method below works in:

  • SQL2016 SP1+

  • SQL2014 SP3+

  • SQL2012 SP4+

Create Event

CREATE EVENT SESSION [tls] ON SERVER

ADD EVENT sqlsni.trace(

WHERE (([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%'))))

Set Event Target

ALTER EVENT SESSION [tls] ON SERVER

ADD TARGET package0.ring_buffer(SET max_events_limit=(100000),max_memory=(10240))

WITH (MAX_MEMORY=10240 KB,STARTUP_STATE=ON)

GO

Start Event Monitoring

ALTER EVENT SESSION [tls] ON SERVER STATE = START;

Stop Event Monitoring

ALTER EVENT SESSION [tls] ON SERVER STATE = STOP;