Press "Enter" to skip to content

Category: Extended Events

Extended Events Updates in Azure SQL DB

Published 2023-11-14 by Kevin Feasel

Dimitri Furman fills us in on recent happenings:

To better support Extended Events in Azure SQL, in recent months we have made several improvements. Now, you can:

  • View event data in SSMS without downloading xel files from Azure Storage
  • Watch live data for an event session
  • Use the XEvent Profiler
  • Use the histogram target
  • Monitor Extended Events performance
  • Use granular Extended Events permissions

Much of this is already available in the box product. Dimitri also notes some performance improvements and other updates as well.

Comments closed

The Search for Extended Events Information

Published 2023-09-15 by Kevin Feasel

Grant Fritchey stays on the first page:

Here’s their paraphrased (probably badly) story:

“I was working with an organization just a few weeks back. They found that Trace was truncating the text on some queries they were trying to track. I asked them if they had tried using Extended Events. They responded: What’s that? After explaining it to them, they went away for an hour or so and came back to me saying that had fixed the problem.”

We all smiled and chuckled. But then it struck me. This wasn’t a case of someone who simply had a lot more experience and understanding of Profiler/Trace, so they preferred to use it. They had literally never heard of Extended Events.

Why?

This led Grant to perform some search engine shenanigans and what he found was curious. A couple of points with search engines, though:

  • Search engine results will differ based on your location (IP address) and whether you are signed in or not. Google is particularly selective about this stuff. It might also affect Bing, but let’s face it: if you’re using Bing to search for anything other than images, you’ve already resigned yourself to failure.
  • In my case, a search for “extended events” (without quotation marks) did show quite a few pages which I’d consider reasonable for the topic: a Microsoft Learn quickstart article on using extended events, Brent Ozar’s extended events material, a SQL Shack article on the topic, etc. A good number of these links are content from the past 5 years, as well.
  • Grant mentions the “page 1” effect in search engines, and he’s absolutely right. The vast majority of people performing a search never leave the first page of results. This is part of why Google went to an infinite scrolling approach rather than showing explicit numbered pages.
Comments closed

Error Trapping with Extended Events

Published 2023-09-14 by Kevin Feasel

Chad Callihan wants to know what’s going wrong:

Many times, when I’m using Extended Events, I’m filtering on an Id or procedure name in query text and tracking what’s hitting a database. In these cases, I’m assuming queries are going to complete successfully. Did you know Extended Events can be useful when you’re expecting things to go wrong? Let’s look at how we can use Extended Events to track down error messages.

You’ll get a fair amount of noise on a busy server–especially dev servers where people are writing queries and trying things out—but this is a great technique for seeing if something has gone wrong and we need to look into it before a customer reaches out.

Comments closed

The Benefits of Extended Events

Published 2023-09-14 by Kevin Feasel

Tom Zika talks extended events:

If you look at the Tags section of my blog, you can see that Extended Events is one of the top tags. Also, my first (and currently only) public speaking session was on Troubleshoot Real-World Scenarios with Extended Events. So you could say I am a fan.

Read on to see why, including the type of things you can do with extended events, as well as the normal (because true) complaints.

Comments closed

The Joy of sp_HumanEvents

Published 2023-09-13 by Kevin Feasel

Erik Darling makes a pitch:

While my relationship with Extended Events is complicated for many reasons:

  • Awful documentation
  • Hardly any guidance on usage
  • Almost nothing useful about what type of target to use when
  • Everything stored in XML
  • Slow, unfriendly GUI in SSMS

My need to use them while consulting outweighs my gripes and grievances about how Microsoft has chosen to write about, use, and present the data to you.

That’s where my stored procedure sp_HumanEvents comes in handy.

In fairness, Erik put his virtual money where his virtual mouth is, and sp_HumanEvents is put together quite well.

Comments closed

Capturing Autogrowth Events in SQL Server

Published 2023-09-13 by Kevin Feasel

Ben Miller shares an extended event session with us:

I wanted to share one of the Extended Events I always put on a server when I am in charge of it. It has to do with File growths and captures some important things for me. Before you say that it is in the system_health extended events session, I know that it is there. I have had system_health sessions cycle pretty fast and there are a lot of other events in that trace, so I decided to make my own for just that specific thing so that I can archive the sessions and keep the disk clean as well as pull this information into a table and analyze data in a tabular way instead of mining XE files.

Read on for that script and what it does in practice.

Comments closed

New Script: sp_HealthParser

Published 2023-07-25 by Kevin Feasel

Erik Darling has a new script for us:

I recently found myself in the midst of a client issue that lead me to need a bunch of corroborating data from the system health extended event session.

There’s hardly any good documentation on it, and even fewer well-written queries or resources for parsing data out of it.

So now I’m making it easy for you, because I care about you more that Microsoft does.

Click through for a link to the script and to get an idea of what it provides.

Comments closed

Loading Multiple Extended Events Files in SQL Server

Published 2023-06-27 by Kevin Feasel

Jose Manuel Jurado Diaz reviews the tapes:

As the volume of data grows, SQL Server creates multiple extended event files to store the captured information efficiently. These files are usually saved in a designated target folder. However, when it comes to loading and analyzing these files, administrators often face the challenge of dealing with multiple files individually. Manually loading each file can be time-consuming and inefficient, especially when dealing with a large number of extended event files.

Read on to see which function you can use to read multiple Extended Events files and how it works.

Comments closed