Press "Enter" to skip to content

Category: Extended Events

The Building Blocks of Extended Events

Published 2021-09-15 by Kevin Feasel

Ed Pollack takes us through the basics of extended events in SQL Server:

Extended Events are an excellent way to collect data about a SQL Server that provides a vast array of events that can be used for performance monitoring, troubleshooting, or auditing a server. In this article, I’ll explain the building blocks of Extended Events data collection.

While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.

This article walks through the steps to create, configure, and implement Extended Events in SQL Server, providing the prerequisite code and concepts to build an automated collection process.

Read the whole thing.

Comments closed

Finding the Culprit in a Database Timeout

Published 2021-07-28 by Kevin Feasel

David Fowler sets us straight on query timeouts:

Not very helpful, right? And unless you’ve got some sort of monitoring in place, for example you can find these in SQL Sentry by looking for aborted queries, or you’re picking these up in your applicaiton logging, you’re going to have a hard time tracking down what query actually tripped the timeout. As you’re reading this post, I’m going to assume that you haven’t got monitoring.

So how do we go about figuring this out?

Click through for the answer.

Comments closed

Filtering Extended Events with Actions

Published 2021-07-20 by Kevin Feasel

Grant Fritchey gives us a technique to filter extended events:

Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don’t have to be collected in order to put them to work filtering your Extend Events.

Read on to see how.

Comments closed

Live Extended Events Data with Azure SQL Database

Published 2021-04-27 by Kevin Feasel

Grant Fritchey is doing it live:

Once you’ve created an Extended Events Session that is output to Azure Storage, you’ve done most of the work. The trick is really simple. Get the Azure Storage account set up with a Container. Create a Shared Access Signature (SAS) with the right permissions (rwl, read, write, list). Get the token from the SAS (it’s a long string). Use it, along with the path to the container to create a Database Scoped Credential. Create the session using the same path and container that you defined in the Credential. Done. You’ve got an Azure Extended Events session gathering data for you and outputting to a file in Azure Storage.

Now, what I’d like to tell you is that you can open up the Live Data window from SSMS. You can’t.

Grant does give us a workaround which kind of does the trick, but this is an obvious place where some additional developer care would be valuable.

Comments closed

Extended Events in Azure SQL Database

Published 2021-04-20 by Kevin Feasel

Grant Fritchey compares and contrasts extended events on-premises to extended events in Azure SQL Database:

I have long advocated for the use of Extended Events. I’ve been posting all sorts of blog posts on how to implement them, how they present unique opportunities for new and interesting data, and how they do so much more than the old trace events, yet, put less of a load on the system. All of that is true, until we hit Azure SQL Database.

Now, don’t get me wrong, Extended Events are still awesome, amazing and wonderful. It’s just that, Azure SQL Database is going to force us to hop through a few hoops. I want to be up front with these… I’m trying to find the right word here, challenges? Maybe. Frustrations? Yeah, kind of. Limitations? Again, sort of, but not quite. We’ll settle on as neutral a term as possible: differences. For the moment.

Read on for the first part in an ongoing series.

Comments closed

Capturing Deadlocks with the system_health Extended Event

Published 2021-04-19 by Kevin Feasel

Jack Vamvas is hunting deadlocks:

An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users. 

I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team. 

Click through for the script.

Comments closed

Embracing the XML

Published 2021-04-06 by Kevin Feasel

Grant Fritchey has some advice:

While XML is, without a doubt, a giant pain in the bottom, sometimes, the best way to deal with Extended Events is to simply embrace the XML.

Now, I know, just last week, I suggested ways to avoid the XML. I will freely admit, that is my default position. If I can avoid the XML, I will certainly do it. However, there are times where just embracing the XML works out nicely. Let’s talk about it a little.

Just need to do a little victory dance here. I didn’t explicitly say “embrace the XML” but close enough…

I think the biggest problem DBAs have with XML is that they end up treating it like a dreadful task: I need to shred XML for an extended event. But to do that, I have to learn how to query it using this quasi-language, and so they get stuck trying to fuss with something somebody else did, moving symbols around in the hopes that they get the right incantation. By contrast, a day or two really focusing in on how XQuery and XPath work would clarify a lot and make the process much simpler.

There is a fair counter-point in asking how often you’ll use this, and if the answer is “probably never,” then poke through and just try to get it working. But I’ve got a bit of bad news: “probably never” is probably wrong.

Comments closed

Extended Events without XML

Published 2021-03-30 by Kevin Feasel

Grant Fritchey shows how you can avoid working directly with XML in Extended Events:

One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped.

Look, I get it. I don’t like XML either. It’s a pain to work with. It’s actively difficult to write queries against it. If there weren’t a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let’s see how.

Click through for two such methods. Another thing I might note is that quite often, you only need to mess with the XML once to set up your session and once to set up how you’ll view and handle your results. If you’re constantly writing XPath queries from scratch to work with Extended Events, that’s by choice.

1 Comment

Reading Extended Events Files with Powershell

Published 2021-03-17 by Kevin Feasel

Emanuele Meazzo shows how to work with Extended Event *.xel files:

However, I’ve found myself in a tricky spot, as I had multiple instances recording events, and those events had to be analyzed as a whole.
I could have simply written a script to get the data from each instance, querying the system function sys.fn_xe_file_target_read_file and then uploading it somewhere else, but this approach has the issue of adding additional load on the source instances, and I didn’t want to add additional load to the already overworking instances I was trying to monitor.

I then wondered, can I move the files over from the busy servers and read them from another machine? I surely didn’t want to open each file manually and exporting it to a table and/or CSV in order to mash them together, too

Read on to see how.

Comments closed