Merry Christmas from Santa and Definitely Not Erik
Video Summary
In this video, I’m stepping into the jolly spirit of Christmas and sharing a bit of magic with you all! As Santa Claus himself, I’m offering something special: use the code HOHOHO from now until January 1st, 2026, to get my LearnTeenCast.com course at a whopping 75% off. But wait, there’s more—Erik’s Performance Engineering course is also up for grabs with his own unique discount! So, spread some holiday cheer and share this video with your friends and family. Ho, ho, ho! Remember, Erik wouldn’t be so generous with such a big discount, but as Santa Claus, I am all about spreading joy and generosity. Happy holidays from the North Pole!
Full Transcript
This is definitely not Erik Darling. This is definitely Santa Claus. Ho, ho, ho! Can’t you tell how jolly and merry I am? Anyway, it’s Christmas, and in the spirit of Christmas, I’m gonna give you something. Ho, ho, ho! I’m Santa. If you use the coupon code HOHOHO from now until January 1st of 2026, you can get my LearnTeenCast.com You can get Erik’s LearnTeenCast.com with, definitely not Santa’s course, definitely Erik’s course, LearnTeenCast.com with Erik. And Erik’s course, Performance Engineering for 75% off. Ho, ho, ho! I’m Santa. Look at me. Definitely not Erik. Erik would never give you a 75% off coupon code for that stuff. He lacks the generosity that Santa has. Ho, ho, ho!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
T-SQL Multi-Statement UDF Plan Caching and Reuse in SQL Server
Video Summary
In this video, I delve into the intricacies of multi-statement table-valued functions (MTVF) in SQL Server, specifically focusing on parameter sensitivity and plan caching issues. Building upon yesterday’s discussion about scalar UDFs, today’s episode explores how these functions behave differently when recompiled, leading to varying execution plans and performance impacts. I demonstrate this through a series of demos, highlighting the differences between using a function with and without an `OPTION (RECOMPILE)` hint, and explain why certain execution plans are chosen over others. By running multiple executions and analyzing query plans in QuickQueryStore, we uncover how SQL Server handles plan caching for these functions, revealing the parameter sniffing problem that can arise. If you have encountered similar issues with your own functions or need help optimizing them, consider reaching out for consulting services. Additionally, supporting my channel as a member of the community will ensure you don’t miss any future insights on T-SQL and database performance optimization.
Full Transcript
Erik Darling here with Darling Data. And in today’s action-packed episode of YouTube, we’re going to continue our conversation about UDF, plan caching, and parameter sensitivity, whatever. I forget what I call these things. But anyway, this is what we’re talking about. Because yesterday we talked about scalar UDFs and what happens there. So today we need to talk about these and what happens here because it is the next logical step in the hierarchy of T-SQL functions. Alright? So let’s do that. But down in the video description, if you would like to hire me for consulting, perhaps you have parameter sniffing problems with functions of your own and you’re like, well, Erik Darling, why can’t you help us too? You can do that. If you would like to buy my training, you can also do that down in the links below. Where these lovely fingers point. You can be. You can become a supporting member of the channel, just like PBS. You can ask me office hours questions. And of course, as always, if this kind of content pushes you in your happy place, well, please do like, subscribe, and tell 50,000 or so friends. Anyway, past data community summit, Seattle, Washington, November 17th to the 21st. Kendra Little and I will be tag teaming to you tantalizing, titillating days of T-SQL pre-cons. Like I keep saying, it’s going to be the best T-SQL pre-cons that humanity has ever witnessed. So you should be there and get a t-shirt to commemorate your presence there because otherwise no one will believe you were there, right? Be like that Skid Row concert you went to in 1987. Did you get the shirt? No, you weren’t there then. Anyway.
Let’s get this database party started. And let’s see. We’ve got our function queued up here. Now, just like the ScalarUDF version of this, I had to write two versions of this function. One, normal function that we’re going to be using in our demonstrations or majority of our demonstrations. And two is a version of this with an option recompile hint in it, which probably already gives away the whole thing that, well, we have a parameter sniffing thing that can happen with these two. That probably messes the whole gig up, right? There I go. Blowing the whole waterworks. But anyway, I have reapplied my filters to this extended event session. So that’s great.
And what we’re going to do is we’re going to run the recompile version of this multistatement table valued function. Well, we can see that it is indeed a multistatement table valued function because it returns a table variable. And the primary driver of this UDF is to insert data into that table variable. And then, well, we just say return here. If we wanted to keep things like normal, we might say return that table variable, but we just say return. Don’t ask me why. I swallowed a fly. I swallowed a fly. But let’s make sure that these things are both in here and all correctamundo and that good stuff. So if I run these two functions, or rather this one function with, again, we’re going to do the same couple, same two of the recompile things.
Actually, there’s a third one hiding on me down here. Look at that. All right. Wonderful. We’ve got three. Run these recompile things. You might notice that these taken a little longer than we might like. The days really drag on, don’t they? Anyway, if we go look at the live data for these, we’ve got a query plan over here that looks like this for the first one. It takes about 5.4 seconds to run. Now, in the last set of demos with the scalar UDF, where I talked about how the query that calls or invokes the scalar UDF, the query itself is inhibited from going parallel because it invokes a non-inlineable scalar UDF.
Multi-statement table value function. Part of what took me a little bit to write these demos was working around this part is that the insert into a table variable has the same general problem that invoking a scalar UDF does, where if we look at the properties over here, I’m just going to make this a little bit wider, apparently a little bit wider. We have this thing in our query plan now. So whereas the last one had a non-parallel plan reason talking about the presence of a scalar UDF, we are not allowed to insert into the table variable.
This is true of all table variables, not just ones in multi-statement table value functions. Table variables can’t be modified using a parallel execution plan, including inserts. So part of why this one is so perturbingly slow is that.
Let’s close that. And for the second invocation, we have a slightly different looking execution plan, right? This one does some stuff and then does a hash join down to here. This one does some stuff and this one does the loop joins again. So this is the key lookup plan.
This one runs a bit faster, right? The total time on this is 238 milliseconds. Whereas for this one, it was five and a half seconds, which is no good at all. And then this one, this one takes 4.9 seconds and uses a similar plan to the one before, except there’s a hash join here.
You know what? These were showing different plans before. This time, I guess it didn’t work out so well. This, this, oh wait, there was something weird and different. Oh yeah. So there is a difference here. This is an actually, this, this was a funny one.
This one, this one took me a minute to catch. This one does a hash match inner join to the post table, right? This one does a right semi join to the post table. So it is a slightly different execution plan.
That caught me off guard there for a second. Someday I’ll have this all memorized, but we’ve cut the query plans. We see we got three different ones when we recompiled. Cool. Now let’s go free the proc cache, right?
And let’s say you select the top one from this. Remember if we like, we don’t want to cross apply to this. We could theoretically outer applying at correct results, but for this, we want to have the scalar sub query in the select list because this will not reduce rows.
Cross apply would reduce rows because it’s a like relational operator. And it’s like an inner join where if rows on like, like the rows don’t exist here, we won’t get a row back, right?
And we want our results to maintain correctness across different executions. So we need to put the, put the function call in the select list for this one. We could outer apply to it. But again, that’s a little bit more of a relational thing, whereas this is just like, go find me a result.
So, uh, we’re going to run this for reputation equals one. Let me make sure I did this. I can’t remember. It was too long ago. And let’s run this.
And that’s going to take about five ish seconds to run and get a result back. And if we come over here, uh, we will have a new entry in this. And we can see that we, we used the query plan that we got when we ran this for the one row with a reputation of one, right?
So that’s that new thing. Now I’ve remembered a clear data and not clear filters because that was annoying last time. But, uh, if we now run this for the 800,000 people, this is going to take about 20 seconds because we, it’s about five seconds per run and we’re returning four rows.
So the rules of mathematics tell you that you should take the number five, like about five, and you should multiply it by four because we have four rows. Four is not negotiable on that.
So this will take 21, 22 seconds, 20 seconds even. Great. So we’ve got that. And now we’ve got, uh, four query plans and we’ll see that SQL Server reused that same plan each time, right?
We, we reused the hash match inner join plan where we go down and scan the post table. Fantastic. We did it, right? We’ve, we’ve, we’ve cracked the case. So let’s do the same thing that we did.
Uh, we’re, we’re only going to do three just because, um, the, the plan for 11 was also about five seconds. So it doesn’t really make a lot of sense to show that one here. Um, I’m just going to do a one and three for this one, just to move things along a little bit.
So now if we run this for three, we get a, we get like the faster query plan for this back. And if we run this for the 800,000 rows here, we get those four rows back pretty quick. So, um, what I’m going to do is because there’s not a view for multi-statement table valued functions, the way that there is for, um, scalar UDFs, uh, they’re not tracked in their own DMV.
Uh, I’m going to use, um, quickie store to look at this and, uh, we should see, well, if we come over here a little bit further, five executions of each one of these, uh, UDFs or rather it’s the same UDF.
We just got different plans for it, right? You can ignore the all plan IDs here from the last hour. We got, uh, for a query ID 28475, we got plan ID 6007 and 6008. Uh, there was another plan ID for this from an earlier run that still ends up in all plan IDs.
It was don’t, maybe don’t want to worry about it so much. Actually, that might’ve been the plan ID for the number 11. I forget the, would be nice if there was a row for it, but what the heck? So we get the five executions and we can see the differences looking in quickie store in average min max in total duration between these, right?
Uh, we can see that, uh, on average, uh, one of these did a lot better than the other one. So over those five executions, the, the execution time for the slower one really piled up. And this is again, uh, a parameter sensitivity issue inside functions in SQL Server.
So hopefully this has answered all of your deepest, most burningest questions about, uh, multi-statement table value function, uh, caching and plan reuse rolls right off the tongue, don’t it? Anyway, uh, we’re going to stop here because we have, we have reached the end of these demos clearly because the next function down there on the bottom is inline table valued functions.
So now we’re, we’re going to go do that next. That’s going to be tomorrow’s video because we still have to make another video for tomorrow. Otherwise we have no video for tomorrow and people will say, Eric, why is there no video today?
Tomorrow. All right. Cool. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you tomorrow where we will talk about inline table valued functions.
All right. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
T-SQL Scalar UDF Plan Caching and Reuse in SQL Server
Video Summary
In this video, I delve into the intricacies of scalar UDF parameter sniffing, specifically focusing on how different parameters can lead to varied execution plans. I walk through a detailed example using two versions of a scalar user-defined function—one without and one with a recompile hint—to illustrate why parameter sniffing is crucial in understanding query performance. By leveraging extended events and dynamic management views, I demonstrate the nuances of plan caching and reuse, highlighting how these functions can significantly impact execution times based on input parameters. This video aims to provide insights that go beyond just stored procedures, emphasizing the importance of considering function behavior when optimizing SQL Server queries.
Full Transcript
Erik Darling here with Darling Data. And today’s video is going to be about Scalar UDF parameter sniffing. There was a question in recent office hours where I was like, I got blog posts about that. And then I went back and I read the blog post. And it’s funny, like, reading old content because you’re like, oh, I could make that better. Oh, I could fix that. But, you know, updating old material always seems to get get pushed away in favor of forging ahead, creating new material. I went back and I actually reviewed the file that I had on that stuff. And I was like, well, it’s like, I had to get more into some of this stuff. So I actually ended up using completely rewriting completely different functions and giving much more interesting tests on these things. So the next three videos are going to be about Scalar, multi-statement and inline user-defined function parameter. Well, like plan caching parameters sniffing. So we will endeavor to do our best covering those. Down in the video description, you will find all sorts of useful links. You can hire me for consulting. You can buy my training. You can become a supporting, that means with money, member of the channel. You can also do other fun things with your life, like ask me office hours questions. And of course, if you like this content, please do like this.
Subscribe and tell a friend. Past data community summit. Oh boy. These are probably sound like my last pitches that I can do on this thing. But November 17th to 21st, Seattle, Washington. As usual, me and Kendra Little are going to do two days of the absolute most magnificent T-SQL pre-cons that have ever existed. Just the entire span of human existence. So I hope that you’ll be there getting swag and other things from me. T-shirts and stickers and buttons and all the other stuff. So anyway, let’s start this database party up here. So there are a few things that I need to walk through before I show you exactly what happens when these functions start executed.
The first one is a view that I’m going to use to look at scalar UDF details. This hits a few dynamic management views and gets a pretty, like the reason why this is a view here is because I don’t feel like having this code interspersed throughout the presentation and having to reference the entire thing over and over again. So we’re going to look at the sys.dm.exe function stats view. Brand spanking new in SQL Server 2016. Glad you’re keeping up. And we’re going to use this to look at scalar function performance metrics. The second thing is, of course, the scalar UDF itself.
All right. So this is going to be the scalar UDF that we’re going to use. There’s actually two of them. And there’s two of them for a reason that I’m going to explain in a moment. But the idea here is to find out, based on someone’s reputation, we find people whose reputation is like 25% lower and 25% higher.
We can, of course, manipulate this if we wanted to, but it seemed like a good starting number. And we sum up all the reputation for other people who have a similar or all the post scores for people who have a similar reputation. And we look at those. It’s kind of silly, but it sounded like a fun function to write, so I wrote it.
So I don’t really care if it has a, I don’t really care if it delivers actionable business insights for anyone. Because the idea is to show you a behavior, not to show you actionable business insights. I’m on a dashboard. My eyes are up here, as they say.
So there’s this one. But then I also had to create a version where there’s a recompile hint inside it. And the reason I had to do this might surprise you. If you run a query, let’s say like a select query from blah, blah, blah, blah, blah, blah, blah, and you reference a scalar or multi-statement user-defined function in that query, and you have a recompile hint on the outer query, it does not recompile the scalar or multi-statement function in your query.
Fun, right? So the reason why that’s important is because over here, I had written both for the scalar, but this version has the multi-statement function column in it.
But I had written a loop. And the reason that I wrote a loop is because I needed to find out which of these values generated different plans. And the easiest way for me to do that was to grab the histogram from an index, put that into a temp table, and then have a cursor go over the temp table.
I did this in two different ways. One was just grabbing all of the range high keys. And then one way was grabbing all the range high keys plus one. So like I was trying to see if like the in-between numbers gave me any different plans.
Mostly they didn’t. And mostly I think they didn’t because I do not have a direct equality predicate in this. It’s a greater than, equal to, less than, equal to predicate.
So this was maybe a little bit of a wasted effort adding the plus one, but it was a fun experiment anyway. And then basically what I do is I cursor over the table, grabbing the range high key, and then execute.
Well, you can see there’s a recompile in the name of this function. This is clearly not the scalar version of the function, but this is the multi-statement version. And basically it’s just me trying to find different plans getting generated.
The way that I examined those plans getting generated was with extended events. And of course, I used my fabulous store procedure, SPHumanEvents, to spin up an extended event that grabs actual execution plans so that I could see what plans the function generated on each call.
Because getting execution plans normally, you don’t see that, right? Like when you get an actual execution plan for a query that calls a scalar UDF or a multi-statement table-valued function, the actual execution plan for the function doesn’t show up in the query.
That only happens for functions that are inlined into the code. So like if you have a non-inlinable scalar UDF as mine is, you get nothing, right?
It doesn’t show up in there. You can get an estimated plan, but the estimated plan often doesn’t match the actual plan. That’s another funny thing that I ran into quite a bit with this. It was a bit of a head scratcher at first.
So that’s what this session over here is. And there are some filters on this. That’s why you might see displaying zero of filtered event. Displaying zero of filtered zero events.
Total. What? Okay, whatever. Microsoft English. It’s a nightmare.
Anyway. So that’s this option recompile version of the function here. So what I want to begin by showing you is we can turn off actual execution plans for the moment. They’ll only get in our way.
Is if I run the recompile version of the store procedure, this was my big finding with the loop that I ran. If I run this and we keep an eye on this window over here, I might have to tinker with the filter, so don’t be mad.
If I run these three queries, all of these three queries will generate different execution plans because they’re going to recompile. There’s an option recompile on this each and every time.
So that’s running this version. And if we come over here and look at the live query data, wow, it worked. We have three query plans for this. And the reason why we generated three different plans is notice this number right here changed.
That’s the second parameter in the function. That’s the reputation one. This is the one that really drives cardinality estimation for the query plans that we get. So running these for one, three, and five, clearly I didn’t need that whole loop through 195 histogram steps because the first three, four lines in the histogram gave me everything.
So great. Anyway, for reputation one, we get this plan. Which takes about 1.2 seconds.
We can see that it is a big parallel plan. We scan an index over here. We scan an index down here. And the whole thing takes, again, about 1 point. Well, actually, it’s about 1.3 seconds.
The second query plan looks like this, where we have some constant scans and some compute scalars and blah, blah, and a nested loops join.
And then we go parallel over here and we do some stuff. The third plan looks like this, where this one’s a little bit different from this one, right? This one, we have one, I think it’s one set of constant scan stuff over here. But this one, you know, we still have one, but it’s different, right?
Because it’s a different, like, join over here. This one is nested loops, nested loops, nested loops. This one is nested loops hash join, right?
So we change things a little bit here, right? Because there’s a key lookup in this plan. Oops, this one. There’s a key lookup in this plan. And this one, we just scan the index on the post table.
This one takes about 735 milliseconds. And the one prior takes about 75 milliseconds. All right. So let’s keep those numbers in mind as we forge forward in our path to figure all this stuff out.
So there’s some stuff in here about the histogram that is unimportant. And there’s also some stuff in here about getting some frequencies. When I used to work in market research, you would call getting counts of various things frequencies.
How frequently did this happen? What’s the frequency of this? I’m going to throw the whole thing into the garbage. But this stuff is not pertinent to us.
This was pertinent to me writing the demo. So we can move on from that. But what I want to show you here is let’s come over here and let’s clear data out of this thing. And let’s run dbcc free proc cache because we’re going to have the non-recompile version of this first scalar UDF.
And what we’re going to do is we’re just going to get the top one row for where reputation equals one. And that takes about 1.3 seconds. Crazy, right? If we come over here and look at live query data, well, I guess it was more like 1.2 seconds.
I’m sorry. I misled you by 0.1 of a second. I hope you can forgive me. I hope you can. Find it in your heart.
Now let’s run this query. And let’s get everyone with a reputation over 800,000. Run this. And this is going to return four rows in about five-ish seconds or so.
And if we come over here and we look, there are four rows. There are four new execution plans. We reuse that hash join execution plan every time, right? And some of the times are a little bit different.
Like this is a 952 milliseconds. There’s 990 milliseconds. There’s 993 milliseconds. And there’s 1,003 milliseconds or 1.003 seconds. So every time that we ran the function, the function reused the plan that was cached for.
All right? Let’s clear that out. And let’s repeat our experiment. First, let’s see in here. Let’s validate some stuff.
Here is our function. Here are the five executions. And we have the total and average worker time and the total and average elapsed time. Something that I didn’t talk about up there that is kind of funny is all of the function plans that we saw, it’s inside of a scalar UDF, right?
And one thing that a lot of people, what they mess up about scalar UDFs, and I’m actually going to come back to these because it’s interesting enough to sort of backtrack a little bit. What everyone says about scalar UDFs is, oh, they prevent parallelism, which is true, right?
For the non-inlineable scalar UDF will inhibit parallelism for the query that calls it. But the body of the function is completely free to go parallel, right? Like this is clearly a parallel execution plan.
There is clearly parallelism in this execution plan, right? We can see the parallelism, right? We can smell it. It smells like fast. And we can see the parallelism in this one.
So the body of the scalar UDF is completely free to go parallel, but the query outside that calls that function is not free to go parallel, right? So I’ll actually show you that in the next demo just in case you don’t believe me.
Anyway, let’s free the proc cache and let’s run this now for reputation three, right? So let’s run this. And actually, we’ll turn on query plans for this one.
We’re going to have an extra execution of this query, but that’s okay. If we go to the properties over here, we will have this little thing in the query plan. Non-parallel plan reason. T-SQL user defined functions not parallelizable.
So they inhibit parallelism for the query that calls them, but the body of the function perfectly fine to go parallel, right? Good for us. Anyway, if we come over here and look at the live query data, we’ll see the two executions of this and the two query plans for this that do in fact use parallelism in quite a bit of the plan.
And now, so remember the last one took, I don’t know, like three, four seconds? But that was because the query plan for that one for each execution took around 991 second to run.
These function calls, I’ll take 70, well, rather, these function calls specifically take about 68, 69 milliseconds to run. So when we come over here and we run that 800,000 query for the same four rows, this comes back a lot faster because we reuse a slightly faster execution plan.
So we can already see that T-SQL non-inlineable scalar UDFs get a plan cast for them and further executions will reuse that execution plan, right?
So we come over here now and we look, remember the first two in here were the ones from our executions. The next four are from what our query returned, right? So this one, two, three, and four, these all took around 90 to 100 milliseconds or so.
And we got, but we got faster return results because we were using a generally faster execution plan for this query. So the plan reuse thing here can be a pretty big deal. Not a lot of people think about functions when they think about parameter sensitivity.
Everyone looks at stored procedures and SP execute SQL and like, ah, you parameter sensitive jerk. But functions, just as likely to happen. Fun stuff, right?
And just to sort of prove things out, let’s come over here and we’ll notice that the total and average times for these things are way down, right? From the initial invocation there. And just to sort of prove things out a little bit further, we’re going to do this for five as well.
So if we run this, did I clear this? I didn’t. Let me make sure this is cleared out. Oh, oh, I didn’t mean to clear all filters. I just meant to clear data. There we go.
Anyway, we’ll just deal with it. All right. So there’s our first execution. And we can turn execution plans back off. And this is why I had things filtered out. So here is our query plan for that.
There’s one other query plan in here for the calling query, right? Well, it’s, but this is the one that we cared about. But now if we come back over here and we run this for the 800,000 people, this is going to take a few seconds, but we get results back.
And then we have, if we scroll a little bit, we will have the query post execution plan for all of our executions of that. Oh, we’re up at the top.
So let’s scroll down a little bit, but you get the point. Oh, that’s not the point. Point is down here a little bit further. So there’s one reuse of the plan. There’s two.
There’s three. There’s four reuses of the same plan. So T-SQL, scalar UDFs, most definitely, most definitely. Cache execution plans and reuse them.
And if we look over here, then we will see, of course, the times on that. And these times went up a little bit. That query plan is a little bit slower for the results that we were looking at there.
So as always, I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where most definitely we are going to talk about the multi-statement variety of UDF.
And we will find out how plans are cached and reused for these. So we have that to look forward to, don’t we? All right.
Thank you for watching. Thank you. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Hi Erik! How do you see the DBA role evolve in the next couple of years ? Will it go to a more coding level or maybe more to a multi-database role ? Thank you for the great content.
Hi Erik! When will you be coming to Sweden?
What setup do you use to record your YouTube videos?
First of all, thank you for everything you share! When it comes to SQL Server 2025, which new performance-related feature do you believe has the greatest potential to become a real success?
you’ve already told us all the sql books you like, what are the books about lifting yhtat you like?
In this video, I discuss the evolving role of database administrators (DBAs) over the next couple of years and share my thoughts on how it might shift towards coding or a multi-database role. I also dive into some personal questions from viewers, such as my travel plans to Sweden for SQL Server conferences and the setup I use for recording YouTube videos. It’s been interesting to reflect on the future of DBAs while sharing practical advice on equipment and techniques that can help improve your own video production process. Additionally, I touch on what new performance-related features might be coming in SQL Server 2025, or rather, why they may not live up to expectations based on current demos and updates. Overall, it’s been a great opportunity to engage with the community and share insights that can help both DBAs and developers navigate these changes.
Full Transcript
Erik Darling here with Darling Data, and it is my favorite Monday of the week because I get to answer your office hours questions. Five of them, this many. I’ve counted and double counted to make sure that I don’t shortchange anyone or overcharge myself. So we’ve got that going for us. If you want to ask me office hours questions, you can do so. There’s a link down in the video description. It will take you to my website where there’s like, you know, a little language that’s going to be in the description. So I’m going to have the language around like, hey, if you want to ask a question with some details, here’s how to do it. But there’s also a handy link to the Google doc form that you submit your questions to no email required, right? Just send it in there, right? No, just drop it in. While you’re down there in the video description, hunting for the office hours link, you can do all sorts of other wonderful things like hire me for SQL Server consulting, buy my SQL Server training, become a member of my SQL Server channel and all sorts of other stuff. And of course, if you enjoy this content, well, you can always like, like, subscribe, tell all your friends, you know, all that good stuff. Past Data Community Summit coming up short order. Very close now. You can smell the pre-cons that me and Kendra Little are doing. Two days of them, T-SQL, the best T-SQL pre-cons that have ever been pre-conned. So, you know, if you’re not there, you will be missing out entirely.
on the most formative event of your life, right? But anyway, let’s get this T-SQL, or not T-SQL, SQL Server Office Hours, Performance Office Hours, Party Started. Oh, that database is missing an arm. Ah, AI. That’s not, that’s, that’s not nice. Chopping arms off of these poor databases. Anyway, let’s go look at what we’ve got here. So, hi, Eric. How do you see the DBA role evolve in the next couple of years? Will it go to a more coding level or maybe more to a multi-database role? Thank you for the great content. I am notoriously bad at forecasting these things. I’m just straight up not good at it. You know, I’ve, I’ve lived through, I think like three DBA extension, extinction events now. You know, it was like VMs, the cloud and well, I guess we’re living through the AI one at current.
But, you know, I think that if you are good at what you do and you are good at learning things, you know, you, you will, you are, you are not going to be extinct. But, um, as far as like the, the two things you mentioned, I don’t think that it’s going to go to, I don’t, I don’t think that it’s going to go to either one specifically. Um, you know, uh, you, you might see a shift to, uh, or you might see yourself shift to a different technology, a different database platform more and more.
I don’t know. Uh, I, I, I don’t know what industry you’re in, where that might happen. Uh, a lot of my clients are firmly entrenched in the Microsoft stack for better or for worse. Um, you know, so like a lot of them, there is no like multi-database thing, unless it’s for like some microservices or like some outside feature. Like, um, you know, you’ll see them move some micro microservices to like, uh, like Aurora Postgres, but it’s not stuff where they’re like sitting there staring at performance and like, like biting their nails over it. Um, they might go to Elasticsearch for full text stuff rather than keep, uh, like punching themselves in the face with full text indexes and SQL Server.
But, um, you know, uh, like when you think about like what like DBAs generally do, you know, you’ve got your, like your infrastructure DBAs, you’ve got your performance, like, uh, oriented DBAs, which is like what I do. And then you have the people who like, like, who are like developers and stuff. Uh, the people who do, uh, performance tuning work and development work have already, like already do a lot of coding type stuff, right?
You rewrite queries or, you know, like, like the developers make the features and the performance tuners make the features faster, right? Type things. Uh, the infrastructure type DBAs, they’re just doing infrastructure in the cloud mostly now, right? You still have a lot of on-prem people, of course, but for people who are part of that shift, you know, like whatever they were, whatever they were doing on-prem, they now just have to do like through a crappy portal and like maybe some like command line scripts and stuff.
So, um, you know, I, I don’t generally know where, where the whole thing is going to go and end up. Um, but as far as I’m concerned, uh, people are always going to have performance problems. And so that’s why I’m very happy to keep doing my performance tuning stuff.
So that’s the best answer I can muster on that. I just had lunch. So this is like 90% chance I’m going to fall asleep during or after this video. Uh, hi Eric, when will you be coming to Sweden?
Well, uh, I don’t have any vacation plans to Sweden currently. Uh, but if there is a SQL Server conference in Sweden that you feel that I could make a meaningful contribution to, uh, and they are the type of conference that accepts pre-con events, uh, where I can, I can, you know, defray some of the cost of traveling overseas, uh, then I’m, I’m, I’m always happy to show up.
So you tell me when I should come to Sweden based on when there is an event in Sweden that I can attend. And again, defray some travel costs and potentially have a little tax write-off as well. So you tell me when I’m coming to Sweden.
I can’t tell you. All right. Uh, what setup do you use to record your YouTube videos? Well, it’s a good question. Um, I have a Nikon Z30 pointed at me. Um, I didn’t, I bought it because they were like, this is the ultimate content creator camera.
And then like, it got like one firmware update and like in like, I don’t know, I guess it’s 2019 or something and nothing since. So, uh, I don’t know if it is the ultimate creator camera.
It took a lot of work for me to get it to the point where it didn’t overheat and shut off after like 30, 40 minutes. So maybe, maybe not. Uh, I don’t know if that’s the best advice that I, uh, I don’t know if that’s advice you want to take, get it.
Maybe the advice is not get it. Like maybe there’s better advice out there. Um, I’ve got this shore wireless thing that I use. Um, I don’t know if you recall, uh, a few months back, my, my microphone thing snapped and I had to get these clip on things, but it’s been working great ever since.
Uh, and aside from that, I’ve just got a bunch of hot lights. I’ve got two, one on one, like, you know, photography type lights on either side of me. And then a couple of ring lights in front of me over here.
Uh, aside from that, um, it’s, it’s all held together by bubble gum and magic as far as I’m concerned. All right. First of all, thank you for everything you share.
Well, obviously we haven’t eaten dinner together. Uh, when it comes to SQL Server 2025, which new performance related feature do you believe has the greatest potential to become a real success?
Uh, quite frankly, none of them. Um, so there are features that I think are cool. Like I think optimized locking is cool. Um, I also thought the optimized Halloween protection thing was cool until it got pulled out cause there were bugs in it, but I was really looking forward to that one.
Um, aside from that, when you look at like performance stuff in 2025, there’s nothing really all that interesting in there. Um, there’s like the oppo thing, which is built on the pisspo thing, but the pisspo thing isn’t that great.
And they haven’t made the pisspo thing any better. So I don’t think the oppo thing is going to be all that great. Uh, and then like, like the few demos that I’ve seen about like, uh, batch mode improvements for some of the mathematical functions, like sum and min and max and average, all the, all the improvements are measured in microseconds.
So I’m like, you can’t like, like, I remember like when, uh, when, uh, what was it? JSON first came out in SQL Server 2016. There were my actual Microsoft blog posts comparing JSON and XML for stuff.
And the, all the, all of the demos ended with, and as you can see, there is a 300 micro sec, micro second difference. JSON is clearly superior.
And I’m like, get out of here, like, leave me alone. Yeah. Uh, so I don’t know. Uh, I’m not, I’m not all that crazy about SQL Server 2025 for this stuff. Um, clearly this is the fabric in AI release.
This is not a SQL Server release. Nothing good happening here. Uh, the vector indexes are laughable at the moment, right? They’re read only. Um, I had posted the video about the code that they currently runs when you created a vector index.
It’s like, like who pushed that dead horse out the door? Oh, it’s amazing. Anyway, um, what was I going to say? Oh yeah. It was, there was a, there was a, Bob Ward posted a picture the other day of, uh, the SQL Server 2025 book on a shelf with all of his other books and the SQL Server 2025, 2025 book is like a pamphlet about this big.
All the other books are like, like a good solid ribeye thick, right? Maybe a healthy porterhouse thick. That’s that’s, those are steak measurements in case you’re a vegetarian. But it was just funny.
It was like, damn, like, uh, not much to say about that one. All right. Well, anyway, I dug a pretty good hole on that one. I’m going to have to send flowers.
Uh, you’ve already told us all the SQL books you like. What are the books about lifting? Excuse you that you like. Um, well, um, the, the, the, the two books that have made the biggest difference for me, uh, are the two starting strength books.
Um, it’s, there’s the blue book and the gray book. Um, one is, uh, for about beginner, um, barbell training. The other is about intermediate barbell training. Um, I suppose I could put links to those in the video description if you’re interested in picking those up.
Um, but they are excellent at, um, both, uh, describing, uh, the, uh, how to do the lifts properly. I think it’s like 72 pages about how to properly do a squat, which if you, uh, read and slowly absorb them as you’re doing those lifts, you can, you can really like feel why this is the right way of doing things, or at least, uh, I think, I think a very optimal way of doing things.
Uh, and so it’s not only like how to do the lifts, but also like how to program, um, a lot of, uh, uh, take, takes a lot of the programming questioning out of there. Um, you know, there are of course like templates in the book on how to do something, but, uh, past a certain point, you really should just get yourself a proper coach who is good at programming and make, uh, good calls on these things.
Uh, cause, um, you know, you’re, you’re going to get certain, uh, rep and set schemes stuck in your head. And it’s going to be very difficult for you to figure out, uh, alternate ones and which ones would be appropriate at your stage of training.
So, uh, if, if you, if you, if you do find yourself, um, you know, uh, going into the gym and getting, uh, quite advanced with the, both the amount of weight that you can lift and all that other stuff, and you find yourself sort of like hitting plateaus and not like being able to like meaningfully add weight to the bar any further than, uh, getting a coach really is, um, uh, the next logical step for you there.
Anyway, I think that just about covers everything here. Uh, been a pleasure as always. Uh, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video. I’ve got some interesting, at least I hope it’s interesting stuff lined up for this week. So, um, we’ll, we’ll slog our way through that as we are slogging our way through, uh, yet another Monday of corporate doldrums and all the other stuff.
Uh, you know, there’s a reason that people used to drink at lunch and it’s sad that we’ve lost that in our, in our society. Anyway, thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
New in SQL Server Management Studio 22: Open Execution Plans In a New Tab!
Video Summary
In this video, I share an exciting new feature in SQL Server Management Studio 22 that was recently added as part of the preview release. As someone who frequently deals with query plans and execution paths, I can attest to how frustrating it is when you lose a plan after doing something else within your query window. This feature allows you to open an execution plan in a new tab directly from the query results, ensuring that you don’t have to worry about accidentally losing or overwriting important plans. It’s a simple but incredibly useful addition that streamlines the process of tuning and comparing queries. Additionally, I highlight another issue I’ve opened for SSMS, suggesting the inclusion of an option to keep actual execution plans enabled by default across all tabs. This would be particularly beneficial for query tuners and presenters who rely heavily on these features during their work.
Full Transcript
Erik Darling here with Darling Data. And this is a very short video because it’s Friday and we’re all in love. Just to talk about a new feature that got added in SQL Server Management Studio 22 preview. If you haven’t gotten a chance to download and try that out yet, I don’t blame you. It took me a little bit to get to it too. The SSMS team is working fastly and furiously. Remember the cadence at which you went from 20 to 21 to 22 has been whiplash speed. So this was actually a feature that I suggested and I’d like to thank, of course, Aaron Stilato and the whole crew who works on SSMS for noticing and taking care of this wonderful addition to SQL Server Management Studio. So what it is, is if you run a query and you get a query plan for it, now you can open that execution plan in a new tab so that you don’t lose it if you do something else within your query window. The number of times this has happened to me and I’ve had to rerun a query to get the query plan is absolutely infuriating. And so I just want to show you what it does and how it works. Now, I just got SQL Server Management Studio 22. I have not done any setup or anything on it. But I just want to show you what it does and how it works. Now, I just got SQL Server Management Studio 22. I have not done any setup or anything on it.
So if it looks weird or anything like that, I guess that’s why. But, you know, I think it looks nice, right? Dark mode and all that other good stuff. So what I’m going to do is just run this query and we’re going to see an actual factual execution plan show up down here, right? You can admire all its glory. Look at this. Oh, geez. We’ve got spills and look at all this stuff going on. Oh, boy. We got to tune this thing. But what if we wanted to like, you know, try some stuff and then and then I don’t know, like compare execution plans. What would we do? Well, we could go the clunky route and save it or we could right click and we could say show execution plan in new tab. It’s beautiful. Look at that. And then we do this and it pops up in a brand new tab. And now we can do whatever you want in this window without losing our query plan. It’s fantastic. It’s wonderful. We don’t have to worry about accidentally hitting something. or doing something without remembering to save stuff. We can just pop something off to a new tab and move on with our day, right? Get back to tuning queries, all that good stuff.
Now, there is one other issue that I have open for SQL Server Management Studio. And I think it’s probably an important one for query tuners and people who present about query tuning generally. Because nothing is worse than forgetting to turn on actual execution plans when you are when you’re going to do things, right? Because if you have a lot of different tabs that you’re dealing with or whatever reason, let’s just say, maybe you’re just a fellow old person who works with SQL Server quite a bit.
And the old memory might lapse here and there. I have an open issue to add an option to keep actual execution plans enabled for all tabs. Now, this isn’t obviously this wouldn’t be a default, right? Because not everyone wants execution plans on all the time. This could even maybe be something that you like a setting and presenter mode, because, you know, that’s maybe a little bit more geared towards the type of people who would who would who would like this button there.
But it is something that I think would be a useful addition to SQL Server Management Studio. So we’re going to I don’t know why I just refreshed that we’re going to I’m going to promote that issue in this video as well. The link for this will be down in the video description.
But SQL Server Management 22 Studio 22. I don’t know why I keep forgetting the word studio. This is this is gives you some idea about the forgetful nature of the aged population working with SQL Server. But looking good, looking real nice.
I like it’s all smooth and pretty and got got all the familiar buttons that I’m that I’m used to. So I don’t get lost and have another grandpa episode. Where’s my damn buttons?
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will download SQL Server Management Studio 22 and test it out. Give it a give it a give it a give it a little hoot and a holler and and see how it goes.
And I don’t know. It’s just about it’s Friday. We should we should not have to watch overly long videos on Fridays. We should we should be able to get back to enjoying ourselves day drinking, optimizing our wine storage cabinets, whatever it is that we do to enjoy ourselves.
I don’t know. Smoke four packs of cigarettes and stare at the sky. That’s sounds like a nice time.
Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Batch Mode Sorts and Row Mode Indexes in SQL Server
Video Summary
In this video, I dive into the intricacies of batch mode sorts and rowstore indexes in SQL Server, sharing insights from a recent client-facing issue that highlighted the challenges and nuances involved. We explore how forcing parallel plans can sometimes lead to unexpected results, such as the need for additional sorting even when an index perfectly aligns with query requirements. I also discuss the trade-offs between optimizing for speed versus memory usage, providing practical advice on when to use the `MAXDOP` hint to achieve better performance in specific scenarios.
Full Transcript
Erik dishwaskeling here with Darling Data. And today’s video, we’re going to talk about batch mode sorts and rowstore indexes. And hopefully I said that right as I’m reading it from the teleprompter in front of me. Great. So this was an issue that a client of mine had trying to like, like various parts of their workload, you know, row mode execution just doesn’t cut it. You need to get batch mode stuff involved. But sometimes, especially batch mode on rowstore is a tricky thing to sort of get happening in the way that you want it to happen. So we’re going to talk about a little bit of that. And we’re also going to talk about, of course, the little caveat with batch mode sorts and rowstore indexes. So we have much on our plate. Now, I did mention that this was a client facing problem. And, you know, I do have clients. I do have nice people who pay me for my time to do things for them and have a client-facing problem. Help them with things. If you would like to be one of those lucky people, there is a link down in the video description where you can hire me to do SQL Server stuff. It’s a crazy scheme, I know, but it works. Trust me. You can also, by training, become a channel member, ask me office hours questions. And of course, if you find this channel content just so groovy and ghouly that you want to make sure that it spreads far and wide like the herpes simplex virus, you can, of course, like, subscribe and tell a friend. Get me in that sweet YouTube algorithm or whatever.
Past Dita Community Summit. Oh, boy. Oh, it’s so close. Two days of T-SQL pre-cons. Me and Kendra Little. The entire event is in Seattle, November 17th to 21st. So you should be there. Hopefully you bought tickets and all that stuff at this point because we’re only a few weeks away and sometimes planning this sort of travel takes some time. You got to start early, right? My wife tell you when she books travel, it’s like a million years in advance. So I had chat. So like by the time this video publishes right now, it’s still October. By the time this video publishes, it will no longer be October. So I had said chat GPT. My birthday is in November. Can you give me a birthday themed drawing? Just like iterate on the one that we’ve been using and it left in some Halloween this right? We still have a ghost and a vampire thing, but it is a festive birthday and I am kind of a spooky, spooky person. So we’ve got, we’ve got a ghost and a vampire showing up to my party. So anyway, let’s go talk about the stuff that we wanted to talk about. See, I’m so spooky. I have a black background sometimes. All right. So I think I already did this, but let’s just make sure. Yeah. Wonderful. All right.
We’ve got an index. So what I want to show you first is, and I don’t want to run this like actually like at real time run this because it takes a long time. So just really what I just want to show you is the estimated execution plan for this. Now, this is a query where I am forcing a parallel plan. And since if you watched my video about what Microsoft is doing to create their vector disc and indexes, you should know that the enable parallel plan preference hint is used in that code.
So it’s safe for production now, right? Because Microsoft uses it in production, so you can use it in production. So now it’s safe. They’ve blessed it. So what I want to show you specifically here is that when this query executes in row mode, right? And if we, let’s buy ourselves a little bit more query plan real estate up here, we can generally visually infer that this plan ran in row mode.
Because like, like we have a repartition streams and repartition streams there, they don’t like none of the parallel exchanges support batch mode segment and sequence project aren’t used in batch mode. We get a window aggregate operator for windowing functions when we execute in batch mode.
The filter, of course, could be in batch mode. Actually, I mean, it probably would be if I didn’t disallow it. But and then the gather streams is, of course, another parallel exchange operator. So that can’t be batch mode either. So this is a fully row mode plan. But the thing that I want to point out here is that because we have an index, right? And if we scroll back up here, I should probably sell a little bit of my query plan real estate. We have a non-clustered rowstore index that not only fully covers our query, but supports the window function specification exactly right.
So user ID and sort descending, which is what we’re asking for in here, right? So because we have this, we don’t have a sort operator in here where SQL Server has to like, like, like basically rearrange data from the way it’s stored somewhere to the way that the window function needs it to create its row number, right? So we don’t have to do that there. Now, next is me levels like, you know, try like, hey, like, get that out of the way. Like that’s, that’s gonna, that’s gonna come in handy later. The thing about the sort. So keep that in mind. Now I’m like, okay, well, we want batch mode, right? We’re like, like, we’re gonna use our auxiliary columnstore helper table, we’re gonna do this left join to it. It’s got a clustered columnstore index, it’s got no rows in it, we’re gonna do this thing so that we get a batch mode thing. And we’re gonna say no, but we’re gonna use this now safer production query hint in there. The thing is, though, if we get the estimated plan for this, it’s the same as last time, right? And like, we zoom around a little bit, we’ve got row and like, you know, the like, none of this stuff can be in batch mode, like, like repartition streams, segment sequence project gather streams, but in our filter operator is also still in row mode.
Now this was kind of an interesting one. And what it comes down to for batch mode on rowstore in this query plan specifically, is batch mode on rowstore takes one look at the text column in the comments table and says, hell no. The text column is in VARCAR 700, right? So if we remove that from the query select list, right, we’re gonna specifically name our columns in here. And we’re gonna say no text column, right, we’ve removed it from existence. And we run this, right, we all let’s just get an estimated plan for this. Now we see a much more batch mode plan. Right? But we now we have a sort, we’re using that same nonclustered index, right? So the data from the index is in order. But when we read from this, we’re reading from it in batch mode. Okay. So let’s run the query. And let’s look at how this thing fares. So let’s start over here on the left. And we get a do do do do memory grant of 1418 megabytes. So that’s about 1.4 gigs, I think, depending on how you like divide if it’s by 1000 or 1024, you might, you might have a slightly different take on exactly what that is. But let’s just call it 1.4 gigs for the sake of 1400 megs. Yeah, it’s about 1.4 gigs there. All right, all right, cool, we did the math. But this query finishes in about 1.3 seconds. But the thing is that, why is this sort here? And it’s kind of an interesting thing, because this only happens in parallel plans, right? So if we come down here, where we’re going to do almost the same thing, except we’re going to force this query to run at max.1, right, we’re going to say, hey, and just keep in mind for this query, we didn’t even need this to get batch mode on rowstore.
If we check the estimated plan here, right, we’ll see that we do, we have a very similar plan, but without the sort, right? So reading from the row mode index, with a max.1 plan, we don’t have to sort data here, we can actually rely on the order that this data came from.
So, but if we run this, at max.1, okay, so this whole thing, you know, like it’s a fully batch mode plan. So each operator only has the operator time for itself. So we have 5.2, and then another 137 milliseconds, and then another 35 milliseconds. So let’s just say it was about five and a half seconds total. But the memory grant for this is 120, 1024 KB, which is like one meg. So this is an interesting thing. And this is, this is specifically just a product limitation for the SQL servers implement implementation of batch stuff. It can’t like use the, like in a parallel plan, where batch mode is used like this. It cannot trust the order that things come out of the index. And it can, the index is like the, it’s not the, not the, not that the index is out of order, but the order that stuff gets read in, in batches might not be true to like the, the sort thing. So like it might not maintain things correctly. So we still have to sort data here. So the question for you becomes, what do you want to optimize for? If you want to optimize purely for query speed, well, it’s probably fine to have the, the, this thing run in parallel and have a sort in it and use like 1.4 gigs of memory.
If you want to optimize for memory usage and you don’t, and you’re willing to sacrifice some time for that, you can of course force the plan to run at max.1, not sort data and use less memory. But this was a very interesting thing. And I had to do a little bit of research on the batch mode sorting to make sure that I was correct in this. So, so I talked to some nice people who, who worked with the product and they said, yeah, that’s true. And I said, yeah, that’s great. So yeah, batch mode on rowstore, using windowing functions, when you get a parallel plan, right? Even if you have an index that perfectly puts your data in order for the windowing specification, the parallel plan sort of negates all that and you will still have to sort things. When a serial plan, right? You do not, like you see the, the can trust the order coming from getting stuff from the index and you do not have to sort things.
Reading from big tables with a single thread, even in batch mode does take more time, right? 5.2 seconds versus 829 milliseconds. So really depending on what you want to optimize for, you might choose to hint max.1, get rid of the memory grant, take a little bit longer, or say, screw it, use the memory, use the, use the extra threads, finish as fast as you can.
Anyway, that was about it there. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I’ll see you in tomorrow’s video where we will talk about, I don’t know what kit. Well, maybe I’ll just make it up as I go as usual. All right. Thanks for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into an experiment I’ve been working on to visualize performance issues in SQL Server’s query store using spatial data types. I explore how adding shapes based on metrics like CPU and duration could potentially help users better understand the relative impact of different queries. This is very much a work-in-progress—far from being production-ready, it’s more of a test to see if this approach holds any value for others. I’d love to hear your thoughts: do you think visualizing performance issues in this way would be useful? Or does it just add unnecessary complexity? Your feedback will help me decide whether to continue pursuing this idea or focus on other areas.
Full Transcript
Erik dishwaskeldarling here with Darlene Data. And in today’s video, so I’ve been tinkering with some big ideas lately, right? And one idea that I had was to try to use, so like SQL Server for years has had this spatial data stuff in it. And one idea that kind of occurred to me was no one ever uses this. I wonder what it’s all about. So what I wanted to do was try to attempt to add some shapes to query store. So that when you’re looking at the results, you might be able to infer a little bit of the sort of sense of proportion of performance problems that one query might be having or causing. So we’re going to talk about the geometry of performance issues in this video. And this video, honestly, is a little bit of market research because I would love to hear if you think this is something that would be useful to you and something that I should pursue working on. Right now is just sort of something that I’ve been testing out a bit and marinating on. It is nowhere near complete or what I want it to be. But I think you might be able to see a little bit of kind of where I’m going with it. So I did want to talk about it to sort of figure out if anyone would care that it’s there.
Or if you would just stare at it, you would just see a new tab show up called like spatial results and scratch your head and walk away. So we’re going to do that in this video. Down in the video description, if you would like to hire me for consulting, buy my training or support the content on this channel, you can do all three of those things there. Those do require giving me money. You can also do things for free like ask me office hours questions. And of course, like subscribe and tell all your friends. Hopefully your friends were not affected by the recent AWS outage. So we do, we do, we do pray for their AI souls if they were. Past Data Community Summit coming up in Seattle, November 17th to the 21st.
Kendra Little and I banging out two days of the most magnificent T-SQL pre-cons that have ever been endured on this planet. So I do hope to see you there. I will have all sorts of neat gifts to give you, to give those who show up. If you don’t show up, beat it. I don’t have no use for you. Anyway, I still haven’t, I still haven’t changed this. I still haven’t quite thought of a good November theme. My birthday is in November, so maybe it’ll be my birthday month theme. Maybe I’ll do that. That sounds nice, right? Cool. Anyway, let’s talk about what I’ve been doing over here. So apparently I just left that up.
So what I’ve been trying to do, and I just stapled this on to the very end of the last Quiky and Quickie store. This is by no means production ready code or what things will look like when they’re done. I’ve got a thoughtfully named column here called G, which attempts to draw a polygon using geometry in the ST Geom from text. And just to start off, I decided to use total CPU and total duration.
I really, in order to get the shapes to be visible, I really had to jack up. I had to do some math on the count of executions for this, because otherwise the squares were like this big, which wasn’t very helpful. And then I thought that, you know, maybe I would look at some slightly different metrics if what I was troubleshooting were a parameter sensitivity issue.
So I’ve got another column down here called P. I didn’t format P quite as nicely as G. Let’s fix that on the fly. There we go. And this one looks at some slightly different metrics, but I still have to really jack up the count of executions locally in order to get things to sort of behave the way I want them to. But anyway, when we run a query store now, and I’m going to just do this from scratch so you can see what happens.
We get some results back, and I haven’t set up to look at like a specific parameter sniffing thing or anything. I just want to show you kind of like what the general layout and stuff is, as well as some of the sort of limitations on spatial results that I can’t do much about. So we get this new tab back up here, right? It’s called spatial results. It shows up between results and messages.
And by default, it will go to the first spatial column. You can see over here, that is the column that I helpfully alias G. And if we want to change the label, we have to do that manually. I cannot currently change. I cannot currently set up a default label. Now, what’s annoying is when you first choose the label column, it doesn’t actually label anything.
But if you just click on the zoom thing, then it shows up. So that’s kind of interesting. But then now we can at least sort of see the queries that are in here. Another thing that I haven’t quite figured out yet is the stacking, right?
So like all these shapes sort of like blow together and they don’t maybe help differentiate things as well as they could. There’s also some that are clearly missing labels, right? I think the labels just get hidden if the shapes have a shape over them or something.
I’m not really sure how that works, but I don’t know. Like some or maybe I don’t even know where this one is supposed to be. And maybe this one was supposed to be here. I honestly don’t know what’s going on in spatial world.
But it is kind of cool that if you hover over, well, of course, it’s going to disappear. If you hover over one of the shapes, you get some information back. One reason why I might need to separate this out into a different result set is so that I can sort of choose the columns that I want here.
By default, it just lists out the columns and the ordinal position of the table, which is not the most helpful thing in the world. So I’m probably going to need a second result set if I want to show more pertinent information in here and not have like the beginning of the query plan XML showing up. Because why on earth would you need that?
Right. But not very helpful to anyone. So the other thing that I can’t do or maybe maybe I can, but I haven’t quite figured it out yet is a way to label the accesses on this thing. Right.
Because we have these two lines of numbers going up and down and across. But what are they? Right. Well, for this, it’s total CPU and total duration. Right. Which, you know, I know because I did it. But if you ran this and got this back, you would say, what are these numbers?
What do they mean? I don’t get it. Right. So maybe figuring out a way to label things and there would be helpful as well. But if we change the spatial column from G to P, we will get some slightly different squares back.
We still maintain the query text label, which is very helpful. And we still get back, I guess, depending on precisely where we click some, you know, the same sort of pop up with the columns that are in there. This block is apparently forgotten to be labeled as his.
Well, I mean, probably the one hiding behind this one has forgotten to be labeled as well. So obviously some limitations, obviously some stuff in here that needs to be like worked out probably mathematically. I’m not quite sure how to do that yet.
I’m also not quite sure how far to sort of pursue this. Given some of the limitations and some of the little annoyances, it might just be more confusing than it is helpful to a lot of people. It might have to be like an optional thing that gets sent back.
Like if you are like cool with however this works. And then, of course, there’s also this sort of dynamic bit of, you know, figuring out, you know, what metrics to draw the squares by. If the count of executions is sufficient to draw a square where the size, you know, is visible on the screen.
So there’s like some stuff that really has to like, you know, get, you know, figured out at first. But I do want to sort of get a general sense of if you, the community, the people who watch these videos, hopefully the people who use my store procedures like SP Quickie Store would find any of this stuff at all useful when troubleshooting performance things.
Like the query store GUI itself is a godawful disaster. And whoever designed it has clearly just a lot of disdain for humanity and user experience. But they do have colorful graphs, right?
They do have charts and graphs. There are some visuals there that people find helpful. The one thing that Quickie Store doesn’t have going for it, pretty pictures. So I figured I’ll try to draw some pretty pictures.
But there we are. Some things that I don’t want to do are leave people with needing to export data somewhere else, right? I don’t want to have to copy and paste data.
I don’t want people to have to like have like Grafana or Tableau or Power BI to go do this stuff because it’s an extra step, right? Most of the time when I’m working with clients, the last thing I need is an extra step with a dependency on it that might not exist. So what I want to do is just have something that works in SSMS, gives you some feedback on the proportion of a query’s performance impact, and gives you a way to sort of like judge based on whatever metrics you care about, like what stuff you should be dealing with.
So if you like it, let me know. If you don’t like it, think it’s stupid, say, Eric, stop wasting your time on these fanciful feats. Go back to work.
Go spend time with your wife and kids or something. I don’t know. But feedback is requested. So thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
I hope that you will give the feedback that I have requested. And I don’t know. We’ll figure it out from here, won’t we? All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Introducing sp_QueryReproBuilder: Quickly Get Executable Query Examples from Query Store
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
I know that you praise indexed views as the best alternative to columnstore in Standard Edition. If their tragically limited syntax supports my query, how do you feel about using them for performance improvement on Enterprise Edition?
You mentioned you like select into #tmp and creating the indexes after. I find not having control over the nullability of the column is a bit problematic if you want a primary key after. Is alter table #tmp add unique cluster sufficient? Or do you have any tips on controlling the nullability, even isnulling it doesn’t seem to help sometimes.
What is your choice of background music when working away on T-SQL?
Had any surprises, good or bad, with Accelerated Database Recovery?
Hi, Erik! Can scalar UDFs be sniffed the same way as stored procedures?
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.