Press "Enter" to skip to content

Category: Extended Events

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

Causality Tracking in Extended Events

Published 2020-01-15 by Kevin Feasel

Grant Fritchey explains what causality tracking is in SQL Server Extended Events:

So, then what happens? It’s simple. A given task, let’s say, for example, an INSERT query, will be given a GUID. Then, all the events associated with that task, from the compile, to the query start, query finish, waits, recompiles, etc., all get associated with that GUID. They also receive a sequence number.

Therefore, through the use of causality tracking, you can see all the behavior associated with a given task and the order in which it occurred. On a test system, with no load and no activity, this isn’t that big a deal. On a real system under load, whether we’re talking, dev, test or production, where you’re going to see tons of simultaneous tasks occurring. Causality tracking enables you to isolate everything and group by task. You can pluck one set of behavior out of the mess.

That’s really useful, though it does add overhead. It also will never be available for Profiler.

Comments closed

Backing Up Extended Events Sessions

Published 2020-01-13 by Kevin Feasel

Jason Brimhall shows us how to back up Extended Events sessions using Powershell:

Quite some time ago, I shared a few articles that peeled back the top layer of how to use PowerShell (PoSh) with Extended Events (XEvents). Among those articles, I showed how to retrieve the metadata, predicates and actions, and targets (to mention a few). Those are prime examples of articles showing some of the basics, which means there is plenty of room for some deeper dive articles involving both PoSh and XEvents. One topic that can help us bridge to the deeper end of the XEvents pool is how to generate scripts for our XEvent Sessions.

In this article, I will venture to show how to generate good backup scripts of our sessions using PoSh. That said, there are some caveats to using PoSh to generate these scripts and I will share those as well.

Read the whole thing, especially because there is one doozy of a caveat at the end.

Comments closed