Press "Enter" to skip to content

Category: Extended Events

Collecting Database Name with Extended Events

Published 2020-12-28 by Kevin Feasel

Aaron Bertrand has a philosophical dilemma:

Now, in order to capture this data to DDLEventLog, we have a background process running on a schedule that extracts all the new data from the latest .xel files and loads it into the table. (There’s also a process that moves files we know we’ve consumed and deletes them 90 days later to keep the loading job linear.)

This revealed an edge case where it is possible to lose one aspect of the data: database name. When originally building the session, I thought database_id would be enough because the loading process could always look up the name from there. But this is brittle. Imagine the case where a user drops a table, drops the database, then the loading process pulls data from the session. That data about dropping the table contains just a database_id that no longer exists.

Read on for Aaron’s thoughts and ultimate decision.

Comments closed

Use a Separate Deadlock Extended Events Trace

Published 2020-12-04 by Kevin Feasel

Kendra Little explains why it makes sense to have an extended events trace specifically for deadlocks:

We recently had customer ask why SQL Monitor creates an Extended Events session to capture deadlock graphs, when SQL Server has a built-in system_health Extended Events trace which also captures deadlock information?

There are a couple of reasons why a dedicated trace is desirable for capturing deadlock graphs, whether you are rolling your own monitoring scripts or building a monitoring application. I like this question a lot because I feel it gets at an interesting tension/balance at the heart of monitoring itself.

Click through for the answer.

Comments closed

Finding the Most Costly Statement in a Stored Procedure

Published 2020-10-13 by Kevin Feasel

Grant Fritchey takes us through one method of figuring out what which statement you’re waiting to finish when running a stored procedure:

A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don’t have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital.

Happily, Extended Events are here to help.

Click through to see how you can use extended events to figure this out.

Comments closed

Using the Ring Buffer for Monitoring

Published 2020-08-20 by Kevin Feasel

Eitan Blumin uses Extended Events to track activity:

Extended events provides a solution similar to client side trace. It basically can capture all events that a trace can capture (and more), and it also supports a wider choice of target types. And that, is where its true power lies.

It just so happens that Extended Events has a target type called “ring buffer“, and it gives us exactly what we need.

The ring buffer is easy to set up and if you don’t need permanence, works great.

Comments closed

Ignoring security_error_ring_buffer_recorded Events

Published 2020-07-30 by Kevin Feasel

Erin Stellato recommends putting the system health extended event on a diet:

In Aaron’s post he refers to the security_error_ring_buffer_recorded event as “unactionable noise”, and frankly I can’t think of a better term for it.  I’ve never used it to troubleshoot it any authentication/security issue, and I’m very confident that Microsoft isn’t using it in its current state either.  In terms of the volume of that event, for the new client system that I looked at recently the five system_health files covered about four (4) hours of time.  Of the 1,851,741 million events captured in that time frame, the security_error_ring_buffer_recorded event showed up 1,838,882 times.  That’s 99.3% of the events…absolute noise.

Erin shows you how to turn this off and get rid of a mess of unhelpful messages.

Comments closed

Capturing the T-SQL of Prepared Statements

Published 2020-07-07 by Kevin Feasel

Grant Fritchey shows us how to use Extended Events to capture the T-SQL of a prepared statement:

Why would I be concerned with prepared statements? Wouldn’t sql_batch_completed and rpc_completed cover us? Well, no.

What happens when you use sp_prepare? What happens when you’re using an ORM tool that’s using prepared statements? You may see queries that look like this:

EXEC sp_execute 5, 48766;

What the heck code is that executing? Let’s find out.

Read on for the code. It’s not the type of thing you want running 24/7 against all of your prepared statements (certainly not on a busy system!), but very useful for troubleshooting when you don’t have easy access to that next level up in the application chain.

Comments closed

Improving the system_health Session

Published 2020-06-18 by Kevin Feasel

Aaron Bertrand takes a look at a pre-configured tool and gives us ways to make it better:

I was recently trying to troubleshoot a SQL Server replication-related deadlock that our monitoring tool didn’t capture, and tried to find information about it in the system_health Extended Events session. With the default retention settings and the amount of noise contributed by security ring buffer events, I quickly discovered that the session only had data going back less than two hours. Meaning unless I started investigating an event immediately after it happened, all evidence had rolled out forever.

Read on for Aaron’s guidance around this. The natural next step is to build out your own extended events which capture what you need.

Comments closed

Getting Row Counts After Execution

Published 2020-05-19 by Kevin Feasel

Grant Fritchey gives us a few ways to find row counts after a query has finished:

But, if you really want to get picky, batches and procedures frequently have multiple statements. This means that the row count you’re getting isn’t for a particular table. If you want to get that, then you need to capture the statement level stuff, sp_statement_completed or sql_statement_completed, depending on if you’re looking at procedures or batches.

However, this is one of those “be careful what you wish for” moments. Because, if you capture all statements in a system, you’re going to have a very large amount of data to deal with. This is where filtering is your friend.

Click through for Grant’s full answer.

Comments closed