Extended Events in SQL Server 2012

Extended Events were introduced in SQL Server 2008 as a light-weight monitoring mechanism. Most DBAs typically use SQL
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
, 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

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.


ADD EVENT sqlserver.connectivity_ring_buffer_recorded(


    WHERE (([sqlserver].[database_id]=(5)) AND ([sqlserver].[client_hostname]<>N'DENALI'))),

ADD EVENT sqlserver.login(SET collect_options_text=(1)


    WHERE (([sqlserver].[database_id]=(5)) AND ([sqlserver].[client_hostname]<>N'DENALI'))),

ADD EVENT sqlserver.logout(


    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



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.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |