14Feb 2022 by Grant Fritchey No Comments

Extended Events Session Properties

SQL Server
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. Session Properties Window When you open the Extended Events session properties window for an existing session, in SSMS 18.1, it should look something like this: See the problem? Well, that is the problem. Here, look after I resize it: There it is. At the bottom. By default, the window isn't sized correctly so you see everything. In fact, I'm in the habit of maximizing the window, just because it makes it easier to work with. However, I…
Read More
03Jan 2022 by Grant Fritchey 4 Comments

Identifying Recompile Causes

SQL Server
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. Extended Events for Recompile Causes You knew I was going there. Seriously though, we know that, since SQL Server 2005, all recompiles are at the statement level (unless you call for a recompile of a procedure or create that proc with a RECOMPILE hint). So, capturing the recompiles and getting grotty on the details means capturing statements. Understand that this can be expensive, especially in terms…
Read More
27Sep 2021 by Grant Fritchey 1 Comment

Login Timeouts

You Can't Do That In Profiler
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. Extended Events You must have known I was going to bring up Extended Events. Surely. Well, I am. Now, if you search up "timeout" in the events, you find lock timeouts, execution plan timeouts (ooh), and stuff like that. It's not related to the login timeout. So, look up "connection" or "log in". You get a lot of information, but again, none of it is related to timeouts. In fact, the best info is in process_login_finish. It does include login…
Read More
19Jul 2021 by Grant Fritchey No Comments

Filtering Extended Events Using Actions

SQL Server, You Can't Do That In Profiler
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. Using Actions To Filter Extended Events Actions, also called Global Fields, are additional bits of data that you can add to a given Event when you're setting up an Extended Events Session. They are programmatic additions to the Event, as described here. Think of them sort of like triggers. In practice, adding an Action, database_name, to an Event, like the rpc_completed…
Read More
10May 2021 by Grant Fritchey No Comments

Query Compile Time

SQL Server, You Can't Do That In Profiler
A question that came up recently was how to track the query compile time. It's actually a pretty interesting question because, there aren't that many ways to tell how long it took to compile the query, and they don't necessarily agree. For most of us, most of the time, compile time for a given query doesn't matter. However, I love telling the story of the query I had on an old system that could run in 90ms, but took 5 minutes to compile. In short, sometimes compile time matters. How To See Query Compile Time If you want to see how long it takes a query to compile, you have, to my knowledge, three options. The first, and possibly easiest, is to look at the plan properties on an execution…
Read More
26Apr 2021 by Grant Fritchey 2 Comments

Using Extended Events Live Data With Azure

Azure, SQL Server, You Can't Do That In Profiler
In my last post I showed some shortcomings of Extended Events, however, it is possible to use Live Data with Azure. Let's explore exactly how that works. To get started, you'll need to follow the directions here to get set up with Azure Storage as the output target of your Extended Events session within your Azure SQL Database. There is a little bit of prep work, but it's all laid out in Microsoft's document. I found the Powershell to be a bit sketchy, but it shows you what's needed. The T-SQL just works. Live Data With Azure 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…
Read More
19Apr 2021 by Grant Fritchey No Comments

Extended Events and Azure SQL Database

SQL Server, You Can't Do That In Profiler
Knowledge of how your system behaves is vital to better control, maintain, and grow the system. While Azure provides all sorts of wonderful assistance within Azure SQL Database, you're still going to need that same knowledge. When it comes to getting detailed information about Azure SQL Database, the tools are a little more limited than with an on-premises instance of SQL Server, or any virtual instance of SQL Server. There are no trace events. To see individual query calls, recompile events, query store behaviors, and so much more, you're going to have to use Extended Events. I'm going to write a series of posts on using Extended Events with Azure SQL Database. Tradition would call for this first post to be an initial how-to. Instead, I want to take a…
Read More
05Apr 2021 by Grant Fritchey 1 Comment

Extended Events: Embrace the XML

SQL Server
While XML is, without a doubt, a giant pain in the bottom, sometimes, the best way to deal with Extended Events is to simply embrace the XML. Now, I know, just last week, I suggested ways to avoid the XML. I will freely admit, that is my default position. If I can avoid the XML, I will certainly do it. However, there are times where just embracing the XML works out nicely. Let's talk about it a little. Copy This Query I have a theory. It goes like this: There has only, ever, been a single XML query written from scratch. All other XML queries are just copied from that one and then edited to do what is necessary OK. Maybe that's not entirely true. In fact, I know it's…
Read More
29Mar 2021 by Grant Fritchey 9 Comments

Extended Events: Avoid the XML

SQL Server, Tools
One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped. Look, I get it. I don't like XML either. It's a pain to work with. It's actively difficult to write queries against it. If there weren't a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let's see how. Live Data Window I have a video that goes into this in detail right here. But the core concept is simple.…
Read More
12Oct 2020 by Grant Fritchey 2 Comments

Most Costly Statement in a Stored Procedure

SQL Server
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. Finding a Costly Statement Query tuning is initially an act of discovery. Which queries, batches, procedures are inflicting the most pain on us. That pain could be measured a bunch of ways. The three most common, in particular order, are: Frequency with with a given query/batch/procedure is called.Resources used by that query.Length of time that it takes…
Read More