SQL Server: Introduction to Extended Events

Learn how to use Extended Events to perform analysis and troubleshooting on SQL Server using either Transact-SQL or the Extended Events UIs. Applicable for developers, DBAs and anyone who uses SQL Server from SQL Server 2008 onwards.
Course info
Rating
(207)
Level
Intermediate
Updated
Sep 25, 2012
Duration
5h 0m
Table of contents
Introduction
Extended Events Architecture
Extended Events Objects
SQL Server 2012 UI Integration
SQL Server 2008 SSMS Add-in
Defining Event Sessions
Event Session Management
Basic Targets
Basic Troubleshooting Scenarios
Description
Course info
Rating
(207)
Level
Intermediate
Updated
Sep 25, 2012
Duration
5h 0m
Description

Extended Events were added in SQL Server 2008 as a method of performing analysis and troubleshooting of SQL Server, with the ability to troubleshoot scenarios that have never been possible before. SQL Trace has finally been deprecated in SQL Server 2012 and so it is now imperative that developers and DBAs learn how to use Extended Events. This course explains the architecture of Extended Events, and how to create and manage event sessions, using events, actions, predicates, and basic targets. It then gives comprehensive coverage of the Management Studio UI for Extended Events that was added in SQL Server 2012, as well as the freely-available SSMS add-in that Jonathan wrote for SQL Server 2008 and 2008R2. The course ends by presenting some common troubleshooting scenarios that can be easily accomplished using Extended Events. It is perfect for those with no experience of Extended Events, those who are new to the Extended Events UIs and those with some experience but who want to solidify their understanding of Extended Events and how to use them. The information in the course applies to all versions from SQL Server 2008 onwards.

About the author
About the author

Jonathan Kehayias is a Principal Consultant with SQLskills. He is the youngest person ever to achieve the Microsoft Certified Master - SQL Server 2008 certification and has been a SQL Server MVP for many years.

More from the author
SQL Server: Change Data Capture
Intermediate
2h 10m
Mar 27, 2015
More courses by Jonathan Kehayias
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, this is Jonathan Kehayias from sqlskills. com. And I'm recording this course for Pluralsight. This course is an introduction to Extended Events in SQL Server. And this is Module 1, which provides an introduction to the rest of the course. This course is going do apply to all versions of SQL Server. From SQL Server 2008 onwards. Some of the features that are going to be covered inside of this course are going to be specific to SQL Server 2012. And some of the changes that have occurred with Extended Events in the 2012 release. Whenever this is the case, it will be pointed out as a part of the course. Extended Events was added as a new feature in SQL Server 2008. And it provides a new method of collecting diagnostic data from SQL Server in a way that you could never get detailed information before using the previous features available. SQL Trace in SQL Server 2012 was deprecated as a feature in the product. And deprecated means that it will be removed at some future release of SQL Server. But it still remains available in the current release. However, because SQL Trace has been deprecated that makes Extended Events its final replacement. And making sure that you understand how to use Extended Events is going to be critical to being able to support SQL Server in the future.

Extended Events Architecture
Jonathan Kehayias: Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is Module 2; Extended Events architecture. So with Extended Events, we've got a complete change in the architecture for doing diagnostic data collection and collecting troubleshooting data from SQL Server and one of the things that's really important is understanding how that architecture change was made to be able to best leverage Extended Events for doing your diagnostic data collection inside of SQL Server 2008, 2008 R2, or 2012. So what we're going to look at in this module is the architecture overview for Extended Events and how it was actually implemented inside of the database engine, what the event execution life cycle is so that you understand what's going on when events are actually executing and firing inside of the engine all the way through how dispatching occurs and data is actually collected into the targets for consumption by your later for analysis, and then the Extended Events objects that exist inside of the engine and how you might use those for collecting data or defining your Event Sessions for reducing the amount of manual troubleshooting work you have to do.

Extended Events Objects
Hi. This is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is module 3, Extended Events Objects. So, with the introduction of Extended Events inside of SQL server, there's a new complexity for being able to manage the number of objects that get exposed and then being able to understand those. So, as a factor of migrating from trace to Extended Events, you're going to have to learn about these new objects. Understanding the different objects and then how they are properly used is going to be very important for actually being able to leverage the power behind Extended Events inside of SQL Server. So this module, we're going to take a look at the Metadata views, we're going to take a look at Packages, the Events, the Predicates, the Actions, the Targets, and the Types and Maps that get exposed as objects inside of Extended Events and how you'll make use of each of these for being able to really leverage the power behind diagnostic troubleshooting with the Extended Events inside of SQL Server.

SQL Server 2008 SSMS Add-in
Hi. This is Jonathan Kehayias from sqlskills. com, and I'm recording this course for Pluralsight. This is module 5, SQL Server 2008 Management Studio add-in. Back in 2008 when extended events were first introduced, there was no UI support for SQL Server 2008. And this led me to develop a UI for supporting extended events in SQL Server 2008 that's become an open source project known as the extended events management studio add-in. It's available for free, and open source, on CodePlex at extendedeventmanager. codeplex. com. What this UI does is it gives you the basic UI support for extended events in 2008 and 2008 R2, and it's implemented as a management studio add-in. There's an underlying library DLL that actually drives all the functionality for the add-in in management studio, and this library DLL was designed so that it could be loaded into PowerShell and allow you to perform management of extended events in SQL Server 2008 and 2008 R2 through PowerShell. In this module what we're going to take a look at is how you install the add-in, and then how you use the add-in to manage extended events inside of SQL 2008 and 2008 R2, and how you can configure some of the add-in options that affect the way the add-in actually functions while working with SQL 2008 and 2008 R2.

Defining Event Sessions
Hi. This is Jonathan Kehayias from SQLskills. com, and I am recording this course for Pluralsight. This is Module 6: Defining Event Sessions. This module is going to focus on how you define your event sessions and some of the considerations that you should make as a factor of creating event sessions inside of SQL Server Extended Events. Some examples of the things that we're going to talk about are the minimum requirements for creating and modifying event sessions and how that will effect what you want to plan for as a part of issuing DDL commands against a database engine that you have events sessions running on. We'll also talk about the different ways that predicates can be defined and how they can affect event session performance and why you might want to change the way that you order predicates to get best performance from your event session. And then we'll also talk about actions and how they can be used to add data to events, but some of the considerations around when you might want to not use them because they should typically only be used when necessary to provide additional information that is going to be beneficial for the type of trouble shooting or diagnosis that you are working on. In this module, we'll cover the event session requirements, some basics on predicate usage, understanding how to write predicates that will short-circuit, and then how to add data with actions to your event sessions.

Event Session Management
Hi. This is Jonathan Kehayias from SQLskills. com, and I'm recording this course for Pluralsight. This is Module 7: Event Session Management. So, in this module, we'll talk about the methods of being able to manage event sessions and we'll look at how to do programmatic creation of the event sessions using the DDL commands for Extended Events inside of SQL Server. This may make fine tuning predicate definitions a lot easier for complex predicates over trying to do them inside of the 2012 UI, and they can be used to deploy an event session to multiple servers using a central management server inside of SQL Server 2008 and higher where you have the CMS capability. As we talked about in the previous module, event sessions do have specific requirements for being created and modified, so that's something to keep in mind when you're doing event session management through DDL operations. In this module, we're going to cover management DDL, working with event sessions, session catalog views, and active session DMVs for querying target data and finding out information about the active sessions that are running on the server.

Basic Troubleshooting Scenarios
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is Module 9, Basic Troubleshooting Scenarios. So with Extended Events we're going to take a look at some of the basic troubleshooting scenarios and just like the SQL Trace it should only be used in scenarios where it's going to provide the best method of identifying a problem or collecting the information necessary for troubleshooting a problem. Some of the basic scenarios that we're going to take a look at for using Extended Events include identifying deadlock issues and this is actually going to be probably different than what you might expect when we talk about deadlocks, identifying blocking issues that are occurring inside of the server, identifying recompilation issues with your query work load and we'll take a look at how you can identify not just the statement recompiled but why it recompiled using Extended Events, identifying when errors occur and then we'll take a look at some specific scenarios where Extended Events might make it a lot easier to identify the root cause or exact point in code where the error occurred during query execution. We'll talk about tracking session-level waits and how you might make use of that for being able to do diagnostic troubleshooting inside of SQL Server, and then viewing historical system health information in SQL Server 2012 because it's being collected by default. You just have to know where to go look to find it. And this is a great way to be able to get baseline or a benchmark of the activity that's been going on for a longer period of time just by looking at the historical information that's being maintained by SQL Server.