Extended Events were introduced in SQL Server 2008 as a light-weight monitoring mechanism. Most DBAs typically use SQL Profiler to monitor and analyse SQL Server events. However, SQL Profiler is not a lightweight tool and consumes too many resources for use in a production environment.
In SQL Server 2008, Extended Events can only be configured but using T-SQL and no user interface was available. SQL Server 2012, adds a user interface for configuring Extended Events.
To configure Extended Events using SQL Server Management Studio, go to the Object Explorer, under the Management node you will see the new Extended Events option .
Let us assume that we need to monitor user connections to the SQL Server.
Right-click the Sessions and select New Session Wizard which will allow you to configure using the Session Wizard.
Next, enter a session name and you can set the session to start with SQL Server startup by selecting Start the event session at server startup option. From this option you can make ensure your extended event is constantly running.
From the Choose Template tab you can either select an existing template or create your own session. For this example, the Connection Tracking event session template is used.
From the next wizard option, select Events to Capture where you will be presented with options for selecting the events which need to be captured.
After selecting the events, you will see the description about the event and the event fields associated with that event. Since there are lots of events, this feature can be used as an event search function.
Next, select the global events such as client host name, cpu id and database name from the Capture Global Event wizard option.
Typically you will not need all the events to be captured but only some filtered events. Set the Event Session Filters to allow you to set filters, you can also combine your filters with AND or OR conditions as shown in the below image.
Next, specify the session data configuration so that users can set the Maximum file size.
At this point, you can script the session so that it can be re-used again.
CREATE EVENT SESSION [Connection Tracking] ON SERVER
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id)
WHERE (([sqlserver].[database_id]=(5)) AND ([sqlserver].[client_hostname]<>N'DENALI'))),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.server_principal_name)
WHERE (([sqlserver].[database_id]=(5)) AND ([sqlserver].[client_hostname]<>N'DENALI'))),
ADD EVENT sqlserver.logout(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE (([sqlserver].[database_id]=(5)) AND ([sqlserver].[client_hostname]<>N'DENALI')))
ADD TARGET package0.event_file(SET filename=N'D:\Program Files\Microsoft SQL ServerDE\MSSQL11.DENALI\MSSQL\Log\Connection Tracking.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=ON,STARTUP_STATE=ON)
GO
At the final confirmation, you have two options – start the event, or alternatively watch the collection live which is a new feature in SQL Server 2012.
As in SQL Profiler you can watch data immediately in SQL Server 2012. When you select the relevant event, more details attached to that event will also be displayed.
