Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Managing SQL Server Extended Events in Management Studio

MSSQLTips author Brady Upton By:   |   Read Comments (7)   |   Related Tips: More > Extended Events
Problem

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?

Solution

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:

ALTER DATABASE Dev SET OFFLINE WITH ROLLBACK IMMEDIATE

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:

ALTER DATABASE Dev SET ONLINE WITH ROLLBACK IMMEDIATE
GO
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


Last Update: 7/17/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, July 17, 2012 - 9:57:22 AM - Dustin Jones Read The Tip

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?


Tuesday, July 17, 2012 - 10:14:07 AM - Stan Read The Tip

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


Tuesday, July 17, 2012 - 11:49:32 AM - Carl Reeds Read The Tip

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 - 1:06:50 PM - Jonathan Kehayias Read The Tip

Stan,

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.  

http://connect.microsoft.com/SQLServer/feedback/details/732870/sqlserver-query-post-execution-showplan-performance-impact

Carl,

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.

http://extendedeventmanager.codeplex.com/

 


Tuesday, July 17, 2012 - 1:49:09 PM - Brady Read The Tip

Thanks for clarifying Jonathan.

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


Thursday, October 11, 2012 - 2:37:56 AM - Alexei Vladyshevski Read The Tip

 

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

Screenshot attached
http://snag.gy/sPvbd.jpg


Wednesday, July 17, 2013 - 2:54:49 PM - Amit Banerjee Read The Tip

Alexei,

 

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.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.