Press "Enter" to skip to content

Category: Extended Events

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.

Leave a Comment

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.

Leave a Comment

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

Including Database Name in Extended Events

Published 2021-01-15 by Kevin Feasel

Aaron Bertrand has a change of heart:

In my previous tip about SQL Server Extended Events, I discussed the trade-offs involved with choosing to store the database name instead of looking up the name via database_id later. At the time, we decided to use the latter approach since we are generally not concerned about the edge case where DDL is performed against a database that is then dropped before collection occurs.

Something else that has since come up: we want to filter out activity against a set of databases matching a specific name pattern since the DDL activity there is system-generated and does not need to be audited. We quickly learned that you have to be careful with how you use the database name in an Extended Events predicate.

Click through to understand the problem as well as the solution.

Comments closed

Collecting Database Name with Extended Events

Published 2020-12-28 by Kevin Feasel

Aaron Bertrand has a philosophical dilemma:

Now, in order to capture this data to DDLEventLog, we have a background process running on a schedule that extracts all the new data from the latest .xel files and loads it into the table. (There’s also a process that moves files we know we’ve consumed and deletes them 90 days later to keep the loading job linear.)

This revealed an edge case where it is possible to lose one aspect of the data: database name. When originally building the session, I thought database_id would be enough because the loading process could always look up the name from there. But this is brittle. Imagine the case where a user drops a table, drops the database, then the loading process pulls data from the session. That data about dropping the table contains just a database_id that no longer exists.

Read on for Aaron’s thoughts and ultimate decision.

Comments closed

Use a Separate Deadlock Extended Events Trace

Published 2020-12-04 by Kevin Feasel

Kendra Little explains why it makes sense to have an extended events trace specifically for deadlocks:

We recently had customer ask why SQL Monitor creates an Extended Events session to capture deadlock graphs, when SQL Server has a built-in system_health Extended Events trace which also captures deadlock information?

There are a couple of reasons why a dedicated trace is desirable for capturing deadlock graphs, whether you are rolling your own monitoring scripts or building a monitoring application. I like this question a lot because I feel it gets at an interesting tension/balance at the heart of monitoring itself.

Click through for the answer.

Comments closed

Finding the Most Costly Statement in a Stored Procedure

Published 2020-10-13 by Kevin Feasel

Grant Fritchey takes us through one method of figuring out what which statement you’re waiting to finish when running a stored procedure:

A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don’t have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital.

Happily, Extended Events are here to help.

Click through to see how you can use extended events to figure this out.

Comments closed