Tag Archives: SQL2012

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

Legend:
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

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

SQL Server 2012 for the DBA

This past Wednesday night, the 18th Jan, I had the privilege to speak at the local SQL Server user group – JaxSSUG.

I was asked by Tom Brenneman to co-present on some of the new SQL Server 2012 hotness. I am currently working on SQL Server 2012 content that will be the basis of an upcoming 4-day class so I was able to put together a quick overview presentation. It was called Delta Force: SQL Server 2012 for the DBA.

JaxSSUG presentation

It was a great evening with approximately 60 people attending. I was definitely the supporting act while Adam Jorgensen (blog | twitter) headlined with his presentation on the BI Tabular Model and PowerView in SQL Server 2012.

Thanks to Scott Gleason, Tom Brenneman and JaxSSUG for putting on a great event.

You may download the slide deck for my presentation here.

SQL Server 2012 for the DBA (virtual class)

A new skill for a New Year
Pragmatic Works Training has a full and varied schedule of classes coming up. You can find the list here.
I will be teaching the SQL Server 2012 for the DBA virtual class on 19th – 22nd March. Registration is now open for this class.

Overview

SQL Server 2012 is the newest version of SQL Server available from Microsoft. This four day (half-day) course is designed to introduce SQL Server DBA’s to the new and exciting features available in that version. We use a variety of lab exercises to ensure lecture sessions are backed up by hands-on experience. With the hands-on labs, you will experience how the new features can be utilized in your everyday DBA work. This course will cover topics such as AlwaysOn, ColumnStore indexes, SQL Server Data Tools, the FileTable feature, Azure and administering the BI platform within the context of SQL Server 2012.

Prerequisites

The target of this class is SQL Server DBA’s, developers, architects or IT professionals who have average to good T-SQL skills already. This class assumes that you have a good understanding of SQL Server fundamentals. For example, you should know and use the SQL Server Management Studio (SSMS) interface and associated toolbox items before attending this class. It is not mandatory but some experience using the Business Intelligence Design Studio (BIDS) interface will be an advantage to you.

Day 1 – Administration and Performance:

Module 1: Deprecated and Discontinued Features
Module 2: ColumnStore Indexes
Module 3: DMV’s & Extended Events
Module 4: Security
Module 5: SQLOS
Module 6: Central Management and DRU

Day 2 – AlwaysOn and Disaster Recovery:

Module 7: Availability Groups
Module 8: Using Availability Groups
Module 9: Clustering Enhancements
Module 10: Disaster Recovery Enhancements

Day 3 – Programmability & Azure:

Module 11: SQL Server Data Tools (SSDT)
Module 12: Data-tier Applications (DAC Pac’s)
Module 13: T-SQL Enhancements
Module 14: FileTable and Full-text Semantic Search
Module 15: Introduction to Azure

Day 4 – Administering the BI platform:

Module 16: SSIS Administration
Module 17: SSAS Administration
Module 18: SharePoint Administration
Module 19: Azure Reporting

On sale: $495