Extended Events Session Filters in SSMS

Recently during a demo at a SQL Saturday the query to pull the Extended Event session data, didn’t return the expected results. The session I used for the demo was the create database statement.

Prior to the session, I deleted the Create Database session, however did not delete the target files because they are part of the demo.  Then I recreated the session, just as I had done before.  However, this time was there was a difference when I attempted to read the target data.  The entry for the newly created database was not showing up when I used the GUI, however was showing up when I read the XML.  During the session, I was not able to figure out why that was the case.

When I got home I did some more testing.  As I turns out, when I had done the same session the previous week at a different SQL Saturday, I created a filter for the data and that filter persisted, even though I had deleted the session and created it again.

So I decided to do some testing.  I made sure there were no longer any filters on the session data, then deleted the Create Database session.  Just to have a clean slate, I then restarted the SQL Server service.

Then I created two databases, test4 and test5.  As you can see below, they are both showing up when reading the event file using the GUI.

Then I added a filter to only show the test5 database by right clicking on the test5 value and then clicking, “Filter by this value”.

This resulted in only the test5 database showing in the grid.

Now that the session was up and running, I create another database, test6.   I restarted the Extended Event session to force the new database event to be written to the target.  Then I read the target again.  As you can see below, not only is test4 not showing up, but the newly created test6 is also not appearing.

I right clicked the test5 value and then to “Filter by this value”.  What I saw kind of surprised me a bit.  Below is a screenshot of what I saw, two entries for the test5 value.  This means that one had to exist before I tried to filter by the value again.

I clicked the Clear All button, and sure enough, all three databases now appear.  Just as in the image below.

What I found interesting is that the filter persists, even after deleting the session and creating it again.  Of course, deleting sessions is not a common task and recreating it with the same name and storing the target files in the same location is even less likely to happen.  However, I did find this interesting.

 

Thanks for stopping by my blog!!!!

Create Database Event – When does it fire?

Recently when presenting my Extended Event session at a SQL Saturday an attendee asked if the Create Database session I used as a demo would pick up the Tempdb that is recreated at start up. The answer is that it will not.  But this question got me thinking, what about a restore or attaching a database, would the event pick that up?

I have to admit, while I suspected that it would not pick up the TempDB creation, I was not 100% certain on whether or not the Create Database event fires on start up.  So I wanted to do a quick test and the answer is that the event does not fire when the SQL Server service is restarted.

What about when a database is attached?  When attaching a database, this event does indeed fire.  Which of course makes sense, at least to me, you are creating a new database when attaching the database files.

What about when a database is restored?  Although a restore is in reality a drop and recreate the database, it does NOT fire the Create Database event.  I tried both restoring the database with the same name and a different name and neither one was picked up by the Create Database event.

Thanks for visiting my blog!!!

 

All my Extended Event Options are Greyed out

Have you ever tried to change the options for an Extended Events session, only to see this.

There are a number of options that are greyed out and can’t be changed.  This is because the session is running.  There are some options that can be changed, just none on the Advanced tab.  This is an easy fix, if you need to change one of these options, simply stop the session and make your change.  Don’t forget to restart the session.

Something to keep in mind.  If you are using the file target, stopping and restarting the session could delete your oldest file and will create a new session file.  This could potentially result in the loss of key data.

Thanks for stopping by my blog and I hope you learned something.

Extended Events Max Dispatch Latency

When you create an Extended Event session, there are a number of properties that you can set.  These properties are spread through out the tabs in the Session Properties window.

Below you will see the four tabs,  General, Events, Data Storage and Advanced.

Just as a brief summary let’s go over a few of the key properties in each tab.

In the session tab, the obvious properties is the “Session Name”.  But there is also another key property, “Start the event session at server start up”.  This is an important property that can assure the session starts in the event of a server restart.

The event tab is exactly what you think it might be, it is where you will decide what events you want to capture and what data columns you would like to capture.

There are a number of options for storage and you will set this in the Data Storage tab.  Where the data collected is stored is referred to as targets  Two of the commonly used targets are ring_buffer, this is memory, and file target.

