Press "Enter" to skip to content

Category: Extended Events

Automating Data Collection with Extended Events

Published 2021-10-05 by Kevin Feasel

Ed Pollack continues a series on extended events:

While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.

This article dives into one method for collecting and retaining all event data for a specific set of events. A system like this is highly customizable and provides a solid starting point for Extended Events development. This should be viewed as a collection of puzzle pieces; individual pieces can be adjusted as needed to produce a monitoring solution that fits the needs of a given situation, even if it is vastly different from what is demonstrated here.

Read on for the process. Shredding XML isn’t pretty, but the good news is that with a setup like this, you only need to do it once…unless you need to change it later, so get it right the first time and bam, problem solved.

Comments closed

Troubleshooting Login Timeouts

Published 2021-09-28 by Kevin Feasel

Grant Fritchey shows us another use of extended events:

I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn’t see anywhere else, let me share it with you.

Read on to see how, though as Grant notes, it’s not as easy as searching for “login_timeout” or something named similarly.

Comments closed

Capturing SQL Server Login Details with extended Events

Published 2021-09-24 by Kevin Feasel

Jack Vamvas shows how to track SQL Server logins:

I have to capture logon information details for a specific logon on a SQL Server.   Specifically – the client_hostname, nt_username & username. What i’m looking for is a log recording a successful connection made to the server.     The event should be triggered a) when a connection is made & b)   from a connection pool. 

Click through to see how.

Comments closed

The Building Blocks of Extended Events

Published 2021-09-15 by Kevin Feasel

Ed Pollack takes us through the basics of extended events in SQL Server:

Extended Events are an excellent way to collect data about a SQL Server that provides a vast array of events that can be used for performance monitoring, troubleshooting, or auditing a server. In this article, I’ll explain the building blocks of Extended Events data collection.

While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.

This article walks through the steps to create, configure, and implement Extended Events in SQL Server, providing the prerequisite code and concepts to build an automated collection process.

Read the whole thing.

Comments closed

Finding the Culprit in a Database Timeout

Published 2021-07-28 by Kevin Feasel

David Fowler sets us straight on query timeouts:

Not very helpful, right? And unless you’ve got some sort of monitoring in place, for example you can find these in SQL Sentry by looking for aborted queries, or you’re picking these up in your applicaiton logging, you’re going to have a hard time tracking down what query actually tripped the timeout. As you’re reading this post, I’m going to assume that you haven’t got monitoring.

So how do we go about figuring this out?

Click through for the answer.

Comments closed

Filtering Extended Events with Actions

Published 2021-07-20 by Kevin Feasel

Grant Fritchey gives us a technique to filter extended events:

Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don’t have to be collected in order to put them to work filtering your Extend Events.

Read on to see how.

Comments closed

Live Extended Events Data with Azure SQL Database

Published 2021-04-27 by Kevin Feasel

Grant Fritchey is doing it live:

Once you’ve created an Extended Events Session that is output to Azure Storage, you’ve done most of the work. The trick is really simple. Get the Azure Storage account set up with a Container. Create a Shared Access Signature (SAS) with the right permissions (rwl, read, write, list). Get the token from the SAS (it’s a long string). Use it, along with the path to the container to create a Database Scoped Credential. Create the session using the same path and container that you defined in the Credential. Done. You’ve got an Azure Extended Events session gathering data for you and outputting to a file in Azure Storage.

Now, what I’d like to tell you is that you can open up the Live Data window from SSMS. You can’t.

Grant does give us a workaround which kind of does the trick, but this is an obvious place where some additional developer care would be valuable.

Comments closed

Extended Events in Azure SQL Database

Published 2021-04-20 by Kevin Feasel

Grant Fritchey compares and contrasts extended events on-premises to extended events in Azure SQL Database:

I have long advocated for the use of Extended Events. I’ve been posting all sorts of blog posts on how to implement them, how they present unique opportunities for new and interesting data, and how they do so much more than the old trace events, yet, put less of a load on the system. All of that is true, until we hit Azure SQL Database.

Now, don’t get me wrong, Extended Events are still awesome, amazing and wonderful. It’s just that, Azure SQL Database is going to force us to hop through a few hoops. I want to be up front with these… I’m trying to find the right word here, challenges? Maybe. Frustrations? Yeah, kind of. Limitations? Again, sort of, but not quite. We’ll settle on as neutral a term as possible: differences. For the moment.

Read on for the first part in an ongoing series.

Comments closed

Capturing Deadlocks with the system_health Extended Event

Published 2021-04-19 by Kevin Feasel

Jack Vamvas is hunting deadlocks:

An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users. 

I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team. 

Click through for the script.

Comments closed