A walk-through of creating the Activity Tracking template using Extended Events.

4

June 9, 2015 by Kenneth Fisher

T-SQL TuesdayIt’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.

EEWizard

EEWizard2

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.

EEWizard3

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.

EEWizard4

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.

EEWizard5

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.

EEWizard6

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

EEWizard7

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.

EEWizard8

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.

EEWizard9

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.

EEWizard10

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.

EEWizard11

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.

EEWizard12

-- 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.

4 thoughts on “A walk-through of creating the Activity Tracking template using Extended Events.

  1. […] A walk-through of creating the Activity Tracking template using Extended Events T-SQL Tuesday #67 – Extended Events for DBCC Visual Business Intelligence – Data Sensemaking Requires Time and Attention Installing and upgrading default scripts automation – part one – Introduction New Free Quizzes: Indexing, Query Writing, and More ColumnStore Indexes evolution from SQL Server 2012, 2014 to 2016 Excel crashes when enabling macros New course: Index Fragmentation Internals, Analysis, and Solutions Still Using Windows Logins for your Databases? You’re Doing it Wrong Getting Started With Always Encrypted Estimating the Size of your Database Backups 42 Things You Should Never, Ever Say To a DBA The Best Tool for Comparing Whether Two T-SQL Queries Produce Identical Results High Performance T-SQL using Code Patterns SQLXML Bulk Loader Basics The Cult of Automation SQL Server Monitoring with Powershell and the SQL Server Platform: Analysis and Reporting (Part 3 of 3) Continuous Integration for Databases: Getting Buy-in Interview with Steve Stedman about the Database Corruption Challenge Microsoft Breathes Life back into SQL Server BI On-Prem Minion Backup intro webinar June 3! Coming Soon SQL 2016 Live Query Statistics (LQS) SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server Enable Query Store on a Database – SQL Server 2016 How To Move TempDB […]

  2. […] SQL Server DBA non-technical interview questions T-SQL Tuesday #67 – Extended Events for DBCC A walk-through of creating the Activity Tracking template using Extended Events. #TSQL2sDay: #Powershell and Extended Events T-SQL Tuesday #67 : New Backup and Restore Extended […]

  3. Bill says:

    Follow up session on reading the extended events?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: