SQL Server: Advanced Extended Events

Learn how to use the advanced features of Extended Events for more in-depth troubleshooting on SQL Server, either through Transact-SQL or the Extended Events UIs, applicable for developers and DBAs who have some Extended Events and troubleshooting experience, for SQL Server 2008 onwards
Course info
Rating
(58)
Level
Advanced
Updated
Apr 1, 2013
Duration
4h 14m
Table of contents
Introduction
Getting More From Event Sessions
Event Session Options
Advanced Targets
Advanced UI Features
Extended Events .NET API and PowerShell Integration
Advanced Troubleshooting Scenarios
Description
Course info
Rating
(58)
Level
Advanced
Updated
Apr 1, 2013
Duration
4h 14m
Description

This course builds on the SQL Server: Introduction to Extended Events course and delves deeper into the Extended Events system to show you more advanced features, options, targets, and scenarios. The course starts by showing how to develop more powerful event sessions and also set the various event session options to control memory management, event dispatching and retention, and how to track linked events. It then presents the advanced event targets, along with the potential problems you may encounter when using them, and the advanced features of the SQL Server 2012 Extended Events UI in Management Studio, and how to leverage Extended Events using both the .NET API and PowerShell. The course ends by presenting some complex troubleshooting scenarios and shows how they can be investigated using Extended Events.This course is perfect for those with some experience of Extended Events and troubleshooting, as all the knowledge in the SQL Server: Introduction to Extended Events course is assumed. 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 is the Advanced Extended Events course, Module 1: Introduction. So, this course applies to all versions from SQL Server 2008 and onwards, but some of the features that I'm going to talk about only apply to SQL Server 2012 and I'll make sure that I point these out as we go through the course so that you understand the difference between 2008 and 2012 functionality. Extended Events was introduced inside of SQL Server Version 2008 and it's been improved with the implementation in SQL Server 2012 to bring it along with parity to what was available inside of SQL Trace and there's a lot of new methods for being able to collect diagnostic data from SQL Server that get exposed by Extended Events and were never available inside of the engine. Since SQL Trace is deprecated inside of SQL Server 2012, that means that it will be removed from a future version of SQL Server, but it's still available inside of the current release and this really makes it important that you understand how to use Extended Events to be able to support SQL Server from the current release in SQL Server 2012 on to future releases. This course is designed to build on the concepts that were previously taught in the SQL Server: Introduction to Extended Events course on Pluralsight. So if you haven't seen that course yet or there's parts of this course that are confusing, I would definitely recommend that you go back and take a look at that course because it covers most of the basic concepts that are needed to understand the concepts taught in this course.

Event Session Options
Hi, this is Jonathan Kehayias of SQLskills. com and I'm recording this Advanced Extended Events course for Pluralsight. This is Module 3: Event Session Options. So in Extended Events, the event session options control the behavior of the event session once it gets started inside of the engine and the options actually affect the way the event session is set up so they can typically only be changed when an event session is in a Stop state. Configuring the session options can affect the performance of the event session and it's really important to understand how that performance might be affected, which is why we're covering this in a completely separate module in the Advanced course. It can also affect how the events get processed by the event session and whether or not the events will be lost under an excessive amount of event generation depending on the type of events that have been defined or the specific targets that you might be leveraging for the event session. Generally speaking, the default option values are going to be acceptable for most of the event sessions, which is why we didn't cover this information inside of the basic course, but if you have one of those event session workloads where you need to tune the session options to be able to minimize the amount of event loss, this module will show you how to do that and which options are specifically important for that. In this module, we're going to cover each of the event session options that exist, their default values, and reasons why you might want to change the option values for a specific event session or a specific workload that you might be working with.

