• Login/Failed login auditing
  • Has this login been used recently?
• C2 Auditing and Common Criteria Compliance
• Audit Columns
• Temporal Tables (?)
  • Permissions required for developing with Temporal Tables.
• Change tracking (?)
• Triggers
  • Logon triggers
    • Escaping from a runaway logon trigger.
  • DML triggers
  • DDL triggers
    • DDL Trigger to log database level security commands.
    • Can we get a license for DDL triggers?
    • Performance is bad. Did you change anything recently? No. Are you sure?
• Tracing/Profiler
  • What is this default trace you speak of?
• Extended Events
  • A walk-through of creating the Activity Tracking template using Extended Events.
  • Want to be more comfortable with the Extended Events Viewer?
  • What is the system_health extended events session?
• Server/Database Auditing
  • Reviewing the SQL Server Audit
  • Keep an eye on those Audit files!
• External Tools

Something I noticed as I was working on this post, is that, in general, the subjects I know the most about, I’ve blogged the most about. To some extent this is just because I know more, I work on it more, so I have more to say. But there is also a fair amount of knowledge and familiarity from researching and writing about the subject.

I’m not perfect by any means, and there is a distinct chance I’ve missed something in my list above, so if you have any suggestions for something I may have missed or that you’d like to see mentioned let me know. No promises but I’ll give it a shot.

So take some time this month and review what information is available (for example the deadlock graphs), run some tests, and read the data.

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.