Category Archives: Technical SQL

Introduction to Extended Events

I have done a few presentations on the intro to Extended Events and I wanted to get the content out here on my blog for you. So, here it is:

Extended Events (XEvents) were first introduced with the release of SQL Server 2008. They were powerful but not adopted due to the fact that they were difficult to understand, setup, configure and use. They were very complex to figure out compared to traditional tools such as Profiler, SQL Trace & Activity Monitor. This was because there was no user interface and all operations were command-line or query driven. The query toolset works well for DMV’s, but DBA’s were unwilling to try to navigate the minefield that they thought XEvents were.
Just what is / are XEvents ?
Well, Books On-Line describes them as: “a lightweight, highly scalable, highly configurable performance monitoring system that uses very little system resources and allows admins to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.” WOW, that’s a lot of buzz words!!
Let’s break it down.

Now, an event is ANY recordable activity of significance that occurs in the normal execution path of a piece of code (disk read, memory page out, etc). The Event Tracing for Windows (ETW) framework is a process within the Windows Operating System of logging kernel or application events to a log file.

Event Tracing for Windows Concepts
Here is a diagram of the internals of the Event Tracing for Windows framework:

Event Tracing for Windows framework

The events themselves are sometimes sourced from providers which are applications that contain event tracing information.
Event tracing sessions are merely enable the capture of events that occur in the system. The events and associated information is stored in a buffer until that information is flushed.
Consumers receive the events from a log file or in real-time. Consumers can accept multiple sessions simultaneously and the system will deliver events in chronological order.
Controllers start and stop the event trace session and enable providers.

ETW References:
MSDN: Event Tracing
MSDN Magazine: Improve Debugging And Performance Tuning With ETW

The Extended Events (XEvents) feature within SQL Server is an architecture that uses and builds on this ETW framework.

XEvents Concepts
Here is a diagram of the internals of XEvents:

Exended Events within SQL Server

The XEvents Engine is a collection of services that:
     Enables the definition of events
     Enables processing event data
     Manages XEvents objects in the system
     Maintains a list of XEvents sessions and manages access to that list
An XEvents Package is a container for XEvent objects. The packages are package0, sqlserver, sqlos, SecAudit, sqlclr and ucs (unified communications stack), etc. Contained in an XEvent package is a combination of the following:
     Events: An event is a point of interest where something happens in the execution path of SQL Server. There are 446 events in SQL 2012 and 763 events in SQL 2014 and each one corresponds to an event from SQL Trace. When an event fires, that event returns state information relating to the corresponding point of code. This basic information can be added to by the use of Actions
     Actions: These are bound to the event during the definition of the Event session and perform a specific task when the event fires. This task can be: collecting additional information from memory, a stack dump and data inspection, aggregating event data, run-time statistics collection or user-input collection. In SQL 2012, there are 43 actions that can be used.
     Types: Straight forward, data type of the information contained in the event. There are 28 types in SQL 2012 including ansi_string, unicode_string, boolean, guid, filetime & callstack.
     Maps: This is the lookup table that will let the admin know what the information inside the event actually means. For example, the “lock_mode” map system table will map the key to the value for various types of locks that can occur.
     Predicates: These are the set of rules that control the circumstances under which the event actually fires and are used to evaluate events as they are processed. They also provide us with the ability to filter the events that we are capturing.
     Targets: These are the event consumers that process events, either synchronously or asynchronously. Synchronous targets have the event buffered to them by the executing thread, and asynchronous targets have the events dispatched to them by the dispatchers in the Database Engine when the buffers fill up.
XEvents Targets are the data consumers or endpoints of the event data. Targets can write to a file, store event data in a memory buffer, or aggregate event data. Targets can process event data both synchronously, on the thread that fired the event, or asynchronously on a background system thread. For performance reasons, asynchronous targets should be used whenever possible. When an event fires, the synchronous targets are served the data immediately to minimize the impact to the executing session. After all of the synchronous targets have processed the event data, the data is queued in the session buffers where it waits to be dispatched to the asynchronous targets.

XEvent Targets:
     Event File (asynchronous)- Creates two types of files, log and metadata for writing complete buffers to disk. The metadata file describes the output log file and allows the events and associated actions to be parsed correctly. The file names and locations must be provided to use this target. Use to write event session output from complete memory buffers to disk.
     Event Ring Buffer (asynchronous) – Temporarily holds event data in memory, in either a first-in first-out mode where events are discarded in the order received or, on a per-event mode where a set number of each event type is kept and then discarded.
     Event Pairing (asynchronous) – Many kinds of events occur in pairs, such as lock acquisitions and lock releases. Use to determine when a specified paired event does not occur in a matched set. Matches two events based on one or more columns present in the events. Matched pairs are then discarded leaving only unmatched events for analysis. To use this target, you must provide the events to be matched and the columns to be used for the matching.
     Event Tracing for Windows (ETW) (synchronous) – Use to correlate SQL Server events with Windows operating system or application event data. Only one ETW session to the SQL Server provider can be open at a time. For this reason, the ETW target obtains a reference to this one session for all ETW targets regardless of their XEvents session scope.
     Event Counter (synchronous) – Counts all specified events that occur during an XEvents session. Use to obtain information about workload characteristics without adding the overhead of full event collection.
     Event Bucketing (asynchronous) – Creates buckets based on the user-specified columns, and only collects and retains data for the specified source. If the event data is too large for retention, it is truncated. Use to count the number of times that a specified event occurs, based on a specified event column or action. This is also referred to as the Histogram Target.

