Press "Enter" to skip to content

Category: Extended Events

Choosing the Right Extended Events

Published 2025-04-01 by Kevin Feasel

Grant Fritchey makes a choice:

For this blog post I want to focus on the last one, Event Overload. There really are a lot of events in Extended Events. I don’t just think that’s a good thing. I think it’s a GREAT thing. However, I get it. I hit the same problem, regularly. Which events do I use to do thing that I’m trying to do? Except for blogs like this one, there’s not always a lot of guidance on these things. Microsoft has (good) documentation on Extended Events in general, but not on every event, plus, not a ton of guidance. (some though).

Click through for Grant’s advice.

Leave a Comment

Reporting Query Errors via Extended Events

Published 2025-03-27 by Kevin Feasel

Tom Collins wants to know what’s going wrong:

We have an ETL job failing every night . The ETL job is using SQL Server Stored Procedures ,  so we’re looking to be trace the errors reported in SQL Server, to give some clues on how to fix the problem?

Do you have an approach to monitor and save SQL Server  error messages ?

This is an extremely useful way of catching problems in code, as well as people goofing up ad hoc queries.

Comments closed

Capturing Stored Procedure Executions via Extended Events

Published 2025-02-26 by Kevin Feasel

Haripriya Naidu creates an Extended Events session:

If you’re a DBA, how many times have developers asked you to check whether a particular stored procedure is hitting SQL Server?

Yes, we’ve all been there. Typically, you either run Profiler or set up an Extended Events session to trace it. But the question is, have you ever been confused about which event action to use?

Read on for the list of candidates and when you should use each.

I do mildly disagree with Haripriya’s last note around running Extended Events in production. There’s a small performance hit for most events. There are a few events that do have a much stronger impact, but for the large majority of events, you can (and probably should) have them running to provide the relevant diagnostic information. This isn’t like Profiler or even a server-side trace, where there’s a fairly significant overhead cost.

Comments closed

SSIS: Operating Completed with Problems Logged

Published 2025-02-03 by Kevin Feasel

Slava Murygin uses Integration Services to process an Analysis Services cube:

If you process SSAS cubes via SSIS packages you might notice a weird message like “Full Processing:Warning: Server: Operation completed with XXX problems logged.“

How you can get that message (if you have that problem):

Slava notes that the simple solution doesn’t actually work, as both services decide not to store that information. But Slava does provide a solution to the problem.

Comments closed

The Pain of fn_xe_file_target_read_file

Published 2024-10-21 by Kevin Feasel

Tom Zika plants a flag:

I haven’t had a rant post in a while. There is a saying: “Anything before the word ‘but’ is ignored”. I love Extended Events, but … reading the extended event file is so much pain.

It feels like there is a conspiracy between Microsoft and Big Pharma SQL Monitoring because the best analytics tool available in SQL Server (and I mean Extended Events and Query Store of course) have the worst GUI and supporting tools. I’m focusing on XE in this post.

Read on for Tom’s rant. To add on to it, the feedback item Tom links to now has a new update from Dimitri Furman, indicating (based on how I’m reading it) that the fix will be in SQL vNext, not SQL Server 2022.

4 Comments

Monitoring for Blocked Processes and Deadlocks with Extended Events

Published 2024-10-02 by Kevin Feasel

Lori Brown’s speaking my language:

Here is a way to set up an extended events session that can be used to collect blocked processes and deadlock records.  I use similar code to collect this type of data each day and have a job that pulls the data into tables which can be used to evaluate the resources that are being blocked and deadlocked on.

When checking for blocked process records, you must set the blocked process threshold in the configuration.  I have mine configured with the threshold set to 30 seconds.  This means that blocked process reports are generated every 30 seconds. 

Click through for that configuration setting, as well as the Extended Events session to do the work, and even code to pull the results into a table. It doesn’t get much simpler than that.

Comments closed

Tracking Procedure Execution Paths with sp_statement_completed

Published 2024-09-24 by Kevin Feasel

Grant Fritchey shows off an interesting use case for Extended Events:

Another discussion in Gothenburg (such a great group of people) around Extended Events lead to a (admittedly, tiny) idea for an additional use for sp_statement_completed.

The basics for sp_statement_completed are pretty straight forward. If you want to capture a procedure’s behavior, you use rpc_completed. If you want to know about the individual statements within the procedure, you use sp_statement_completed. Now, I don’t recommend turning this on across the board or without some good filtering in place because, it’s likely to generate quite a bit of data. However, it can be useful, including in the following manner.

Click through for a silly but clarifying example from Grant.

Comments closed

Missing Columns in the Extended Events Live Data Explorer

Published 2024-09-04 by Kevin Feasel

Grant Fritchey explains a UI oddity:

Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work FevziKartal.

The rest of this post is just me replicating work already done by others. I just want to see it in action.

Read on for the example and what happens when you don’t have any events in the live data explorer.

Comments closed