Which brings to the last table, Advanced.  This is where the Max_Dispatch_Latency property can be found.  The max_dispatch_latency property is the maximum duration that an event, once captured, would reside in the buffer before written to the target.  This doesn’t mean it will always take this long, just the maximum amount of time it will take.  The default for this property is 30 seconds.  For the most part, this is a good number and should avoid changing it.

You would logically think that the minimum setting would be zero seconds.  If you think that way, you are correct.  However, 0 does not mean 0 seconds.  When this is set to 0 it means the event will stay in the buffer until the buffer becomes full.  This is the same set setting the “Unlimited” option you see below. Given this, the true minimum is one second.

When I present my Extended Event session I usually use the Create Database event in my demo.  Once, I create the database, I go immediately to the target to see if the event has been written to the target.  Usually that takes just a few seconds.  I only see the event written to the target about 50% of the time.  This is because of the max_dispatch_latency.

Here is a really nice article by Jonathan Kehayias on the properties of an extended event session, click here.

Thanks for visiting my blog and I hope you learned something!

Events…No Events

Extended Events is a great way to capture information about what is happening on your server.  You can capture information about deadlocks, failed logins, wait stats and many more important performance data points.

One of the nice features of the Extended Event interface in SQL Server Management Studio is the searching feature that can be used when setting up a session.  In SQL Server 2017 there are over 1,500 events that can be utilized.  Searching through all those events can make it a challenge to find the event you are looking for.

Luckily Microsoft has given us some help.  If you look at the image below, you can see under the “Event Library” box the two letters “de”.  As expected, this returns any event name that has “de” in the name somewhere.  It could be at the beginning, middle or the end of the event name.

The events returned will be based on two additional things category and channel.  Both of these options help to limit the number of events you will see in your results.

Category is simple groups of events.  For example, in the database category you will find many events related to databases. These include the following:

        • Database Starting
        • Database attached
        • Database created
        • Many..many more

The Channel is also a grouping of events, but this grouping is more on who and or why the events might be used. Below is a sampling of what you will find in the Analytic channel.

I have never really changed either one of these and I have been able to find every event I needed.

Now, what does it mean when I enter a value, for example “deadlock” into the Event Library search box and I get this:

Notice that no events are showing up.  Well there are a few reasons why this is happening. First thing to check is the Category and Channel to see if you are limiting the results.

Another reason is that all the events that contain that string are already in use.  If you look at the above screenshot, you will see that there are five events related to deadlocks and the are all in the “Selected Events” pane.

Now you can see that all 5 events are now showing up to select from.  This is a very short blog, but hopefully you learned something.

Thanks for stopping by my blog!!!

Where are the events for Extended Events stored

Today I received an email from someone who attended my Extended Event session on Boston last fall.  He was asking what table stores information about what Extended Events exist on the server.  The answer is sys.dm_xe_objects data management view.

In this DMV, obviously there are a number of columns.  This post will focus mostly on the object_type column.  This column has a number of potential values, including Event, Target and Action.  When setting up a SQL Server Extended Event session these are a few of the key items that need to be filled out.

Let’s just take a few moments to identify what these are.

Target – This is where the data captured by the Extended Event session will be stored.  Many times you will find the target to be the event_file target, although from time to time you will find the data going to other targets.  The data will be stored in an XML format. See the image below.

 

Actions – These are also referred to as Global Fields.  These are the fields that many of the events have in common.  You can decide what data you would like to collect.  These differ from the Event Fields in the sense that are shared with all  the events.  Event Fields are data points that are specific to that event.  Sometimes you will find a particular data point in both the Actions and Event Fields.

Events – This is the focus of this post.  Events are things that happen on the server and you would like to capture the what, when and other information about the event.  As each release of SQL Server comes out, there are more and more events included. The image below is an example of some of the events you might want to use.  In this case they are related to Deadlocks.

Now back to the purpose of this post, where is this information found in the system tables.  As stated above, the information can be found in the sys.dm_xe_objects DMV.

If you run this query you will see all the events for that instance.  When I run this on my SQL Server 2017 server I get 1,505 events.  Although, your results might be slightly different depending on version.

SELECT Name
, Description
FROM sys.dm_xe_objects
WHERE object_type = ‘event’

Here what the data set looks like.

There are really two columns that are important for this post, the Name and Description columns.

Thank you for stopping by and a big thank you to the attendee that emailed me the question.