An XEvents Session is just a collection of events, the actions that occurs when an event fires and the targets that are the destination of the event information. XEvents Session options also define the start and stop parameters, timeline of events and actions, memory buffer size, and how event loss can occur if the buffer fills up.

XEvent Session State

An XEvents Session has implied boundaries that contain the configuration and session objects (events, actions or targets). These boundaries do not prevent the same object from being used in multiple sessions. They just define the context of that specific session. Policies are attached to sessions to define buffering and dispatch.
XEvent Session Boundaries

XEvent References:
MSDN: Extended Events
MSDN: Extended Events Tools

Here is a link to the PowerPoint slide deck that I use in my presentations on this subject:
PowerPoint Slide Deck on Extended Events

Please Note:
When you install the RTM or SP1 versions of SQL Sever 2012, there is a bug in the “Activity Tracking” template within the Extended Events management console UI and wizard. My good friend Jonathan Kehayias ( blog | twitter ) discovered the bug and has posted a fix on his blog. You can get it here.

Aaaaaand, that’s it.
I will be posting a follow up soon with a demo on how to run XEvents on your server to perform some basic event capturing.

How to get Gum out of MyHair

Well, here is a fun issue I ran into this week at one of our clients that I’d like to chat about.

Now, I may not have much hair to get gum stuck in, but some people have had this happen to them before.
But, because I am a database professional and I live inside of computers, I’d like to look at a virtual gum-in-hair problem.

First, Let’s have a look at a couple of tables that I’ve got here.
MyGum table
As you can see, MyGum is a nice gum product table that lists gum types, brands & colours, etc.

MyHair table
The MyHair table has the names of a bunch of people with hair types, colours and … well, would you look at this, some of them have gum stuck in their hair.

Oh, dear. Let’s do a quick query of the 2 tables to see what kind of gum is stuck in their hair …
Table join error
Uh, oh !! Error !!
“Wow, I’ve never seen a collation error when doing a plain join of 2 tables in the same database before. Isn’t the collation set at the server or database level ?” you might say.
Well, grasshopper, that is a good question. Collation can also be set at the column level. Here’s the article in Books Online that discusses column collation :

So, let’s look at the collation on these 2 tables
MyHair MyGum collation
Would you look at that … some bloody developer, dumb user, um, silly person has gone & changed the collation of the Gum_ID column.

O-Kay, that is easy to fix. All we need to do is right-click on the table in Management Studio and select “Design” from the drop down. Then, we look at the “Collation” property under “Table Designer” for the Gum_ID column. We delete the content to set the collation to database default or click on the ellipses to choose the specific collation that we would like. We click OK and click Save. Voila, we should be good to go. Let’s try our joining query again, shall we ?
Table join success

And THAT is how to get a little bit of collation Gum out of MyHair.

WARNING : You will need to make sure that no other columns have funny collation as you will need to fix them all.
WARNING 2 : You will need to be aware of the collation of TempDB, too, as using and joining temp tables will cause this same issue.

Clear recent project list in BIDS

So, you’ve been working, working, working in Business Intelligence Development Studio (BIDS), which is a variant of Visual Studio, and you’ve been cranking out some really awesome BI projects. Problem is that you REALLY don’t want to close and reopen your BIDS because now you have a loooong list of recent projects to scan through to find the one that you want to work on.

Click NOW !!

Click NOW !!

Well, have I got a deal for you …

If you click on this registry file, you will receive the solution that you have been looking for. This will clean that list of recent projects Like NEW !!

Here is the registry entry for the project that will be cleaned out, if you prefer to do it manually :

Perhaps your list of recently opened files is getting too extensive. If you click on this registry file in the next 18 minutes, I will throw the file list cleaner in COMPLETELY FREE !!

Here is the registry entry for the file list that will be cleaned out, if you prefer to do it manually :

Act now to save yourself valuable minutes and possibly hours of frustration EVERY DAY !!

Disclaimer: Professional coder on a closed system. Typical results, not experienced by everyone. If you have a project or file list longer than 40 items, call Devin Knight.