A Little About Index Union Query Plans In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the fascinating world of index union plans, a somewhat rare but intriguing query plan pattern that can sometimes appear in SQL Server. I explore how these plans work by demonstrating examples where SQL Server uses two nonclustered indexes to satisfy a query with multiple equality predicates or an `ORDER BY` clause. I also discuss the differences between simple concatenation and merged concatenation, explaining when each is used and what you might see in the execution plan. Additionally, I delve into scenarios where additional columns are required, illustrating how SQL Server can use key lookups to retrieve necessary data post-index union. This video aims to demystify these complex plans, helping you understand why they occur and how to design indexes more effectively to avoid or optimize them.
Full Transcript
Erik Darling here with Darling Data. Today’s video, we’ve got another one of our query plan pattern videos. And this one is about the wonderful, the fabulous, the somewhat exceedingly rare, index union plans. All right, I know you’re excited for this one. Before we talk about that, the usual things apply here. If you would like to get a membership to this channel for as low as four American dollars a month, there’s a link to do so in the video description. Otherwise, liking, commenting, subscribing, all completely fine activities by me. If you would like help with your SQL Server, I can do all this and more at a reasonable rate. Ding! If you would like some affordable training on SQL Server Performance points tuning, it’s tuning. I’ve got all that and then some for about a hundred and fifty USD for about twenty four hours of content. You can, you can buy that and you can watch it for the rest of your life. How long it’s good for. There is no expiration date. Aside from like, I don’t know, maybe if I die and the bills stop getting paid. That might be it. Likewise, if I die, I won’t be at this event. Otherwise, I’ll see you there on my birthday, November fourth and fifth in Seattle, Washington at Past Data Summit where Kendra and I will be talking and just doing wonderful things with SQL Server and showing you how to solve all sorts of performance mysteries and become the data whatever you are. I don’t know, there are too many titles. Everyone wants to say DBA developer, but there are like 50 million data titles now and I can’t keep track of them all. So whatever data you do, get better at it with me and Kendra at Past Data Summit. And with that out of the way, let’s go do something fun with query plans because that’s what we love doing here at Darling Data. All right, so the first type of index union plan that you might see would look something a little bit like this. And what you’ll have are two index accesses to different nonclustered indexes on your table and then a concatenation of the results of those indexes. All right, now what I’ve got up here for indexes is I’ve got one on owner user ID and I’ve got one on score. And so when I run this query and I say, hey, I want to know where owner user ID equals zero or score is greater than 10.
SQL Server says, well, I’ve got a couple indexes here that are pretty good. I can use both of these. Now, I’m not often one that recommends single key column indexes. They work out well to show you the query plan that I care about in this demo. And this isn’t necessarily solely the domain of single key column indexes. You might see this if you had two indexes that led with those same columns and had maybe different key columns afterwards or in different included columns or all sorts of other different arrangements. You might also see SQL Server sometimes choose to do two other things.
You know, when SQL Server is picking which indexes to use and it needs to fuse indexes together from a table to satisfy all the columns from a query, it essentially has three options. Well, I guess, I guess technically four options. It can scan the clustered index. It can hit a nonclustered index and do a lookup back to the clustered index. It can do index union, which is what we’re looking at, and it can do index intersection, which is what we’re going to look at in the next video. So this is index union, where it takes results from two indexes, munges them together, and then does some, well, in this case, aggregation to get the records out. So the first example that we saw just uses regular old concatenation.
So it’s just taking all the results, plopping them together, and, you know, just producing the result that we need to count things over here, right? The other option that you might see, when you have sometimes two equality predicates, or sometimes if you have an order by, you might see things change a little bit, is if we run this, and what’s different in this query is that we are asking not for where up here we said score is greater than 10, here we’re saying score equals 10. So we have two equality predicates here, and in this plan, rather than just a regular old concatenation, we have a merged concatenation. So SQL Server is essentially taking two sorted inputs that it can merge together, rather than taking two inputs where either one or both is unsorted, and just cramming them all together into one. You’ll notice that this plan is a little bit different, because rather than, and I should have maybe showed these back to back, but it really only makes sense when I talk about this one thing, and it could get confusing otherwise, and confusing sort of like using a mouse to grab tiny little lines in SQL Server Management Studio. This plan changes a little bit after the concatenation, where the the unordered version, just the straight concatenation, immediately does a hash match aggregate, and the first operator after the merge join is a stream aggregate, which indicates you have sorted input already.
Otherwise, we would have seen an explicit sort operator here, where SQL Server would have put the data in order for us. Now, we’re going to jump into the crazy world of needing additional columns, right? Because like I said, when SQL Server is choosing which index or indexes to use on a table, it has options available to it.
Right? And one of those options is to do an index that we can use a union plan with a key lookup. So if you’ll notice in this query, we are selecting the post type ID column, and we’re grouping by the post type ID column. So we need that column from somewhere.
However, that column is not a clustered index key column. It is just a regular old column in the table. So it is not automatically going to be part of our nonclustered indexes. And when we run this query, we’ll see that SQL Server has chosen a somewhat different approach, where we still do the index union, right? We still hit our indexes here, and we still concatenate them here.
But now we have a key lookup back to the clustered index to get that post ID column. If we hover over the lookup and we see what was output there, we can see post type ID. So SQL Server can use an awful lot of indexes on your tables to satisfy different parts of predicates, depending on selectivity and other costing options. And sometimes that’s great, and sometimes it’s not. Where I see it being not great is when folks have added a whole bunch of single key column indexes to, say, every column on the table. And SQL Server chooses to use a whole bunch of that and have to put all those together. And then sometimes even additionally doing a key lookup to do that. If you see query plans doing that, and they look very weird and confusing to you, and you start wondering, is this a view? Why is it hitting this? Why is this table getting hit so many times? It might, the answer just might be in the index design being used for this stuff. Now, I’m going to say not single key column indexes, but like very nearby indexes can be incredibly useful for stuff like interval queries. But we’re going to talk a little bit more about stuff a little bit closer to that in the next video on index intersection, which is when SQL Server, again, chooses two nonclustered indexes, but in a slightly different way.
All right. So, with all that being said, this tiny little chunk of, this tiny little nugget of SQL Server knowledge, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you find this sort of content useful, or at least amusing. Pick a somewhat positive adjective from the potpourri of positive adjectives you have in your head, and just give me that one. I’ll take it.
It doesn’t really matter too much to me. And I will see you over in the next video on index intersection. Again, thank you for watching. My rates are reasonable.
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.
A Little About Nested Loops Prefetching In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into a fascinating and somewhat humorous artifact that I believe might be the funniest SQL Server artifact I’ve encountered: a little red gate showing up in PowerPoint presenter mode. It’s unexpected and certainly adds a unique twist to what could have been a straightforward discussion on nested loops prefetching. This SQL Server feature can lead to some interesting behavior, especially when dealing with parameter sniffing issues in stored procedures. I walk through how the prefetching mechanism works, demonstrating its impact on query performance with both ordered and unordered scenarios. The video also touches on the nuances of parallel execution plans and how they exacerbate these issues. For those interested in diving deeper, I provide links to relevant blog posts by Craig Friedman and Paul White. Additionally, I share information about affordable SQL Server training and upcoming events where you can catch me live. So, buckle up as we explore this quirky aspect of SQL Server together!
Full Transcript
Erik Darling here with Darling Data, feeling absolutely magnificent today, positively capital, if you will, because in today’s video, I don’t know if you’re ready for this, you might need to strap yourself in to something, because we’re going to be entering the funniest artifact that I think I’ve ever encountered in my life, is this. little red gate thing showing up over PowerPoint in presenter mode. That’s cute.
We’ll just… I don’t know, I can pick one again there. Since I already clicked and you already kind of saw it, we’re going to be entering the sexy world of nested loops prefetching. Huh? Yeah, alright.
Hope you don’t have your tight pants on. Before we get into the incredibly lewd body world of nested loops prefetching, if you happen to like me enough to spend $4 a month, if you’re not currently supporting… As long as that $4 a month wouldn’t like come from like the $4 a month it takes to support a starving child somewhere, you can subscribe to my channel via membership.
There’s a link to do that in the video description. If that would take money away from a starving child, which I would never want to do. You can do other things to show your undying support, your unmitigated devotion to being a dated darling by liking my videos, commenting on my videos, and doing the one time maximal effort dance by subscribing to my channel.
If you are in need of SQL Server help, many of you are. I see you out there struggling, having problems, and can’t tell which end of the pants go on first. If my rates are reasonable. I can do this stuff. I can do more stuff at a reasonable rate. Whatever you need, let me know. If you are in the market for some affordable SQL Server training that will last you for the rest of your life.
I have over 24 hours of it available streaming. You can just play it and learn. Like, put your head… fall asleep with it on. Whatever you want to do. And you can get that for about $150 with the discount code SPRINGCLEANING there down below.
If you would like to see me live and in person. If you would like to see me up and talking about database-y things. In the flesh, as it were. But again, this video is going to be so sexy that I don’t think we need to add the word flesh to it. It might just be overkill.
You can catch Kendra Little and I performing two days of SQL Server miracles, November 4th and 5th in Seattle at Past Data Summit. And as always, if there is an event nearby you that you think I would be a good addition to. And they are in need of a pre-con speaker.
You just let me know what that event is. And perhaps I can work out some way of showing up. Because that’s what I do best is just show up. Like I do here. Every day.
So with that out of the way, let us continue on to the nested loops prefetch party. Alright. So I believe I’ve already created this index because it says query executed successfully.
And I did. We are large and in charge here. So first off, no video about something so just titillating would be complete without blog posts from some of my favorite bloggers of all time. Craig Friedman, Microsoft fella, used to write a whole bunch of really great stuff about SQL Server.
From what I understand now, he’s busy trying to figure out how to use an iPhone. And of course, Paul White has a good article about this stuff as well. So if you are interested in diving further into this, these links will be available in the video description.
You might have to do something so bold as scrolling to find them. Some people will ask me where to find things. And I will say, you did not scroll far enough.
I might start charging for butt wiping lessons for some people who refuse to read things. So what I’m going to do is I’m going to first drop clean buffers. And I’m doing this because what I want to show you is that when data that you are prefetching is not already in the buffer pool, you will do additional reads from disk.
Because SQL Server reads ahead to get data that it will need for other iterations of things. It will go and do that ahead of time so that it doesn’t have to issue synchronous IOs all the time. Because, well, I guess at the time, you know, the spinning disks that were available when all this prefetching stuff went in there were not very good at supplying data quickly to CPUs.
Granted, that story has gotten better except in the cloud. So if you are in the cloud, this might still be relevant to you. I don’t know.
So we are going to do this. And I want you to note that I am selecting the top 1,000 here. That’s one and three zeros. All right.
There we go. One, triple zero, thousand. And if we look at the query plan for this, though, we will see that SQL Server went ahead and read almost 1,400 rows or almost 1,400 or did rather not wrote SQL Server. Yeah, did almost 1,400 additional, almost 400 additional rows over the thousand that we have selected there.
And that was because SQL Server was like, oh, I might need those later. And if we go and we hover and we hover over the nested loops to get the tooltip up because we don’t want the tooltip interfering with the right click, which is one of the worst user interface things that happens in SSMS. If we right now right click on this so that we can get the properties overlaid over the tooltip and we go over here, you will see that we have an optimized nested loop that does not show up in the tooltip.
And that the optimized nested loop used, used, used, used, used, used, used. I’m just going to mangle everything today. The unordered prefetch.
So it went out and just sort of scatter gathered stuff from disk. It went, you, you, you, you, you, you, you, you, you, you, you, you, you. It was like one of those, like grocery shopping challenge shows where it’s like you need to put as much expensive stuff in the cart as possible. And someone just runs down the aisle almost shoveling everything in.
We can, of course, change that attribute by asking for stuff in order. And if we run this and we look at the nested loops here, we will now see that we did an ordered prefetch of things.
Right. But this nested loops is now not optimized, but it is ordered from the prefetch, from a prefetching scenario. And this one also did also read an additional 399 rows here.
Okay. So we got, we got that. We got that going for us. Now this will be exacerbated in parallel execution plans because you’ll have multiple threads rushing out and doing prefetchy things.
Like on this one where we read 1700 instead of 1000. That’s up from the 1399. We did some, did some extra work in there.
And this one down below where we will read an amazing, a whopping 1410. Right. So 410 additional rows.
So that, that will, that will change with a parallel plan. But that’s not really the point of this video. The point of this, the point of that stuff was just kind of show you where you see the nested loopy stuff in there. And also to, um, I guess kill a little bit of time.
I don’t know. I did want to show you like the additional read stuff. So, uh, where this can get really nasty is in store procedures where parameter sniffing is a thing. So I’ve got this store procedure ginned up here.
And it’s going to select the top 50,000 rows from the post table. And just to make things easy to demo without having to worry, without having to fiddle too much with things. Um, I’ve got it hinted to use the specific index that we want.
Uh, and I’ve also, uh, got this set to max.1 to, um, sort of highlight where these things can be particularly problematic. Uh, also up at the top, we are doing the same thing that we did above with the checkpoint, the drop clean buffers and a slight wait for delay to let the buffers clean out. So, uh, we’ve got this thing created.
At least I think we do. Let’s double check that there. And, uh, what I’m going to show you is what happens when this. So this is just like another weird parameter sniffing thing. Because some very low numbers of rows, SQL Server will say, oh, we don’t need an optimized nested loops.
We don’t need to prefetch anything. We’re just, it’s very, it’s a very small amount of data. We don’t have to worry about doing any of the like more involved stuff to optimize IO retrieval. So what I’m going to do is I’m going to grab the top.
Well, I mean, it’s still doing the top 50,000, but this person only gets one row. Most of the one second that we just waited for that was the drop clean buffers thing. If we go in the query plan and look what happened, this thing actually finished in about 13 milliseconds.
Right there. So if we, uh, get the properties of this, we will see that this is not optimized and that the, uh, the prefetch attribute is just straight up missing. So SQL Server doesn’t tell you, uh, SQL Server only, rather SQL Server only tells you if, uh, uh, ordered or unordered prefetch is are used.
If neither one is present, that thing is just missing. It doesn’t say like ordered pre like prefetch false or anything like that. It’s just straight up not in there.
So if we go and we look at how this runs. So this, this user, this is John Skeet. John Skeet has about 28,000 rows in the post table. Uh, this will get pretty slow.
All right. We were just waiting and waiting and waiting. And since John Skeet, since we’re getting the top 50,000, but we don’t quite have 50,000, we only have about 30,000 rows in there. I did want to grab one user ID.
That’s the community user ID that has like 220,000 something rows in there that, uh, will take a little bit longer than this even. But if we go look at the query plan now, you’ll see that we spent a lot, not much time in here. Right.
Notice we’re still harboring under the estimate from, uh, the, the, the last compilation. So this is like a parameter snippy thing, but now this nested loop section takes about six seconds or sorry, uh, this key lookup takes about 5.9 seconds. A nested loop takes an additional a hundred milliseconds.
And then we finish out the top there. So not doing that, that read ahead IO really hurt us in here. Now, if we go and we get for user ID equals zero, which has the, again, the 220,000, some odd rows in there. And we’ll actually fill out the 50,000, uh, uh, 50,000 row row goal from the top.
Things will look, um, slightly worse, right? That was, that was about six seconds. And now we are up to about 10 seconds, right?
So this key lookup situation took 10 seconds, one, zero tough time. And where that changes though, is if we recompile this and we go back and we run this the first time for John Skeet, right? So the 22656 user that didn’t take five something seconds.
Did it that took about half a second, right? There’s 531 milliseconds. And if we go look at the nested loops join now, uh, even though the optimized part is false, the ordered prefetch thing is true. So SQL Server did go out and do a bunch of prefetching to optimize the IO from the lookup, right?
And now if we get out of here and we go look for user zero, this will also be pretty quick, right? It’s not going to be quite as quick as John Skeet, but we’re only, we’re only about 300 milliseconds, uh, different rather than like three seconds different, right? Cause remember these numbers almost scale in the same way where, when we ran for user ID six, whatever first that had one row and we didn’t get the prefetching, uh, John Skeet then took about 5 point something seconds.
And this one took about like about 10 seconds. So these go almost up in the same amount, but just in milliseconds rather than, uh, rather than full seconds. So if you are ever dealing with a weird situation with a query plan or even a, you know, something in a store procedure or parameterized, uh, parameterized query where you have a nested loops join.
And, and like the plan doesn’t plan itself doesn’t change. And you, you suspect parameter sniffing and you’re like, maybe you go to query store, maybe you use SP quickie store to look at quickie store. And you’re like, well, this thing generates different execution plans, but they all look the same.
They all use nested loops. What’s going on? Uh, doing something like this and looking at the properties and figuring out if sometimes the, um, the nested loop join uses prefetching and sometimes doesn’t is probably going to be your answer. Now there are various ways of fixing that.
Um, you know, if you have, if you know kind of what you’re doing, uh, you may be able to add an optimized forehand. Uh, to your query to continuously get the prefetching, right? Cause the prefetching is based on cardinality estimation for low numbers of rows.
SQL servers like, no, we don’t need to go out of our way for that. Um, I, if, if I’m remembering correctly, there’s like a, it’s like 25 rows that you, at minimum that you need to get the, the prefetching stuff. But, uh, those details may have changed and may actually be subject to change.
I don’t, I don’t, I don’t recall, uh, exactly how long ago I had, I had figured that one out, but, uh, it may, it may be different now. And, um, it’s kind of a pain in the butt to find all the different test scenarios for these videos and still have them take a reasonable amount of time where my camera will stay on and won’t overheat and all that other good stuff. So anyway, uh, the takeaway here is if you’re dealing with a situation where a query continuously produces a nested loops join, either for a key lookup or even a table to table join, and you, uh, produce the same nested loops plan.
But one of them is much slower than the other sometimes. The answer is probably going to be at the nested loops operator where you are going to be missing the prefetch for some and not missing the prefetch for others. The prefetch can make a very big difference, especially on crappy hardware.
So with that out of the way, with that brilliant recap out of the way, everyone, I hope everyone’s doing okay now. I hope everyone is sufficiently covered from, from this, this just charged video. Uh, uh, I hope you enjoyed yourselves.
I hope you learned something. Uh, I hope that you will continue to watch these wonderful videos and, uh, support this channel in whatever way you, you are able to. Um, cause I, I do appreciate you.
Anyway, uh, that’s about it for this one. Uh, thank you very much for watching. Um, I believe it’s, it’s nearly time to go to the gym and, um, you know, make, make, get a, get a physical workout, break a, break a physical sweat on top of the mental sweat that I spent talking about nested loops prefetching. Ah, all right.
That’s good here. 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.
In this video, I dive into the often-overlooked world of cursor options in SQL Server, sharing insights from my experience and the wisdom of Paul White. I explore how different cursor options can significantly impact query performance, especially when dealing with parallel execution plans and memory grants. By examining practical examples, I demonstrate why it’s crucial to carefully consider cursor options before implementing them, as their default behavior might not always be optimal. Whether you’re a seasoned SQL Server professional or just starting out, this video offers valuable lessons on how to fine-tune cursors for better performance without rewriting complex logic from scratch.
Full Transcript
Erik Darling, right here with you. With Darling Data, too. The force of Darling Data behind me. The strength and might of Darling Data. That’s where we’re at. That’s why we have a barbell for a logo. Anyway, in this video, we are going to talk about the curse of cursor options. Because this is, I mean, you know, you can talk till you’re blue in the face about, you know, not using cursors. But you end up seeing them in a lot of code. And, you know, depending on how much time and effort and, you know, how much someone has paid you, you might find it easier to adjust the cursor options that you’re using, rather than try to rewrite this whole, you know, rewrite this whole, you know, this whole big, complex, undoubtedly nested cursor scenario with, like, you know, decades of business logic built into if statements and checking things and whatnot. Because I’ve certainly been in that position. And I want to say right off the bat that everything I’ve learned about cursor options, I’ve learned from Paul White. I have a tremendous amount of respect for his ability to know what I’ve learned about cursor options.
what kind of cursor to use immediately. I don’t. I always have to, like, refer to notes and think about things and try different stuff. I do not have the mental capability to know exactly which cursor options to use immediately. So I’m going to put that way out front there. You know, there is stuff that I’ve, like, read about cursors from other sources, but I’ve only ever understood it when Paul told me. Which, I don’t know. Maybe it’s the accent. I don’t know. I don’t know. He doesn’t type with an accent, so it’s kind of weird. God, I’m messing this whole thing up. So embarrassed. Anyway, before we talk about this, let’s talk about you and me. Because I have this here YouTube channel, and you might enjoy it so much that you think, you know what, I can part with four bucks a month to make Erik Darling feel good about himself. You might also think, I’m not giving this twit four bucks a month. You might want to like or subscribe or comment, one of those things. If you subscribe, you will be joining the legion of over 4,300 other data darlings in the known universe who get lovely flowers thrown at their faces and feet and then you will be able to find out the way out front there. And I’ll see you next time.
If you’re into that sort of thing, it might be something you’d consider. If you need SQL Server help, maybe you have a lot of cursors in SQL Server and you need some help tuning those. Apparently, apparently I can help with that. I ain’t too proud to bring that up. But in general, if you need a health check, performance analysis, hands-on query and index tuning, or I don’t know, anything else really. I’ll do all the knobs and buttons on that thing. If you’re having a SQL Server crisis or if you want developers to get trained so that you have fewer SQL Server crises to call me about, all good ways to spend money on me. If you want to get some low-cost training for a SQL Server, whether you’re at the beginner, intermediate, or expert level, you can get all of mine for life for 75% off. There are links to do all of these things in the video description.
So you should go look at the video description where the links are and click on them. It’d be the smart thing to do. If you want to see me live and in person, there are no links in the video description for this. That would be overbearing and I would have to change stuff too often. So this just gets a slide. Friday, September the 6th, I will be…
Actually, this video might be coming out on the Monday after I do this, so it’s going to be too late for that. Forget I said anything about Dallas. November 4th and 5th, I’m going to edit this slide apparently. November 4th and 5th, I will be at Past Data Summit with Kendra Little co-hosting two raucous…
rockin’… It’s going to be great. Two days of performance tuning content all about… well, I mean, obviously SQL Server. I don’t know. What else am I going to do with my life? So you can catch me there.
And with that out of the way, let us begin the festivities. Let us join the dance, my friends. All right. Let’s get over to SQL Server Management Studio. Let’s make sure that everything is in good working order here. And what I’m going to do is I’m going to run this query because this is important.
We’ve done exercises like this before where, you know, you come up with a query that runs reasonably fast, but then you put it in some other context, like maybe inserting into a table variable or something, and all of a sudden performance bites. And you’re like, what happened? I don’t understand it.
The answer, my friends, is always going to be in the execution plan. Always. It will be there. It will tell you a good general selection of things.
Let’s put it that way. So let’s run this query. And this query will finish quite reasonably in a bit under a second, right? We get a parallel execution plan.
Everything looks pretty okay here. If you were to write this query in real life, you would probably say, cool, it’s under a second. I’m done. I’m out. Don’t need to call Erik Darling about this one.
But you might if you do some other stuff. So some cursor options, like fast forward, will prevent a parallel execution plan. Other cursor options cause SQL Server to generate weird checksums on data when cursor tables are populated.
Again, these are all things that will be in the query plan that I’ll show you. This can happen. A lot of the times people in SQL Server will just declare a cursor and let SQL Server do the rest.
That’s a big mistake. Because SQL Server can make all sorts of weird choices about your cursor. They can have all sorts of performance side effects.
So if you use scroll, keyset, dynamic, or optimistic cursors without the read-only option also included there, you can end up with really awkward query plan stuff. Because SQL Server adds this checksum to see if rows changed between cursor.
So what I’m doing here is I’m using some syntax that I wish were more popular. Because I see a lot of people try to close and deallocate cursors in the wrong place in code. Myself included.
I’ve gotten caught with my pants. I mean, I’d say around my ankles, but it was like one leg is around an ankle and the other leg is pulled up over my face with that. So I’m using a cursor variable here.
So I don’t have to worry about closing or deallocating. SQL Server will just do that when the time is right. It does complicate the syntax a little bit because you have to, like, declare this and then set the options for it later. It’d be a lot nicer if SQL Server just let you declare the cursor variable with the options you care about.
But what can we do? T-SQL does not see too many improvements, does it? It’d be nice if the summer interns would do some of that stuff.
You ever see some of the syntax available in other databases? It’s real nice. Makes SQL development nice and easy. A lot less crap to do.
T-SQL, you are looking at doing maximum work in everything. So let’s run this just as a fast-forward cursor, right? And this is what I mean to show you about the parallel execution plan thing.
You may notice that this is no longer finishing in a little under a second. This took about five seconds to finish. Here we have our no longer parallel execution plan.
And this is not a side effect of the cursor variable. If we declared a regular cursor with all the options, it would do the exact same thing. There’s no difference there.
But if we, let’s see. Oh, good. We get a warning about this. The query memory grant detected excessive grant, which may impact the reliability. The reliability of what?
I do not know. Just the reliability. The grant size initially was 1,024 KB. Massive, right? Huge.
And used was 16 KB. This is what we are warned about in SSMS. This is what we got instead of commas. Or let’s just say number separators.
It doesn’t have to be commas. I learned recently from a bug in SP Quickie Store that the French use spaces for number separators. So glad that got fixed.
I do want to keep friends with the French. They do have my favorite food and they do have my favorite place to smoke cigarettes. Thanks, France.
Anyway, that’s a bummer. And if we go to the properties over here, we don’t get a loud warning about this. This we have to go digging for. And we zoom in way over here. Let’s put that somewhere nice and near my head.
We have this non-parallel plan reason. No parallel fast forward cursor. Yes, indeedy-doody. No parallelism for you there. Be very careful where you use fast forward cursors.
Sometimes fast forward. In some scenarios, fast forward can be the best option. In other cases, fast forward can really bungle up performance. So be careful there.
My favorite cursor options are local static. But, you know, depending on what you’re doing, that might not make sense either. Cursor options are crazy like that.
So anyway, let’s look at the second example of cursor weirdness. Now, we have to quote out fast forward because we cannot combine these two things. And we have to quote in dynamic.
And what’s going to happen here is performance is going to get even worse than it was before. Not only, well, we actually do get a parallel plan on this one for a bit. But that parallel plan has to do some stuff.
That took about 10 seconds. And if we look at the plan for this, so you have this whole thing over here. So this is where SQL Server populates this sort of hidden worker table that the cursor is going to work off of later. So you have the population query, which grabs all the data you care about and puts it in here.
And then you cursor over the populated table rather than cursoring over the actual table over and over again. The trouble is, even though this is in a parallel zone, right? You can see all the parallelism stuff in here.
You can see the little zoomy buttons across these operators. Notice that this sort now takes nine seconds. It takes nine seconds because, oh, we don’t need the properties. We just need the tooltip.
Calm down. Calm down. Calm down. We have this output list. And that output list contains CHK1002, which is a checksum that SQL Server generates on the entire row that ends up in the cursor table to see if there are any changes between cursor executions, which you need to do some handling for if you’re writing that type of cursor.
It’s almost the equivalent of doing something like this, where you’re selecting the top one owner user ID with this checksum across everything in there. And you won’t see the CHK1002 in here, but you will see this EXPR1001. If I were to, there’s a reason why there’s a misspelled note over this query.
I’ll pretend that didn’t happen. Here I go making fun of the reliability, and I’m saying, don’t run this, PAA. PAA, don’t run this.
Like a mel. And this would take about a full minute to run. So the checksum that happens in here is somehow worse than what happens in the cursor. But if we give this cursor the read-only attribute, right?
If we switch from dynamic-only to dynamic-read-only, we give SQL Server that extra information. Somehow, this select top one query was not enough for it to go off of. Don’t ask me why.
And we rerun this. This will finish just about instantly because we no longer have the CHK in here. And we actually have the population query is just about the exact same as it was when we ran our top one query just by itself. So, like I said, sometimes if you’re tuning code that involves cursors, you may be a full-time employee.
It may be straightforward enough, or maybe you got paid enough to turn the whole cursor thing into set-based code. If that’s true, I’m happy for you. Usually in SQL Server, getting rid of cursors is a pretty darn good idea.
If you’re in a situation where getting rid of the cursor is not an option, sometimes fine-tuning the cursor options might be an option. And you might be able to get much, much better performance if you’re able to do that rather than rewrite the entire cursor thing. Of course, that really depends on what you do with the cursor later.
You know, well, cursors do have, you know, their own sort of overhead and issues. And, you know, I generally don’t recommend them. There are times when they can solve a problem pretty quickly and easily that writing set-based code for would not be very, very easy at all to do.
So, I understand why they’re there. And I also do my best to try to understand the options available to me when using cursors because they can have a profound effect on how fast your cursor runs. So, with that out of the way, as always, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that, uh…
God, I’m talking about curses. I hope that you’ll like and subscribe and member and training, consulting, all that good stuff. Uh…
Because, you know, that’s… That’s how I drink enough to deal with cursors. Anyway, we’re gonna call that one here. Uh…
Once again, thank you for watching. You… Uh… You have now made it to the end of SQL Server. You can shut it down now. There’s nothing left to talk about, right?
That’s good. That’s good. Uh… Alright. Bye.
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.
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.
Kendra and I both taught solo precons, and got to talking about how much easier it is to manage large crowds when you have a little helper with you, and decided to submit two precons this year that we’d co-present.
Amazingly, they both got accepted. Cheers and applause. So this year, we’ll be double-teaming Monday and Tuesday with a couple pretty cool precons.
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
Here are the details!
Day One: A Practical Guide to Performance Tuning Internals
Whether you’re aiming to be the next great query tuning wizard or you simply need to tackle tough business problems at work, you need to understand what makes a workload run fast– and especially what makes it run slowly.
Erik Darling and Kendra Little will show you the practical way forward, and will introduce you to the internal subsystems of SQL Server with a practical guide to their capabilities, weaknesses, and most importantly what you need to know to troubleshoot them as a developer or DBA.
They’ll teach you how to use your understanding of the database engine, the storage engine, and the query optimizer to analyze problems and identify what is a nothingburger best practice and what changes will pay off with measurable improvements.
With a blend of bad jokes, expertise, and proven strategies, Erik and Kendra will set you up with practical skills and a clear understanding of how to apply these lessons to see immediate improvements in your own environments.
Day Two: Query Quest: Conquer SQL Server Performance Monsters
Picture this: a day crammed with fun, fascinating demonstrations for SQL Server and Azure SQL.
This isn’t your typical training day; this session follows the mantra of “learning by doing,” with a good dose of the unexpected. Think of this as a SQL Server video game, where Erik Darling and Kendra Little guide you through levels of weird query monsters and performance tuning obstacles.
By the time we reach the final boss, you’ll have developed an appetite for exploring the unknown and leveled up your confidence to tackle even the most daunting of database dilemmas.
It’s SQL Server, but not as you know it—more fun, more fascinating, and more scalable than you thought possible.
Going Further
We’re both really excited to deliver these, and have BIG PLANS to have these sessions build on each other so folks who attend both days have a real sense of continuity.
Of course, you’re welcome to pick and choose, but who’d wanna miss out on either of these with accolades like this?
pretty, pretty, pretty, pretty good
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
See you there!
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. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
A Bug With Inline Index Creation Syntax In SQL Server
Thanks for watching!
Video Summary
In this video, I share a lighthearted look at inline index creation bugs in SQL Server, which have persisted even through updates to SQL Server 2022. This quirky issue, which I reported long ago and still exists today, adds an amusing twist to my usual technical content. As always, if you enjoy the channel and are looking for more in-depth SQL Server performance tuning, health checks, or training, there’s a special offer available: 24 hours of expert-level content for just $150 when purchased at 75% off. Plus, I’ll be teaching a full-day pre-con on Friday, September 6th, and hosting two days of SQL Server performance tuning content at Data Saturday Dallas and the Past Data Summit in Seattle. So, whether you’re looking to improve your skills or just want to see me in person, there are plenty of opportunities coming up!
Full Transcript
Erik Darling here with Darling Data. And, um, well, this video is gonna be, this is an easier one, cuz I’ve scheduled this for a Friday. I’m not recording this on a Friday. But, uh, in the order that I’m recording videos today, this is also my Friday of video recording for the day. So, I would also like an easy one, where I don’t know if I’m recording videos, I don’t have to talk a lot about anything particularly in-depth. Um, much like on a real Friday, even though I believe, even though, even though today is Monday, and this is going out on a Friday, I am exhausted. I’m not gonna say anything too goofy about Mondays, or coffee, or alarm clocks, or any of that stuff, because, uh, I don’t know.
Don’t don’t wear shorts, as they say. So, let’s talk about inline index creation bugs in SQL Server, cuz this one’s kind of funny, because I reported this a long time ago, and no one has done anything about it. It exists to this day in SQL Server 2022. Uh, I don’t know. Apparently, the summer interns were busy with other things, so. Whew. Let’s have ourselves a time. Uh, if you like my channel, and you have gotten sick of just the mere act of liking and commenting, and you’ve, you’ve, you’ve, like, subscribed all of your, you know, primary and alt YouTube accounts, uh, one, one way that you can support the channel is to sign up for a membership. There is a link to do that in the description of the video, along with links to other things, uh, that you might find useful. I don’t know. Maybe you will, maybe you won’t. Uh, it all depends on how you click them, I guess.
Uh, if you need help with your SQL Server, if it is in bad shape, uh, if performance is not good, or things are going awful for some other reason, I’m pretty good at fixing it. Uh, health checks, uh, health checks, performance analysis, hands-on tuning, dealing with emergencies, uh, training your developers so I don’t have to deal with emergencies anymore. Uh, uh, these are all things that I excel at. Um, I already made an Excel joke in the video, so I’m not gonna redo it here.
I mean, you, you deserve better than that. I’m not gonna go there. Don don’t wear shorts. Uh, so, um, yeah, my rates are reasonable. As always. Uh, if you want some very reasonably rated, uh, performance tuning content at the beginner, intermediate, or advanced, slash expert, slash crazy cuckoo brains, uh, I can’t believe you’re gonna do that with SQL Server level of training.
You can get all mine. It’s 24 hours of content for life for 75% off, which brings it to about $150 US dollars. If you’d like to see me in person, this is where I’ll be, uh, on a Friday just like this one. September the 6th, I will be at Data Saturday Dallas doing all sorts of Data Saturday things.
Uh, but on a Friday too, because, uh, Friday I have to teach a full-day pre-con. And then Saturday will be the 7th, and that’ll be Data Saturday Dallas, but it’s all, it’s, it’s all combined in there. And of course, November 4th and 5th, my, my birthdays, I will be at Past Data Summit in Seattle co-hosting two.
Bang em up, smash em up, hit em up. Days of SQL Server performance tuning content with the lovely and brilliant Kendra Little. So, um, you should come see me at both of those.
Now, without further ado, let’s repartee. Alright. Now I gotta get my head under the party hat. Just in case this is the shot that YouTube picks for like the, the splash image for my video.
This, I gotta get my head under the party hat. Make sure that’s lined up. I really should find a party hat that maybe looks like it’s on a head already.
I don’t know. I’m probably too lazy for that. We’re just gonna have fun now. Alright. So, here’s the bug as it exists. And has existed ever since inline index creation syntax was created.
Uh, now I’ve, this is sort of fitting because I did recently write a video about complaining that you can’t create a filtered index on a, or can’t create a filtered index on a computed column, something like that. One of those videos wild time, obviously very memorable experience for all.
Uh, where if you create a video, uh, create a video, if you create a table. Oh, dear me. If you create a table that has a computed column in it like this one, and it doesn’t matter if you persist it or not.
Uh, if you try to do this, uh, SQL Server will say, you cannot do that. You cannot do that with that column. You are wrong to even try.
This red text signifies your idiocy in the matter. Your, your, your idiocy in the matter and your illiteracy in reading the documentation. Uh, we can, we can prove that doubly by adding the persisted keyword and starting this whole tragic experiment over again. And trying to do this and being met with the same level of resistance from SQL Server where we cannot create that thing.
But this is where things get real wild. If we create our table with this definition, right? We’re gonna, we’re gonna drop the table if it exists.
We’re going to create the same columns right here. Uh, and it doesn’t matter if this one’s persisted or not yet again. But we’re gonna inline the index creation syntax right here like this.
Right? See what we’re doing? Very sneaky, very tricky. And if we create this table, this, this, this completes successfully.
Qua? How dare you? What, what’s gonna happen next? You know what’s gonna happen next? Errors.
Errors happen next. If I run this query, and I don’t even call, I don’t call the crap column. Uh, I don’t do anything where the definition of the, the crap column would be expanded. Uh, and I run this, I get these errors.
Invalid column name crap. Over and over again. Even though I’m not selecting crap, I’m just selecting ID. And then down here, he says, cannot retrieve table data for the query operation because the table dbo.ohyeah schema is being altered too frequently.
Because the table dbo.ohyeah contains a filtered index or filtered statistic, changes to this table… Oh, I’m sorry. This is a long error message. Bring that down a little bit.
There we go. Get in there, baby. Hey, yeah. Contains a filtered index or filtered statistics, changes to the table schema require a refresh of all table data, retry the query operation, not retrying. And if the problem persists, use SQL Server Profiler.
Profiler? Hmm. Use SQL Server Profiler, you say, to identify what schema altering operations are occurring. Even if you just try to get a simple count of what’s in the table, SQL Server will spittoon that same error message at you, and you will get all of that same awfulness in red text on the screen.
Isn’t that wild? Isn’t that a weird bug? And just when you thought you were a bad developer…
Someone got paid a lot of money for that. A lot of money. Someone is getting paid for that to this day.
Someone is on the payroll. For that. Someone… Someone got a generous grant of Microsoft Stock. For that.
Someone’s probably on a boat somewhere. Named Microsoft Stock because of that. Now, well. What can you do? Anyway.
It’s Friday. So am I. So are you. So are we. So let’s… Let’s get out of here. Let’s go do something better with our lives, right? Even though I’m just pretending it’s Friday and it’s Monday, this is…
This is the end of my recording days. So… You have no idea how relieved I am. Anyway.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something like you’re maybe not that bad of a developer. I hope that you will like and subscribe and comment and membership and training and consulting me. So I don’t get lonely.
Anyway. There we go. I think… That Microsoft should give me a generous grant for finding all the bugs that I do in SQL Server. I get…
There’s no glory in reporting them. Aside from making… Getting… Sometimes minor improvements made to their product. But sometimes they linger on for years and no one cares to do anything. So…
You know. You gotta take it as it comes, I guess. I don’t know. I’m gonna go take a margarita as it comes. Which is without triple sec or coin trope. Because those are gross.
They shouldn’t be in your margarita. They make your teeth feel like they’re wearing sweaters. To know better. You’re a grown up.
Anyway. That’s good for now. 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.
Rewriting T-SQL Scalar UDFs So They’re Eligible For Automatic Inlining In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the intricacies of scalar UDF inlining and how it can impact query performance. You’ll learn about the limitations that prevent a UDF from being inlined, such as invoking time-dependent functions like `getDate`, and why sometimes rewriting a scalar UDF to accept parameters instead is the best workaround. By sharing practical examples and detailed walkthroughs using the Stack Overflow database, I illustrate how making small changes can significantly improve query performance. Whether you’re looking for tips on optimizing your SQL Server queries or just curious about the nuances of UDF inlining, this video offers valuable insights. So, if you found it helpful, don’t forget to like, comment, subscribe, and maybe even join my channel as a member for more exclusive content!
Full Transcript
Erik Darling here with Darling Data, voted by BeerGut Magazine to be the SQL Server consultancy most likely to be run by your best friend. So, isn’t that wonderful? Don’t you love being best friends? I’m having a nice time with this relationship. In today’s video, we are going to talk about function rewrites for UDFN. UDFN lining. Why are we going to talk about this? Well, I do have to go to a website after I get out of the slides, but we’re going to talk about this because scalar UDFN lining, for all of the good that it tries to do with your awful UDFs, has some kind of silly limitations that have some kind of silly workarounds, and sometimes you have to employ those in order to get UDFN lining to work correctly. One actually may wonder, if you have to say, if you have to write a little bit more out loud in front of the world in front of 4300 data darlings, if you’re going to rewrite the scalar UDF so that it can use UDF inlining, why not just rewrite it as the inline version of a UDF? The answer is, sometimes that’s hard. Sometimes a UDF is perfectly inlineable just making a small change rather than having to make all of the changes required for writing an action.
Inlineable inlineable inlineable inlineable inlineable function. And boy howdy, sometimes the easy way out is the best way out. Maybe that’s a little grim, but I don’t know. Anyway, before we talk about that, let’s do the housekeeping. If you like this channel, if you like my content, if you’ve already liked and commented and subscribed, but you want to do more, you just have this deep yearning to say, Erik Darling, thank you for the hours and hours of content that you provide for free. You can sign up for a membership. The link to do that is in the description of this video. It is the name of this channel with slash join at the end. I didn’t make that up. That is not a database thing. That’s not like a database joke that I customized for this. That’s just how Google chose to do it with YouTube. I don’t know. It works for me. I’m fine with it. If you need help with SQL Server that is beyond what you can find for free on YouTube or blogs, and you would like a young, good looking consultant to come in and appraise your SQL Server for what it’s worth, I can do all of these things and more. And as always, my rates are reasonable.
If you would like some low-cost, high-quality content, there are 24 hours of it available at my website, training.erikdarling.com. There is beginner, there is intermediate, there is expert slash advanced, whatever you want to call it. I’m not going to say that I’m going to melt your brain because I’ve had my brain melted in the past standing in front of a microwave in the 80s, and it was not pleasant. So there will be no melted brains on my watch. There will be plenty of smart, learned people using SQL Server, though.
You can use the discount code SPRINGCLEANING to get 75% off. There is also a link to that in the video description. Would you believe it? Marketing wizard that I am. If you would like to see me live and in person, either in Dallas or Seattle, I realize maybe not the two most convenient locations for a lot of you watching, but that’s what I got so far. If you would like me to come to a more local venue, tell me when your local venue is having a thing where I could do a thing, and I’ll show up to the thing.
There’s always that. I like going to things. Sometimes. I mean, it’s nice to just, you know, drink alone for a little bit. But Friday, September 6th, I will be at Data Saturday Dallas, and I will be bringing both pokey bribes and sticky bribes, sparkly sticky bribes.
Ooh, you are feeling sleepy because it’s lunch, and you just ate a sandwich and a cookie out of a cardboard box. Now you’re not going to pay attention for the rest of the day. You can come catch me at either one of these events, Past Data Summit or Data Saturday Dallas.
I’ll be there. Past Data Summit, I’m especially looking forward to. One, not because it’s an election. It’s my birthday, and I’m co-hosting two days of performance tuning content with Kendra Little, and that’s going to be a great time.
I don’t think that the election is going to be a great time, especially in Seattle. It can’t go well either way. So with that out of the way, let’s begin the pastry.
I mean, the party. All right. I think I used that joke before. I can’t remember now. Anyway, we’re off. We’re off to the races. Oh, that hat fits much better when it’s not zoomed in than when it is zoomed in.
I should probably shrink that hat down a little bit. But objects in hat may be larger than they appear. Anyway, so I told you we had to look at a website, and so that’s what we’re going to do first.
And one of the limitations on UDF inlining is here. The UDF does not invoke any intrinsic function that is either time-dependent, such as getDate, or has side effects, such as new sequential ID.
Either of these arrangements will prevent your UDF from being inlined. It sounds terrible, doesn’t it? Well, why do we want our UDF to be inlined?
It’s also a good question. It’s also a very good question. So Scalar UDFs and SQL Server have two main performance drawbacks. One is they force the query that calls them to run single-threaded.
They are ineligible for parallel execution plans, and that can make a very big mess for big reporting-type queries when you call them. The other downside is that Scalar UDFs are not invoked once per query. They are invoked once per row that has to get processed through the function.
So if your function has to run over a query that needs to do something with a lot of rows, your function could run a lot of times. And if your function is eligible and is inlined, just like if you wrote an inlined table-valued function, your query will suffer from neither of those fates. It may suffer other performance maladies and whatnot, but those two won’t be it.
Unless you do something else that causes that to happen. Then I can’t help you. Well, I mean, I can help you.
My rates are reasonable. But I’ve seen your code. You seem to practice doing things bad. It’s a thing.
It’s a thing that I’ve noticed. All right. So let’s get into our Stack Overflow database, and let’s set the compat level to 160. 160 is not explicitly necessary for this. We could also be in compat level 150.
Isn’t that just a dream and a joy? We could be in either one of those. As long as you’re on SQL Server 2019 or 2022 or in Azure somewhere. If you’re on SQL Server 2017 or lower, none of this is available to you.
So sorry about that. You should maybe think about upgrading rather than watching YouTube videos. Go download and install it.
I don’t know. So here’s our function. Let’s create or alter it just to make sure that it is properly created or altered. And here’s the sticky part of our function. We have this fallback where if end date is null, we pass in the get date built-in thing.
It’s pink, so I think it’s some kind of function, right? All the functions that are built-in in SQL Server are pink as far as I know. I don’t have anything against that.
I rather like the contrast. If you look up here, it’s like case when blue date diff pink. It’s kind of nice. Mentally, if date diff were also blue, it would all blend.
You wouldn’t know what you were doing. It would be impossible to tell anything apart. So I guess that’s also true when you nest a lot of functions. That’s why I have to separate things out like this so that all this pink does not run into one just dolly-esque mess of drippy things.
So let’s run this query. And I’m going to be honest with you. This query is going to drag on for a little bit. Oops.
Query is going to go for about 16, 17 seconds. It’s not going to be a good time for anyone. All 16, 17 seconds of it. Because, I mean, let’s be honest.
Waiting 16 seconds for rows to return is not anyone’s idea of a good time. I don’t think my microphone picked up on it, but the fans on my laptop, they’re all flustered right now.
Spitting like crazy. If we look at the query plan, we are going to pay attention to two things. One is if we look at the properties here, we will have, as is usual, when we have a scalar UDF in our query plan, we have this non-parallel plan reason.
Since I’m on SQL Server 2022, I actually have a reason here. If you’re on an earlier version of SQL Server, it will probably just say non-parallel plan reason could not generate valid parallel plan.
But since I’m on SQL Server 2022, this actually shows up. And you can figure out exactly why your query could not generate a parallel execution plan. If we peruse some of the plan operators, we will see that, well, we do spend some time in other places.
Oh, geez, that didn’t go well. We spend about four seconds in here. We spend about two seconds in here. There are a few hundred milliseconds in here. But really where things get boggy is in this compute scalar. This compute scalar is what is responsible for the execution of our scalar UDF.
We can see the number 1166178 in here. All right. That’s an important number because when we go…
Oh, man. I just… Hang on. I got to fix my zoom-in. It changed to a red cursor. And I do not want a red cursor. I want a pink cursor. When we go look at this query, it’ll either be exactly that number or it’ll be twice that number.
I’m dying slowly because I practiced this demo and there might have been more stuff. Anyway, here we are. We can see the execution count of our sneaky function matches the number of rows that were returned by the previous query.
And what’s important here is that the total worker time for the function was not the problem. The main problem was the sheer number of executions that the function had to be invoked for and that it forced the rest of the query to run on a single thread.
All right. So this function didn’t actually take up a whole lot of CPU time, but it just dragged on and on and on because it had to execute so much, which is obviously not what we want. This is not an acceptable situation for us.
If you’re on SQL Server 2016 or better, you will have access to this wonderful dynamic management view called sys.dmexec function stats. Sys.dmexec function stats will tell you information specifically about your scalar UDFs.
It does not track inline table valued functions or multi-statement table valued functions. This is purely for the inline, for the scalar UDFs. And I have a feeling that this thing coming around was maybe part of what Microsoft wanted for doing this scalar UDF inlining thing.
I have a feeling. Maybe. You know, it’s just a guess. Just a little guess. Yes. So like I said, this is a very silly reason to need to rewrite a function in this way, right?
Because just because we have get date here, we can’t have the function get inlined. But we can change the function a little bit. Again, this might be where you’re looking at me and saying, Eric, why don’t you just write the whole function as an inline table valued function?
And then we don’t have to think about it. And like I said earlier, when I pose the same dilemma, a lot of the times when I’m rewriting stuff and trying to help clients get things to go faster, there might be very complex logic inside of a scalar UDF.
And the only thing that might be holding that UDF back is the invocation of get date in one or many places in the UDF. And sometimes it’s a little bit easier to just get that out of the way so that SQL Server can do the rest of the inlining work rather than me spending a lot of time writing a very complex inline table valued function trying to get everything working.
So let’s create or alter this to make sure that it is properly created or altered. And let’s run this version of the query now, where we pass in get date from outside of the function, right?
Notice that get date is now out here rather than in there. And you would think that that wouldn’t be a meaningful enough distinction to have anything happen, but it is.
It’s wild. It’s wild what SQL Server has gotten up to. Because now when we look at this query plan, we will see two things. We will see parallelism. And we’ll still see a compute scalar, but the compute scalar has these weird, funny extra branches in the query plan.
And this compute scalar down here with these nested loopy joiny things in it, this is where the function is invoked inline in the query. So this function no longer ran 1.1 something million times. It was inlined into the query and just kind of ran normally.
I mean, I guess technically since it’s a nested loops joint, it may have actually run that many times, but it wasn’t like the same black boxed UDF invocation stuff before. And we got a parallel plan out of it.
So now the whole thing finishes in about 3.7 seconds rather than about 16 seconds. So moral of the story here is that get date in your scalar UDFs will prevent your UDF from being inlined.
If you can replace the get date invocation from within the UDF with a parameter where you can pass get date in from outside of the UDF, SQL Server can then inline your UDF and maybe make performance better for you too.
If you could shave 12 seconds off a query just by adding a parameter to a UDF and passing in get date from the outside, wouldn’t you do that? Wouldn’t that be a nice, simple, easy thing to do? I mean, this is a simple enough function, I suppose.
I suppose you could just put that case expression in somewhere, but sometimes you have to deal with the cards you’re dealt. And sometimes in more complicated situations where maybe this wouldn’t be so readily available to you, just replacing get date in the UDF with a parameter that is assigned get date outside of the UDF is a better option.
So, sometimes it’s so simple you just want to smack yourself silly or your brain melts on the sidewalk. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that this was one of the finer experiences that you’ve had watching a SQL Server video. If you do care to like, comment, or subscribe, or become a member, or buy my training, or hire me to do consulting, we could have magical times together just like this, you and I.
Because we are best friends after all, aren’t we? All right. Great. Anyway, thank you for watching. 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.
Why Expressions Are Better Than Local Variables For Performance In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into a common issue many developers face: the preference for using local variables over expressions in SQL Server queries. I explain why expressions are often superior for performance and cardinality estimation, providing practical examples to illustrate the differences between using expressions versus local variables. The discussion includes how these choices can affect query plans and execution estimates, especially when dealing with date columns. Additionally, I address common pushback from developers who worry about repeating themselves or maintaining consistent values across their code. I also touch on membership options for my channel, offering ways to support the content and upcoming events, while emphasizing that even without financial contributions, viewers can still engage by subscribing, liking, and commenting on the videos.
Full Transcript
Your best friend, Erik Darling here with Darling Data. Magnificent, the wonderful, the wondrous, omnipresent Darling Data. Up in all your data business. In today’s video, we are going to talk about expressions versus local variables. Because this is one that comes up a lot with clients where, you know, in, you know, programming in general, people have this fear of repeating themselves. Deep fear of repeating themselves. And, apparently, they’re still under the weird impression that typing something once and assigning it to a variable is going to do something magical and magnificent for them. But it’s not going to. Right? And I know that I’ve recorded a lot of stuff about local variables, but this is a very specific case to comparing date, date, date, date, date, time, whatever columns. And so we’re going to talk through all of that good stuff. Before we do that, if you feel so obliged, so kind, if you feel just so beneficent in your ways, you can get a membership to my channel. There’s a link for that in the channel description. It is the one that is the name of my channel with slash join at the end.
Then you can choose just how generous you’re feeling every month when it comes to a little bit of channel support here. At some point, there will be member exclusive stuff. I’m just a little bit crunched right now. I’m preparing for, well, we’re going to talk about it in a few slides, but I’m preparing for a few events in the near future that just have a lot of my attention right now on top of the normal client work, on top of having a wife and kids and all that other stuff. So I haven’t had a lot of time to put thought into how I want to structure the membership stuff here, but I will probably be getting to that probably around the holidays here. So if you get in now, you’ll probably get some sort of bonus multiplier thing. I don’t know yet. I got to figure it all out. So if you have no money, if your pockets are empty, if you cannot pay for a cheeseburger today or next Tuesday, you can do other things that let me know you care and that you love me and that you’ll never leave me.
Like you can like and comment on, you can like my videos. You can comment on my videos. You can subscribe to my channel and join an excess of 4,300 other data darlings out there in the YouTube-averse. God, I feel awful saying that. I don’t know why it came out of my mouth. So, and subscribe to my channel for free and just watch the stuff and maybe, you know, enjoy yourselves, learn something, that song and dance. If you need consulting, I am pretty good at this stuff. I can also do other stuff, SQL Server related. If you need something not SQL Server related, I would wonder what it is.
But we can always work on that. And as always, my rates are reasonable. If you want some cheap, well, you know, not cheap like quality, but you know, if you want some cheap money-wise SQL Server training, you can get about 24 hours of it from me and me alone for about 150 US dollars with those discount codes and stuff. There is beginner, intermediate and expert level content in there. So no matter where you are, you can learn something and enjoy yourself.
Thank you for watching. Let me just end it right here, right? Like I said, this is a chat GPT image. Deal with it. I do have upcoming live events. Friday, September 6th, I will be at Data Saturday in Dallas giving away pokey treats and, oh, where are they? Sticky treats.
So I have all sorts of treats for people who show up. November 4th and 5th, I will be at Past Data Summit in Seattle co-hosting two. Oh, those banger days of performance tuning content with Kendra Little. And you can spend both of those days with us because it’ll be the best two days of your SQL Server life.
All right. And that’s all there is to it. And with that out of the way, let’s begin the festivities. Let’s get to partying here because that’s what we do best.
All right. So what we’re going to talk about, again, is why expressions are better than local variables in SQL Server for performance and cardinality and all sorts of other important things. So what I’m going to do is I’m going to create a table called Express Yourself. Sort of a little bit of a joke there, a little play on words, you know.
Express yourself. But because we’re talking about expressions. That wasn’t obvious. I don’t know what is.
And that table is pretty simple. It’s just two columns. And really the important thing is that we have this thing in there. Right. Great. We did it.
We rule the world. We rule the seas and the skies and the dirts and all that stuff. And if we query that table and we look at this query plan, because that’s largely what we do around here at Darling Data is look at query plans and go, oh, not going to make it. When we look at the execution plan, you will see that we got a rather good estimate when using expressions in our query.
I actually framed this probably badly for that particular zoom in. When we use expressions in our aware clause, we get pretty good estimates about what will happen for things. And I know I’ve talked about it before.
If we use local variables rather than expressions, we get actually just do both of these at the same time because they’re both equally annoying. If we use local variables rather than expressions. And also I have two slightly different expressions in here for the later local variable.
One adds one year and the other adds, oh boy, Zoomit is struggling today. One adds one day or maybe it’s just my mouse that’s struggling. It’s tough to tell right now.
Struggles abounds. We get the same kind of goofy estimate for both of them of 171024, regardless of how many rows are actually liable to come out. Now we saw when we use the expressions that this, at least the first one, I didn’t try the second one, obviously.
I was just trying to get to this demo. But we got a good estimate before. Now for this one, we are off by 606%.
And by this one, well, this one is just way lower compared to what actually comes, or way higher compared to what actually comes out of the table. But notice we get the exact same number for both of them. The same thing is true if we use a stored procedure.
Now this cardinality estimate is going to be a little bit different because the start date in this one is a parameter, whereas before they were both local variables. So we’re going to get a slightly different estimate on this one, but it’s still going to be pretty wrong compared to when we just put the expression in there. So if I run this, right, and if I just go get the minimum start date in the table, we get the same result back for both of these.
But notice that the one that we use the expression in, right, is a lot closer to reality than the one that we use the local variable in. Now, before we had that guess of 171,000 some odd something there. This change is because we have a parameter for start date.
SQL Server is able to do good cardinality estimation for start date, but it does bad cardinality estimation for end date because it’s a local variable. So having the expression up here yields better results than storing something in a local variable like we did down here. Where I run into pushback sometimes with these things is someone will inevitably say, well, yeah, but I need everything to be the same date going across all of them.
The good news is if you operate with your start date parameter and you add time to that, it’ll always be the same no matter when a query runs. But where it won’t be is if you use, of course, one of the non-deterministic built-in functions like get date, right? If you use get date over and over again, it’ll be blah.
If you build off the start date, it’ll be blah. That was a good blah, not a bad blah. I thought the smile kind of would help with that.
Maybe it did. But now if we run this, we can sort of illustrate the problem generally. We’ve got to wait like five seconds because there’s a five-second wait for in there.
So let’s put these a little bit closer to each other and let’s zoom in. So the start date parameter for both of these is before and after the five-second wait for is the same, which means that if we use an expression to calculate later using the start date parameter, that’ll be the same for both of these two.
If we set later to a value based on start date, that will also be the same for both of these. Notice that these two blocks in here line up across both of these columns. Where that doesn’t work is when you build something off get date over and over again, right?
So if we just add five seconds to start date, that’s a lot different than adding five seconds to get date before and after the wait for. There is indeed a five-second difference here between 41 and 46. So that’s where people get kind of weird and screwed up.
Now, another thing that people will generally do in these procedures is have some sort of handling in here for start date, right? They’ll say, oh, if start date is null, set it to get date. I don’t know.
I guess that’s reasonable. But you’ll get the same results from this, right? Like this won’t change anything. This will make for a weird cardinality estimation, though. And the reason why this is going to be weird is because if you pass in start date up here with a null value, SQL Server doesn’t do cardinality estimation based on you fixing it here.
SQL Server does cardinality estimation based on start date being null. So if this is the kind of thing that you have to do, well, we’re going to talk about your options in a few minutes. I promise.
But you would end up with the exact same situation here as you did before, where if instead of passing in the start date with a value at, you know, when you execute the store procedure, you pass it in and you correct it within the store procedure, everything turns out the same, you know, including this column being five seconds different, right?
So, okay. Let’s say, for some reason, you want to use that local variable to insert data into a column. Let’s say it’s like the run date for a batch process and you, for all the rows that you put into a table for a particular run of that batch process, you want all the times to be the same.
It’s totally cool to use a local variable for that. It’s also cool to have a parameter for that. If you need to update column, like you need to do like a bunch of updates across a bunch of tables to say, hey, like these, you know, tables all changed at the same time, it’s totally cool to use a local variable for that.
You’re not going to mess with performance there, right? That’s not where local variables cause problems. If your local variable is getting used as a filter somewhere, that’s kind of where you should sweat it, but only if you’re having a performance issue.
You know, while it is nice to go around and correct all of these problems so that your code base is just, you know, a golden god where there’s nothing wrong with it and everything runs perfectly, most of the time when you’re trying, when you’re like me and you’re a consultant trying to make something go faster, you have to kind of set a threshold with things.
And a lot of the times if, you know, queries just aren’t going slow, I’m not going to spend a lot of time typing to fix them. Excuse me. Because it’s just not worth it because I have other slow queries to go fix.
I’ve got to fix the slow queries before I can circle back and start, you know, sort of fine-tuning everything. Your options when you have this problem and if there’s a performance issue are things that I’ve talked about a whole bunch on this channel before. You can use option recompile at the statement level.
You can use parameterize dynamic SQL and pass your local variables into that. Or you can pass those local variables to another store procedure because that other store procedure or the parameterized dynamic SQL will, of course, accept your local variable as a parameter, right?
Because those are different modules and they sort of change the context of things enough for you to get the better cardinality estimates from that. You can also just use a recompile hint and not bother with all the extra typing and testing that dynamic SQL involves. If you do go the route of turning local variables into parameters and passing them into store procedure or to dynamic SQL, this is where you have to be careful about, especially with time-based ones, right?
With, you know, if you have an integer or a string and you’re looking for a quality predicates, you know, it’s where you have to worry about skewed data within the column as a problem. When you’re dealing with date, date time, stuff like that, date time to zero through seven, when you’re dealing with that, you need to worry about the skew being in the range that people look for.
And that’s not something that the parameter-sensitive plan optimization that came up in SQL Server 2022 addresses. Parameter-sensitive plan optimization only works for one parameter and only if it’s an equality predicate and only if the heuristics kick in to say, yes, you should do this.
You have to meet those three things. And then you get, I think not coincidentally, three different execution plans based on the small, medium, and large buckets of values. But range values don’t get that.
So equality predicates, yes, greater than, no, less than, no, greater than, equal to, no, less than, equal to, no. If you are worried about parameter-sensitive plan stuff and you have date range predicates, one thing that you can do is build your procedure with dynamic SQL sort of like this.
And I’m going to highlight this and run it and then I’m going to talk to you about what I did. So normally, when you have a date range, you’re worried that someone will be searching for a small range and then suddenly start searching for a large range or be searching for a large range and then start searching for a small range.
And either way, in either direction, sharing the query plan between those can be pretty bad. One thing that you can do with your dynamic SQL is set it up so that you execute a slightly different query based on how many, like, you know, based on some spectrum of things like this.
So for this, I’m just saying if it’s less than one month, less than or equal to one month, add one equals select one onto the end of the query. If the difference is between two and six months, add two equals select two.
And this is just to keep things simple. These aren’t necessarily the date ranges that I would always use or the month differences that I would always use. This is just to make things kind of simple when we’re looking at stuff and testing.
And then if it’s more than six months, then we want to attack and three equals select three on. And what that gives us when we execute the procedure is this. Let’s start with one in here.
And let’s highlight this. Remember to highlight this whole thing and run it. What I want you to pay attention to is the cardinality estimate in here. We’re just doing a simple single table query.
We already have a good index in place. It’s not like SQL Server has a big variety of plan choices to choose from here. SQL Server is just going to seek into this index no matter what I do. I’m just getting a count.
There’s no need to think about lookups or anything else. What I want you to pay attention to here is the cardinality estimates. Because under normal circumstances, if we weren’t adding that and one equals or and something equals select something onto the end of the query, SQL Server would reuse the cardinality estimate for all three of these examples that I’m going to show you.
Because we’re adding those slightly different strings on with slightly different literal values, those are going to hash out to different queries. And SQL Server is going to come up with different cardinality estimates for each one. Within that, if we were to execute the same one multiple times, SQL Server would totally reuse those query plans for each individual one.
But it’s not going to reuse the plan for one month and the one for six months. You’re not going to have that parameter sensitivity in there. So now if we change this to do three months and we rerun this.
Actually, before I do that, let me come over here and show you the messages tab. Because we’re printing out our dynamic SQL. And there’s our one equals select one.
And then if we change that to be three months, which falls into our two and six bucket, then in the messages tab, we’ll see our two equals select two in here. And in our execution plan, we’ll have our accurate cardinality estimate for that.
Right? So good news across the board there. And now let’s change this to nine months. Because that will get us outside of the final bucket of six there.
And if we rerun this and we look in the messages tab, we will see the three equals select three at the end here. And if we look at the execution plan, we’ll have an accurate cardinality estimate for that. And this is generally, like the name of the game here is to just find a generally good number for cardinality that makes sense across whatever, however many buckets you want to create with your different things in here.
Are there more complicated schemes for this? Yes. Could you build your own histogram for this? Yes. Could you figure out all sorts of other things to do with, like, optimize for? Absolutely.
You could totally do that. But this is one thing that I use quite a bit when working with clients because this helps me come up with a fairly quick and dirty solution to generate different but reusable execution plans based on how big of a date range we’re dealing with. So anyway, in general, for most things that you will do in SQL Server, using expressions is better than using local variables for performance because of cardinality estimation.
You can get very wrong estimates, and depending on, like, where those estimates are used, you could have all sorts of trickle down bad plan choices, bad, you know, bad choices in, you know, how many rows or pages we decide to start locking, all sorts of things. And you can have bizarro performance issues because of that. I can’t say it enough that you should just avoid that situation altogether.
Most of the time, what I tell people is if you’re using local variables and you’re having trouble with a query that’s using local variables and a join or where clause, slap a recompile hint on it and see if SQL Server comes up with a better execution plan. If it does and you’re cool with the recompile hint, leave the recompile hint on. If it does, but you want plan reuse, then turn that into dynamic SQL that’s parameterized, and you will be able to do all sorts of neat tricks to get the good execution plans across the board.
So, with that out of the way, from your best friend here at Darling Data, me, Erik Darling, thank you for watching. I hope you enjoyed yourselves. I hope you learned something new.
I hope that you will continue to watch all of these, all of this wonderful free content, regardless of if you sign up for a membership or hire me for consulting or buy my training. Come to see me perform live at Jocko’s Comedy Hut, Paramus, something, I don’t know. Anyway, these lights are getting hot and my brain’s starting to fry, so I’m going to call this one and I’m going to figure out exactly which video I’m going to record next.
So, as always, thank you for watching. You’re too kind.
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.
Why You Shouldn’t Use The Plan Cache To Figure Out CPU Load In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into why the CPU per database query can be misleading when trying to identify which databases are using the most CPU on a SQL Server. I share my frustration with the endless stream of regurgitated advice on LinkedIn and other platforms, particularly around SQL performance tips that seem to lack substance or context. The video explores common pitfalls of this query, such as plan cache disturbances from various factors like restarts, memory pressure, and recompile hints. I also highlight how recompile hints can make the query even less useful by removing plans from the cache. To address these issues, I suggest adding more detailed information to the query, such as the oldest and newest plan ages, total execution counts, and worker time statistics. Additionally, I share a humorous yet instructive story about my interaction with ChatGPT, which turned out to be hilariously wrong about optimized for ad hoc workloads queries showing up in DMExec query stats. This experience underscores the importance of validating AI-generated information before relying on it.
Full Transcript
It’s your friend Erik Darling here with Darling Data. And in today’s video, we’re going to talk about why that CPU per database query is pretty stupid. I didn’t realize until I signed up for LinkedIn how much awful content there was out there about like, I mean, well, SQL Server in particular, because I can sniff that out. But just data stuff in general, like every day, my feed is just absolutely inundated. I’ve been fascinated with these chat GPT posts that are like, like, top 10 rockstar SQL performance tips, avoid select star, dude. And you’re like, what? Like, I, if I ever catch one of these people in person, I’m going to smack them. Like, it’s just the dumbest, most regurgitated advice that I’ve ever heard in my life. And I don’t know, I don’t understand how it gets so much engagement. Like, I feel like LinkedIn has an algorithm that looks for, its own, like, like, like, its own, like, like, AI nonsense and boosts it. Like, anything that has, like, like, crappy emojis to as bullet points. It’s like, the world needs to see this. So check out this AI advancement. You’re like, oh, God. This is why I start drinking early. Anyway, before we talk about that, let’s, let’s do the housekeeping stuff.
If you feel monetarily motivated to support my channel, you can sign up for a membership. There were, there were some comments in the comments recently about how someone couldn’t see a way to sign up for memberships. And it really, so if you’re watching it, unlike, you’re watching it, like, using the YouTube app on a device, you won’t see it. Apparently, you have to be using a web browser. So what I’ve done to circumvent all these issues is I scourge the depths of Google, and I found the URL that you need to use, which is apparently your channel URL slash join. As a database person, I feel particularly stupid that I did not try to join. But that, that link will go in all of the videos going forward. And I also am going to backport that to older videos so that everyone can use the join sign up link and be happy.
If you, if you, if you find yourself in a situation where, oh, you don’t have an extra four or so dollars a month in your pocket that you feel like sharing with little old me, you can, you can support my channel in other ways. You can like posts and you can, videos, video posts, and you can comment on them. And you can also subscribe to the channel and join now 4,300 data darlings out there in the world who also subscribe to my channel and get pleasant little tinkly notifications every time I publish something.
If you are in need of SQL Server help, if you need a health check, performance analysis, hands-on tuning, if you are having a SQL Server emergency, or you want me to train your developers so that you have fewer SQL Server emergencies, as always, my rates are reasonable. If you want just some training, you can get a whole bunch of it for about 150 bucks US. If you go to that URL or click on the link in the video description and you use the discount code SPRINGCLEANING, you can get all that for 75% off, which is pretty, pretty good.
Once again, this image is generated by ChatGPT. I did not make spelling mistakes. I think it’s funny how bad ChatGPT is at things. And I will talk some about something that I caught ChatGPT being wrong about this morning when we’re talking through the demos. But if you want to see me live and in person, and you are the type of person, you can see the dates and places there.
And you are the type of person who needs bribes to show up to things. I have Darling Data pins, which are a little hard to see because of the glare and the bright lights. And I also have these hot SQL action pins, which are very hard to see because of the glare and the bright lights.
But I can assure you they are absolutely magnificent. I also have sparkly Darling Data stickers. Ooh la la. Look at that. Look at the sparkles.
So if that didn’t hypnotize you into coming to see me, I don’t know what will. So I have pointy bribes and I have sticky bribes. And I hope at least one of them works on you.
Anyway, now with my brand new redesigned ready to party slide, let’s do this. Got sick of all the white space and decided to spruce things up a little bit. So I want to make sure everyone out there knows that says let’s party.
These are not fencing videos, so it does not say let’s parry. And these are not baking videos, so it does not say let’s pastry. We are going to party.
We are not going to parry or pastry or any mix of the two. All right. Anyway, at some point in your long, glorious database career, you probably will have seen a query that looks something like this.
Where you go and look at sys.dmexec query stats. And you look at sys.dmexec plan attributes to get the database ID out. And you group the CPU by the database ID.
And then you do some fancy math to figure out what percent of the CPU on the server is occupied by query plans that do this stuff. Right?
Or that did stuff in a database. This can be very weird for a lot of reasons. And we’re going to talk about all of them because it needs to be talked about lest you think that this is a good idea. I guess if there’s nothing else for you to do, you’re that bored in your life.
I guess if you’re like the average no-lock enjoyer, this might be an okay query for you to run to get a crappy idea of which database uses the most CPU. This can be wrong for a number of reasons. If anything has disturbed the plan cache, either in total or in part, you know, restarts, memory pressure, changing settings, queries with recompile hints, someone running DBCC free proc cache, updating statistics and having plans get invalidated, and all sorts of other things, they can really hide stuff.
So this really isn’t a great query. Actually, another thing that can make work look weird is if queries originate in a different database than the database they’re querying that can also cause some issues. So this is, you know, not a very good query practically for most people to use.
If you’re on a server and you really have no idea which database uses the most CPU, that’s a little bit silly. On my server, rather on my laptop, it’s a rather stable place. You know, I don’t, at least at the moment because I’ve been preparing for this, I don’t have a lot to do on here that would mess things up.
But we could add a few things to this query to make it a little bit more useful. I still probably wouldn’t use this as like the ultimate arbiter of truth about which database uses the most CPU, but you could at least spruce things up a little bit so that there’s some better information in there.
Stuff like getting the oldest plan and the newest plan and the total query execution count for each database can get you somewhat better information about what’s going on, like on the server as a whole. Or at least give you some contextual information to help you, you know, figure out if this is actually a good piece, a bit of information for you.
Excuse me. Don’t know where that came from. I may have swallowed a fly. So for this, we have the total CPU.
We have the total query execution count. I don’t know that this is necessarily the order I would present this in if I were trying to, you know, make this a pretty report, but it’s pretty okay, at least for this video.
And, you know, it might help a little bit to figure out, you know, it’s like, well, do I have a few really hard-hitting queries? Do I have a lot of queries that just kind of do stuff?
Maybe help you figure that out. But then this plan cache age column up here, that’ll tell you the difference in, well, days, hours, minutes, seconds, and milliseconds between the oldest plan and the newest plan in your cache.
If that number is very big, you might have a little bit more confidence than if that number is very small. But there’s one thing that can always mess this up, and that is recompile hints or stuff getting removed from the plan cache. So if I run this, this should remove one of the plans from the cache for the Stack Overflow Clean database, at least if I did my math right.
And now Stack Overflow Clean, which used to be second up here, is now way down here with almost nothing on it. So a plan getting removed from the plan cache can really pull that out. Where that’s also true is if you use recompile hints.
So if I run this query with a recompile hint, right, and this thing will take about four seconds, but it uses a pretty good chunk of CPU. That was the big chunk of CPU that was in the results before that’s not there now.
And then we look here. Oops, you know what? I forgot. Okay, actually, this is illustrative. So this is all the crap.
I actually meant to show you this just later. But this is all the crap that you get in the plan cache that this query will count as being responsible for the databases using CPU. So there’s just like a whole bunch of like background system stuff that can happen in there.
But focusing in on the query that we care about, which is the one that has that funny looking GUID in the text, if we run that query with a recompile hint, it will not be in the plan cache. And we will not know that anything ever happened with it, right?
So if we come up here and we look, and please don’t fail me, please don’t fail me now, and we look at this, Stack Overflow Clean, even though we just ran a query that took four minutes of wall clock time and about 23 seconds of CPU time, that doesn’t show up in here. So recompile hints alone can make this query pretty useless.
If we come down here and we run this without the recompile hint, we just run this thing. And that’s another four seconds. And if we look at the query plan, I’ll show you what I mean about the, oops, go away, tooltip.
We don’t need you. We need the properties. And we look at the query time stats. I said query time stats. There is about 22 seconds of CPU right there, which with a recompile hint is totally unaccounted for.
If we come and look at this query now, where we’re searching for that funny looking GUID thing in there, now it’ll show up and we’ll see the execution count and we’ll see the worker time. But without the recompile hint on it, that’s fine.
But with the recompile hint, that all disappears. So coming back up here and looking, we should see our friend up in here. Now Stack Overflow Clean has an additional 22 seconds of CPU time associated with it.
But again, if we remove that query from the plan cache, it’ll all disappear. So this is not a great query for actually figuring something out. Again, if you have absolutely nothing better and you just, and you want like the worst, again, like the average NOLOCK enjoyer style of data, return data.
If you really just don’t care that much and you just want like a bad idea of which database uses the most CPU without anything else contextually around it, you can use that query. Then you can probably get a bunch of wrong information back, just like the average NOLOCK enjoyer.
Now, I promised you a funny story about ChatTPT, so here it is. While I was working up to this, one of the things that, you know, because I love picking on optimized for ad hoc workloads, one of the things that I was looking into was if the compiled plan stub queries would show up in DMExec query stats, which, you know, they did.
But since it was something that I had to like sort of answer for myself because I had never specifically looked at it, I decided to say, hey, ChatGPT, do queries, if I turn on, in SQL Server, if I turn on optimized for ad hoc workloads, do the queries that get a plan stub, will their CPU, like, will their resource usage show up in sys.dmexec query stats?
And ChatGPT was like, no, won’t do it. They won’t be in there until the second execution when they get a full-blown plan, and then they’ll be in DMExec query stats, and then you’ll be able to see them.
And I’m sitting here staring at SQL Server, completely contradicting that. And I was amused by it, and I was like, hey, ChatGPT, can you write me a query that would prove that? So it prints out this query that looks at, you know, sys.dmexec query stats and cache plans, and, you know, sort of like up above, it gets the database ID out, and then in cache plans, it matches the plan handle there to sys.dmexec query stats and the database ID for both of those.
And it was like, yeah, here you go. And so I ran it, and of course I had a bunch of hits between cache plans and query stats. And so I came back, it was like, hey, ChatGPT, your query’s returning rows.
I think they do show up in there. And ChatGPT was like, you’re right, I was totally wrong about that. Cache plans, compile plans, steps do show up in there.
Here’s a query that’ll prove it, and I reprinted the query that it just showed me when I asked it to prove that they wouldn’t show up in there. So for God’s sake, stop caring about AI.
It’s always wrong. It’s always wrong about these things. You should think of any AI as being sort of like, pretend you have an administrative assistant, and you might ask an administrative assistant to write up a short summary of something for you, or take some notes, but then you would also validate it.
If you asked your administrative assistant to say, hey, come up with a report that shows me these things, you would probably want to make sure that you wouldn’t grab that report and then run to a board meeting with it. You would probably check the numbers first.
So if you’re out there in the world really sweating AI and thinking that it’s going to change your life and it’s going to take your job, it’s not quite there yet. You don’t have a lot to worry about.
It’s actually a rather sad state of affairs. So anyway, that’s my amusing slash depressing ChatGPT conversation from this morning. And of course, ChatGPT is what backs Copilot.
You know, there’s other ones out there like Claude and stuff. And Claude is a little bit better, but Claude got this one wrong too. So, you know, AI.
What can you do except laugh at people who think it’s good for anything? All right. Well, that’s about enough here.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. For the record, I will not be publishing any of the queries from this because I still find them equally useless. I should actually, before I wrap up, I should give you a couple ways that are better at doing this.
If you’re on Enterprise Edition of SQL Server, you can use Resource Governor, assuming that different databases have different logins associated with them. And that would be one way to sort of get a rough idea of which workload groups for which database are using the most CPU because it does track that. The other thing that you could do is if you are on a reasonable version of SQL Server, and by reasonable, I mean it’s at least 2016, you could also use Query Store.
And Query Store is a bit more historical than the plan cache. And you could go to each database and, you know, SP, whatever, in each, for each, around each, some preposition each database. And you could hit the Query Store DMVs to figure out which ones have the most kind of CPU usage in there.
That would give you a much closer idea to reality because, you know, Query Store usually generally has, like, I think by default, like 30 days of data in it, which is probably a much better indication of which databases are using a lot of CPU. So the plan cache query these days, real stupid, old and busted. You know, I think, you know, resource governor and Query Store are to much better options.
So with that finally out of the way. Once again, I hope you enjoyed yourselves. I hope you learned something. I hope that you will join the 4,300 other data darlings who subscribe to this channel and get notified when I drop videos because that’s – it’s probably a pretty good group.
It’s probably better than the group of people who generate AI content for LinkedIn and all that. People who think that they can use that plan cache query to figure out which databases have the most CPU work on the server because ship of fools, isn’t it? Ship of fools.
Anyway, thank you for watching. I have other videos to record, as you can see by the numerous tabs up top. So I’m going to close this depressing one and we’re going to do something else. I don’t know what’s next.
I haven’t decided yet. You’ll find out when we get there, I guess. All right. Thank you for watching. My rates are reasonable. 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.
Kendra and I both taught solo precons, and got to talking about how much easier it is to manage large crowds when you have a little helper with you, and decided to submit two precons this year that we’d co-present.
Amazingly, they both got accepted. Cheers and applause. So this year, we’ll be double-teaming Monday and Tuesday with a couple pretty cool precons.
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
Here are the details!
Day One: A Practical Guide to Performance Tuning Internals
Whether you’re aiming to be the next great query tuning wizard or you simply need to tackle tough business problems at work, you need to understand what makes a workload run fast– and especially what makes it run slowly.
Erik Darling and Kendra Little will show you the practical way forward, and will introduce you to the internal subsystems of SQL Server with a practical guide to their capabilities, weaknesses, and most importantly what you need to know to troubleshoot them as a developer or DBA.
They’ll teach you how to use your understanding of the database engine, the storage engine, and the query optimizer to analyze problems and identify what is a nothingburger best practice and what changes will pay off with measurable improvements.
With a blend of bad jokes, expertise, and proven strategies, Erik and Kendra will set you up with practical skills and a clear understanding of how to apply these lessons to see immediate improvements in your own environments.
Day Two: Query Quest: Conquer SQL Server Performance Monsters
Picture this: a day crammed with fun, fascinating demonstrations for SQL Server and Azure SQL.
This isn’t your typical training day; this session follows the mantra of “learning by doing,” with a good dose of the unexpected. Think of this as a SQL Server video game, where Erik Darling and Kendra Little guide you through levels of weird query monsters and performance tuning obstacles.
By the time we reach the final boss, you’ll have developed an appetite for exploring the unknown and leveled up your confidence to tackle even the most daunting of database dilemmas.
It’s SQL Server, but not as you know it—more fun, more fascinating, and more scalable than you thought possible.
Going Further
We’re both really excited to deliver these, and have BIG PLANS to have these sessions build on each other so folks who attend both days have a real sense of continuity.
Of course, you’re welcome to pick and choose, but who’d wanna miss out on either of these with accolades like this?
pretty, pretty, pretty, pretty good
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
See you there!
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. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
