Press "Enter" to skip to content

Category: Extended Events

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

Exporting Extended Event Results to CSVs and Tables

Published 2024-06-07 by Kevin Feasel

David Fowler comes in with a quick tip:

This is something that I’ve seen pop up a couple of times on various forums recently, how can we export the results of an XEvent session. So I thought I’d answer the question here.

It’s actually a very simple thing to do in SSMS, although the option is not always easy to spot.

Click through to see how you can do it in T-SQL, as well as within SSMS.

Comments closed

Extended Events Tracing on Read Scale-Out Azure SQL MI

Published 2024-03-04 by Kevin Feasel

Kendra Little goes on a journey:

It took me more than half hour to figure out how to start an XEvents trace on a read-scale out instance of Azure SQL Managed Instance. It’s hard to monitor read scale-out instances, so tracing is desirable! I started with a simple trace of sql_statement_completed. Hopefully this saves other folks some time.

Click through for that process. The process seems a bit painful, to put it kindly.

Comments closed

NEXT VALUE FOR and ROWCOUNT

Published 2024-01-17 by Kevin Feasel

Jose Manuel Jurado Diaz diagnoses a problem:

In the realm of SQL Server, certain combinations of commands and functions can lead to unexpected conflicts and errors. A notable example is the conflict between the NEXT VALUE FOR function and the ROWCOUNT setting. This article aims to dissect the nature of this error, explaining why it occurs, its implications, and how to effectively capture and analyze it using Extended Events in Azure SQL Database. For example, we got the following error message: Msg 11739, Level 15, State 1, Line 11 – NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. – NEXT VALUE FOR (Transact-SQL) – SQL Server | Microsoft Learn

Read on for more information, including what the error is telling us and how we can capture cases of that error using extended events.

Comments closed

Using Extended Events in Azure Data Studio

Published 2023-12-04 by Kevin Feasel

Josephine Bush tries it out:

I know I can use extended events (xevents) in Azure SQL DB when in SSMS, but I wanted to learn how to use them in Azure Data Studio (ADS).

Click through to see the normal workflow in SQL Server Management Studio, followed by the workflow in Azure Data Studio. I’d also recommend, at some point, finding good extended events sessions and saving the T-SQL to create them.

Comments closed