Press "Enter" to skip to content

Category: Extended Events

Per-Query Wait Stats with Extended Events

Published 2020-03-31 by Kevin Feasel

Grant Fritchey shows us how to see the waits associated with a specific query:

And that my friends is only the waits associated with the one query. TA-DA indeed!

Now, we could get into filtering this stuff too. Toss the ones that have little to no duration, ensure that I only capture for a specific query or procedure, all would be helpful. However, this is how you can easily identify just the waits associated with a single query, and only that query.

Click through to see how. The one thing I’d caution here is that the query which received waits isn’t necessarily the query in the wrong—it might be the fourth or fifth session in a blocking chain. But this is a great technique for getting additional per-query info when you can control the experiment.

Leave a Comment

Exploring the Extended Events Live Data Window

Published 2020-03-18 by Kevin Feasel

Grant Fritchey takes us through the Extended Events Live Data window:

One reason a lot of people don’t like Extended Events is because the output is in XML. Let’s face it, XML is a pain in the bottom. However, there are a bunch of ways around dealing with the XML data. The first, and easiest, is to ignore it completely and use the Live Data window built into SQL Server Management Studio.

I’ve written about the Live Data window before, and I’ve been using it throughout this series of posts on Extended Events. There’s a lot more to this tool than is immediately apparent. Today, we’re going to explore the basics around this tool

Read on to see what you can do with this. It’s a lot more powerful than it first appears to be.

Leave a Comment

Collation Conflicts with Extended Events

Published 2020-03-16 by Kevin Feasel

Jason Brimhall takes us through a nasty scenario:

Have you ever run into an error like this?

Cannot resolve the collation conflict between “pick a collation” and “pick another collation” in the equal to operation.

This kind of error seems pretty straight forward and it is safe to say that it generally happens in a query. When you know what the query is and you get this error, it is pretty easy to spot and fix the problem. At least you can band-aid it well enough to get past the error with a little use of the collate clause in your query.

But what if the error you are seeing is popping up when you are trying to use Management Studio (SSMS)? The error is less than helpful and can look a little something like this.

In this case, it was when trying to expand Extended Events sessions. Click through to see what’s going on and how to fix the problem.

Comments closed

Extended Events and Query Store

Published 2020-03-12 by Kevin Feasel

Jason Brimhall takes us through some of the internals of Query Store as exposed by Extended Events:

One of my favorite questions to ask during some of presentations on XE is “What was the first version of SQL Server to have Query Store?” You can imagine the wide array of answers but what is interesting is how often  the correct answer is always missed. I hear lots of answers for 2012, some answers for 2017 and somewhere in between for 2016. But never does the correct answer pop up.

Right now, I hope you are scratching your head at that last statement. You see, the question is somewhat of a trick question. The first version of SQL Server that has QDS is SQL Server 2014. However, the first version where you can actually use it is SQL Server 2016. This fun fact is visible when we start exploring SQL Server from the realm of XE. Let’s take a look.

Read the whole thing.

Comments closed

Finding Long-Running Queries with system_health

Published 2020-03-10 by Kevin Feasel

Grant Fritchey shows us where we can find long-running queries easily:

Wouldn’t it be great to just quickly and easily take a look at your system to see if you had any queries that ran for a long time, but, without actually doing any work to capture query metrics?

Oh, yeah, I can do that right now, and so can you.

All we need is something that is built into every single server you currently have under management (sorry, not Azure SQL Database) that is SQL Server 2008 or better: system_health

Grant then ties this into general benefits of Extended Events and shows how you can query and view the results.

Comments closed

Capturing Query Errors with Extended Events

Published 2020-03-03 by Kevin Feasel

Jack Vamvas shows how to capture query errors using Extended Events:

If you’re troubleshooting SQL Server query errors , you’ll already know Extended Events are highly useful and very effective method to capture SQL Server errors.

To use the script you’ll need appropriate privileges to create the Extended Event. You will also need to have some space available on the disk to store output files.

If you want something a little less permanent, you can use the ring buffer target. I put together something like this a long time ago and enjoyed IM-ing coworkers and saying “You forgot the join criteria” with no other context. Freaked them out the first couple of times…

Comments closed

When the Optimizer Can Use Batch Mode on Row Store

Published 2020-02-27 by Kevin Feasel

Erik Darling looks at some internals for us:

Things like Accelerated Database Recovery, Optimize For Sequential Key, and In-Memory Tempdb Metadata are cool, but they’re server tuning. I love’em, but they’re more helpful for tuning an entire workload than a specific query.

The thing with BMOR is that it’s not just one thing. Getting Batch Mode also allows Adaptive Joins and Memory Grant Feedback to kick in.

But they’re all separate heuristics.

Read on to see the extended events around batch mode to help you determine if it’s possible for the optimizer to use it for a given query.

Comments closed

The Histogram Output with Extended Events

Published 2020-02-11 by Kevin Feasel

Grant Fritchey talks us through the histogram output in Extended Events:

The histogram target behaves similarly to the event_counter target. The event_counter target counts the number of times that an event occurs. However, the histogram target lets you pick a grouping mechanism for the histogram. You can use either an action, or an event field.

For demonstration purposes, what I want to know is, per object in the database, how many times are the statistics automatically updated?

Read on to see the test, including event setup, data-building queries, and usage of the histogram itself.

Comments closed

Tracking Query Store Changes

Published 2020-02-05 by Kevin Feasel

Erin Stellato shows how to watch for Query Store changes whether due to settings modifications or running out of space:

The Query Store feature is a bit unique in that its status can change without user interference, which means it is important to understand how changes to Query Store are logged.  A couple weeks ago John Deardurff posted a question on Twitter asking specifically whether the SQL Server ERRORLOG is written to when the OPERATION_MODE changes to READ_ONLY because  MAX_STORAGE_SIZE_MB is exceeded.  I had never tested to confirm, but I know there is an event in Extended Events that will fire when the limit is reached.  I also know that when a user makes a change to a Query Store setting, it is logged in the ERRORLOG.

Click through to see how to watch for this and what the changes look like.

Comments closed

Tracing a Session with Extended Events

Published 2020-01-31 by Kevin Feasel

Jason Brimhall shows how you can trace a specific session using Extended Events:

The ability to quickly and easily trace a query is important to database professionals. This script provides one useful alternative to trace a specific spid similar to the method of using the context menu to create the trace within SSMS and Profiler.

This is yet another tool in the ever popular and constantly growing library of Extended Events. Are you still stuck on Profiler? Try one of these articles to help remedy that problem (here and here)

Read on to see how.

Comments closed