Press "Enter" to skip to content

Category: Extended Events

Fun with XESmartTarget

Gianluca Sartori shows off a useful project with a new series of posts:

Some time ago, I started a project called XESmartTarget. I find it super useful and you should probably know about it. It’s totally my fault if you’re not using it and I apologize for all the pain that it could have saved you, but it didn’t because I did not promote it enough.

Now I want to remedy my mistake with a 10 days series of blog posts on XESmartTarget, which will show you how useful it can be and how it can be used to accomplish your daily DBA tasks using Extended Events.

In this first post of the series, I will introduce XESmartTarget, show how it works and how to configure it. For the next 10 days I will publish a post to show you how to solve a specific problem using XESmartTarget. Let’s go!

Click through to get off to a good start.

Comments closed

Fun with the SSMS Extended Events UI

Grant Fritchey airs a few grievances:

I like Extended Events and I regularly use the Session Properties window to create and explore sessions. I’m in the window all the time, noting it’s quirks & odd behaviors, even as it helps me get stuff done. However, found a new one. Let me tell you about just a few of them.

Click through for some examples of UI oddities when working with session properties.

Comments closed

Causality Tracking with Extended Events

Chad Callihan puts on the lab coat and safety goggles:

Causality is defined as the relationship between cause and effect so it’s safe to say that “causality tracking” is an appropriate name.

When you have causality tracking turned on for an Extended Events session, you can follow along with how one event in SQL can lead to various other events. By following the initial attach_activity_id.guid that gets assigned, you can follow statements, waits, statistic changes, and more. It’s a great way to filter the flury of events occuring on your server and narrow your focus.

Read on to see what causality tracking does and how you can benefit from it.

Comments closed

Why Did It Recompile?

Grant Fritchey answers an interesting question:

Strictly speaking, a recompile isn’t really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here’s one way to get it done.

Click through for an extended event which does the job.

Comments closed

Determining the Right Batch Size for Deletes

Jess Pomfret breaks out the lab coat and safety goggles:

I found myself needing to clear out a large amount of data from a table this week as part of a clean up job.  In order to avoid the transaction log catching fire from a long running, massive delete, I wrote the following T-SQL to chunk through the rows that needed to be deleted in batches. The question is though, what’s the optimal batch size?

I usually go with a rule of thumb: 1K for wide tables (in terms of columns and row size) or when there are foreign key constraints, 10K for medium-width tables, and about 25K for narrow tables. But if this is an operation you run frequently, it’s worth experimenting a bit.

Comments closed

Automating Data Collection with Extended Events

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

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

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

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

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