It’s the second Tuesday of the month and that means T-SQL Tuesday time! T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over 5 years ago. Each month a different blogger will present an idea to blog on. Side note: If you are having a hard time coming up with blogging ideas you could do worse than joining in every now and again. This month’s host is Jes Borland (b/t) and she wants us to talk about Extended Events.

Not my best subject I’ll admit. In fact, while I’ve studied it a little bit I’ve never actually gone through and implemented it. So while others are talking about some of the cool things you can do with it I thought I would go to the very basics and do a walk-through of setting up a simple EE (Extended Events) session. I’m also going to cheat a little bit and call this my answer to Russ Thomas’ (b/t) DBA Monthly Challenge for April 2014 – Profile Something; with Extended Events Another side note: This is also a great place to come up with blogging ideas.

First of all what are Extended Events. Extended Events is a system for collecting information about, you guessed it, events within SQL Server. It is meant to replace SQL Server Profiler which has been deprecated. Note: Profiler is not deprecated for Analysis Services. It became available in SQL Server 2008 but only in T-SQL. The GUI showed up in SQL 2012. Also, fair warning, the T-SQL behind it changed just a little when you get up to SQL 2012. So scripts like Jonathan Kehayias’ (b/t) script to convert Profiler traces to Extended Events only works on 2012+ not 2008 or 2008 R2.

For my walk-through I’m going to use the GUI in SQL Server 2014 and I’m going to generate a simple session that records what queries are running and how long they take. Basically just like the common and simple profiler trace using just the Default Trace template and a filter.

To start open the instance in question in the object explorer. Navigate to Management, Extended Events, Sessions. Right click on Sessions. I’m trying to make this as easy as possible so I’m going to choose the New Session Wizard.

Hit Next to go to the Set Session Properties tab and enter the name of the session. In this case I’m not creating a session that I want to start each time the instance starts so I’ll leave the Schedule box unchecked.

Hit Next and go to the Choose Template tab. In this case I’m going to choose to use an event session template, since again, I’m trying to make it as easy as possible on myself. I’ve selected the Activity Tracking template. You can see in the description box that it is meant to be similar to the Default Trace which is exactly what I was going for.

If you are following along you can see that at the bottom of he page there is a Finish button so I could just finish up right here but I’m going to restrict this to just one database like I frequently do with the ‘Default Trace’ in Profiler. So I’m going to hit Next again.

You can see that there are already a number of events already selected on the Select Events to Capture tab. As I understand it this is similar to the Events Selection option in Profiler. I’m not going to change any of them I’m just going to hit Next again.

Now we are on the Capture Global Fields tab. These are the columns to be downloaded from each event. Again we hit Next.

This tab is the one we have been looking for, Set Session Event Filters. I’ve added the field sqlserver.database_name into the additional filters and set it equal to AdventureWorks2014. Next yet again.

Specify Session Data Storage is where we can pick where the data will be stored. I’ve selected to save to files. Specifically 5 files of 50MB each. Of course that’s because I’m doing this on my workstation. On a server I’d pick larger files and possibly more of them. If you hit Next at this point you will go to the summary and have the option to script or create.

I went ahead hit the Script button so I can read through the output. It’s one of my favorite ways to learn T-SQL.

CREATE EVENT SESSION [Basic Trace] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.is_system,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_server_principal_name)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.database_mirroring_state_change(
    ACTION(package0.event_sequence,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.transaction_id)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.error_reported(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.transaction_id)
    WHERE (((((([package0].[equal_int64]([error_number],(8957))) OR ([package0].[equal_int64]([error_number],(17550)))) OR ([package0].[equal_int64]([error_number],(17551)))) OR ([package0].[equal_int64]([error_number],(15457)))) OR ([package0].[greater_than_int64]([severity],(18)))) AND ([sqlserver].[database_name]=N'AdventureWorks2014'))),
ADD EVENT sqlserver.full_text_crawl_started(
    ACTION(package0.event_sequence,sqlserver.database_id,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.transaction_id)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.full_text_crawl_stopped(
    ACTION(package0.event_sequence,sqlserver.database_id,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.transaction_id)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.hash_warning(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.missing_column_statistics(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.missing_join_predicate(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.object_altered(SET collect_database_name=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.transaction_sequence)
    WHERE ((([package0].[not_equal_uint64]([database_id],(2))) OR (([package0].[greater_than_int64]([object_id],(0))) AND (NOT ([sqlserver].[like_i_sql_unicode_string]([object_name],N'#%'))))) AND ([sqlserver].[database_name]=N'AdventureWorks2014'))),
ADD EVENT sqlserver.object_created(SET collect_database_name=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.transaction_sequence)
    WHERE ((([package0].[not_equal_uint64]([database_id],(2))) OR (([package0].[greater_than_int64]([object_id],(0))) AND (NOT ([sqlserver].[like_i_sql_unicode_string]([object_name],N'#%'))))) AND ([sqlserver].[database_name]=N'AdventureWorks2014'))),
ADD EVENT sqlserver.object_deleted(SET collect_database_name=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.transaction_sequence)
    WHERE ((([package0].[not_equal_uint64]([database_id],(2))) OR (([package0].[greater_than_int64]([object_id],(0))) AND (NOT ([sqlserver].[like_i_sql_unicode_string]([object_name],N'#%'))))) AND ([sqlserver].[database_name]=N'AdventureWorks2014'))),
ADD EVENT sqlserver.plan_guide_unsuccessful(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.server_memory_change(
    ACTION(package0.event_sequence,sqlserver.is_system,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.server_start_stop(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_server_principal_name)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')),
ADD EVENT sqlserver.sort_warning(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.transaction_id,sqlserver.transaction_sequence)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2014')) 
ADD TARGET package0.event_file(SET filename=N'Basic Trace',max_file_size=(50))
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=OFF)
GO

And because I want to see what happens I don’t run the script but also hit Finish.

There is a nice little check box to go ahead and start the session immediately. I’m not going to check it since I want to see starting and stopping the session after it’s been created.

And it’s been created. One of my favorite parts about Extended Events over profiler is that once a session is created I can start and stop it without having to re-create it each time. Stopping and starting it is as simple as right clicking and hitting start and then right clicking and hitting stop when I’m done. Of course both of these can be done via script as well.

-- Start the event session
ALTER EVENT SESSION [Basic Trace]
ON SERVER
STATE = start;
-- Stop the event session
ALTER EVENT SESSION [Basic Trace]
ON SERVER
STATE = stop;

As this has become a fairly long post I’ll stop here but fair warning I’ll be doing a post in a few weeks on reading the results.

Filed under: Extended Events, Microsoft SQL Server, SQL Judo's Monthly DBA Challenge, SQLServerPedia Syndication, SSMS, T-SQL Tuesday Tagged: Adam Machanic, Extended Events, microsoft sql server, Profiler, SQL Judo's Monthly DBA Challenge, T-SQL Tuesday, Trace