Press "Enter" to skip to content

Category: Extended Events

Extended Events Tracing on Read Scale-Out Azure SQL MI

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

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

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

Extended Events Updates in Azure SQL DB

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

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

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 Joy of sp_HumanEvents

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