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
02Sep 2024 by Grant Fritchey No Comments

Missing Columns in Extended Events Live Data Explorer

SQL Server
Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work FevziKartal. The rest of this post is just me replicating work already done by others. I just want to see it in action. Columns in Live Data Explorer Back when I wrote the 2017 version of my query performance tuning book (no link, a) get the 2022 version for reasons I'm about to explain, b) this post is just about testing FevziKartal's work, not self-promotion), I was on board with #TeamXE. Jonathan Kehayias had taken me under his wing and explained the virtues of Extended Events and I was sold. I was also grossly ignorant. I thought that the way you…
Read More
19Aug 2024 by Grant Fritchey 4 Comments

What Are the Most Common Blockers to Adopting SQL Server Extended Events?

SQL Server
Yeah, stupid long title. It's a question I put to several different AI engines. I'm curious what the aggregated knowledge of the internet has to say on the topic of blockers to Extended Events adoption. I'll leave it to you to do the search on your favorite engine and get the wordy, long-winded, answers. However, I want to bring up some of the points that were raised in order to talk about them. First, we'll talk about some of the odd answers. Then, we'll talk about the consensus. Also, one engine gave me a blatantly poor example Session, so I want to point that out. Note: I'm not dinging on AIs. I'm growing to like the little monsters. We're going to address valid points that they bring up. However, I…
Read More
12Aug 2024 by Grant Fritchey 1 Comment

Observing Extended Events

SQL Server, You Can't Do That In Profiler
Sure, you can right click on a running session for Extended Events and open the Live Data window, but are there other ways of observing what Extended Events is capturing? Yes. Extended Events Output Let me just get out of the way immediately, there's probably a way to do this programmatically with DBATools. I need to dig in there, and I'll post what I find. But what can we do right now? SQL Server Management Studio (SSMS). Here are all the sessions currently on my test/demo machine: Some of these are built in, system_health for example. Most of the rest are mine. So, how can we see what they do? Well, notice that every one has a little plus sign next to it: If we click on the plus sign,…
Read More
01Jul 2024 by Grant Fritchey No Comments

Only Capture Extended Events For a Given Time

SQL Server
It's a great question. Let's say you want to capture stored procedure completions. But, you only want to capture them between 3AM and 4AM. Can you do it? Output of rpc_completed Let's create a really simple event: CREATE EVENT SESSION [RPCTimeBoxed] ON SERVER ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1) WHERE ([sqlserver].[database_name]=N'AdventureWorks')); If we start this event, run some code, the output within the Data Explorer window looks like this: Just a couple of points here. Notice the fields in the event. None of them are dates or times. However, up above, we get the timestamp column. Done, right? Let's use that. But first, what does AI, through CoPilot tell me? CoPilot To The Rescue? I asked CoPilot. It took a couple of refinements to get it on board with the idea that…
Read More
27Jun 2024 by Grant Fritchey No Comments

Data Technology Learning Resources

AWS, Azure, Database Fundamentals, PostgreSQL, SQL Server
Hey all! Quick one here. Tracy Boggiano, awesome person that she is, has put together an interesting collection of data over on GitHub. It's titled DBA Resources, but it goes beyond DBAs. It's much more about the different data platforms on display. And, it's in Github. Create a pull request and you can add to it. Get your own blog on there, whatever. Anyhoo, worth a look. I'm going to do some contributions. Oh, AND, it's multi-platform, cause, isn't everything these days.
Read More
24Jun 2024 by Grant Fritchey 4 Comments

Exploring Window Functions Execution Plans

SQL Server
There are quite a few different ways that you're likely to see window functions evidence themselves within your execution plan. Let's take a look at one example. Window Functions For our example, I've got a pretty simple query: SELECT soh.CustomerID, soh.SubTotal, ROW_NUMBER() OVER (PARTITION BY soh.CustomerID ORDER BY soh.OrderDate ASC) AS RowNum, Soh.OrderDate FROM Sales.SalesOrderHeader AS soh WHERE soh.OrderDate BETWEEN '1/1/2013' AND '7/1/2013' ORDER BY RowNum DESC, soh.OrderDate; Nothing to it really. What kind of execution plan does this generate? Here's the plan with runtime metrics (aka, an actual plan): I showed the missing index suggestion (and let's remember, they're just suggestions) just for completion and to show I'm not hiding anything. Potentially, adding an index could speed up the query. However, that doesn't affect what we're going to talk…
Read More