10Jun 2025 by Grant Fritchey 2 Comments

T-SQL Tuesday #187: It’s Extended Events. It’s Always Extended Events

SQL Server
Joe Fleming wants to know how I solve weird problems. I'm not sure I'm actually qualified to answer this question since I, myself, am a weird problem, but I'll give it a shot. One aspect of my job is to provide support to our clients. No, I'm not on call (thank the gods), but I'll get roped in for, well, the weirder ones. No, no, not the weirder clients (although...), the weirder problems. Recently we had one, not exactly weird, but it did take a few odd turns along the way. The Problem A client asked us to tell them when a query ran long. Simple. We have a long running query alert, all built in to Redgate Monitor, so, done. No, see, we like getting alerted when queries run…
Read More
09Jun 2025 by Grant Fritchey No Comments

SSMS 21 Fixes Irritating Problem

SQL Server
I have a proper blog post coming out tomorrow for T-SQL Tuesday, but today, celebrate with me that SSMS 21 finally, at long last, fixes the irritating fact that the New Session window in SSMS for Extended Events always opened to the wrong size. Here's SSMS 20: At the bottom of the screen on the right, there's actually more to be seen. Now, every version of SSMS from 2012 to version 20 has hidden the Causality Tracking. If you resize the window (I was in the habit of maximizing it as soon as I opened it), you can see Causality Tracking. Here's SSMS 21: There it is! Causality Tracking! WHOOOP!! There's been a whole bunch of other, little, fixes all over SSMS 21, but this one, I'm excited about. Now,…
Read More
26May 2025 by Grant Fritchey No Comments

Query Hints and Estimated Plans

SQL Server
I'm working on an update to my Query Performance Tuning book for SQL Server 2025 and I found myself wondering, will a query hint be immediately apparent in an execution plan without runtime metrics (AKA, Estimated Plan)? My assumption was a resounding yes, but you have to check. Query Hint in Estimated Plan I've got a simple query that I wanted to test this with: SELECT p.Name, p.Class FROM Production.Product AS p WHERE p.Color = 'Red' AND p.DaysToManufacture > 15 OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); This one is going to very subtle in it's behavior. The execution plan, estimated and actual, with or without the query hint, is going to be shaped like this: The devil as they say, is in the details. Let's look at the properties of the first…
Read More
12May 2025 by Grant Fritchey 2 Comments

SSMS 21

SQL Server
Just a quick one this morning. I'm on the road and haven't worked up a full blog post, apologies. I've been using the preview for SQL Server Management Studio 21 for a little over a week now. You should check it out. It's been mostly stable (I got a crash. I didn't report it because I couldn't replicate it). I think the most interesting thing has been how much it's largely the same. It's in a new home within Visual Studio. However, the majority of the behaviors I've hit so far have been normal. Heck, Redgate SQL Prompt is working for me and my team told me we don't support SSMS 21 yet. I've spent most of my time in Extended Events and the Live Data Window. So much of…
Read More
21Apr 2025 by Grant Fritchey 2 Comments

Can AI Read Execution Plans?

SQL Server
Yeah, yeah, second AI post in a row. I promise not to make a habit of it. But I saw someone else mention that you can feed them XML and the AI will read the execution plan. I had to test it out and then overshare my results with all of you. We Need A Query Here's a query: SELECT c.CustomerID, a.City, s.Name, st.Name FROM Sales.Customer AS c JOIN Sales.Store AS s ON c.StoreID = s.BusinessEntityID JOIN Sales.SalesTerritory AS st ON c.TerritoryID = st.TerritoryID JOIN Person.BusinessEntityAddress AS bea ON c.CustomerID = bea.BusinessEntityID JOIN Person.Address AS a ON bea.AddressID = a.AddressID JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE st.Name = 'Northeast' AND sp.Name = 'New York'; This query results in this execution plan: There are some tuning opportunities here.…
Read More
31Mar 2025 by Grant Fritchey 4 Comments

How Do You Pick Events in Extended Events?

SQL Server
A while back I wrote about using AI to explore why people are not using Extended Events. You can read all about it here, but a short summary of the biggest blockers would be: Familiarity (more comfort in Trace or DMVs) Lack of Knowledge (just don't know how it works) XML (I agree) Event Overload (there are just too many) For this blog post I want to focus on the last one, Event Overload. There really are a lot of events in Extended Events. I don't just think that's a good thing. I think it's a GREAT thing. However, I get it. I hit the same problem, regularly. Which events do I use to do thing that I'm trying to do? Except for blogs like this one, there's not always…
Read More
24Mar 2025 by Grant Fritchey 2 Comments

Can You Tell Why a Plan Didn’t Go Parallel?

SQL Server
Let's face it, most of the time, you probably don't want your SQL Server execution plans to go parallel. After all, that's why, instead of adjusting the Cost Threshold for Parallelism (as you should have), you've set the Max Degree of Parallelism to 1 (I see you out there). However, some of you recognize that, in fact, some queries need to run in Parallel. Yet, sometimes, a query you think should run in parallel doesn't. Can you tell why a plan didn't go parallel? If It's Not Parallel, Why Not? The good news is, this question is really simple to answer. I'm sure you'll be shocked when I tell you that it's right there in the execution plan. The bad news is, you're going to have to open up the…
Read More
17Mar 2025 by Grant Fritchey No Comments

Can You See Table Valued Parameters in Extended Events?

SQL Server, You Can't Do That In Profiler
I live for questions and this was an interesting one. Can you see Table Valued Parameters that have been passed in to Extended Events? I literally have no idea. I'm sure we'll see something, I just don't know what. Time to find out. Table Valued Parameters I don't want to get in to whether or not table valued parameters are a good or bad thing. Like anything else, I'm sure they can be used for good or for evil. However, just like knowing what value was passed to an integer, I can see why you may want to know what was passed in to a table valued parameter. To get started, let's create a table type: CREATE TYPE ErrorList AS TABLE ( ErrorTime DATETIME, UserName sysname, ErrorNumber INT, ErrorMessage NVARCHAR(4000)…
Read More
23Sep 2024 by Grant Fritchey No Comments

Additional Use for sp_statement_completed

SQL Server
Another discussion in Gothenburg (such a great group of people) around Extended Events lead to a (admittedly, tiny) idea for an additional use for sp_statement_completed. The basics for sp_statement_completed are pretty straight forward. If you want to capture a procedure's behavior, you use rpc_completed. If you want to know about the individual statements within the procedure, you use sp_statement_completed. Now, I don't recommend turning this on across the board or without some good filtering in place because, it's likely to generate quite a bit of data. However, it can be useful, including in the following manner. What Path Did I Take? I've got a silly, example, procedure here: CREATE PROC dbo.PathTest ( @Path1 INT, @Path2 INT, @Path3 INT ) AS IF @Path1 = 1 PRINT 'Path 1'; ELSE IF @Path2…
Read More
16Sep 2024 by Grant Fritchey No Comments

Can You See Who Forced a Plan

SQL Server
I had an excellent group of people in Gothenburg Sweden when I taught there and I was asked: Can You See Who Forced a Plan? I didn't know the answer for certain, so I said what I always say: I don't know, but I'll see if I can find out. Query Store System Views One of the first places I'd look to see who forced a plan is the system views in Query Store. No, I don't think it'll be there, but it's worth a look. The obvious place it could be is sys.query_store_plan. After all, that's where a plan will be marked as is_forced. But you look through that and there's nothing about who forced a plan. And looking through the other views, there's nothing showing that. So, this…
Read More