Advanced Targets
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this Advanced Extended Events course for Pluralsight. This is Module 4: Advanced Targets. In the Introduction course for Extended Events, we looked at two of the basic targets that exist, the ring_buffer and the event_file, and these are really good for general-purpose event collection inside of Extended Events, but one of the challenges associated with these two targets is that it requires that you do post collection processing to find any important facts or events that might exist in the data that you collected. Extended Events also includes additional specialized targets that can be used to simplify diagnosing problems and aggregating the data that is being collected by your event session on the fly to simplify work as a database administrator. In this module, we'll take a look at the histogram target and how you can use that for simplifying your data collection, the pair_matching target for identifying when a starting or ending event don't match up inside of the system and that might be something that we want to look at, the event_counter target for counting the frequency of occurrence of the events inside of your event session, and then how Extended Events integrates with Event Tracing for Windows or ETW and how you can leverage the ETW target for end-to-end tracing from inside of SQL Server out to the Windows kernel and then back into SQL Server for any of the operations that have to leave the Database Engine during code execution.

Advanced UI Features
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this Advanced Extended Events course for Pluralsight. This is Module 5: Advanced UI Features. So, in SQL Server 2012, the new UI for Extended Events makes using Extended Events significantly easier and the basic features that are contained inside of the UI were covered in Module 4 of the Introduction to Extended Events course that's also available on Pluralsight. In this module, we're going to take a look at how you can do some advanced analysis using the grouping and aggregating features inside of the UI and then how you can leverage some of the features, like merging of files, to be able to simplify your problem identification when the event sessions have been designed around leveraging these specific UI features. In this module, we're going to cover a couple of topics - for example, the way that you'll go about grouping and aggregating your data; how you can perform merging of files and there's some really neat methods around ways that you might do this - for example, merging Extended Events files with files that were generated by the Server Audit feature inside of SQL Server 2012; and then how you go about designing your event sessions so that you can leverage these advanced UI features for better analysis.

Extended Events .NET API and PowerShell Integration
Hi, this is Jonathan Kehayias of SQLskills. com and I'm recording this Advanced Extended Events course for Pluralsight. This is Module 6: Extended Events. NET API and PowerShell Integration. So, inside of Extended Events in SQL Server 2012, there are additional ways to be able to manage event sessions. and Extended Events can be managed outside of Transact-SQL if you want to do programmatic access to the core functionality. The. NET API can be used with any of the. NET languages to be able to manage your event sessions and also read the event data that's been stored in files or that could be opened as a part of the live stream against an active event session inside of SQL Server. Additionally, the PowerShell provider can be used to manage your event sessions from the PowerShell command line. In this module, we'll cover the considerations for leveraging the. NET API, the Extended Events object model that exists inside of. NET, using the Extended Events reader to read files and the live stream that's available from an active event session, and then using the PowerShell provider for Extended Events.

Advanced Troubleshooting Scenarios
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is Module 7: Advanced Troubleshooting Scenarios. So, the purpose behind the Introduction to Extended Events course was to give you a basic background and structure for understanding Extended Events and then this course has been geared towards simplifying your task of actually leveraging Extended Events and understanding ways to make troubleshooting complex problems that exist inside of SQL Server a lot easier to do as a Database Administrator. If you properly leverage all of the concepts that have been taught in the Introduction course, as well as in this course, it will be really helpful to allow you to successfully leverage the Extended Events implementation inside of SQL Server 2008 and SQL Server 2012 for doing your complex problem analysis. In this module, we're going to take a look at a number of advanced troubleshooting scenarios, including how to troubleshoot tempdb latch contention inside of SQL Server 2012, how to troubleshoot problems with lock escalation in SQL Server 2008, and then the differences between 2008 and 2012 for doing this (SQL Server 2012 makes lock escalation investigation a lot easier), how to troubleshoot problematic page splits in SQL Server 2012 using the transaction_log event so that we look for a very specific type of log operation that tells us we're having problematic page splits, how to troubleshoot orphaned transactions inside of the Database Engine, and finally how to troubleshoot ASYNC_NETWORK_IO issues and how do you find out what's the actual cause of network I/O-related waits, it's not necessarily your network infrastructure, it could be your application design, and then how do we pinpoint which applications are actually leading to the high accumulation of this specific wait type.