Extended Events Update In SQL Server 2012

Introduction

With the release of SQL Server 2012, Microsoft introduces a GUI in SQL Server Management Studio to create, modify, display, and analyze your session data. Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.

You can manage extended events in SQL Server 2012 using the Extended Events node specified under Management Folder in Object Explorer Window. If you happen to be connecting to a SQL Server 2008 server using SSMS from SQL Server 2012, this node will not exist. In this article I demonstrate how to create a simple Extended Event using the new GUI in SQL Server 2012.

Now you will see some important tasks to be done when creating a simple Extended Event in SQL Server 2012.

Step 1

Open SQL Server Management Studio and drill down to "Management" -> "Extended Events" -> "Sessions". There are two ways to create a session, you can either choose a New Session or the New Session Wizard. Right-click on the session and select "New Session Wizard" as shown in the image below.

Step 2

By opening the New Session Wizard, an "Introduction" window will appear. Click "Next" to continue. In the next window, "Set Session Properties" is opened where you can enter a Session Name and click the "Next" button to continue.

Step 3

The next screen will allow you to create the new event session based on a previously created template, or one of the templates provided by default with SQL Server 2012. Choose the "Do not use a template" option and click the "Next" button to continue.

Step 4

The "Select Events To Capture" window is opened. Select the events we want to capture. Suppose I choose database_attached, database_created, database_detached, database_started, and database_stopped. Click "Next" button to continue.

Step 5

The next window that appears is the "Capture Global Fields" window. This window allows you to specify the actions that you want added to each of the events in the session. For example, if a database was created then you would want to know which database it was so the database_id action would be the correct option. Suppose I select database_id, database_name, nt_username, server_instance_name and SQL_text. See that in the following image.

Step 6

The "Set Session Event Filters" page is displayed where you can define filtering on the events in the session. Leave the "Set Session Event Filters" window as it is and click the "Next" button.

Step 7

Once the filters have been created, the next step is to define the event storage. You can choose to either save the data to a file or work with the most recent data (ring_buffertarget). You can choose the number of events you want to keep. Suppose I choose work with the most recent data and click the "Next" button.

Step 8

The next window displays a Summary of the information selected in the previous windows. Click the "Finish" button to finish. After clicking the Finish button a "Success" window provides you the opportunity to start the event session immediately and to open the Live Data Viewer for the event session. Click the ""Close"" button to finish.

Step 9

Back in SSMS, the "Live Data Window" appears.

Step 10

Now run the following SQL statement to see the captured event along with the details we selected in the Live Data monitor:

create database ExtendedEvents

alter database ExtendedEvents set offline

with rollback immediate

go

 

Live Data monitor

Summary

As you can see, the Extended Events GUI in SQL Server 2012 makes it much easier to create, modify, display, and analyze your session data and these can be really helpful in troubleshooting SQL Server operational and performance issues.