Press "Enter" to skip to content

Category: Extended Events

Including Database Name in Extended Events

Aaron Bertrand has a change of heart:

In my previous tip about SQL Server Extended Events, I discussed the trade-offs involved with choosing to store the database name instead of looking up the name via database_id later. At the time, we decided to use the latter approach since we are generally not concerned about the edge case where DDL is performed against a database that is then dropped before collection occurs.

Something else that has since come up: we want to filter out activity against a set of databases matching a specific name pattern since the DDL activity there is system-generated and does not need to be audited. We quickly learned that you have to be careful with how you use the database name in an Extended Events predicate.

Click through to understand the problem as well as the solution.

Leave a Comment

Collecting Database Name with Extended Events

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

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

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

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

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

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

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