In this video, I delve into the intricate world of SQL Server performance optimization, focusing specifically on how better indexes can enhance your buffer pool utilization. The buffer pool is a critical component in SQL Server’s memory management, serving as the cache for data pages from tables and indexes. It’s essential to understand that while many sysadmins or VM admins might allocate memory to SQL Server without deep knowledge of its needs, proper index management can significantly improve overall performance by reducing unnecessary maintenance and ensuring efficient use of the buffer pool. I also touch on how unused indexes can lead to increased blocking and wasted resources, emphasizing the importance of regular index consolidation and cleanup as part of any database tuning process.
Full Transcript
I’m working on my performance art. That’s me cutting myself off from the world. I’m going nowhere as an artist. So this video continues along the Steam tradition of SQL Server Performance Tasting Menu videos in which I show you some crazy kooky little thing about the way SQL Server works without much of a narrative or without much of a story to back it. And in this video, which, I don’t know, may exist somewhere, may exist in other places, I don’t know. Who can tell anymore? It’s a crazy world. There’s probably a deep fake of this video somewhere. In this video, I talk about how better indexes can help you make better use of your SQL Server’s buffer pool, which is always a good thing to do because, you know, a lot of people, when they give memory to SQL Server, they’re idiots. They might be, I don’t know, they might just be sysadmins or VM admins or just come from a non-SQL Server background. They’re just not well tuned into how how SQL Server uses memory, how necessary memory is for SQL Server performance, and things like that. So the three things, the three places where SQL Server uses memory most that people will care about are the buffer pool. That is where you cache pages, data pages from your tables, your indexes, so that SQL Server can access them for different activities. Doesn’t matter if it’s a read or a write, any page that you need to deal with needs to end up in memory.
Beyond that, SQL Server also, as we have learned in the past couple few videos, needs memory to give queries for query memory grants. And those query memory grants can grow quite sizable. In the last video, we saw a 9.7 gig memory grant for just selecting the top thousand rows. That’s a pretty good chunk of memory, almost no matter what kind of configuration your server has. The third thing is the plan cache and a good, stable, reliable plan cache can really, really be useful. I wouldn’t, I won’t say for SQL Server performance, even though it can be, but a good, stable, reliable plan cache can be really useful for SQL Server performance investigations, where you can go and dig in and look at resource intensive queries and other things like that. So with that out of the way, let’s look at how we can make better use of our buffer pool. At some point, flush them back to disk transaction log stuff, boring, has nothing to do with query performance. So I don’t, I put that out of my head. I’m kidding. It’s all very interesting.
So having a lot of unused indexes can also be a problem because not only do you have more things to lock when you modify the table, you know, more blocking, things like that. But you also need, you also end up sort of polluting the buffer pool with those additional indexes that will never help a read query, but need to get maintained when we modify the base table. So, so please, whenever you’re tuning indexes, don’t forget to make index consolidation and cleaning out unused indexes part of that process.
Just adding indexes will often end up causing problems over time. It’s something that I see constantly when working with clients. And it’s something that I like almost always have to hand over a big long SQL file of changes to indexes.
Like you drop out all these, I’m going to merge these in together. We’re going to make one index instead of having five different indexes that are almost the same definition because stuff like that hurts RPO and RTO too, right? And with the longer, the more indexes you have, the bigger your databases and more stuff you have to back up.
When you need to restore a database, there’s more data size to restore. And it’s just, you know, it’s all, it’s all ends up not being good for many different, many different metrics that you can measure with your SQL Server. So thank you for watching.
And we’ll see you over in the next video where I will talk about something else that I haven’t quite decided on yet.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into the intricacies of memory grants in SQL Server, particularly focusing on how they scale based on the complexity and nature of your queries. I demonstrate through a series of examples how sorting operations can significantly impact memory allocation, showing that the size of a memory grant isn’t always directly proportional to the number of sorts or columns involved. By running various queries and analyzing their execution plans, I illustrate how SQL Server allocates memory for different parts of a query plan, emphasizing the importance of understanding these mechanisms to optimize performance effectively.
Full Transcript
Well, I’ve been alerted to the fact that it is nearly 6 o’clock Eastern and that my wine was getting lonely. So it is that time of the day, my people, where I start to put wine in my face. And video quality remains at a consistent low. Won’t be able to tell the difference, I promise. It’ll be seamless. Except for the gentleman yelling out in front of my apartment about I don’t know what.
Anyway, we’ve talked a couple times about memory grants in the context of sorts and like how big they get. for different things. And now I want to record this video to go a little bit more in depth on that so that you can get a slightly better idea of just how big a memory grant can get based on the rows and columns that you are selecting. So, we are armed, we are locked and loaded with query plans turned on. And I realize that this query is written in sort of a funny way.
But it will make more sense when I need to do more things to help you understand how memory is apportioned or something in a query plan. So, let’s run this. We got query plans on. I’m going to run this query and I’m going to get the top thousand rows from the users table ordered by reputation. And since I do not have an index, I do not have my very helpful index on the users table that puts reputation in order, I need to sort my data.
There it is, that sort operator. And because I need to sort data, I need to ask for memory to do that. And I asked for just about 166 megs of memory to get that. You can see that memory grant right there, 166528. Cool. Now, keep in mind that 166 megs of memory is only for a thousand rows of just an integer column. Okay? Cool.
So, 166 megs for that. Let’s look at a situation where I need to sort two sets of data. Now, you might think that because I’m going to have two sorts in this query plan, that I’m going to ask for 166 times 2. But I don’t. I ask for 167.872. Now, you might ask yourself why that is. Well, good question.
The way that this query is optimized, I get a hash join. We talked about this in the sortables video, where hash joins and sorts are blocking operators, not to other queries, but internally, all the rows have to show up in order for them to start running. Because rows stop at that hash join while the hash table is built, those two sort operators don’t execute concurrently.
So, excuse me, we’re able to take memory that gets sent to the sort operator. We can go into the ever useful properties and look at memory fractions, and we can see… So, one is basically 100 for our conversation.
We can see that it absorbed 100% of the memory grant, and it released nearly all of it. The hash match is going to use a little bit of the memory grant as well, but then the other sort is going to take this number, and it’s also going to release that number. So, over here we have this other sort sharing the memory grant.
Where this doesn’t work is when we have non-blocking operators, or stop-and-go operators, as they’re sometimes called in the query plan. So, I’m going to change the way this is written. I’m going to force a loop join here. And when I run this query, the speed makes… the speed is not the thing here.
What I want to show you is that now we ask for just about double. So, what is about 166 times 2? We ask for 333 megs of memory. And this sort and this sort both got independent memory grants.
So, if we look at the properties here, the memory fractions output, this one got half, and this one also got half. So, what does that mean? It means we got 333 megs, about 166 one here, and about 166 one here.
Cool. Great. Wonderful. Fantastic. Everyone’s in a great mood knowing that. Now, we understand that memory grants will scale with whether operators can share memory or not. So, if we can’t share memory, then we can reuse the parts of a memory grant.
If we can’t, then we need to ask for more memory so that we can hand it all out evenly. The size of the memory grant will change depending on, again, not only the length of your data set, but also the width of your data set. And now, if I run this query, this will go up to 9-ish gigs of memory grant.
If I select fewer columns, then the memory grant will go down. But this about me column is in barcar max, and that’s really where the big memory grant comes in. Now, the way that SQL Server estimates memory for string columns is it estimates that they will be half full.
This is to give it some fudge factor because some of them might be quite full, some of them might not be very full, and some will probably be somewhere in the middle maybe, but SQL Server just guesses 50% will be full, and it comes up with a memory grant based on that.
If we look at the execution plan for this, we can see that it indeed asked for 9.7 gigs of memory to sort 1,000 rows with these columns. Now, remember, it was about 166 megs for just the ID column. If we selected ID and display name, it would be 300.
If we added website URL, it would be 900 megs, location 1.2 gigs, and about me goes right up to 9 gigs. So the next time someone tells you that it doesn’t matter if they choose oversized columns for strings to avoid truncation errors, you can now punch them until they stop moving, which is usually a pretty sound approach to that comment.
Unless you just want to teach them this, that’s also okay too. Depends, right? On what, I don’t know.
So, that sucks. And I would not want to see you running into this because, I mean, I have 64-ish gigs of memory in my laptop. I have 50-ish assigned to the SQL Server instance.
So, asking for a 9.7 gig memory grant takes a lot of memory away from the buffer pool. If you needed to run multiple copies of this query, you might end up having a hard time doing that because there’s only so much memory that SQL Server is willing to give out as query memory grant memory at once.
So that’s probably not awesome. Alright? It’s not great.
And if we, I mean, 9.7 gigs is as high as I can get a memory grant for here. There’s a cap on that as well. So, like, if I selected more rows, it wouldn’t go up. But if I had more memory, then it certainly could.
And if I had, if I selected fewer rows, then it could probably get it to go down a little bit. But where’s the fun in that? We’re only selecting a thousand rows and we got a 10 gig memory grant. Now, this goes back to, I can’t remember if I, if I, if I mentioned this earlier.
This goes back to what needs to get sorted. So, it’s not just reputation that we need to sort. Even though that’s the only column we’re saying order by, that is not the only column that we are ordering.
We need to sort ID, display name, website URL, location, and about me by reputation. So, all of those columns need to get written down in memory too. So, think of it like an Excel spreadsheet.
I mean, I know it’s not. But just mentally, this is a good way for people to understand what’s going on. Think of it like an Excel spreadsheet, where when you push that little button in the top left-hand corner, and it highlights everything, and then you choose a column to sort by, and the entire spreadsheet flips to how that column is sorted.
That’s very much like what happens here, where we need to flip all those columns into the order that we’re asking reputation to be in. No bueno. Now, you’re starting to, you’re starting to think about that, that informational versus relational column stuff.
And you’re starting to think, boy, that, that Erik Darling might be right about a thing or two. I can sense it. I can feel it. I can feel it. Another thing that I want to clear up is that memory grants are not the grant times degree of parallelism.
I’m going to run this query at max.8, and we’re going to see that the memory grant goes up a little bit beyond 166. It’s going to go up to about 180 megs. But it’s not going to go up to 166 megs times eight.
In a parallel plan, each thread is considered its own execution context, meaning that each thread, or rather, the memory grant that SQL Server comes up with will be split evenly amongst each thread. There is a little bit of memory overhead for parallelism, the CX packet exchange buffery doodads.
There’s a little bit of exchange overhead in this plan for the hash join. But there is not 166 megs times eight. There’s not like a 1.2 gig memory grant for this query because it’s parallel.
Well, the memory grant gets split up amongst threads. So there is that saving grace, sort of. Anyway, I believe that’s it.
Yes. Cool. All right. So sorts. Be careful with them. Be careful with which columns you’re asking to be ordered by. If possible, split your queries up into the informational versus relational column thing that I showed you before.
There’s probably some other stuff, but, you know, I think you’re smart and that you’ve probably, you probably can just hit rewind if you’re really interested. Right?
Yeah. I think, I think you’re cool. I think you’re good. But remember also that you can fix a lot of sorts by adding indexes and we’ll probably cover that in a different video coming up later. A little bit later.
Not a lot later. A little bit later. All right. See you later.
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 explore the often-maligned table variables in SQL Server and delve into their pros and cons. I start by comparing table variables to temporary tables through a series of stored procedures that insert data into both types of objects and measure execution times. The results show that for high-frequency, low-row operations, table variables can outperform temp tables due to reduced overhead. However, when dealing with indexes and needing column-level statistics for better query optimization, temp tables shine, as demonstrated by a scenario where the temp table’s plan was significantly more accurate and efficient than the table variable’s. I conclude by highlighting that while table variables have their uses, especially in scenarios requiring frequent execution on small datasets, they should be used judiciously considering the potential performance implications of incorrect query plans.
Full Transcript
I was going to write a song about table variables, and then I realized that I have no songwriting abilities whatsoever. That rhyming with variables is unpleasant. Not something that I would wish on anyone in this difficult time. So, I need to turn off query plans according to this. I don’t know what drunk wrote this thing, but whatever. So, table variables have, I think, a deservedly bad reputation for the most part. You can get terrible estimates from them, and even with recompile hints or trace flags, you still don’t get column level statistically information about the distribution of values in your table variables. You’ll get table cardinal. You’ll know how many rows are in the table, but you won’t know the breakdown of the values in those rows.
And also, when you modify a table variable, modifications are all forced to run serially, unless you’re sneaky about it. I have a couple blog posts about those things here. Hopefully, those bit.ly links will be easy enough for you to remember. They are case sensitive, though, so if you’re going to get your little fingers to work, remember to capitalize the correct letters on those. I’ll leave those there and count to a number beyond your imagination as quickly as I can, so that you can memorize them for later. So, first, I want to show you where table variables can be better than temp tables. Like, they have a bad reputation, but sometimes they can be good, too. So, I’m going to create two stored procedures here, and I promise I’m not farting that’s a motorcycle or something.
But I have these two stored procedures here. This first one is called temp table test, where we’re going to insert some stuff into a temp table, the pound sign, and then I have the exact same thing, except we’re going to use a table variable, the at sign table. So, I’m going to go and I’m going to create these two stored procedures. I’m pretty sure I already have them created, but what the hell. Let’s make double short. And then what I’m going to do, and this is why I have query plans turned off, if we’re being honest about things, I’m going to run a test where, for 50,000 iterations, I’m going to, as quickly as possible, execute the temp table stored procedure, and then execute the table variable stored procedure.
I’m going to see which one finishes more quickly. And while I do that, oh, I went up too far. Ha ha! My mouse wheel has been a little wonky lately, but I have both of those. Okay, good. And, well, I run that. I want to bring up that with SQL Server 2019, you do get better table cardinality estimates for table variables, but you still don’t get column level statistical information from table variables. And I’m pretty sure I have a post about that coming up. But this is SQL Server 2017. Not RTM. It’s just, SSMS doesn’t report CU levels for some reason. Microsoft made this big stink of, oh, we’re not a servicing model. We’re not going to do more service packs.
But SSMS is still like RTM, despite the fact that I am on CU 19, SQL Server 2017. Not weird or confusing at all. But we can see, now with these results returned, that the store procedure in the loop that inserted into the temp table, rather, took 17 and a half seconds. And the store procedure that did the same thing with the table variable took five seconds. Now, there is overhead with temp tables. And that overhead is unfortunately where the performance comes from. I know generating the statistics, all that other good stuff, that has overhead when you do it. And when you do it a lot, well, you have to do it a lot. That adds up.
So, if you are, if, and this is a thing that I learned from Michael, Mr. Michael J. Swart, that if you have very, very high frequency execution code, table variables can be a lot better to use than temp tables. Now, this also assumes that plan shape does not matter, that there are no performance issues that could come from, you know, say, joining that temporary object off somewhere else, putting a lot of rows in it and going to do something. You know, it’s just, there’s a lot of circumstances where, you know, like the overhead, the overhead might be worth it.
But, but if you just have very high frequency execution code operating on a small number of rows, and there is no, like, a query optimization impact to the bad guesses that come from table variables, then it might not be the worst thing in the world to use them in those scenarios. I mean, they are there for a reason. Microsoft didn’t devote developer time to them for no reason. I mean, maybe, maybe, maybe, maybe, maybe it could have been like a, a code portability thing between other competitive database platforms and SQL Server, but who knows at the time?
I have heard nothing about the Oracle checklist from people at Microsoft, so whatever. Now, let’s look, let’s look at when table variables are less good. All right, when they’re not quite as hot.
So the first thing I’m going to do, actually I should put a little, put a little space in there, happy little space. No mistakes, just happy little spaces. So I’m going to create a couple indexes on the post table and the comments table on the owner user ID and ID.
And the first thing I’m going to do, the first thing I want to show you rather, is, well, after these indexes create, the first thing I’m going to show you, is that if we create a temp table, when we insert values into that temp table. Now, this is where the column level statistics can matter a lot for temp tables and table variables.
The first thing I’m going to, the first value I’m going to put in there is John Skeet’s ID. John Skeet is legendary. John Skeet has many questions, well, not many questions, but he has many answers, many comments.
He is quite prolific in the Stack Overflow data. And the other set of values in there that I’m going to put in is people who barely use the site, people with a reputation of one, ordered by how recently they created their account.
So people who are not avid site users, if they have any answers, I’d be surprised. They might have like one question. Some people sign up, ask a question and leave.
Some people sign up, then do nothing. They’re just like, oh, I have an account. I was going to answer the data to do something else. I was wrong. You know, there’s like lots of stuff. So the first thing I’m going to do is now that those indexes are done is I’m going to stick those values into a temp table, pound sign T temp table, and then I’m going to get a count from those.
All right, so I’m going to do that. I’m going to turn execution plans back on. And I’m going to prove to you that this is a reasonably fast executing piece of code. The insert takes 232 milliseconds.
There are perhaps things we could do to make that better. I hadn’t really thought about it too much. And then the join down here is a reasonably fast join at 0.016 milliseconds. Now, if we were to repeat this experiment with a table variable, it would take a long time.
All right, so if I do the same thing here, I’m going to run that. But I’m not going to wait to show you the results. I’m going to show you a saved copy of the plan.
Not because this is slow. This part is fine. The insert is fine. What’s not fine is the query that we run next, which runs for, your eyes are not lying to you, five minutes and four seconds. And we have our table variable guess here.
We have our index seat guess here, which is not so good. We guess 24 O’s and we get 2700, 901. And then we go into this nested loops join, expecting 24 O’s, getting 27901.
And then we spend four and a half minutes in a nested loop join, going and getting values out of the comments table. When we are expecting 638 and we got 1304009037. I’m not going to bother trying to figure out what that number is.
It is a big number. And this takes a very long time. This was not a happy time for this query running. And the reason that we got such a better guess from the temp table was because we got column level statistics about what values were in there.
We were able to use those statistics to come up with a better joined execution plan. And we were able to use those to not end up running in nested loops hell for five minutes. Crazy that, isn’t it?
Crazy. Absolutely insane. Crazy. So, if you’re going to use table variables, high frequency execution is one of the primary things that I would look for in a workload that might benefit from table variables. But also you need to look at how you’re going to use table variables afterwards.
If I took those two store procedures that I showed you earlier and I said, hey, we’re going to see what happens when we run this in a loop. Well, this one getting to 50,000 executions at five minutes per execution, that’s not happening. I’m not sitting here for that long.
I don’t love you that much. There are limits to my love and patience. The temp table one would finish rather quickly, even for 50,000 executions. So there are things to consider with how we’re using the table variable down the line.
And if column level statistical information might help those activities further on down the line. So, there we have it. For the most part, if we care about performance and statistical information, then we would probably want to err on the side of temp tables.
If we care about speed of execution, the frequency of execution, then we might want to use table variables. This changes a little bit, like I said, in SQL Server 2019. But I have another post about that where I address that specifically.
Anyway, that is all I have to say here. I’m going to stop talking. I’m going to hit the stop button. I’m going to eat some cashews and probably record another video once I get done feeling gross about cashew things.
Quarantine is not fun. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into the nuances of missing index requests in SQL Server, particularly focusing on instances where indexes aren’t truly missing but are still causing confusion. I share a real-world scenario from my consultancy work, highlighting how a seemingly problematic query with a missing index request might not actually need an index at all due to its infrequent execution and low impact on overall performance. The video explores the importance of domain knowledge in making informed decisions about indexing, emphasizing that while SQL Server’s suggestions can be valuable learning tools, they should be carefully evaluated before implementation.
Full Transcript
This video we’re going to talk about missing index requests, or rather not missing index requests. Indexes that weren’t really missing. But we got complaints about them, didn’t we? Now I can sympathize with the situation a lot because I am a consultant and I see day-to-day servers for the first time a lot of times and sometimes the only time a lot of times. And I can also sympathize with people who other people, you know, new DBAs are a job, right? You get a new job, you look at a database, you’re like, what is going on with these indexes? Or if you’re just kind of new to index tuning and you’re not really sure what to do or where to start. SQL Server’s missing index request can be super helpful, at least as a learning tool to start figuring out exactly what kind of problems indexes solve, stuff they can help with, stuff they can help with, stuff they can help with, stuff they can help with, stuff they can help with, and stuff they can help with. And so, you know, you can see a lot of things that you can help with indexes. You can’t really help with, you know, like, like, you know, you might create a great index, but your predicates are all not sarkable. So you scan everywhere anyway, and you’re like, oh, what happened? But anyway, so this is close to a situation that I ran into with a client where there was some weird code on the server. And the weird code looked sort of like this, where a long time ago, some some rogue user, can you believe that a user did something crappy and unexplained?
expected. I can’t. I can’t. I can’t imagine why a user would do that. Telling lies on the internet. Who would do that? Who would do such a thing? So, there was some code in here to fix when, to fix a parameter to make sure that we could not, we’re not passing a negative value in over here. And for the most part, that was fine because we had, we had this index on owner user ID, and that’s one of the predicates here. And that’s, and that was good enough to get, to seek to a normal, like a, like a smallish range of variable, range of values here. Friday is catching up with me. Rather, Thursday night is catching up with me. So, that index was serviceable enough to get us here. We could do some additional predicate evaluation, the clustered index here, but things were generally fast.
So, like, when we go and we run this query, and we look at the query plan, this finishes quickly, right? 64, 0.064 milliseconds. Fast query. And this has a missing index request on it, too. SQL Server’s like, oh, we could make this faster. And I’m like, I don’t really think you know what you’re doing anymore. But, yeah, sure. If we had an index on those three key columns, we could technically make this faster. We wouldn’t have to evaluate predicates in our key lookup here. Fine. Technically, you are right, SQL Server. Technically, you’re right.
Technically. But I don’t really see, if I, if I see this query running in 0.64 milliseconds, that’s probably not going to be the query that I’m like, oh, we got to fix that. It’s an emergency. That’s not the query that I want to focus query and index tuning for. This query might look a little bit more like something that I might want to fix. This thing runs for a couple seconds.
If you look over at the query plan, it’s fairly expensive, costing 3,473 query books. It’s like a, it’s like a Gucci tracksuit query cost, right? It’s astronomical. And this has a missing index request on it, too. It’s a missing, you know, pattern ID, creation date, last activity date, post type ID.
There’s all sorts of, all sorts of things in there that we can go, oh, if we had an index, this would be so much faster. So the thing is, this is one of those queries where, you know, if you are looking at a server for the first time, or if you’re looking at a server for the only time, you might see this missing index request and go, man, we could really reduce this expensive query down. But this is the sort of thing that does take some sort of domain knowledge to make really good long-term decisions about.
Now, realistically, if we’re, if we’re looking at a server for the first time, we’re analyzing the query plans, or looking at the missing index DMVs, or like digging into the data, we’re going to analyze some stuff. This might jump out to us as a great index to create. We might say, we’ve got to get this in there. But, you know, having the domain knowledge to know that, hey, this query only runs at night.
This isn’t a query that we care about all that much. It can, it can run for two seconds. This is going to populate a few things. It’s going to run a hundred times. It doesn’t really matter a lot. It’s going to happen at midnight when no one’s there anyway.
No one can hear you scream. No one can hear this query scream. So, but maybe, I don’t know, we look at things and we’re like, you know, I’m going to give this index a chance. I’m going to, I’m going to give this index a shot because, um, make up a reason.
Make up a reason in your head. Give me a good, leave a comment with a great reason for why we’re creating this index. But now, after we create that index, our original query that ran in 0.066 milliseconds no longer runs in 0.066 milliseconds. And it runs for more like 10 seconds to do the same thing.
And if we look over at the query plan, we can see our new fancy pants index being used, but not being used very well. Well, we have a very bad guess with this index. It’s guessing that two rows are going to come out of here when we really get about 6 million rows out of there, plus 223.
That’s a lot of rows. And we get this guess that we are only going to have to read 3.05086 rows from this index to get those two rows out. And we’re going to have to do this key lookup.
Well, we estimated the key lookup, estimated number of executions, 1.8 actual number of executions, 6,223. So we had an issue with this guess. And that issue with the guess comes from the fact that we declared a local variable in the query plan, right, or in the store procedure.
This local variable means that we are going to get what’s called a density vector guess. And I have a post that actually by the time this comes out, that other post will have been out for over a while. So you can look forward to that.
I don’t want to spoil too much, but it’s about this sort of thing. But we declared this variable in here. That means we’re getting a not good guess. So if we show the statistics for that new index that we created, we will get a couple numbers back.
And those numbers are going to be the number of rows in the table, this 17 or so million, and this all density. And if we multiply those together, which I’ll do in a second, but first I want to show you that if we were using the actual factual histogram, we would have had a much, much better guess for the number of rows that were going to come out of that.
All right, that’s where that’s six. We have that six million number here. This isn’t dead on accurate because we didn’t sample the entire table. Sorry, this isn’t dead on accurate for some other weird reason.
We did sample the entire table. I’m not sure why that isn’t perfect, perfect, but who knows. But if we multiply these two numbers together, right, this number for the number of rows in the table and the all density of the parent ID column, if we multiply these together, we’re going to get that 3.0508 number back, which is the number of rows that SQL Server thought it was going to have to read to get the two rows out in a match.
So things are a little weird, a little weird in there. So this isn’t all the fault of the index that we added. Partially the code is to blame because we’re getting a bad guess because of that.
But again, this is one of those crazy domain knowledge things where you have to understand that like this code existed, why it got there. And then maybe, maybe, maybe, maybe adding that index was not necessary because the query that wanted it only runs at night and the query that ended up using it, you ended up using it badly.
So, you know, normally, heck yeah, I just want to drop that index. Why bother with it? It’s not going to help anything and it’s going to hurt things. But it might also start a conversation about that piece of code.
Now, even with that index still on there, if we change this store procedure a little bit to recompile, right? So if we add a re, option recompile hint to the statement, then what SQL Server can do is something called the parameter embedding optimization.
It can sniff this local variable with an actual, we’ll replace that with the actual value that gets passed in. And if we do that, even with that other index there, and we run our query, or run our store procedure rather, we’ll go back to using the original query that finishes quickly.
We’ll still get a missing index request for it, but that’s, that’s neither here nor there. Now, sure, option recompile works fairly well, but another option other than recompile might be to use dynamic SQL.
Now, what dynamic SQL can do is it can take that local variable and we can parameterize it. Since this dynamic SQL happens in a different execution scope as the store procedure, it has no idea what the, what the lineage or etymology of that local variable is because it’s parameterized in the dynamic SQL batch, right?
So if we change our store procedure to use dynamic SQL and we execute that, it will also, despite the presence of that crummy index, use a fast execution plan right there, right? Cool.
So, look, it wasn’t all the fault of that poor consultant who told you to add that index. There was some other stuff at play. I don’t know, there was some other stuff we had to think about. But, you know, SQL Server 2019 offers a little bit of hope for this sort of thing.
There is a new missing index DMV that I blogged about that will, as long as the query that asked for the missing index is in the plan cache, as long as it’s there, you’ll be able to tie the query that asked for a missing index to the missing index request.
So that’s fun. That’s great. If you’re on SQL Server 2019 and you have a happy, healthy plan cache, otherwise, I don’t know, you may need some fancy pants expensive monitoring tool to catch this sort of thing.
But, you know, right now, you know, I just want people to be a little bit more cautious with the missing index request. Please at least try to correlate it to the query that’s asking for it before suggesting it or creating it.
If you can’t do that, then, you know, maybe put it as like a something to be on the lookout for long term or, you know, something that you can add in a lower environment, dev test QA, run some kind of, you know, example workload against.
It’s usually a helpful thing to do if you have that sort of time on your hands. But, you know, workload knowledge is pretty important when you’re querying index tuning because you can run into all sorts of weird regressions and things like this that can backfire.
Anyway, thanks for watching. I’m going to hopefully get this video recorded so it happens in both of your ears. If it doesn’t, it’s not my fault.
Camtasia is fickle with this sort of thing. I don’t really know how to fix that. But anyway, I’m going to go do another one of these. I don’t know, maybe I might eat something first, but who knows?
Anyway, goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into how indexes can help us avoid sorting data in SQL Server, which is crucial because sorts can be memory-intensive and slow down query performance. We explore the intricacies of sort operators, their impact on memory grants, and how they internally block other operations. I also discuss how equality predicates in indexes can preserve order without needing a sort operator, while inequality predicates often require one. Additionally, I provide practical examples to illustrate these concepts and highlight the importance of writing queries that leverage index design effectively to minimize sorting.
Full Transcript
And here is the 5,011th attempted recording of this video. I don’t know why it’s so much easier to do this stuff live. I do it live, blaze right through, everything’s good. I record stuff, I’m like, ugh, gotta fix that. My video editing skills suck, so that usually just means take two. In this video, we’re going to talk about sorts, but rather how indexes can help you avoid sorting data. And we’re going to do that because there are some things about sorts that we care about. Things that we care about with sorts is one, they will ask for memory, and the size of the memory grant that they will ask for is going to depend on how long your data is, how many rows you’re selecting, and how wide your data is, how many columns you’re selecting and their data types. The bigger your data is, the bigger a memory grant that sort is going to ask for. Now remember that SQL Server doesn’t work with pages on disk, it only works with pages in memory. Those pages that get stored in memory get stored in the buffer pool, and the more, and memory being a finite resource, the more queries you have asking for large memory grants, that memory has to come from somewhere. Usually that memory, if you don’t have a ton of extra memory on your system, will come from space that it was going to use for the buffer pool.
For a memory saving example, you have one set of parameters that, you know, returns a small number of rows, you don’t have to sort a lot of data, then another set of parameters where it returns a lot of rows, not even returns a lot of rows, just processes a lot of rows to get you your final result set. And that might need far more memory to run optimally. And that’s where you end up with the spilling and the slowness and everything sucks.
And then they’re also, they’re internally blocking. I don’t mean that they are blocking other queries, just inside of an execution plan, all the rows have to get to a sort, and SQL Server has to write them all down in order, and then they can pass things on to other rows downstream, or other operators downstream. And it’s, you know, you just have a bunch of operators sitting around waiting for that sort to finish and do its thing potentially. Unless it’s at like the, all the way to the left in your execution plan, and then I guess it doesn’t matter. So let’s look at how index design can help us avoid sorting data. So I’m going to, well, I can’t remember if I created this index or not. I already created it. Good for me. Sweet. This is so long ago. I forgot.
But what I want to show you first is, for this index, how the data is going to be stored in this index. And I’m going to do it with this query. Now, this, I’m using this query because it gives me a nice, easy set of data to illustrate this point with. So this, this index is on first, the first key column is reputation. The second key column is upvotes. The third key column is downvotes. The fourth key column is creation date descending. We have display name included, which is not going to help us sort data at all, because included columns are in no particular helpful order.
But we’ll get to that later. So if we look, if we think logically about how data is stored in this index, we have a whole bunch of duplicate values for reputation 124. Within that, within this duplicate range of values, we have values for upvotes stored in order next. So this is all 124. And then we have groups of ones, groups of twos, groups of, oops, groups of threes, and groups of four, five, six, seven, eight, down, on down to 10.
All right. So we have 124. Then data in upvotes is in order after that. And then within duplicate ranges or within a range of values in upvotes, we will have downvotes in order next. So for where upvotes equals one, we have downvotes one, one, two. And then within any duplicate range of values here, we will have creation date in descending order here, because we have it in descending order in the index. So for 124, we have upvote equals one, and then downvote equals one, and then creation date equals this.
So this is how the index is in order stored logically, not physically, logically. So if I run these two queries, the first one is just going to say select the top 1,000, those four columns in the index, ordered by upvotes. And the second one is that same query, but with an equality predicate on reputation.
If I run these two queries back to back, the first query will have a sort. We have to sort by upvotes to get data in the order we want here. But if we have an equality predicate on reputation, there’s no sort operator in this plan.
We have an index seek, we have a top operator that’s not a top sort, and then we just have a select. So in this query, with no equality predicate on reputation, ordering by upvotes, now we have to sort data. And this spills a little bit, not enough for me to get riled up about, but it does spill.
Which means that logically, we can select the top 1,000, those four columns in the index, with an equality predicate on reputation, upvotes, and downvotes. And we can order by creation date descending. And we still will not need a sort operator in the query plan to get the data in the order we want it.
Using equality predicates going across like that means that we can traverse the data just like I showed it to you with that first query. So we find equality values here, equality values here, equality values here. And then by the time we get over to the creation date column, that data is in the order that we want it.
That order is preserved. So nice, right? With rowstore indexes, you have that sort of column to column dependency. It’s not true with columnstore indexes, but that’s not what we’re going to get to here.
But like I mentioned, includes are useless. So even if we have an equality predicate on creation date and we say order by display name, well, we’re going to get this one row back, but we’re also going to have a sort operator in the query plan.
Now, this is a top end sort. This is not a fully sorted thing-a-bob. Anyway.
Equalities are also not so kind. So in the original query where I had reputation equals one, and in this query we’re going to say less than or equal to one or greater than or equal to, I believe that’s a million.
I’m too lazy to count all those zeros. But if we run these two queries and look at the execution plan, we will have a sort here, top end sort here. And we’ll have a sort here, another top end sort. Again, this one spills a little bit, not much. And this one doesn’t spill at all because there’s a very, very small number of rows involved in that one.
But so I guess if there’s a message here, it’s an inequality operators do not handle order preservation as well. There are some funny rules to that, though. Let’s take these queries, for example, where I can, we’re going to filter on, we’re going to, well, all of them are going to order on reputation.
All of them are going to order by that first column in the index. These are going to filter on columns that occur later in the index. And these are going to be inequality predicates that occur on the reputation column.
But if we run these queries and we look at the execution plans, none of these need to sort. None of these will have a sort operator in them, even if we scroll all the way down. All we see here is tops, not top end sorts.
So whenever you’re designing indexes, a lot of times a rule will be something like, Use equality predicates first, man. And then, you know, inequality predicates second, man.
And then, you know, include your select list, which is 45 columns because everyone was too lazy to normalize the table. Man. But I think that the indexing advice should be perhaps a little bit different.
So if we have queries where we need to optimize for sorting data, Sometimes I think that it is a wise idea to change the index creation advice to be on equality columns first, and then sorting elements after equality columns, and then inequality predicates, and then your 45 include columns.
I’m kidding. Don’t. Please don’t use 45 include columns. I will cry. I cry every time. I’m going to just stop doing that. Just get…
Stop. Stop. There are better ways. There are better ways in the world. Anyway. So that’s one way to think about indexes. It’s a little bit different from the way that a lot of people think about indexing for things today.
Now, there’s also times when different ways of writing queries can help you avoid sorting data. So what I have here is an index on the votes table. It’s on vote type ID, post ID, bounty amount descending, and creation date.
Now, this query has a couple predicates in it. One is on vote type ID being in 1.3, and one is on creation date being greater than or equal to 2010.
Now, the way that this query plan is going to look, which I’ll show you in a second, we’re going to see seeks for this, and we’re going to see a thing for that, like something. So we’ll get to it.
But this row number function, right, where we’re partitioning by post ID, and we’re ordering by bounty amount, that’s following the rules I just talked about, where, you know, we should have this thing, we should have this equality predicate on vote type ID here, and we’re going to partition by post type ID here, and we’re going to order by bounty amount descending here, and then we’re going to filter on creation date being greater than or equal to here.
But when I run this, something kind of not cool happens. I highlighted too much, and I hit an error message. But if I do what I was supposed to do, if I pay attention a little bit, and I run this query, it’s not going to be fast.
It’s not going to return any rows either, because I’m playing a mean trick on the optimizer, because I’m generating this row number, and I’m filtering where that row number equals zero, but row number doesn’t ever equal zero. But the optimizer is just like, yeah, we’ll go for it.
It’s kind of goofy. But if we look over at the query plan, well, what do we have here? Big old sort. Big old sort. Look at that.
We sorted lots of rows there. And that’s after an index seek. And that’s, well, we have this seek predicate on vote type ID, and we have this predicate on creation date, but still we had to sort data.
And that’s because in, even though it’s seeking, and even though it seeks twice there, and we have two seek predicates, it’s like an or, kind of.
And that is not so hot at preserving data. If we want to avoid the sort there, we need to change the way that we write the query a little bit. So if we rewrite the query to be one expression where we look for vote type ID equals one, and another one where we look for vote type ID equals three, and we run that exact same query, this will turn out a little bit better for us, in that we won’t have to sort any data.
It’ll run a bit faster, too. Well, it’s just about twice as fast. Right? And there’s no sort operator in here. So equality predicates can help you navigate indexes a little bit better.
Indexes can put data in a helpful order to help you avoid needing to sort things. But sometimes we need to write queries in slightly different ways, slightly less lazy ways, that aren’t always totally intuitive.
So there’s some unexpected stuff that does not produce data in the order that we want it, and that the optimizer is like, we’re going to have to sort a lot of that data instead. Yeah.
So that’s that. So now remember, we care about sorts. Similarly, we care about hashes as well, because hashes are similar operators, and that they will ask for memory grants, but typically not size of data memory grants, the way that sorts will.
But we care about memory-consuming operators because they ask for memory grants. Memory grants will often need to take memory from the buffer pool, unless you have lots and lots and lots of memory.
When we ask for lots of memory grants at once, we may run out of memory grant space. We may hit resource semaphore weights. If queries run and they do not get adequate memory, we may spill to disk, and large enough spills can certainly impact performance.
They can make code sensitive to parameter sniffing, where different parameters would produce, well, we need to process different numbers of rows, where we might need far more memory to do that in some circumstances.
And they’re internally blocking, meaning that all the rows have to show up at the sort operator before we can start writing things down. And if we need to sort lots of data, we could be there for a minute when we write all that stuff down in order.
So anyway, this is the five billionth run through of this video, and I am done with it. I am going to produce this one.
I am hopeful that Camtasia will mix it in a way that uses both stereo speakers. And I will see you over in the next video. 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.
In this video, I delve into the intricacies of Common Table Expressions (CTEs) in SQL Server, addressing why they’re often misunderstood and misused. I explain that CTEs are not just about making migrations easier or achieving code parity; their true value lies in their ability to help with complex queries by allowing you to break down a query into more manageable parts. However, I caution viewers against relying on CTEs for performance benefits, as they don’t materialize and can lead to suboptimal execution plans due to repeated re-execution of the underlying syntax. To illustrate this point, I walk through an example where using a CTE led to multiple index seeks instead of just one, highlighting the importance of understanding how CTEs work and when other methods like temp tables might be more appropriate for ensuring performance stability.
Full Transcript
Oh, you’re still here! You poor soul. So, this is the at least third video. And this one is about CTE. And you will find out just why they’re called common. I think they’re they’re crappy, to be honest, because they’re misleading. And the reason I know they’re misleading is because a question that I frequently get during consulting engagements is, should I use a CTEor a temp table?
And the answer is, of course, no. That is not an or question. They are not comparable objects. It’s not a comparable way of doing things. Shut up! Beeping at me. Yes, that’s right. Even in a pandemic. Anyway, let’s get on with the show. So, why do we have CTE? Well, it’s not just to make migrations from Oracle. I don’t know, does anyone migrate from Postgres? I don’t know. But it’s not just to make migrations easy. It’s not just for code parity with other database platforms. No, no, no, no. There are very definite limitations to derived tables. And if you can’t already tell by the little squiggles that exist in this beautiful white snowscape of a document, SQL Server is not going to be able to run this query.
We get an invalid object name x. Why? Because we cannot reuse x after this point. We can run this query. We can say select there and we can select everything from x. But once we try to reuse x, SQL Server is like, I don’t know who that new phone, who this? And SQL Server just stops being able to know what x did.
It’s not keeping track of it. It’s not stalking its x’s. So this is why we have a CTE. Because with a CTE, SQL Server has no problem stalking its x’s. We can run this query just fine. We can reuse x as many times as we want.
But the very reason that this exists is also, I think, one of the greatest shortcomings and one of the biggest misgivings around CTE. Now if I run this query, where I’m going to have a CTE, I’m going to select data from, I should turn on query plans for this. If I run this, we have one index seek into the users table.
But if I union this CTEtogether a few times, I run this query, all of a sudden, we have not one, not two, but three seeks into the users table. CTE don’t materialize. Whenever you re-reference them like this, you need to re-execute the syntax inside of them. The same thing will happen, I didn’t mean to bring that up, with joins. Now I wrote this join in kind of a funny way, but the way that you write the join doesn’t matter. If we run this query and we look at the execution plan, we will see one, two, three seeks into the users table to execute the one, two, three joins that I do to my CTE.
So the first thing that you need to realize is that they don’t materialize. They are not a materialized data set. You cannot index a CTE. You can index the underlying table or tables, but the CTE itself can’t be material. There’s no like, like, with index, like, like, like, create this index when you, when you use a CTE. That doesn’t exist right now.
I don’t know if it ever will. Now, this will get worse and worse as you make syntax inside of the common table expression more complicated. And as you join to the common tables or re-reference the common table expression more and more, if I run this query and I’m not like, actually run, I’m just getting the plan for it. I’m getting the execution plan minus for this query. And I zoom way out. We can see that for each iteration of that CTE, we did quite a bit of work. We have like, we have the base query down here. And if we zoom in a little bit down here, we can see that for the three joins, we have one, two, three times we touch badges. And then we’ll have a one, two, three times we touch votes. And then one, two, three times we touch posts. So we, and that goes all the way down to here, where the one, two, three times that we touch the comments table. So if you ever write a query with a common table expression in it, and you happen to take a look at the execution plan and you think that, gosh, that’s a big complicated execution plan for this query. Well, it’s your fault. You didn’t listen to me. I tried to warn you. Of course, CTE can sometimes be helpful. Sometimes. The problem is that you might just get lucky with them being helpful, and that they may not remain helpful because there are no guarantees with CTE. None at all. So right now I have these two indexes available. I have this index on the users table called users, and it’s on creation date, reputation, and ID. And I have this index on the post table that’s on owner user ID and ID. And because I’m filtering on post type ID, I have post type ID as an included column. So I only want to find where post type ID equals one.
And the thing is, when I go and run a query that really well matches, well, I have index that really well match the join and the where clause for this query. But that index on the users table doesn’t quite cover all the columns I’m selecting from the users table. Well, something kind of unfortunate happens.
And that SQL Server doesn’t use our my, it doesn’t use my index, which makes me incredibly sad. And this query runs for a little bit longer than I’d like. So we can see SQL Server not using my nice narrow nonclustered index here. And we can see this query running for 1.2 seconds over here.
Of course, if I tell SQL Server, well, hey, I want you to use my index, we can get it to. But I want to show you something really quick first. This query SQL Server estimates will cost 92.1 query bucks. 92.1. Keep remember that number 92.1. If we come on down here, and we we use an index hint, we’d say SQL Server use this index. All right, this is case center that might that might not work. We’re going to make sure that we can use index. If we tell SQL Server use this index, please, you’ll notice that this query ran a lot faster. This query ran in 0.94 milliseconds. That’s under 100 milliseconds.
So I’m told, I’m going to check with my check with my legal team on that. So this ran a lot faster. But SQL Server thought that it would cost 126.7 query, just 126. Let’s just call it 0.8 query. 0.79 is 0.8. So I’m going to round up. I’m going to throw a ceiling on that.
126.8 query bucks to run this one that ran much faster. So like I said in a previous video, cost means nothing. Stop paying attention to it. It has nothing to do with reality. It’s an estimate. It’s not an estimate for your hardware. It’s a general estimate for everyone’s hardware.
Isn’t that funny? So we have this query. And we have this query that when we tell SQL Server to use a narrow nonclustered index, it is much faster. But it’s more expensive. So SQL Server doesn’t choose it naturally. The reason it’s more expensive is all over here. This key lookup. SQL Server uses our narrow nonclustered index right here. And we immediately do a key lookup here to go get all those other columns that I selected. There’s no predicate in here. There’s a seek predicate, which represents the relationship between the nonclustered index and the clustered index.
That’s how we find each row. We seek into the non-clustered, seek into the clustered index from the nonclustered index using the ID column. And then we go fetch all of the columns that we don’t have in our nonclustered index. And this is where SQL Server estimates a lot of the costs.
68.2 of the 126.8 query bucks is in this operator. Gosh, you’re expensive. You’re so expensive, but you’re so fast. You’re so much faster than that other query. Well, one way to get SQL Server to use our narrow nonclustered index is to think about our query a little bit more differently. Sometimes I wonder if at some point we’re going to just have bridge words that encompass a couple different words.
Like we could have effect spelled with an A-E. Or we could say like fewer less. Or we could have like a where that’s like W-H-E-R-E. And like there that’s like T-H-E-I apostrophe Y-R-E. That’s something that covers everything so that you can never make another typo. I hope it never happens. I wonder if that’s the direction we’re heading as a society because there’s some not good things out there happening to the English language.
Some true butchery. And I’m responsible for some of it even. But if we think about our query a little bit, if we separate our query mentally before we start writing it, we need to think about the columns that we’re working with. We need to put them into two groups. We have informational columns. Those are columns that we’re just showing to people.
Columns that we’re only going to select, only going to display. And then we have relational columns. Those are columns that we’re going to join on, filter on when our where clause, maybe group by, maybe order by, maybe do some sum summing, averaging, row numbering, whatever. We do something with them other than just showing that.
They’re not just window dressing. So if we think about our query, we separate our query out into like mentally into those two logical groups of columns. All right, we have here we’re only going to have a narrow select list. And then we’re going to do all our relational work inside of this CTE.
And then when we get outside of this CTE, then we’ll select everything from the users table here. All right, we’re joining back to the users. We’re doing sort of a self-join here, but only to the rows that we get out of here. All right, so we’re going to narrow our result set down as much as we can.
And we’re going to use the primary key of the users table to our advantage. And we’re going to join it to itself over here. And when we do that, we no longer have to hint SQL Server to use the index.
All right, SQL Server uses the index on its own. But something else changes about this query, too. Now, with a key lookup like we saw in the last plan, the key lookup can’t happen at two different stages in the execution plan.
Whenever we do our seek into the nonclustered index, we have to also immediately afterwards nested loops join and go fetch the stuff for the key lookup out in the rest of the execution plan. That’s not true here when we sort of rewrite things manually with a self-join. Notice that the clustered index seek into the users table to go get all of our columns happens way over here.
So for the index seek into the users table and the index scan of the post table, we are only bringing a very narrow set of rows across. That means that for all of these operations that happen leading up to here, where we go get all those columns out, we can ask for much less memory to perform all that stuff, especially that sort operation.
Pretty cool, huh? So when we manually write a key lookup, SQL Server can detach those things. The problem is that because this is a CTE, we’re not guaranteed to always get that same execution plan.
For example, if we go back a little bit further in time, right? We’re going to go back from up here. We are only going back through 2013-12-10.
Down here, we’re going to go back to 2012-12-10. And if we go do this and we say run this query, it’s going to slow down. And we’re going to get a different query plan.
So now SQL Server kind of did what it did before, right? Where we have the users table where we seek into there. We sort data here.
We do our fake key lookup self-join here. And then we do a merge join here. And we do some stuff here. And I don’t know.
It’s just all exhausting. It’s just all exhausting. And that takes longer than I would want it to.
Now, granted, we return a lot more rows. 366,000 rows get returned here. But that’s not really the point. Now, the CTEworked well initially.
But at a certain point, the plan changed. It got less effective. And I don’t know. I… SQL Server was free to rewrite our query internally.
The optimizer was free to rewrite our query in any way it wanted. However, we can guarantee things a little bit better if we actually materialize them. So what I’m going to do is stick that initial query into a temp table.
And then select data out. And you’ll see that that happens a bit faster with a bit more stability. Now, this part is guaranteed to happen.
All right? And then when we go and get more data out, we will see the same execution plan happen. All right? So we’ll run this. And notice this part’s not going to get faster because we’re still going to have to return a lot of rows.
But the two individual queries execute a lot faster because we use the best plan for both… Or rather, I think, probably the best possible plan. If we spent, like, a long time tuning stuff, we might be able to get better plans out of that.
But I’m pretty happy. And overall, the only reason that that thing actually runs for three seconds, like it says down here, is because we returned a lot of rows.
Neither of these query plans runs anywhere near three seconds. Even combined, they’re probably just about half a second. A little over half a second. But if we were only to return the top 3,006 rows, like we did for that first query, this would happen very quickly again.
Not a lot of influence here, but the overall results returned more quickly because SSMS is a dirty pig. And it takes forever to fill up buffers and display data and, like, format stuff for this stupid Excel sheet that everyone thinks is what a database looks like. So there’s that.
Anyway, what I hope you learned here is that when you’re thinking about CTE and temp tables, realize that they are two fundamentally different things. CTE, while they can sometimes be useful to help you separate parts of the query out, nothing is materialized.
They do not necessarily reduce complexity for the optimizer. So the optimizer will look at those CTE and is free to rewrite things across CTE, within CTE, CTE and their final finishing query. There is no performance benefit to a CTE.
There are some interesting things you can do with CTE if you throw a top in there, but that’s a little bit beyond what we’re going to go into in this particular demo. We’ll talk about that in some other ones down the line.
Anyway, that’s about all I have to say about CTE. If you want truly materialized data, you must use a temporary object, something where you can store results. SQL Server can come up with statistics about those results and only those results.
Maybe you can index those results in a different way than you would your underlying tables. That makes it a little bit more palatable. And there’s all sorts of stuff that you can do in there as well that can help performance issues.
So anyway, I’m going to stop here. Finally. Finally.
And I’m going to record the next video and do some YouTubing, uploading and processing. All sorts of exhausting, boring, dull things that I have the leisure time to do these days. So I will see you over in the next video.
Thank you for watching. Hope you learned something. Hope you enjoy yourself. Et cetera and so forth. Ah, goodbye enough. 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.
In this video, I dive into the world of trivial plans in SQL Server performance tuning, explaining why they matter and how they can impact query optimization. Starting off with an amuse-bouche-sized introduction to trivial plans, I explore their significance by discussing simple queries that don’t require cost-based decisions, such as selecting top 100 rows from a table without any indexes or filters. As the video progresses, I delve into more complex scenarios where SQL Server has choices to make, like joins and order by operations, highlighting how these can lead to fully optimized plans. I also touch on the implications of trivial plans for index tuning, parameterization, and constraint usage, emphasizing why understanding them is crucial for effective query optimization in real-world applications.
Full Transcript
Erik Darling here with the first, the very first, the firsteth video in the SQL Server Performance Tasting Menu series of videos. And no half-decent tasting menu starts off with anything other than an amuse-bouche. And of course, an amuse-bouche is French for a little delicious thing. Probably not. Not my finest moment. Duolingo just uninstalled itself after I said that. But it’s usually something about the size of this part of your palm, maybe. Alright? Very small and very delicious. So it is technically a small, delicious thing, but that is probably not how a amuse-bouche lines up directly to the French words that you see on your screen there. So anyway, we’re going to talk about trivial plans because they are a good way to whet the appetite. for more advanced things. And I always find them interesting because there’s some stuff that goes along with trivial plans that can kind of hamper query optimization. So let’s run this query right here. Select top 100 everything from users. And right now with no other indexes, I know there’s no choice of data source for this query.
And there’s also no cost-based decision for this query. There’s no join, where, group by, order by, nothing. There’s no cost-based choice for SQL Server to make here. So it just says, whatever. If I thought about this for 100 years, I would never come up with a better plan. It would never be anything better than what I did right here, right now, in just a few milliseconds. And if you go over to the select operator and either hit properties or F4, you will see this optimization level of trivial. And that is how you know SQL Server said, just whatever. Just run the thing. SQL Server’s way of saying, I just don’t care. It’s like when my wife gives me too many choices for something, I’m like, whatever. It doesn’t matter to me.
Everything is going to be within this area of the same. So it does not make a difference. But you can do some kind of funny things and still get a trivial plan. For instance, if I select the top 10,000 rows and I generate a row number here, this plan will still be trivial because we still didn’t have a cost-based choice to make.
The ID column is the primary key. It’s a clustered index. So SQL Server is still just like, it doesn’t matter what we do here. I don’t have to figure anything out. There’s nothing for me. I have no input. Do your best. Don’t worry about it. And the same is true if we tried to get a distinct top 1,000 values from the primary key.
SQL Server looks at the primary key and knows it’s already distinct. So we still have a trivial plan over here. Ooh la la. Fun stuff, right? Now, where we start getting past trivial plans is when we start running queries where SQL Server has choices to make.
Because those choices are all based on costs and those costs are all based on estimates. Those estimates are all based on hardware for a single shut up computer. Shut up from 1998. And it has nothing. Those numbers, those costing numbers have nothing to do with your hardware.
But if we run these two queries, now I want to point out that one of them is selecting the top 1,000 and the other one is selecting the top 5,000, we will get slightly different query plans. All right. Well, I hope this video doesn’t get DMCA’d because someone’s playing whatever that is in the background. Anyway, SQL Server, some choices to make here.
For the 1,000 row query, it shows a hash match flow distinct. And for the top 5,000 query, it shows a full-on hash match aggregate. And what does that mean?
Well, I’m not going to get into how those different operators work right here, right now, because that’s not really what this is about. But we can see how the number of rows that we select influences the operators that SQL Server might choose in different circumstances. So, let’s go on and let’s look at some other things that will get full optimization.
One of them is a join. If we need to join one table to another, SQL Server will have many cost-based decisions to make, like, which of the three joins do I pick?
Is there a particular join within that group that will be better? Do I need to sort data for a merge? Do I use a bitmap for a hash match? Do I want to do an optimize nested loops? Do I want to apply nested loops?
There’s all sorts of things for the optimizer to think about. Now, another thing that, of course, will get us a fully optimized plan is if we order by without a supporting index. So, if I select these, the top 100, SQL Server had choices to make.
And one of those choices for this query was, do I need to go parallel? If I go parallel, will this be faster? Sure. Something to keep in mind is that if you have a parallel plan, you do not have a trivial plan.
Going parallel requires full optimization. Another thing that will get you to a fully optimized plan is a subquery, because a subquery is effectively, for the most part, will effectively be optimized as a join.
Some really simple subqueries will also get you full optimization. Take these two queries here. We have a select top 100 ID from users, and then we have a select top 100 select u.id as ID from users.
And if we go look at these two plans, they’re going to be just about the same, with the exception of this kind of goofy compute scalar down here. But if we look at the details a little bit more closely, this top plan is going to be trivial.
And this bottom plan is going to be fully optimized. So we have a trivial top and a full bottom. What does it all mean?
What does it all mean? If only someone would tell me. Some even simpler subqueries will also get us full optimization. So there’s no logical difference between these two queries right here.
We have a count, and we have a count where 1 equals select 1. There is no mathematical way for 1 to not equal select 1. But because we have this kind of goofy subquery, SQL Server will, for the top query, give us a trivial plan.
And for the bottom query, give us a fully optimized plan. So that’s fun and interesting. Now that we understand that there are some very goofy rules to what gets you full optimization, let’s ask ourselves a question.
Why does trivial optimization matter? Why do trivial plans matter? Why is Erik Darling talking to me about trivial plans? Well, it’s because there’s some stuff that goes along with trivial plans.
And there are some optimization phases that are missed with trivial plans. For example, if I run these two queries, and, you know, again, not to say that missing index requests are the end-all, be-all of index tuning. Far from it.
But if I were a developer and I were, you know, not very well-versed in SQL Server, and if I, I don’t know, was designing queries for a system, I might run a simple query and say, well, do I need an index?
Would an index help me here? And for trivial plans, SQL Server does not ask for indexes. We do not get to the phase of query optimization, index matching, where SQL Server starts thinking about alternative indexes that might help.
For this top query, notice that there’s a little at one here, which means that SQL Server has introduced something called simple parameterization. Simple parameterization means that SQL Server thinks that no matter what value ends up here, no alternative plan is going to be better or worse within reason.
And we can see that there’s another difference between these two queries, and that is that the bottom one, SQL Server has asked for an index. Now, I don’t know that I agree with this index.
It is a very big index as far as indexes go. It’s every, it’s, it’s just on the reputation column, and it includes every single column in the user’s table. At this point, one might ask, gosh darn it, did I choose the wrong clustered index here?
What happened? Why would SQL Server do that to me? But this is one of those things that’s missed with a trivial plan. Another place where trivial plans are kind of a bummer is when we get into adding things like constraints over our data.
Now, SQL Server likes constraints. Not as much as I do. I’m kidding.
Don’t be gross. Anyway, if we are going to be dutiful custodians of our data, we might want to tell SQL Server what is a valid range of search values in here.
And so if I create this constraint on the user’s table that tells SQL Server that every value in here will be between one and two million, we want SQL Server to be able to use that information effectively during optimization phases.
Now, to make sure, because I don’t want you to think that I’m playing tricks on you here, this constraint is not not trusted. It is, in fact, trusted.
We have a double negative. Whoever named this column may have been hallucinating. They may have been on a lot of Robitussin or something, but we have a zero for is not trusted.
So it is not not trusted. It is trusted. Trust me. You’ll have to trust me on that. And because of that constraint, we might expect this query to bail out.
If we select some columns from the user’s table where a reputation equals zero, we want SQL Server to use that constraint, to use that metadata and say, there’s no zeros in here.
Why would we go? Why would we need to do anything? But that’s not what happens. SQL Server scans the entire clustered index. We do 44,440 logical reads against our clustered index.
And if we look at the execution plan, we can verify that we did, in fact, scan the clustered index. Of course, if we add our sort of silly one equals select one subquery over here and rerun that, over in the messages tab, we don’t have anything about logical reads or physical reads.
We don’t have that statistics IO output over here because we didn’t do any. We just have a constant scan. SQL Server looked at the available constraints on the table and said, we can’t possibly have a zero here.
So what happened? This wasn’t just the fault of the trivial plan. This was the fault of the simple parameterization. SQL Server needs to come up with an execution plan where whatever plan it uses for at one when it equals zero has to be safe for at one when it equals one through two million or beyond.
So SQL Server, for a lot of the same reason why filtered indexes don’t get used when you parameterize those queries is because SQL Server needs a plan that’s safe for anyone, for any one value that you might put in there.
So SQL Server says, I’m afraid I can’t do that. And that’s kind of weird. Anyway, trivial plans are fine if you don’t care.
If you don’t care, if the optimizer doesn’t care, no one cares. Trivial plans, probably fine. If you’re not planning on adding indexes, constraints, query tuning, if you’re, I don’t know, one of those people who just only uses AdventureWorks and that’s the only place you do any query tuning, it’s probably fine.
Don’t worry about it. Simple parameterization is, well, I suppose in a way it’s good for plan reuse, but boy, it sure does hurt when we want SQL Server to, you know, give us feedback about indexes or missing indexes or, you know, use constraints that we created.
We went through all that pain of creating constraints. Very sad when SQL Server doesn’t do what we want it to do. Anyway, that’s all I have to say about trivial plans.
Thank you for watching, and I will see you over in the next video where we will talk about something totally different. Ha, ha, ha, ha.
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.
This post will have links to all the demo videos for my presentation, The SQL Server Performance Tasting menu. Since the demos change over time with whatever I currently find interesting, I want to have a place where people can find older ones.
In this video, I delve into the intricacies of multistatement table-valued functions by focusing on their primary driver: table variables. I explore how these functions inherit many of the performance issues associated with table variables, such as serial execution for modifications and inaccurate row estimates that can prevent parallelism. By walking through a practical example, I demonstrate how even simple operations within a function can lead to unexpected behaviors, like not utilizing parallelism when reading from a table variable inside a multistatement table-valued function. I also highlight the hidden costs of these functions, where significant time is spent in parts of the query plan that don’t contribute to row processing, making it crucial for developers to be aware of such nuances and test thoroughly before implementing them in critical code paths.
Full Transcript
I want to talk about multistatement table-valued functions because the primary driver of a multistatement table-valued function is a table variable. We’ve talked about table variables once or twice so far. And because they are the primary driver of a multistatement table-valued function, multistatement table-valued functions inherit genetically a lot of the defects that table variables carry with them, I guess. So let’s look at some of that. Alright? Look at some of that stuff. So what we’re gonna do is declare a simple table variable and insert some data into it. And then we will select data out of it. And the reason that I have this hint here will make a little bit more sense in the next demo. But for now, let’s just rock and roll with this. So this should run for just about four seconds altogether, inserting the rows in and then selecting them out. And that’s pretty simple there. Now, first thing that I want to make sure everyone is aware of is that modifications to table variables are forced to run serially. If we go hit F5, or sorry, if we either hit F4, not F5, we already hit F5 and ran the thing. If we hit F4 on the insert or on the root operator, or we hit F5 or on the root operator, or we hit F5, or we hit F5 and hit F5 or we hit F5, or hit F6 to get F5. And if we hit F5, we hit F5, we hit F5, run the root operator, or hit F5, or hit F5, or hit F5. And we hit F5, we hit F5. And we could not generate a valid parallel plan. This is true of any modification to a table variable, insert, update, or delete. And if you don’t have a particularly big, complicated query inserting into a table variable, this probably doesn’t matter much to you, but it is something that you should should be aware of. And the other thing is that even though we just stuck, let’s see, 1.3, 1.8, 4, 1.3, 1.3 million rows into this table variable, SQL Server only thought one was going to come out. So we get a very bad guess coming out of the table variable. However, at least we can read from that table variable in parallel.
We see you have a parallel, a little parallel icon there. If we go to properties and we look at the rows, we can see that across four threads we had some pretty decent row distribution there. So it is possible to read from a table variable in parallel. The problem is that quite often because you get this very bad estimate here, right, you get this one row estimate, oftentimes the cost of the plan will be so low that you will not get a parallel plan. Here I hinted the plan, I used a query hint to make this go parallel. Under normal circumstances, a query that costs 0.035 query bucks is not going to go parallel. Now, you do need to make one distinction here and that that’s the cost of the parallel plan. We can look at the cost of the serial plan and see if it would be expensive enough to go parallel. Rerun this. I don’t know how long this is going to take. It could be days or hours or minutes, seconds, but ooh la la, I was right.
Because of this very low one row estimate, we get a very cheap serial plan and SQL Server at no point even considers parallel emphasis. This query would have to cost at least 50 query bucks. And if we had an honest guess here, I would be willing to bet that SQL Server would give us a parallel plan.
And we can test that out by trying a recompile hint and see what happens. We’re using a recompile hint, we’ll get table cardinality from the query, but we will not get any column level statistics. And lo and behold, when we use a recompile hint and SQL Server gives us an accurate estimate, then we get a parallel plan. Now, I know that number looks weird to you, but if you recall back to the other video in this series about bitmaps, then you’ll recall why this estimate looks off.
We have an in-row probe here, which means that we have the bitmap filter ruling rows out, which means that we have a reduced number of rows coming out of that scan. So, cool stuff there, right? We got to see that the insert to the table variable was serial. We got to see that we could read from the table variable here in parallel. And we kind of got to see how the estimates that you get from table variables are bad and often influence the optimizer away from a parallel plan because the cost of a one-row query is often so cheap that SQL Server just doesn’t, you know, you don’t break cost threshold for parallelism. There’s SQL Server doesn’t get an expensive enough cost estimate in order to start thinking about parallel plans.
So now let’s take everything that we just did here. And let’s take this query and let’s stick it inside of a multi-statement table valued function because that is sort of the point of this whole thing. So we’re going to take all of that same logic and we’re going to put it inside a function. And now we’re going to call a query or call that function in a query.
And so sometimes I don’t know where some of this comes from. Some of this formatting just just pops in and it looks weird. And I spend a lot of time fixing it. I have SQL prompt too. I don’t know. I don’t know what happens. It’s bizarre. Absolutely bizarre. So we’ll run this. You can see I have SQL prompt. It’s right here.
Before anyone says, this is your SQL prompt, I have SQL prompt. Stuff gets weird. So this query ran for four seconds, which is about what we would expect because that’s what happened when we ran that same query without the function. But I want to show you a couple interesting things. One is that despite having this hint here to use a parallel plan, we do not read from the table variable in the multi-statement table value function in parallel.
There is no parallelism icon here. And the operator immediately following the scan is a distribute streams, meaning SQL Server needs to take one thread and split it up into multiple threads with rows across. So if we look at this, we now have four threads and we have all that there. If we look at this, we have all rows on a single thread. So we took single thread and split it up into four threads and distributed rows pretty evenly across them.
This is another limitation of multi-statement table value functions. You cannot read from the table variable inside of a multi-statement table value function in parallel. If you don’t have a lot of rows in here, that’s probably not a big deal. If you do have a lot of rows in here, which some people will undoubtedly have because I’ve seen people putting tens of millions of rows in table variables and not being sure why things are slow. But, you know, that’s just me. That’s just the kind of stuff that I get called for.
The other thing that’s really interesting about this is that the time disappears. We know this runs for four seconds. If we get the properties of this select operator and we go to query time stats, we can see that we spent 4.6 seconds of wall clock time running this query. But the operators in the plan don’t come anywhere near adding up to that. By the time we get to this final sequence operator, we’re at 1.147 milliseconds.
One might wager that the majority of the time was spent in here and that it is just hiding from us. The time was not spent returning rows. If it was just time spent returning rows, then we wouldn’t have the, it wouldn’t be in the elapsed time here. But we can, we can probably guess that that, remember before when we ran this and it took about three and a half seconds and the whole thing took about four seconds total.
And about three and a half seconds was inserting into the table variable. Well, I’m pretty sure that between you and I, we can make a reasonable guess that that’s what took three and a half seconds in our, in our plan there. Doing all that.
Good times, right? Good times. Good times with functions. That’s all I had to say about multistatement table valued functions.
be careful with them because they do use table variables. Table variables have a lot of performance or potential performance hiccups in them. So please be judicious and test carefully if you’re going to use a multistatement table value function.
Maybe try to separate it out from, you know, big important parts of code as much as possible, something like that. I don’t know, whatever.
Anyway, thank you for watching, and I will see you in yet another video. Goodbye.
Video Summary
In this video, I introduce the SQL Server Performance Tasting Menu series, a collection of presentations designed to share insights into various aspects of SQL Server performance in an engaging yet straightforward manner. The format is intentionally simple and direct because, while there are many talented storytellers in the community who excel at weaving compelling narratives around their topics, my goal here is more about showcasing what I find interesting or important about SQL Server without all the embellishments. Whether you’re a seasoned pro or just starting out, this series offers bite-sized lessons that can be picked up and applied as you see fit—no need to follow every video if you don’t want to; each one stands on its own merit. So, grab your favorite beverage, settle in, and let’s dive into the world of SQL Server together!
Full Transcript
Erik Darling here with Darling Data and a copy of Windows that is nearly as inactive as I am these days. So this is the intro video to the SQL Server Performance Tasting Menu series of videos that I’m going to be recording. And I do this presentation the way I do it because there are there are many people in the community who are fantastic storytellers. They can weave narratives and they have all sorts of fun anecdotes and flowery language for things. And I just sort of have a very specific thing that I want to tell you, don’t want to teach you about something and that’s it. I don’t have that particular gene. So this is set up in a way where I can show you what I think is cool or fun or interesting or depressing about SQL Server. SQL Server and you can watch it or you can watch it. There’s no pressure either way. You don’t have to stick with me from start to finish to learn something. You can learn lots of little individual things as you go. And so that’s that. That is the whole thing. I’m going to start recording the rest of the videos now. Hopefully you enjoy them and learn stuff and all that. So.
See you soon.
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.