Press "Enter" to skip to content

Category: Extended Events

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.

Leave a Comment

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

Antipattern Queries Extended Event in SQL Server

Published 2023-02-06 by Kevin Feasel

Bob Ward enumerates some anti-patterns SQL Server can guilt you over:

If a query uses certain antipatterns, it will be detected during query optimization.  For both SQL Server and Azure SQL (internally on by default), if these antipatterns are detected when optimizing the query, and the query_antipattern event has been added as part of a running extended event session, the output will be captured.  The output will contain the relevant capture fields configured for the extended event session, allowing one to quickly identify which queries contain these antipatterns and are, therefore, prime candidates for tuning.

Read on for more information about this extended event, which is new to SQL Server 2022. I haven’t used this yet, so the two caveats I’m about to give are speculative in nature…though when has that ever stopped me? Caveat the first: just because something shows up as an anti-pattern doesn’t mean it needs to be fixed. There can be good reasons why you have chosen what is normally a less-efficient path. Caveat the second: just because something doesn’t show up as an anti-pattern doesn’t mean it’s fine. These are likely directional and my guess is that SQL Server will be fairly conservative in its estimation of what constitutes an anti-pattern so that you don’t get a lot of false positives.

Comments closed

Finding a Scalar Function Caller

Published 2023-01-23 by Kevin Feasel

Matthew McGiffen searches for the root of the problem:

In this post we look at a method using Extended Events (XE) to identify what parent objects are calling a given SQL function and how often.

The background is that I was working with a team where we identified that a certain scalar function was being executed billions of time a day and – although lightweight for a single execution – overall it was consuming significant CPU on the server. We discussed a way of improving things but it required changing the code that called it. The problem was that the function was used in about 700 different places across the database code – both in stored procedures and views – though the views themselves would then be referenced by other stored procedures. Rather than update all the code they’d like to target the objects first that execute the function the most times.

Read on to see how Matthew did it, as well as some caveats along the way.

Comments closed

Tracking Database Errors with Extended Events

Published 2022-12-07 by Kevin Feasel

Eitan Blumin is watching you:

But interestingly enough – we would be getting an added benefit here. Even if there is no SQL injection attack, it’s still possible that such errors would be raised by the application – simply due to bugs.

Furthermore, these errors in the database may be happening without anyone even noticing! How could that be, you ask? Well, it could be due to bad error handling that “swallows” the error entirely, or because the errors are logged but no one is bothering to look at the logs, or maybe because the errors are caught but an undetailed error message is logged/displayed to the user (I can’t even count how many times I encountered “general database error” messages in applications), or because the developers simply decided to mark this as a “known issue” that they didn’t bother to fix and they didn’t think to ask their DBA about it… The reasons are numerous and varying.

Click through for the scripts. I had built something similar about a decade ago, a simple WPF app which watched for errors. I messaged him with something like “You missed a comma in that IN clause” and I saw him pop up from his cubicle and look around, trying to figure out how I could peek over his shoulder and see the query.

1 Comment

Where Extended Events Go by Default

Published 2022-12-01 by Kevin Feasel

Tom Zika is curious:

Have you ever wondered where the .xel file is saved when you create a new Extended Event session and don’t specify the full path (just the file name)?

Like so: [image removed because you should go to Tom’s site and see it, ed.]

Well, so did I and here’s what I’ve found out.

Click through to learn where these files end up if you don’t specify anything.

Comments closed