Managing SQL Server Extended Events in Management Studio

By:   |   Comments (8)   |   Related: > Extended Events


We've all heard about Extended Events and how they can help troubleshoot SQL Server performance, connectivity, and locking problems, but they seem so difficult to implement using T-SQL. Isn't there a better way to implement SQL Server Extended Events?


SQL Server 2012 introduces a GUI in SQL Server Management Studio to create and manage extended events. Prior to the SQL Server 2012 integration, Extended Events could only be created using T-SQL. In this tip, I'll show you step by step process on how to create a simple Extended Event in SQL Server 2012 using the new GUI in SQL Server Management Studio.

Creating an Extended Event has never been easier with SQL Server 2012. Open SSMS, and drilldown to Management, Extended Events, Sessions as shown in the image below.  By default, you should see an AlwaysOn_health and a system_health session already created. You will notice the AlwaysOn_health session is disabled and the system_health session is running. The system_health session collects system data that you can use to help troubleshoot performance issues. For the most part, SQL Server Extended Events use very little resources. 

Creating an Extended Event has never been easier with SQL Server 2012

There are two ways to create a session. Right click on the Sessions folder and you can choose New Session or New Session Wizard. In this tip, we'll step through using the wizard.

In SQL Server Management Studio, right click on the Sessions folder and you can choose New Session or New Session Wizard.

After clicking New Session Wizard, an Introduction window will appear that will give you a brief introduction. Click the "Next" button to continue.

The next window, Set Session Properties, is where you can specify the session name and whether or not you want the session to start on server startup. In this tip, I'll name the session DB Monitor and choose to start the event session at server startup. Click the "Next" button to continue.

SQL Server Extended Events Set Session Properties window

The next screen will allow us to choose a preconfigured template or create our own. If you've ever used SQL Server Profiler's built in template, these function the same way. In this tip, we'll create our own. Choose the "Do not use a template" option and click the "Next" button to continue.

SQL Server Extended Events pre-configured template or create our own

The "Select Events To Capture" window is an important one. This is where we select the events we want to capture. For this example, I want to monitor when my DB goes offline and when it becomes available, so I'll choose the events that relate to this: database_attached, database_created, database_detached, database_started, and database_stopped. Once you select the events from the "Event library" (on the left), click the right arrow to move them to the "Selected events" (on the right). Click the "Next" button to continue.

In SQL Server Extended Events use the Select Events To Capture window to determine the events you want to capture

*** Note *** - You can also filter category or channels to narrow down the choices as shown below.

In SQL Server 2012 Extended Events you can also filter category

The next window that appears is the "Capture Global Fields" window and this is also important because this window allows you to select the actions you want to capture for each event. For example, if a database was detached you would want to know which database it was so choosing the database_id action would be appropriate. In this example, I'll just choose a few actions to get us started: client_hostname, database_id, database_name, nt_username, server_instance_name, server_principal_name, and sql_text. Click the "Next" button to continue.

The SQL Server 2012 Extended Events Capture Global Fields window

The next window is where you can apply filters on the events to limit data. For this tip, I'll leave this window blank. Click the "Next" button to continue.

SQL Server Extended Events Set Session Event Filters

Specify Session Data Storage is where you can specify how you want to collect the data for analysis. You can either save the data to a file or work with the most recent data and specify the number of events you want to keep. For this tip, I'll choose "Work with only the most recent data" and keep the default of 1000 events. Click the "Next" button to continue.

Specify Session Data Storage in SQL Server 2012 Extended Events

The last window displays a Summary of the information selected in the previous windows. There is also a script button that allows you to script out the T-SQL for the event. Click Finish to create the session.

SQL Server 2012 Extended Events Summary Window

Once you click the "Finish" button you should receive a "Success" window where you can start the event session immediately and watch live data as it's captured. I'll go ahead and select both of these. Click the "Close" button to complete the process.

Create Event Session Success Window

Back in SSMS, you should see the Live Data window:

SSMS Live Data window for Extended Events

The window should be blank now, but if we try to take a database offline we'll see some activity in this window. First I'll run the following query to take the database Dev offline:


Once the database goes offline, you can see the event captured along with the details we selected in the Live Data monitor:

Event data captured when a SQL Server database is set offline in Extended Events

Next I will bring the database online and detach it and view the results in the Live Data Monitor:

EXEC sp_detach_db 'DEV', 'true'
Extended Events when bringing a SQL Server database online and detaching it

Of course, you're not going to be always sitting at your laptop staring at the Live Data monitor so it's ok to close this window and when you need to view events just right click on the session and choose Watch Live Data. You can also stop the session, script the session, and view the properties from this menu.

Extended Events options in SQL Server Management Studio

As you can see, the new Extended Events GUI makes it much easier to create and manage different Extended Events and these can be really helpful in troubleshooting SQL Server operational and performance issues.

Next Steps
  • To learn more about SQL Server 2012 and Extended Events check out this MSDN blog
  • To view all of MSSQLTips Extended Events information click here

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, July 14, 2015 - 12:31:48 AM - Shmuel K Back To Top (38201)

Hello,  thanks for the great article. 

I give permission "alter any event session"  to the development team. 

Which more permission I have to add them to use the extended event in Ssms?

When they try to use it in ssms, they received an error about permission required. 

Waiting for an answer. 

Best regards, 


Wednesday, July 17, 2013 - 2:54:49 PM - Amit Banerjee Back To Top (25884)



The Extended Events UI option is available from SSMS only when you are connected to a SQL Server 2012 instance. It is not available for SQL Server 2008 R2 or 2008 instances. From the screenshot, I see that you are connected to a 2008 R2 instance.

Thursday, October 11, 2012 - 2:37:56 AM - Alexei Vladyshevski Back To Top (19862)

i have installed SSMS 2012, but I dont have Extended Events as shown on picture, why?

Tuesday, July 17, 2012 - 1:49:09 PM - Brady Back To Top (18583)

Thanks for clarifying Jonathan.

I have used your SSMS addin for previous versions of SQL and can't say enough about it. Good job!

Tuesday, July 17, 2012 - 1:06:50 PM - Jonathan Kehayias Back To Top (18582)


Yes, there are plenty of events that can impact performance on a production system.  The showplan events specifically have a very high overhead, as much as 20%+ degredation in performance when one of the events is enabled in a active session, even if it is predicated to prevent it from ever firing completely.


You can sometimes create a script in 2012 that might work in 2008, but there are significant differences in Events, and targets have been renamed in 2012 which is why the UI is not backwards compatible.  If you want to work with 2008 Extended Events download the SSMS Addin I wrote for it from Codeplex and that will give you all the functionality in SSMS 2008 and 2008R2.


Tuesday, July 17, 2012 - 11:49:32 AM - Carl Reeds Back To Top (18581)

It appears to be a SQL 2012 only feature.  I opened a SQL 2008R2 instance from with SSMS2012 and the Extended Events branch is not available underneath Management.

My question is if you script out an extended event created within the GUI in 2012, can you run the script on a SQL2005, SQL2008 or SQL2008R2 machine?

Tuesday, July 17, 2012 - 10:14:07 AM - Stan Back To Top (18577)

Are there any events that youd don't recomvend to use on production server, because it could cause perfomance issues?

Tuesday, July 17, 2012 - 9:57:22 AM - Dustin Jones Back To Top (18575)

Does the instance you are connecting to have to be a 2012 instance? In other words, can you use the Extended Event GUI in 2012 SSMS to connect to a 2008 instance?

get free sql tips
agree to terms