Extended Events – Page 2

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

Reviewing Always Encrypted Queries with Extended Events

Published 2023-06-16 by Kevin Feasel

Matthew McGiffen takes a look:

In previous posts we’ve looked at a number of aspects of Always Encrypted and how it works. I think it’s very useful to understand how it actually works in practice, so in this post we’re going to look at what happens in the background when you execute a query.
I’m continuing on from the examples in the last few posts on this subject. So if you want to repeat the examples for yourself you’ll need to have followed steps from Setting Up Always Encrypted and Executing Queries Using Always Encrypted.

Read on to see what actually happens when you perform a simple INSERT or SELECT operation and there are Always Encrypted columns involved.

Comments closed

SQL Anti-Patterns Extended Event in SQL Server 2022

Published 2023-06-02 by Kevin Feasel

Dennes Torres finds some anti-patterns:

One of the new Extended Event available in SQL Server 2022 is the query_antipattern. This extended event allows to identify anti-patterns on the SQL queries sent to the server. An anti-pattern in this case is some code that the SQL Server optimizer can’t do a great job optimizing the code (but cannot correct the issue automatically).
This is a very interesting possibility: Including this event in a session allow us to identify potential problems in applications. We can do this in development environments to the the problems earlier in the SDLC (Software Development Life Cycle). Let’s replicate some examples and check how this works.

Dennes shows two examples and notes that there are five total listed in the Extended Event, but that the documentation is a bit lacking to explain their intent.

Comments closed

Monitoring Client Timeouts via Extended Event

Published 2023-04-14 by Kevin Feasel

Erik Darling wait until the phone stops ringing and checks caller ID to see who bothered him:

Most applications have a grace period that they’ll let queries run for before they time out. One thing that I notice people really hate is when that happens, because sometimes the effects are pretty rough.
You might have to roll back some long running modification.
Even if you have Accelerated Database Recovery enabled so that the back roll is instant, you may have have 10-30 seconds of blocking.
Or just like, unhappy users because they can’t get access to the information they want.
Monitoring for those timeouts is pretty straight forward with Extended Events.

Click through for a query Erik uses for the task.

Comments closed

Peeking into Azure SQL DB via Extended Events

Published 2023-03-01 by Kevin Feasel

Grant Fritchey observes the observers:

Last week I posted the results from using Extended Events to snoop on what happens inside an AWS RDS database. This week, I’m taking a look at what happens on Azure SQL Database. I’m using the same toolset again, if for no other reason that I’m consistent in my approach. So it’s basically just rpc_completed & sql_batch_completed on the database in question. Let’s check out the results.

Here’s the prior post, in case you missed it like I did.

Comments closed

Maximum Number of Actions on an Extended Event

Published 2023-02-28 by Kevin Feasel

Jonathan Kehayias hits the limit:

Did you know that there is a limit to the number of actions you can add to a single event in Extended Events? While I was playing around with Trace Flag 9708 for my previous blog post, one of the things I wanted to try to see was whether it would be easy to determine the impact of adding too many actions to a single event. I picked a single event, in this case wait_info, and I checked all of the actions for the event in the UI, and tried to create the event session. I was surprised when I got a validation error back so I clicked on the details link and got the following:
Session validation for the alter operation failed. (Microsoft.SqlServer.Management.XEvent)

Read on for more details and to see the limits. Applying Swart’s 10% Rule to this, you’d say 3 actions for any given event, which is probably a little bit low but in the neighborhood of what I’d call reasonable for a given XE.

Comments closed

The Observer Effect with Extended Events

Published 2023-02-21 by Kevin Feasel

Jonathan Kehayias measures the measurer:

SQL Server 2022 offers a new feature enhancement to Extended Events that allows it to now track the performance and publishing metrics of the events that have been enabled in an event session that is running on the server. Four new columns were added in the sys.dm_xe_session_events DMV in SQL Server 2022 that provide additional information about the event publishing performance metrics when an event session is running:

This fits more in the “wacky ideas” category than a sensible thing to do, but it can give you a better idea of how expensive certain events are.

Comments closed

Tracking Azure SQL Managed Instance Logins

Published 2023-02-20 by Kevin Feasel

Michael Bourgon watches the doors:

This is a barebones Xevent (Extended Event) . I’m not using blob storage, just the existing “eh, you have a couple hundred meg worth of ring buffer you can use”. But I needed to see who was using one of our dev instances. I thought we’d moved everything over to the Azure SQL DBs, since there’s a ton of reasons to do so.

Read on for the event definition, as well as Michael’s thoughts on Azure SQL Database versus Azure SQL Managed Instance.

Comments closed

Category: Extended Events