Press "Enter" to skip to content

Category: Extended Events

Writing Extended Events to a Table

Published 2022-02-24 by Kevin Feasel

Gianluca Sartori continues a series on XESmartTarget:

The first approach is extremely painful (XML shredding – enough said).

The second approach requires a file target, which is not always available. It also requires to stop the session to read all the data, or write extremely complicated code to read the data incrementally while the session is running (believe me, I did it and my brain still hurts).

This is where XESmartTarget jumps in to help you! All you have to do is write some configuration in a json file and let XESmartTarget do its magic.

Read on to see how XESmartTarget can help you write Extended Events output to a table without the pain.

Comments closed

Writing Extended Events to CSV via XESmartTarget

Published 2022-02-23 by Kevin Feasel

Gianluca Sartori shows one valuable use for XESmartTarget:

In this case, imagine that you wanted to observe the commands executed on a SQL Server instance and save them to a file to process them later. Of course, Extended Events can do that with the built-in targets. However, when you write to a file target, the file has to reside on the disks of the SQL Server machine (well, actually, the file could be sitting on a file share writable by SQL Server or even on BLOB storage on Azure, but let’s keep it simple). How do you use the storage of the client machine instead of using the precious filesystem of the server machine?

Here is where XESmartTarget can help you with a CsvAppenderResponse. This Response type writes all the events it receives to a CSV file, that can be saved on the client machine, without wasting disk space on the server. You can decide which events to process and which columns to include in the CSV file, but more on that later.

Read on to see how the whole thing works.

Comments closed

Fun with XESmartTarget

Published 2022-02-21 by Kevin Feasel

Gianluca Sartori shows off a useful project with a new series of posts:

Some time ago, I started a project called XESmartTarget. I find it super useful and you should probably know about it. It’s totally my fault if you’re not using it and I apologize for all the pain that it could have saved you, but it didn’t because I did not promote it enough.

Now I want to remedy my mistake with a 10 days series of blog posts on XESmartTarget, which will show you how useful it can be and how it can be used to accomplish your daily DBA tasks using Extended Events.

In this first post of the series, I will introduce XESmartTarget, show how it works and how to configure it. For the next 10 days I will publish a post to show you how to solve a specific problem using XESmartTarget. Let’s go!

Click through to get off to a good start.

Comments closed

Fun with the SSMS Extended Events UI

Published 2022-02-16 by Kevin Feasel

Grant Fritchey airs a few grievances:

I like Extended Events and I regularly use the Session Properties window to create and explore sessions. I’m in the window all the time, noting it’s quirks & odd behaviors, even as it helps me get stuff done. However, found a new one. Let me tell you about just a few of them.

Click through for some examples of UI oddities when working with session properties.

Comments closed

Causality Tracking with Extended Events

Published 2022-01-26 by Kevin Feasel

Chad Callihan puts on the lab coat and safety goggles:

Causality is defined as the relationship between cause and effect so it’s safe to say that “causality tracking” is an appropriate name.

When you have causality tracking turned on for an Extended Events session, you can follow along with how one event in SQL can lead to various other events. By following the initial attach_activity_id.guid that gets assigned, you can follow statements, waits, statistic changes, and more. It’s a great way to filter the flury of events occuring on your server and narrow your focus.

Read on to see what causality tracking does and how you can benefit from it.

Comments closed

Why Did It Recompile?

Published 2022-01-04 by Kevin Feasel

Grant Fritchey answers an interesting question:

Strictly speaking, a recompile isn’t really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here’s one way to get it done.

Click through for an extended event which does the job.

Comments closed

Determining the Right Batch Size for Deletes

Published 2021-12-09 by Kevin Feasel

Jess Pomfret breaks out the lab coat and safety goggles:

I found myself needing to clear out a large amount of data from a table this week as part of a clean up job.  In order to avoid the transaction log catching fire from a long running, massive delete, I wrote the following T-SQL to chunk through the rows that needed to be deleted in batches. The question is though, what’s the optimal batch size?

I usually go with a rule of thumb: 1K for wide tables (in terms of columns and row size) or when there are foreign key constraints, 10K for medium-width tables, and about 25K for narrow tables. But if this is an operation you run frequently, it’s worth experimenting a bit.

Comments closed

Automating Data Collection with Extended Events

Published 2021-10-05 by Kevin Feasel

Ed Pollack continues a series on extended events:

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 dives into one method for collecting and retaining all event data for a specific set of events. A system like this is highly customizable and provides a solid starting point for Extended Events development. This should be viewed as a collection of puzzle pieces; individual pieces can be adjusted as needed to produce a monitoring solution that fits the needs of a given situation, even if it is vastly different from what is demonstrated here.

Read on for the process. Shredding XML isn’t pretty, but the good news is that with a setup like this, you only need to do it once…unless you need to change it later, so get it right the first time and bam, problem solved.

Comments closed

Troubleshooting Login Timeouts

Published 2021-09-28 by Kevin Feasel

Grant Fritchey shows us another use of extended events:

I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn’t see anywhere else, let me share it with you.

Read on to see how, though as Grant notes, it’s not as easy as searching for “login_timeout” or something named similarly.

Comments closed

Capturing SQL Server Login Details with extended Events

Published 2021-09-24 by Kevin Feasel

Jack Vamvas shows how to track SQL Server logins:

I have to capture logon information details for a specific logon on a SQL Server.   Specifically – the client_hostname, nt_username & username. What i’m looking for is a log recording a successful connection made to the server.     The event should be triggered a) when a connection is made & b)   from a connection pool. 

Click through to see how.

Comments closed