Tag: how to create temp table in sql

Hot Tips For Safer Dynamic SQL In SQL Server

---

Thanks for reading!

Video Summary

In this video, I delve into the world of dynamic SQL security and how to keep your data safe from potential threats like SQL injection. I walk through a practical example where we create a table called `DropMe`, insert a row, and then execute a query that inadvertently drops the table due to SQL injection. This serves as a stark reminder of why it’s crucial to parameterize dynamic SQL properly. I also discuss how to safely handle cases where you need to pass in column names or other schema-related elements using SQL Server’s system views like `sys.columns`, `sys.tables`, and `sys.schemas`. By leveraging these views, we can ensure that only valid objects are used within our dynamic SQL queries, significantly reducing the risk of accidental or malicious commands being executed. Additionally, I touch on some practical tips for maintaining security, such as using string functions like `STRING_SPLIT` (available in SQL Server 2016 and later) to safely parse input parameters and logging invalid inputs for further review if needed. Overall, this video provides a comprehensive guide on how to secure your dynamic SQL queries against potential threats while ensuring they remain performant and efficient.

Full Transcript

Erik Darling here with Darling Data, and my microphone is on, but the sound bar just looks funny. Okay, well, we’ll deal with that, hopefully. Anyway, today was my oldest daughter’s fifth grade graduation, which I know sounds a little weird. A lot of people just have like a kindergarten and then an eighth grade, but, you know, in New York they shuffle you from a K-5 to a 6 to 8 school, so fifth grade is apparently a big deal celebration here. And, of course, you know, being here in America, they sang, they do the Pledge of Allegiance and they sang the National Anthem with the Star Spangled Banner and it kind of, kind of struck me sitting there in the audience that, um, there are a few words in the Star Spangled Banner that, um, people, they, like, aren’t common words anymore and just people might not know the meaning of. Like, uh, Gallantly or Rampart or even Spangled might send some people running for the dictionary. Who knows? Kind of, I don’t know, might need, might need some, uh, might need to throw some TikTok lingo in there if we want the Star Spangled Banner to remain a true top 40 hit. So, anyway, uh, in this video, uh, from Erik Darling of Darling Data, we’re going to talk about how you can keep your Darling Data, uh, safer, uh, in the, in the face of Dynamic SQL. And I’m going to show you a few things that I, I will sometimes do when I’m, when I’m working with Dynamic SQL to make sure that no, there’s no, there’s no, there’s no hijinks, there’s no shenanigans, there’s no tomfoolery going on and, and when, when, when queries get executed. So, uh, just, just the first thing we’re going to do is a small primer on, uh, the, the perils, the potential perils of the data.

So, uh, we’re going to create a table called DropMe. We’re going to insert a row into DropMe. And what we’re going to do is just, just run a very simple query where someone has SQL injected a drop table command onto a, onto a, onto a per, onto an input. And we’re going to execute that. And we’re going to see that, uh, at the very end of this, we no longer have our table called DropMe. In fact, we have, we have an error message saying invalid object name DropMe. And we, the reason we have that, that error message is because this is what our, our executed SQL string looked like. We had a totally valid select query, which executed, and I’ll show you that in a second.

And then after that ran, we had a drop table command. Also run. That’s, that’s unfortunate. If we go over to the results though, uh, we will see, well, there’s the result of our first select from DropMe up there. And then here’s the, here’s the result of our, oh, that jumped around a little bit. So you Dickens, zoom it. There’s the result of our first query. And there’s the result of our database name search. And you can see all the stack overflows that I have, uh, on my server, right? Obviously not good.

And obviously the solution is to parameterize the dynamic SQL. And so if we, we sort of rerun that portion of the script again, when we rewrite our dynamic SQL in a safe way, we will get no error messages, but more importantly, we don’t get any search results, which is, which is a good thing, right? Why is it a good thing? Because we didn’t execute an unsafe SQL command, right? I mean, I don’t think it’s a very, very, uh, prodigious way of working through a SQL Server.

If you, you’re okay with user searches running, uh, and returning results and also executing out of bounds commands. So if we look over in the messages tab now, there’s a printout of what we put into our, our database name, right? It’s just S percent wildcard, you know, any old thing that blows after that, uh, and a drop table command.

But in here, this, this looks different now, doesn’t it? We just have the parameter name. We don’t have that string sitting in there. And that’s great for things that you can actually parameterize.

There are lots of things in SQL Server that you can’t parameterize. At least not, like, in a meaningful way. Uh, that, uh, you might need to pass into dynamic SQL.

Like, you might, you might need to pass in column names or a schema name or a table name or even a database name. And none of those things can really be supplied as parameters to dynamic SQL. It’s not allowed.

So, here’s, here’s some stuff that I do to make sure that when, for cases where that, that does have to happen, uh, we, we stay safe. So, uh, SQL Server has all sorts of views in it that enumerate different objects. Like, we can enumerate databases from sys.databases and half a dozen other views.

Uh, we can enumerate, uh, schema from sys.schemas. We can enumerate tables from sys.tables. And we can enumerate columns from sys.columns.

So, when, if I’m going to allow someone to pass in a list of columns or a list, like, a schema and a table or a list of columns, that they want to return from a user query, I need to make sure that those are all safe as well. So, what I, what I’ll do in those cases is, uh, declare some placeholders in here.

And then I’ll do stuff like lookup the schema name and table name that got passed in. And since this, this isn’t dynamic SQL, we don’t have to worry about any filthy, dirty inputs in here. And then I’ll set the, uh, the safe schema and table names, uh, with quote name, because quote name is very important.

Um, uh, I, I was working with a database that had, database name that had a space and a dot in it earlier this week. And that was a real adventure in a number of ways. Um, um, found myself using a lot of parentheses for that one.

Uh, and this will make that safe. And then what, what’s nice to do is if you’re going to, if you’re going to use something like this over and over again, uh, rather than constructing this constantly, uh, just, you just have like one, like, thing that you concatenate the schema name and the dot.

And, you know, one of my big gripes about dynamic SQL is that, or rather dynamic SQL and SQL Server is that like, there’s no, there’s not like enough like typing for it. I mean, not like there’s a lot of like keyboard typing, but there’s no like, like types built into SQL Server to handle stuff like this.

So like, it would be cool if there was, you know, like, like an object type or something where if you passed in, like, you know, like a, like a table or a schema or a database or whatever else, that you would get back like a constructed object with, with the right, the dots in the right places. That’d be cool. It’d be nice if you could just, if you could, if you could, there was some constructor for that.

Like quote name, you, you, you pass something into that and it puts quotes around. It’d be nice if there was something to put dots in the right places too, so that you didn’t have to do foolish things like this and realize that you forgot a dot somewhere. You left a dot in and you didn’t mean to leave a dot and it becomes quite a hassle.

There’s a reason why dynamic SQL is, is a real art, is, is practice as a, is a true art form by few. A lot of hacks out there, a lot of hackers out there, but not a lot of true dynamic SQL artisans. And then I’m going to, because, you know, I’m, I’m, I’m living in the future here on SQL Server 2022, but you know, this, we have this string split function, which came around in SQL Server 2016.

And we have this string ag function, which came around in SQL Server 2017. The problem is, and this is where Microsoft really screws up again, is that you have to be in the database compatibility level that the function was released in, in order to use it. So to be, to use string split, you have to be in compat level 130 or better.

To use string ag, you have to be in 140 or better. And it doesn’t help. Now, now Microsoft was cool from like a query optimization standpoint, where it was like, oh, we’re going to give you all these use hints to like specify which like, like compatibility level you want the database, this query to be optimized in, in this database. Which allows for like, you know, some neat stuff to happen.

Like in 2016, you can like do a parallel, like insert select into a temp table or any other table, but temp table is particularly helpful for. And like, you know, like 140, 150, 150 and up, you can get all sorts of like batch mode on rowstore type stuff and other neat things, right? Like, you know, batch mode or memory grant feedback and other intelligent query processing features, which are lovely.

But, um, not, not these for some reason. For some reason, changing the compatibility level of the query doesn’t unleash these functions. So if you are using a really old version of SQL Server or a really old compatibility level, you are going to have to go the old school route of, rather than using string ag, you’re going to have to use some XML hijinks to construct whatever list, whatever comma separated list of things you want.

And if you are not using, uh, the, the 2016 or better compatibility level, you will have to work out your own string splitting scheme. Now, normally I would be pretty picky about this, right? Because this is not a good SARGA-able thing.

I see a lot of queries with string split in them or like some string splitter in them. It doesn’t have, not necessarily the built-in one. Uh, whether it’s like a, it’s a, it’s a meaningful query, not just hitting some dynamic management views. And, you know, like it’s an aware clause or a join or something else.

And, uh, that’s, it’s not a good idea for performance because SQL Server has no idea what’s in there. And it makes no attempt at, like, um, like, like, like creating a statistics histogram for the results of a string split. So you can get some really bad cardinality estimates when you start getting that stuff involved in a where clause.

But since this is just a very simple query against the dynamic management view, um, I’m not gonna get picky here. Right? This is, like, I can deal with this.

Right? We’re not performance tuning in this one. We are, we are trying to keep our query safe from the hackers. We’re concerned about the security of our stuff. Right?

Not performance tuning in this one. And then, um, if any of these come back as null, someone clearly did something either devious or mischievous or maybe just wrong. Uh, there’s a quote about, like, malice and ignorance and all that.

I suppose that could, I suppose that could, that could apply here. I’m not, I’m not sure how I feel about it. But, you know, just some, some general handling.

And if you wanted to take this further, uh, you know, especially for, like, the column list, you could even, like, you know, have a table variable or some other logging feature. Some other logging thing happened where you, like, logged a list of things that someone passed in that were incorrect. Right?

If you really cared about it, you could totally do that. I don’t care enough to do that here. It’s just gonna make the code longer. But you could absolutely, like, you know, stick a, like, a list of stuff from the column names parameter, uh, that were invalid column names. Right?

It’s a totally doable thing. And then down here, so this is what I kind of meant by, like, some things can’t be passed in as parameters. Like, you couldn’t directly pass this to dynamic SQL as a parameter. SQL Server would say, I don’t know what you’re talking about.

Likewise, you couldn’t pass this to the, you can’t pass, like, you know, database schema object names, table names, stuff like that, without SQL Server saying, no. Can’t do it, not happen. And what this allows for is for if someone wants to select the top 100 rows from the user’s table, they can do that.

And the query will look like this. And one thing that is very nice for you to do in all your dynamic SQL is to put a comment with the store procedure name in there so people know where the hell these things come from. It’s not just some buck-naked query running around in your plan cache or query store that no one can, no one can identify.

But what’s cool about this is then if we were to run our store procedure with something devious, mischievous, and malicious in it like that, it would not show up in there. Right? Our messages tab would still just show the three columns that were actually valid for the user’s table and the comment list.

So, good tips on keeping your dynamic SQL extra safe. Right? And if we look at our drop-me table, it still exists despite someone’s best efforts to drop our poor drop-me table.

Right? Now, this is just one kind of example with object names, like, you know, schema, table, database, column name, stuff like that. You might have other instances where, you know, and I grant you, I grant you every grudge you have against this store procedure for not needing, not actually needing dynamic SQL.

But you might have a more involved case, and without really, like, doing something somewhat complicated, this is kind of just a good general example of, you know, an alternate thing you could do that might make life better, might actually perform better, might cut some crap out of your query plan, stuff like that. Where, let’s say we have a store procedure where we want to search display names in the user’s table of the Stack Overflow Data. You probably won’t be working on this particular store procedure, but you might, you know, let people search like this.

And you might have dynamic SQL. Well, again, an unsanitary dynamic SQL that looks a little something like this, where, you know, you’re just passing in some bare-naked parameters to a thing like this, which is, you know, obviously this is a horrible idea. You shouldn’t, you shouldn’t trust anyone this much.

Wouldn’t, wouldn’t, I wouldn’t trust us, I wouldn’t trust anybody with something like this. But, one thing that you might find useful to do is, rather than have all this stuff in your dynamic SQL, you might find it performs better, and it keeps you safer if you do something like this, where you just, you create a temp table, right, and then you enumerate just the IDs of the things you care about. You stick that into a temp table, and again, like, this doesn’t matter much, right?

If you want to throw a recompile hint on here, whatever you want to do, you can do it to tune up this query, but, you know, for the context of what we’re doing, this is pretty quick and fast. And just dump the contents into a temp table, right? Nice little primary key on there, too, right?

Look at our primary key. Look at our nice clustered primary key on this table. Isn’t it gorgeous? Most beautiful thing you’ve ever seen? I think so.

And then we can use that temp table within the dynamic SQL block, because when you, you know, it’s state, like, we still have that temp table created in this context, and we can execute that dynamic SQL referencing that temp table, which is created outside. Now, there’s funny things about creating temp tables inside of dynamic SQL, in that, like, the first one is that this is not reciprocal. So if you create a temp table inside of dynamic SQL, you can’t reference it outside of the dynamic SQL.

But if you create a temp table outside of the dynamic SQL, you can reference it inside of dynamic SQL, which is pretty cool. I like it. I’m into it.

I dig it. I can get on board with that. The other funny thing about creating temp tables inside of dynamic SQL is that it makes them not cacheable, which might be good or bad. Paul White has three magnificent, wonderful blog posts about temp tables and temp table caching in store procedures, stored procedures.

I end up sending people to those quite a bit, because they are, as Paul is wont to do, he has written wonderful, detailed, comprehensive blog posts about these topics. So, yeah, so, like, you might have an issue where, you know, you create a temp table and SQL Server caches that temp table and part of what it caches is the histogram. And then the next time you go to use that temp table or you go to run that store procedure, SQL Server reuses the histogram for that temp table, which is a wild thing.

Absolutely wild thing. But if you create a temp table inside of dynamic SQL like this, it is not cacheable, and the histogram or nothing else will be cached along with it. Now, that, of course, has upsides and downsides, right?

If you’re running a store procedure a ton, that temp table caching might be a good thing. You might find a lot less contention in temp DB if you, you know, are reusing cached temp tables. You might, you know, you might even find that performance isn’t that big a deal because you meet the thresholds to, like, uncache or whatever you want to call it and update the statistics on that temp table.

But, you know, in general, this is kind of like a more stranger angle on performance tuning an issue with temp table caching. But fun nonetheless, because every, sometimes you get to pull these weird tricks out and, boy, they, they work, work like gangbusters. I don’t, I don’t really like that phrase that much.

I don’t know. It’s kind of, again, it’s kind of old-timey. It’s like a, it’s like a gallant rampart. If my gangbusters on this gallant rampart are all spangled.

Anyway, that’s about all I had for this one. I think some useful tips on making dynamic SQL safer in your database. Again, you know, if you’re letting people pass in database, schema, table, column names, you’re much safer if you look those up in dynamic management views based on what people pass in and either give them the yes, no based on that.

If you, you know, if you’re using other stuff, right, if you’re not using object names and you’re still writing dynamic SQL like this, you are doing yourselves a bad, you are doing your company a bad, you are doing your data a bad. And you should really start writing parameterized dynamic SQL instead. You can follow along with the examples up above if you need to.

And if you, if you really want to start performance tuning stuff in dynamic SQL, you might even find that simplifying the amount of work done in the dynamic SQL and taking, you know, larger, more complex parts out and dumping just like simple lists of keys into a temp table and using that temp table in the dynamic SQL instead gets you far better performance. So anyway, thank you for watching. Hope you enjoyed yourselves.

Hope you learned something. If you like this video, thumbs, thumbs upsies and, you know, helpful praising comments are always welcome. If you think I messed something up or I’m wrong about something, you can, you can tell me that too.

But I didn’t mess anything up and I’m not wrong about anything. So you might, you might, you might, you might be unsatisfied with the response there. If you like this sort of SQL Server content, usually it’s performance tuning, but this is sort of like a hot dynamic SQL tips because here at Darling Data, we believe in hot SQL action.

So, you know, I got all sorts of useful stuff up my sleeve. Don’t, don’t, don’t, don’t make a judgment on account of my sleeves being short. I can, can stuff a lot of useful stuff in here.

But if you like this sort of SQL Server content, you can join nearly 3,700 and, hold on, let’s wait for it, 29 other dedicated, observant, faithful SQL Server or whatever you are, professionals, by subscribing. And I like subscribers because then I get to keep saying bigger numbers every time I do one of these. So that’s nice, that’s nice for me.

It’s a good, good pat on the back for all E-Darling of Darling Data. Anyway, it’s starting to, starting to get hot in here and I, I want to open the door and let the air conditioning in. So I’m going to, going to can this one, upload it and, and hope and pray that you watch it and that you, you follow my instructions here and that you, you don’t end up in the newspapers for, for bad reasons.

So anyway, thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

A Little About Adaptive Joins In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into a scenario where my client upgraded from SQL Server 2014 to 2022, taking advantage of the new adaptive join features. I explain how these intelligent query processing capabilities were previously hindered due to insufficiently wide indexes and the additional overhead of potential lookups or sorts. By creating more comprehensive indexes that cover all necessary columns, we successfully enabled adaptive joins for certain queries, demonstrating their importance in optimizing performance. This video is packed with practical examples and insights into what’s required for SQL Server 2022 to leverage these advanced features effectively.

Full Transcript

Erik Darling here with Darling Data. Today’s video, we’re going to talk about a situation where a client of mine, you know, the nice people who pay me, you could be one of those too if you paid me, they had switched, upgraded to SQL Server, well actually to 2022. So they went from like 2014 to 2022. So it was a pretty big jump for them. And part of the reason why they wanted to go there was because they wanted all of these crazy, fancy, new, intelligent query processing features. One of them was adaptive joins. And the reason for that is because there were a number of queries that we found where adding a join hint, like either hash join or loop join, or sometimes a loop join, or hash join, or just to exclude merge joins from the picture, or just to exclude merge joins from the picture, because screw a merge join, were like helping query performance. And one of the problems that we found once we got to SQL Server 2022 and up the compatibility level, this is Enterprise Edition, of course, because Microsoft only gives the good stuff to people who spend money. I give the good stuff to everyone for free, even though even though I was recently accused of having useless drivel in my videos. The problem is that the nice folks at Beer Gut Magazine, they pay me by the minute to do these, so I do try to stretch them out a little bit. Maybe there is some useless drivel in here, I don’t know.

If you want some real useless drivel, I’ll tell you that this spot on my shirt is from my morning protein shake, because I can’t figure out how to pop the lid, shake up the thing, and then you get protein stuff on the flappy lid, and then drink from the flappy lid, or drink from the shaker, but the flappy lid drips on me. So this is my fitness credibility badge right here, this little protein shake spot on my shirt. Alright, so that’s all the useless drivel in this video. The useless drivel about a protein drivel, right there.

So one of the things that we found was that SQL Server was not choosing adaptive join plans, and one of the reasons why it was not choosing adaptive join plans is because we did not have sufficiently wide indexes. So, to give you a little background on adaptive joins, they were introduced in SQL Server 2017, and they require some sort of batchy mode-y thing to happen, either because you have a columnstore index on your table, or you’re using the fancy pants enterprise edition of SQL Server, and you’re getting batch mode on rowstore. Or maybe you create a temporary table, or just like a helper table in your database that has no rows in it, but has a clustered columnstore index on it, and you can do a fake left join on 1 equals 0 to that thing to get some of the batchy mode-y intelligent query processing features to kick in for you.

So, that wasn’t happening. An extra sort of level of costing that makes the join ineligible for an adaptive join. SQL Server doesn’t want to choose between a hash join and a nested loops join, where on the inner side of the join, you might have to do a lookup to get rows out.

At least, I’ve never seen it choose an adaptive join where there was a lookup involved. So, you know, maybe you can find an example of it and prove me entirely wrong, but, you know, kind of doubt it. Anyway, let’s look at a couple examples of this, right?

All batch mode adaptive joins start out as hash joins. And there’s this, like, threshold for the join, where if it passes that threshold, it will remain a hash join. But if it doesn’t pass that row threshold on the outer part of the join, then it will switch to nested loops.

All right? So, we have, I think I already created this index. Let’s make sure.

I’m not a fool. And let’s, actually, you know what? We’re going to do a little prep work because there’s another index down here. I’m just going to make sure that that index, the second index definitely isn’t there, but the first index definitely is there.

And that will make the demo go a lot smoother. All right? That will at least prolong my SQL Server career.

That will keep me out of the fitness industry for, like, another week or so, I think. All right? So, we’ve got this index.

Now, this is a simple example with just a single key column index. In real life, I know your indexes are probably a bit wider, and your queries are probably even wider than that. So, this is kind of a common thing that can happen.

So, what we got here is a query that does a couple left joins, and we’re going to force optimizer compatibility level 160 right here so that we are totally and completely eligible for batch mode on rowstore. And if we run this query with query plans turned on, it’s not terribly slow, but the point of this demo isn’t to show you a slow query and then a dramatic improvement with adaptive joins.

The point of this demo is to show you what your sort of requirements are for adaptive joins kicking in. All right? So, in this query plan, we get a nested loops join, which was not inappropriate, but this plan also features a key lookup.

And so, the adaptive join is not a thing here. All right? So, we do get a batch mode hash join later on, but SQL Server does not make an adaptive join choice here.

Okay? And that’s all because SQL Server doesn’t want to have to make that choice with the additional overhead of a potential lookup involved. So, let’s create an index that covers everything in the query.

Remember, we do a lookup down here, and what we’re doing in the lookup is we have a predicate on the score column, and we output the score column. So, if we have the score column in the index, we’re going to create a brand new one here, right, that has owner, user ID, and score on it. We’re going to make a brilliant indexing choice, maybe, and we rerun this query.

Now, we get an adaptive join between the users and the post table. We still don’t get one for the comments table. Now, you can have more than one adaptive join in a plan.

SQL Server just doesn’t go for it here. SQL Server doesn’t think it’s worthwhile to invest in an adaptive join here. You can totally have multiple adaptive joins in a single execution plan. But now we get the adaptive join here between users and posts that we didn’t get before because the post table did not have an index that adequately covered all the columns that we needed for it.

Now, I did tell you earlier that merge joins were not part of this consideration. And the reason why merge joins aren’t part of the consideration is because merge joins expect sorted input. And when we need to sort input, like, say, for this query, or rather for this join, SQL Server doesn’t want to think about, oh, we needed to sort that.

I don’t want to, like, in the same way that, like, having to do a lookup is an additional costing, like, perspective for the query, having to sort data to employ a merge join is also an additional costing thing. So merge joins are not part of the picture.

And, you know, of course, because I hinted, even though we don’t need to sort for this part between users and posts, I just have a merge join hint for the whole thing, so it shows a merge join hint here. I just wanted to show you the merge join plan to show you that, you know, if you need to, like, this is why merge joins aren’t a consideration for the adaptive join.

Now, what’s interesting is that neither merge joins nor nested loops joins can operate in row mode. I’m sorry, in batch mode. They can only operate in row mode.

So only, like, if you get a hash join for an adaptive join, that will be in batch mode. But if you get an adaptive join with nested loops, that’ll be in row mode, right? Okay, so that’s all good there.

And I do want to show you one kind of funny thing about this query. Let’s see if I can get a somewhat better execution plan for it. It looks like last night it was a little bit better.

But this query plan features some very odd operator timings. Very odd indeed. And if we look at what happens in here, right? Where’s the funny part?

Where’s the funny bones? It’s kind of right at the end here. If we look at the very end of this query, we get 565 milliseconds and then 1.1 seconds here. But then if we go and look at the query time stats, the CPU time and the elapsed time agree with the operator before the gather streams.

So that’s amusing to me. Anyway, apparently the operator time code could use some work. I would imagine with the influx of summer interns, now that we’re entering June, July, and August, Microsoft will have some top interns on top of this operator timing code.

Any day now, it’ll be all fixed. Anyway, that’s all I had to say for this one. Thank you for watching.

I hope you enjoyed my useless dribble. I hope you learned something, even from the useless dribble. I hope you enjoyed yourselves. If you like this video, as usual, thumbs up and nice comments. Even if comments supportive of my useless dribble are always appreciated.

And if you like this sort of SQL Server content, you can join the… Hold on. I want a freshly up-to-date… I want a read-committed up-to-date number here.

You can join nearly 3,719 other SQL Server professionals or some sort of… I don’t know. I actually don’t know the makeup of the audience.

I say SQL Server professionals. It could be just derelicts off the street who like to watch my videos. Maybe they’re like, I’m going to get my hands on his Adidas t-shirt someday. I don’t know.

But yeah, you could join nearly 3,700 and… Yeah, it hasn’t changed. 19 people who subscribe to this channel and get helpful little bonks on the head. Every time I publish one of these…

Well, I guess you could interpret them as either… Beautiful Gems of Wisdom. Or… Beautiful Gems of Wisdom Drizzled in Useless Dribble. Drift…

Protein Shake. I don’t know. However you want to call it. Anyway, thank you very much for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Efficiently Finding Aggregate Values In SQL Server: Row Mode vs Batch Mode vs Indexes!

---

Thanks for watching!

Video Summary

In this video, I delve into an interesting exploration of finding maximum values in SQL Server queries, showcasing multiple approaches and their performance implications. Erik Darling from Darling Data shares his insights on using cross-apply and derived joins versus the more traditional methods like CTEs, emphasizing how a well-designed supporting index can significantly enhance query performance. The video also delves into the nuances of batch mode and its impact on execution plans, providing practical examples that highlight both the benefits and potential pitfalls of different query writing strategies. Whether you’re a seasoned SQL developer or just starting out, this content offers valuable insights into optimizing your queries for better performance.

Full Transcript

Erik Darling here, with Darling Data. No longer dying. I want to say just about a full week with no antihistamines, which is wonderful, because I no longer feel like I’m dying, which is nice, right? Because, you know, you record these videos and there’s no live audience, so you have no idea if you’re dying on, say, on stage or not. It’s almost impossible to tell. Just have to wait for that one person who always comes along and downvotes my videos. There’s always one.

Just have to wait for them to pipe up someday and maybe let me know why they didn’t like the video. Maybe it’s me. Maybe they’re jealous. Maybe they’re in love with me. So there is that. But I have an exciting announcement. I have the first bit of Pass Data Summit swag. And in honor of the fact that Kendra Little and I are co-presenting two days of action-packed SQL Server performance tuning content, I’ve got these temporary tattoos made. They’re a little hard to see because they’re glossy and the lights in here are bright. And if I put them really close to the camera, it doesn’t necessarily make the situation better.

And it would also eventually make my green screen start to freak out. But they say hot SQL action. Got kind of like a cool Led Zeppelin vibe. Because that’s what all the kids are into these days. Cool Led Zeppelin vibes. That’s what I see on TikTok all the time. Kids just jocking that Robert Plant vibe thing. Anyway, in this video, I should probably mention what I’m going to talk about, right? In this video, we’re going to talk about the best way to find, well, all the queries are going to be looking for max values. But, you know, this could be, you could be looking for a man or an average or something else too.

You could do any of those things. And the reason I want to record this is because I often see queries written. Someone figured out one way to do this. There are many ways to do this. Someone figured out one way to do this and put it everywhere. And it’s not necessarily the best way to do it. There may be many different ways to write this query that are faster than the one way you found and keep repeating. So we’re going to talk about different ways to do it. We’re going to talk about both using cross-apply and derive joins. We are not going to be using CTE.

If you’re the sort of reprehensible dork who white knights CTE by saying that they make things more readable, well, you’re not going to see any here. You would find that if you did write a CTE to achieve this, you would get the exact same performance as if you wrote the derived join type query. We’re also going to look at the difference between row mode and batch mode and how indexes make things better.

Usually. Isn’t that the line on indexes? They make things better? That’s what I keep hearing anyway.

So we got that going for us. So anyway, let’s dive right in, finally. And we’re going to look at the same query written in four different ways. We’re going to look at a very simple way.

And you’re going to see these again, both with cross-apply and derived joins, because that can change things a bit. So we have a cross-apply with a max in there, correlated on owner user ID to the ID in the user table. Makes sense, right?

We’re going to try out top one over here. We’re going to try out row number in here. And remember, this is a correlated row number. Because this row number is correlated inside of the cross-apply, we do not need to partition by owner user ID.

Which is a little bit different from this query, because this one has an extra level of nestation on it, where we are selecting from this. And we are generating a whole row number in here. And then we are correlating the row number outside.

The things here. It’s exciting stuff, isn’t it? Very exciting. All sorts of different ways to skin that cat. Which is probably similar to something I…

Many different ways to feed that cat. Many different ways to pet that cat. Something like that.

I’ve never actually skinned a cat. Never actually heard of anyone skinning a cat. Or needing to skin a cat. So, I don’t know who came up with that phrase. It’s a little gross, actually.

Cats are a little gross. So anyway, let’s look at query plans. The first query, just using the simple max, you know, does pretty okay, I think. SQL Server chooses a hash join.

It does… You know, a lot of the times when you use cross-apply, SQL Server will optimize that as nested loops. But SQL Server here said, nah. We got a hash join, and the whole thing finishes in about 700 milliseconds.

Which is alright. It’s alright with me. This query benefits quite greatly from a bitmap. Remember that bitmap gets used up here and applied down here.

And that bitmap does a lot of… That bitmap goes to work. That’s why this cardinality estimate looks crappy. But because SQL Server came up with the cardinality estimates, and then used the bitmap later, and then did the stuff with the bitmap, and then the bitmap did some good work, and we streamed out a lot of rows.

So we did not get as many rows as we thought we would, passing through the bitmap here. Remember that bitmap gets applied as a predicate right there. So bitmap did some good work, and this query ended up being really fast.

Second two queries stink on ice. They stink in winter, because SQL Server did not do what it did up there. SQL Server did not say, I should just use a hash join and throw a missing index request.

SQL Server built into eager index pool twice. These each take around 27, 28 seconds. These are completely uncompetitive and useless, at least without a good index in place.

The third query, somewhere in the middle. A little bit closer to the fast one, but not quite as fast. It’s about four and a half seconds slower.

The reason why it’s four and a half seconds slower is because, you know, this is something that I caught in an, like, probably, I think it’s been in like two or three Adam Mechanic sessions from back in the day, about, you know, when you’re doing anything with computers, particularly sorting, you really want to, it’s rather, it’s preferable to do many little sorts than it is to do one big sort.

And, you know, so we’re kind of in the middle on this one, where, you know, SQL Server did probably the smart thing by choosing a hash join instead of a nested loops join, but it also did this whole thing where it sorted 17 million rows.

Now, you know, part of the problem with doing the one big sort is that you might not get enough memory. You might spill to disk. This one spilled a little bit to disk. It’s not the end of the world here, right?

It still took about a second and a half to do the whole sort, but, you know, it’s a lot slower to do one big sort than lots of little sorts. And that’s what you get when you do nested loops joins.

You get a whole bunch of little sorts. SQL Server grabs a row, puts it through this side, does the sort for just the data for that row, does that, comes back, does the next one, does the little sort.

And that would be a lot faster if we, you know, had a way to seek into this index, right? Even if we still had to sort stuff in here, it would be faster to do a bunch of little sorts than one big sort.

So now let’s look at things where there’s an index involved. All right, so we created this index and this is highly specialized index on owner user ID and score descending, not only sets us up for success with how we are correlating the post table to the user’s table, but also fully supports our windowing function where we need to partition by owner user ID and order by score descending.

So we have done ourselves a huge favor here by creating this level 1000 IQ index. So we have the same four queries.

We have the simple max. We have the top one. We have the cross supply, correlated row number cross supply, and then the uncorrelated row number cross supply. These are our four queries.

Let’s look at how these plans did. With an index in place, this one kicked butt again, right? Went from about 700 milliseconds to a little under 200 milliseconds. A very simple nested loops plan with a little top in there.

Teeny weeny little top. And then we have nearly the same query down here where SQL Server, that’s a teeny weeny little top, right? So what is kind of amusing is that the SQL Server transformed the max query in here and the top query in here to both just be a sort of a top one query, right?

Like this, they shared the same execution plan. And they’re both fast, right? They’re within a few milliseconds of each other. If I ran these a whole bunch of times, this would probably end up just about even, maybe go back and forth a little bit.

So, you know, decently, well competitive. You could write this query either way with a good supporting index. The third query down, the correlated row number, does a little bit worse.

This is, I mean, just about as good as the max query from the first one with a serial plan. But this is another kind of good example of how doing a bunch of little things is faster than doing one big thing, right?

Because this one takes 800 milliseconds. There’s about 500 milliseconds spent in here. You know, of course, you know, this is one of the things that makes me nervous about missing index requests.

SQL Server is like, we can impact by almost 77%. But, I mean, that’s not where we spent our time. The costing in this plan is kind of a joke, right?

87%. And then where we spend all our time is 10%. But a couple zero percents in there. Good. I don’t know if that’s so smart. And then here’s where the doing the one big thing sucks a whole lot more than doing a bunch of small things.

So the difference between these two plans, right? The bunch of small things plan, like we talked about, like I mentioned earlier, is nested loops. The one big thing plan uses a merge join.

And look how much slower this whole branch is doing the merge join than it is doing the nested loops join, right? We scan the whole index. We segment and sequence project to build the row number over the whole thing.

We filter stuff out later. And like, you know, the filter does the same thing in both places, just to different degrees. But then this whole thing takes just about three and a half.

Oh, that’s a very devilish number. 3.666 seconds. I did not do that on purpose. I did not have the power to do that. I wish I did.

Maybe someday I’ll work out how to attach and detach a debugger just fast enough to get query time to whatever devious numbers I want.

Not today. Not today. All right. So this is all those four queries with a good supporting index in place. If you ask me what my preference is, a good supporting index, you can write this query in a couple different ways and still get reasonably good results.

Supporting index will help just about, you know, anything that you do. So you should have those. You should make one.

The next set of queries, these are a little bit different. So we got rid of the supporting index for these. And what we’re going to talk about in these is just how batch mode affects things. Now, for the first query, and we do get batch mode in the first query, right?

We have batch mode on the hash match and we have batch mode on the join. But for the first query, it doesn’t get a whole lot faster. Right? At 602 milliseconds, it saved about 100 milliseconds from the row mode run.

The second two queries where, I don’t know, God has forsaken us yet again and we build a couple more eager index spools like this, these are totally uncompetitive. There’s no batch mode going on in here.

Where batch mode does make a difference is with the uncorrelated row number, right? So if we come down here and look, we got batch mode in a couple different places. We have it on the sort, we have it on the window aggregate, which I don’t, which is brand new for all of these things we’ve talked about and we have it on this hash join.

Now, one piece of SQL Jeopardy that you can absorb and I don’t know, you can choose to do whatever you want with it. You can, you can, you can, you can choose to take it in, chew it a little bit and spit it out or you can, you can digest it and you can, you can have this wonderful belly full of knowledge where under normal circumstances, so batch mode sorts can sort data across DOP threads, but unless they are the child aggregate, child operator of a window aggregate, they cannot, they cannot be read from using DOP threads, or they’ll be read from single threaded, but in this case, we get the window aggregate so we can read from the sort across our DOP 8 threads and that, this thing ends up being fairly efficient.

Not quite as good as the, the, the simple max in the, in the cross supply, but it’s still much better than it did with the, with the index in place, this thing was like five seconds without the index in place, this thing was also not fast, so getting this one down to one second is a pretty good win for batch mode, right?

So batch mode can make a difference with these things when, when you write queries in somewhat strange ways. All right. So now, the only thing we have left to look at now is taking cross supply out of the picture and using derived joins instead.

And we’re going to look at that with the index in place, that ends up just about the same as the other ones without the, without, so we’re going to look at these without the index.

And the two ways we’re going to look at is with and without batch mode. So one thing that’s really important to point out here before we get too far into things is that one of these queries is actually logically incorrect if we just use a derived join, and that’s this one right here.

And if you’ve been writing queries for, you know, 10, 15 minutes, you’ll probably see why. Because with derived joins, because we can only do the, the correlation outside of this, SQL Server is going to give us an incorrect result for this.

We’re just going to get the top one post ordered by score descending in here, and this gives us an incorrect result. If we come over to the results pane, that second query only returns one row, when it should be returning 600 something rows.

So this one, out of the running. We’re not even going to, we’re not even going to spend time on that one because it’s such an idiot. All right. So this one, bleh, you don’t do the right thing, you are gone, you are stricken from the record.

But the derived join actually ends up the same as the cross supply, just about, within, you know, what, 10, 11 milliseconds of each other, so not a big difference here. And then the second two queries with the, both, what you notice something here is that what happened to the uncorrelated row number in the first query, and the first, like, example that I showed you happens to the correlated row number in the second one now.

These both end up with very close execution plans. Right? And again, the problem here is that we are doing the one big scan and sort.

Right? So again, in row mode, this is fairly painful. Right? So we didn’t get the nested loops, we had to do the big sort for both of these, and they both end up with just about the same amount of time.

There’s apparently about a 300 millisecond difference, I don’t know why. Maybe, maybe, maybe I was staring at the computer funny when they ran. But using the derived join, nothing ends up like remarkably better or, I mean, aside from the query that returns wrong results, there’s like the lack of eager index pools, which I guess is a blessing.

You know, there’s, you know, this was the, one of the queries that would have eager index pool, but this one is just, you know, gone. You know, we don’t need you. But the one that didn’t eager index pool ends up the same as the uncorrelated row number one.

So, I don’t, I don’t think that’s necessarily a great arrangement. Right? Because we’re doing the one, again, we’re doing the one big sort. We want to avoid doing the one big sort. And then finally, the same derived join scheme, but this one where, where batch mode is going to be in play, we’re going to get pretty decent results, I think.

Right? Again, the top query with just the simple max does the best at about 600 milliseconds. The second query which returns wrong results, we don’t care about you.

Again, that returns just the one row and just that one row is not the correct result. We need about 600 rows from all these. And then the second two queries that do benefit from batch mode end up just about identical and just about one second and 40, well, 30 to 43 milliseconds, which again, is not enough of a timing difference for me to care much about.

So anyway, some key points and takeaways from this video. One, supporting indexes really do make a huge difference when you’re trying to write these kinds of queries.

Row number is very frequently not the best choice. Row number can be much more competitive when you have batch mode in place.

And if you don’t have a good index in place, you really want to avoid the top one thing and the correlated row number thing because you will most likely end up with a nasty eager index spool on the inner side of your nested loops join and you will suffer tremendously both personally and professionally for the remainder of your days on this planet.

And the other thing is that when you need to do this kind of stuff, batch mode makes a huge difference and the more you can gear your queries towards doing lots of small sorts versus one big sort or even generating lots of small row numbers even if you don’t need to sort, even if you’ve been a whopping genius and you’ve created the right index, generating that row number over a lot of rows is a lot slower than generating a bunch of row numbers over smaller sets of rows.

So if you find yourself dealing with slow queries that look like this, as much as you can, either try to get batch mode involved or try to get a good index and try to get lots of little sorts and lots of little row numbers involved because generating a row number both with a sort and without a sort over, you know, in this case 17 million rows is incredibly painful but, you know, getting batch mode for this made it a whole lot less painful.

Cool. All right. So, with that, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. If you like this video, you can give it a thumbs up, leave a nice comment.

If you’re feeling extra charitable, I’ll accept the one person who always gives me a down vote and never leaves a comment. You’re still welcome here.

Just wish you were, I wish you were a nicer person. That’s all. If you enjoy this sort of SQL Server performance and student content, you can subscribe to my channel and you can join.

Hold on. Let me get the most up-to-date number here. You can join nearly 3,708 other lucky subscribers in getting a bonk on the head every time I publish one of these.

And if you need help with this sort of thing, my rates are reasonable. So, thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Why Some Long Running Queries Don’t Have Wait Stats In SQL Server

---

Thanks for reading!

Video Summary

In this video, I delve into a fascinating aspect of SQL Server query performance that often goes unnoticed—how serial plans with eager index pools can hide the true nature of their execution. I explain why some queries mysteriously run for a long time without showing any significant wait stats or weights in the plan. By comparing a serial and parallel version of the same query, I demonstrate how the parallel plan provides more transparency compared to its single-threaded counterpart, which can be misleading due to the eager index spool operator. This video aims to highlight the importance of scrutinizing execution plans, especially when dealing with large tables, as eager index spools can significantly impact performance without obvious indicators in wait stats or query costs.

Full Transcript

Erik Darling here. Surprise! With Darling Data. Recording a little bit on the early side today because I have a lot of stuff to do later and I don’t want to interrupt my great streak of recording a video every day because, just to be honest with you, the YouTube videos, they get a lot of action on them. They get a lot of views, they get a lot of thumbs going in the right direction, straight up in the air, and they get a fair amount of comments. And, you know, written blog posts covering the same material have not nearly generated the same amount of engagement or interest. So, you know, I think investing more time in the videos and the recording stuff is, you know, kind of, I think the direction of the video is, you know, the direction that I have to go in order to get people to listen. I don’t know, maybe it’s getting to see this handsome mug smiling at you that keeps people in there. Who knows? It’s certainly not the slightly remaining, remainders of bed head that I haven’t quite worked out yet. Actually, it’s more like a headset head because I was wearing my headset earlier and now I have this, like, dent here. And then everything kind of poofed around it. Just in case you were wondering.

Why my head looks a little extra lumpy today. I got headset head. Or headset hair. Anyway, in this video, we’re going to talk about why some queries sometimes mysteriously run for a long time but don’t really tell you why. Now, there are going to be other peripheral reasons why you might see this, but they’ll be represented by weights. So, like, if a query gets blocked, you’ll see that that query spent a long time waiting on an LCK to take a lock and do something, right? If a query spent a long time reading pages from disk, you’ll see, like, page.io latch underscore some two-letter combination weights. Now, there’s a lot of stuff that when a query waits on things, it shows up for a reason. There are a few reasons. We’re going to talk about just one of them today. There are a few reasons why a query might run for a long time but not actually show you any particular weight that it waited on.

Of course, there is the scenario where, you know, you might have a query that runs a long time but the whole time is just burning away on CPU. Now, in a parallel query, you’ll at least see a lot of CX weights that represent that time. In a serial query plan, you know, the SOS scheduler weights just might not be crazy enough to account for all of the time that we spent, you know, doing a little merry-go-round on the CPU and that can be rather confusing.

But in this one, we’re going to look at how serial plans with eager index pools in them kind of hide the work that they do, like where that time in the query is spent in a weird way. So, I’ve got the same query, essentially twice run here, and one of them is limited to max.dop1 and the other one is allowed to go as dop as I allow, which on this server is 8. And if we look at the query plans for these, and I’ve already, I’ve taken the opportunity to run before, before recording the video so that I made sure everything looked right, made sure everything looked right when I did this.

Both of these queries, despite one being serial and one being parallel, run for nearly the same amount of time. Right? That’s not a very big difference for a serial execution plan versus a query that ran with a degree of parallelism of 8. Ocho, as they say in my favorite restaurants.

So, like, where are the differences? Well, they’re slight, but they’re there. And I think an interesting one is over here.

So, the serial scan of the post table took just about 5 seconds even. But, for some reason, a parallel scan of the post table only got, took 200 milliseconds more. That’s weird, right?

Why would a parallel scan take 200 milliseconds more? That’s kind of strange. But, the funny thing about both of these is that at least this one is honest, right? This is a serial execution plan.

We have a serial operator here. There is no indicator of parallelism because this query didn’t go parallel. This one is, of course, a filthy stinking liar. Right?

And if we zoom in, I’ve talked about this a little bit before. Or if we zoom in on the properties of the index scan on the post table, we get this kind of funny thing where all the threads, all the rows end up on a single thread. Right?

How 17 million rows end up on thread 3 this time. If we ran this query 8 times, they’d probably end up on a different thread every time just based on however SQL Server is feeling at the moment. So, like, the first, you know, first sign that the query plan is lying to us a little bit is in that, you know, a parallel scan takes 200 milliseconds longer than a single threaded scan.

And, of course, this is a limitation imposed by the eager index spool. You’re only allowed to build an eager index spool on a single thread. You can read from it in parallel.

Like, if we look at the properties of the eager index spool over here, we can see that all 8 threads had some stuff happening over here. Right? So, that’s just one sign of the dishonesty.

But that’s not really what the video is about. I’ve talked a lot about eager index spools. So, what I want to talk about in this one is how the serial plan is hiding what it did exactly. If we look at the properties of the select and we look at wait stats for the parallel execution plan, we can see that we spent 266 total seconds.

Remember, it’s milliseconds. So, you chop off those last three digits for savings and you get to seconds. So, about 266 seconds waiting on exec sync.

Now, in the context of this query, exec sync is happening while these threads synchronize and build this eager index spool. Right? So, we had eight threads that needed to synchronize, build that spool up in tempDB, load the 17 million rows in it, and then have SQL Server read the 4,390 rows that we cared about from it.

Which, you know, is at least honest. Right? Seeing that 266 seconds was spent in there, that’s, you know, eight threads times whatever the time was.

Maybe that’s eight times 38 or something. I don’t know. I’m not terribly good at math. But the single-threaded plan up here tells us absolutely nothing.

Right? If we look at this, we have three wait stats registered to this query plan. And not a single one of them shows us anywhere near the almost 38 seconds that this query executed for.

Right? We have 44 milliseconds of memory allocations. We have 12 milliseconds of SOS scheduler yields.

And we have 8 milliseconds of reserve memory allocations. That does not, that’s nowhere near 30 seconds. Right?

These are all definitely milliseconds. And so, and I find this quite dishonest. Now, there are other times when either a serial or parallel plan might exhibit this type of thing, but you would still have a wait stat associated with it. And this is something I’m going to record a different video about, how wait stats for sort spills and hash spills at least surface themselves in some ways in either query plan wait stats or wait stats in general.

But I’m going to not spoiler that whole thing just yet. I’m going to leave a little something on the table for when I record that. But, so what’s interesting about these sessions is, and this is something that I tell people to be careful and to keep an eye out for all the time, is when pure select queries do a bunch of writes.

Right? So, this is indeed session ID 115. Oh, that’s the wrong armpit.

There we go. Oh, wait, maybe that was the right armpit. Yep. You know what? I just can’t get out of the way of that thing. Trust me, this is session ID 115. We had two select queries, and both of those select queries combined did quite a few writes.

Now, we weren’t inserting into, I mean, we weren’t instructing SQL Server to insert anything into a temp table, but it did build those spools up in tempDB. So, that accounts for the writes here. But, like, if you were doing, like, select and an insert, or select and something like insert into a temp table, or select into a temp table, or something else that were, like, there was obviously a reason for writes to occur.

It would be quite strange to see, or it would not be strange at all to see a select query involved with writes. But these are just two queries that selected data and returned it. There’s absolutely no reason for them to write anything, except those spools.

You know, another reason for select queries causing writes could be stats updates. It could be spills. It could be query store related.

There are other reasons why you might see it happen. But, always, like, it’s something that I really tell people to be on the lookout for, because, especially if that query is slow, then those writes could absolutely be the root cause of why it’s slow. So, you know, figuring out what caused the writes is, you know, an exercise in executing the query, getting the actual execution plan, like we did in this tab.

But it’s certainly one that’s generally worth doing. So, what do I think about this? Well, this is more just like an FYI video.

I really do think it would be helpful if Microsoft did add some weight stats to account for things like this, because it can be quite opaque to end users. Exactly why a query was slow. Now, like, let’s just take a step back, and let’s just pretend that we found these queries either in the plan cache or query store or some other monitoring tool where we didn’t have the benefit, the absolute luxury of an actual execution plan.

You know, we would see this. And, you know, if you’re typical, if you’re one of the normies out there, you might start looking at query costs, and you might say, oh, 82%, oh, 87%, and you might completely ignore these. Because these, with a cost of 11%, remember the actual execution plans, we spent about 30 seconds apiece in those eager index spools.

And this is, that’s where the majority of the time was spent. But, you know, you would say, oh, but the cost is so low. It’s only 11%.

How bad could it be? How bad could it be, man? Well, this is how bad it is. 30 seconds apiece. So, you know, always be on the lookout for this stuff. Eager index spools are a particularly nasty query plan operator to run into, especially if they are latched on to rather large tables.

Off small tables, they’re not really of consequence. But, you know, big tables, they’re definitely of consequence. So, just, you know, be very mindful of that.

And if you’re looking at, you know, if you’re running any script, whether it’s spquickiestore or spblitzcash or, you know, whatever dustbin junk drawer script you have that interrogates one of those or the other, maybe you’re using the query store GUI to investigate things. If you have, if you see a select query that is causing a bunch of writes, pay special attention to operators in the query plan that might be responsible for them.

Whether they’re spools or whether they’re memory-consuming operators like sorts or hashes that might be spilling. Because if it’s ending up on the sucker board of things that you need to tune, those might be good places to start. So, anyway, I have to go start the rest of my working day now.

Hopefully, wear some headphones to mask the headphone scalp that I have currently. Thank you for watching. Hope you enjoyed yourselves.

I hope you learned something. I hope that you will, you will deign to give this video a thumbs up or leave a nice comment. You don’t, you don’t have to say anything about the hair. We can skip the hair this time.

I don’t know. Maybe just, maybe some, say something nice about my keen fashion sense, I guess. If you like this video and you like this sort of content, you can, you can subscribe to my channel and you can join nearly 3,653 other happy YouTubers out there who get notified, who get a knock right in the head every time I drop, every time I publish one of these videos. Wouldn’t that be, wouldn’t that be nice for you?

So, anyway, time to go work. Thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

For The Last Time, It Doesn’t Matter How Many Rows You’re Putting In A Table Variable

---

Thanks for watching!

Video Summary

In this video, I delve into table variables in SQL Server and why Microsoft’s advice about their usage is often oversimplified. I start by challenging the notion that a table variable containing fewer than 150 rows is safe to use, drawing parallels between this advice and other dubious recommendations from various sources. The core of my discussion revolves around the limitations of table variables when it comes to cardinality estimation and plan shaping. By walking through an example with a table variable and comparing its performance against a temporary table, I illustrate how SQL Server’s lack of statistical information on table variables can lead to suboptimal query plans, even for small datasets. This video aims to provide a more nuanced understanding of when it’s appropriate—or not—to use table variables in your T-SQL code, emphasizing the importance of considering how these variables will be used later in queries and their potential impact on performance.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we are, well, I had my sights set on something completely different. I had my gaze set upon far grander shores, but then there was a YouTube comment earlier saying, Microsoft’s advice about table variables is that as long as there’s fewer than 150 rows in them, they’re okay. Everything’s hunky-dory, just go about your business. And given the quality of Microsoft’s advice on a lot of other things, I would treat it with the same level of suspicion that I would treat, I don’t know, politicians, alligators offering to help you cross rivers, I don’t know, homeless people handing you soiled bags. There’s a lot of things. There’s a lot of things that, like, I would just be like, mmm, no thanks. Not what you’re selling today. Not interested.

So, the thing with table variables is that the number of rows that you put in them does not matter. What matters is what you do with them later. Right? So, if you’re going to use table variables, this is when you probably shouldn’t. This would be your mental checklist for times when you probably shouldn’t use table variables, regardless of how many rows you’re going to put in them. So, if you’re going to put in them. So, if you’re going to put in them. So, if you’re going to put any amount of data into a table variable, and you’re going to correlate that table variable to larger, to one or more larger tables in any way, you probably shouldn’t be using a table variable.

And that goes for anything in this list and more. Joins, where’s, subqueries, ins, not ins, exists, not exists. Anything that requires a relational activity between a table variable and another table of any meaningful size means you shouldn’t be using a table variable. So, when table variables are sometimes okay is if you want to pass data between procedures. Now, I talked how, another video where I talked about how if you use temp tables for that, you can get a rather polluted plan cache, which may not be the end of the world. Passing in lists, right? Instead of passing in a list of comma separated values and parsing it out. Often, using a table value parameter is a better choice. But even then, I might even suggest dumping the contents of your table value parameter into a temp table and then using that instead.

Where table variables usually win out in a typical speed test, unless you have in-memory tempd enabled, which if you’re on stupid managed instance, you can’t do, is if you have very high frequency code calls, like hundreds or thousands of calls a minute or second or millisecond or microsecond or nanosecond or whatever unit of time you want to deal with. I don’t know what, I don’t know what, I don’t know what the difference between metric and metric units of time is. Do they have kilominutes or kiloseconds? I don’t know. Weird thing to think about. So, and you’re only ever like putting a little bit of data into a table variable and then retrieving a little bit of data just from that table variable.

Right? So, when you shouldn’t use them, most of the time. When you should use them, very, very small controlled portion of the time. So, I’m going to talk a little bit about why the number of rows, and hopefully this is the last time I have to talk about this, because I feel like I’ve recorded this video 17 times. So, hopefully this is the last time I have to say it.

The number of rows that you put into a table variable is not what makes using a table variable okay. So, here’s a simple example of why table variables can screw you up regardless of how many rows you put in them. You ready for this? Alright.

I’ve got a table variable called at t. It’s got one column in it. It’s an integer. It’s got a clustered index on the integer. Actually, let’s fix that. Let’s make sure everyone knows that’s an integer.

Let’s not minimize it by calling it an int. I don’t like that. And I’m going to put two groups of data in there.

I’m going to put one group of data that represents a larger number of rows across tables in the Stack Overflow database. So, user ID 22656 belongs to a gentleman named John Skeet. His name has come up a number of times across my videos.

He is prolific in the Stack Overflow community. I don’t know if he still is, but at one point he was quite prolific. And then I’m going to put 99 other rows in there. And the 99 other rows that I’m going to put into my table variable are very low reputation users.

And that’s ordered by the most recent creation date. So, these are the people who joined the site and haven’t been around a long time. The reason why this matters is because table variables, even with indexes created on the columns, do not get column level statistical histograms created for them.

And SQL Server has no idea how to produce cardinality estimates for them. Now, that stuff has changed a little over the years. There’s trace flags and recompile hints and compat level 150 plus, I believe, enterprise only.

We’ll get you something called table variable deferred cardinality estimation, which tells you how many rows in total are in the table variable. But it does not tell SQL Server what the values in those rows are. Very specific about this.

Say that very, very slowly. SQL Server has no idea what’s in your table variable. It just knows how many things are in your table variable. Alright.

And then what I’m going to do is I’m going to just get a count from my table variable joined off to two other tables. And I’ve run this ahead of time. Because if I were to sit here and make you watch me run this now, I don’t think we’d be friends anymore.

So, here’s our initial insert into the table variable. Alright. Let me get rid of that tool tip and all sorts of goofy things popping up on me. And this is fast enough.

300 milliseconds. Very few people would complain too much about that. Now, we still get the inability to perform a parallel insert here. But for putting 100 rows into anything, that doesn’t matter.

Right. What matters is down here. Now, let’s look at this query plan a little bit. And we can see where SQL Server, because we’re using compat level 150 here. SQL Server correctly estimates that 100 rows are going to leave the table variable.

But again, it has no idea what the contents of those 100 rows are. And so, when it starts attempting to do joint cardinality to the other tables, everything goes right to hell. So, we mess up here by almost 2,900%.

We mess up here by a really, really big number. I don’t even know what percent that’s going to end up being. But that’s a big number with a very small estimate here.

And this all drags on for nearly two minutes in total. All right. You look at the final operator here. We have a minute and 54 seconds.

And then, for some reason, this batch, this hash mode, this batch aggregate, sorry, this hash aggregate runs in batch mode. There’s a lot of hab-hab-hab-habas in there. And so, we have to add 500 milliseconds to the 150, sorry, to the 1 minute and 54 seconds here.

So, nearly 1 minute and 55 seconds in total. All right. Tally that up.

It’s a long time. All right. It’s a bloody long time. That was a bad accent. I apologize. Never going to do that again. And the thing is that this kind of stuff does not happen with temp tables where SQL Server can generate histograms and do proper cardinality estimation with data in temp tables.

So, a couple things to do in here. All right. Let’s run this real quick.

And what I want to show you is that under different circumstances, under sort of normal circumstances, the temp table variables are backed up by sort of hidden temporary objects up in tempDB. Here we have AB575B71. All right.

That is our secret temporary object backing up the temp table. And if I run this query a couple more times, we will just get different sort of values in here. They’ll be a little bit different every single time I run this.

There’s a B11, blah, blah, blah. Now, the reason why this query is quick is because we’re just, like I said, one of the times when table variables are generally okay is if we’re just selecting a relatively small amount of data out of them. But what’s interesting here is that, you know, the data that we put into this table variable, which is ID 22656, and then a bunch of pretty small numbers.

SQL Server knows how many records are going in total are going to be in the table variable, but has no idea how to make a guess for this. If you notice, go back to the query plan, or sorry, go back to the results, we get zero records back from that. No single row in that table variable qualifies for that where clause.

But SQL Server still thinks that 30 of the 100 will. So we get a 30% guess there. We had 1,000 rows in there, we would guess 300.

We just get a stock 30% guess. If we had an equality predicate, it would guess 10%. And if we had a unique index on there, we might guess 1. But, I’m sorry, unique index and an equality predicate, it would probably guess 1.

But even that would be wrong. And all sorts of just wrong things in there. So temp tables don’t have that issue. Right?

If we run this code, now this code matches the exact same query that I ran the first time with the table variable that took a full two minutes. If we run this, notice this number might look familiar if you remember the really bad estimate from the initial plan. But now SQL Server, well, a couple of things happen.

One, we actually get parallelism in this plan. So this improves by a little bit, right? This is like 300 milliseconds, now it’s 55 milliseconds. And this query down here, that used to take a full two minutes, finishes in 15 milliseconds. Why?

Because SQL Server can make good guesses. Right? SQL Server is now, oh, you know what? I forgot to change that. Let’s make this equivalent. That’s supposed to be 99.

Let’s do that again. There we go. SQL Server can make just fine guesses now. Right?

They’re not perfectly spot on, but it was a lot closer. Right? That was only off by 1,400%. This was only off by 2,400%. Not off by like 483 million percent, whatever that was. So, again, not perfect join cardinality, but SQL Server was able to do some stuff differently, and we got a much faster execution plan because it at least had some reasonable idea of what was inside that temp table.

Right? The reason for that is that SQL Server, like I said, generates statistics on temp tables that it does not create on table variables.

Now, what I’m going to do is I’m going to use a couple trace flags in here to print out cardinality estimation information for this query. And if you look at the messages tab, what you’re going to see down a little bit is text that looks like this.

Right? And what do we see in here? Well, we see a cardinality estimate on the base table posts where we get this number, 1.7e plus 07.

And then this, CST call black box card equals 1. Cardinality of 1. Right?

And that’s going to be repeated throughout all of the cardinality estimation attempts for this query all throughout here. This black box thing shows up over and over again. And that black box thing is indeed the table variable because it has no statistics.

Right? There is no statistical information about… Oops.

Sorry. I got a little carried away with the highlight there. There is no statistical information about what is in our table variable. Right? That just doesn’t exist there. Now, that will exist is with the temp table.

Right? So keep in mind, the only thing I’m sticking inside my temp table is the value 22656. And if we run that same thing for that, well, what do we get back?

Well, we get back a bunch of junk that we probably don’t need that, you know, other stuff that came through there because I had a very expansive query to show all these things. But the thing that’s important is this first line where we have…

Well, let me squeeze that over a little bit. There we go. The thing that matters is this top line here where our pound sign temp table gets a single row histogram with 22656 in it. And so SQL Server can do accurate cardinality estimation.

SQL Server can figure out exactly how many rows are going to qualify in there. Right? Pretty good.

Pretty good stuff. Remember, SQL Server wasn’t able to do that for the table variable. So, let’s go back to this plan. Look what we got.

That was 27,901 of 12. Right? That one of one here, but no idea how to match that one to that one down there. Temp tables just didn’t have that problem.

So, just to reiterate, does not matter how many rows you’re going to put into your table variable. It matters how you’re going to use your table variable later. Table variables do not get statistical information about the data that populates them.

So, if you’re going to join those table variables off to other tables, where cardinality estimation and plan shape and a whole slew of other things might matter for performance, you probably shouldn’t be using them.

All right? Now, this kind of stuff might not matter at first. What I’m going to relate it to is, since I obviously just came back from the gem, right? If you’re the type of person who does not have a very challenging database size-wise, or does not have a very challenging workload, or does not have a very challenging performance criteria for their queries, you can almost use whatever you want and get away with it.

All right? But if you’re the type of person who wants to get better at this stuff, and who wants to someday maybe work on challenging databases and challenging workloads, these are the kind of small technique things that you’re going to have to get used to fixing.

Now, relating it to the gym, if you’re the kind of person who walks in, maybe puts a couple of 45-pound weights on a bar and does some squats, your form is almost never going to matter.

135 pounds is not going to be enough to destroy your life. But if you’re the type of person who wants to get a 400, 500-plus-pound squat, these little technique things are what’s going to make a big difference.

All right? Breathing techniques, where you place the bar, foot position, hand position, right? How you descend, how you rise, all these things.

All these technique things make a far bigger difference when you’re dealing with far bigger weights. In the same way, all these little technique things with T-SQL make a far bigger difference when you’re dealing with far bigger databases, data sets, far more challenging workloads, and all sorts of other stuff that make your job hard.

So please, stop telling people that the number of rows that you’re going to use is what makes a table variable okay or not. It’s all about how you’re going to use that table variable after it’s populated.

Okay? Because every time I hear someone say that, the smackin’ hand comes up. Just, you’ve got to fight it.

You’ve got to keep the smackin’ hand down. It’s like that movie Idle Hand, where it’s just like, all of a sudden, I have a knife. I don’t actually have a knife. It’s just the smackin’ hand.

The big smackin’ hand. Anyway. Thank you for watching. I promise tomorrow’s video will not be about table variables. I hope.

God, I pray it won’t be about table variables. I hope you enjoyed yourselves. I hope you finally learned that the number of rows you put into a table variable is not what makes using a table variable okay.

If you liked this video, please, you can do the thumbs up thing.

You can leave a nice comment. You can tell me how good I look after the gym. If you like this sort of SQL Server content, you can join the, let’s see, let’s get a fully updated number here.

All right. Here we go. The nearly 3,632 other people who have subscribed to this channel, so you can get a little ding every time I post one of these web gems for you.

All right. I’m going to go not think about table variables for a while. Might think about the bottom of a bottle of wine for a little bit. That’s about where I’m at.

Anyway, thank you for watching. Please stop telling people that the number of rows that you’re going to use is what makes using table variables okay. For the last damn time, it’s not okay.

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.

Erik Being Allergic To Slow SQL Server Queries

---

Thanks for watching!

Video Summary

In this video, I dive into the world of SQL Server performance troubleshooting by walking through a practical example using stored procedures and table variables. I explore how different compatibility levels can affect query plans and performance, demonstrating both the benefits and drawbacks of using table variables versus temporary tables. By experimenting with index creation and reordering queries, I show you how to optimize your code step-by-step to achieve better performance. This is a day in the life of what I do for a living—analyzing and improving SQL Server performance issues. Whether you’re just starting out or looking to refine your skills as a performance tuner, there are plenty of takeaways here that can help you tackle real-world challenges. So, if you enjoyed this video, don’t forget to give it a thumbs up and leave a comment with any questions or insights!

Full Transcript

Erik Darling here with Darling Data. I wasn’t actually sure that the record button worked on that. Things are a little touch and go for a minute. And I have finally gotten my awful nerd allergies subdued to the point where I think I can make it through an entire video without anything terrible happening. We’re going to see if that actually works. We’re going to see if that happens. If it doesn’t, this is going to happen. I don’t mind. I don’t get too attached to these things. I upload them. I delete them from my hard drive. They’re on YouTube. If anything ever happens to YouTube, it just wasn’t meant to be, I suppose. So in today’s video, we’re going to talk about all the different angles of performance troubleshooting stuff, right? Because that’s what I do for a living. I trouble shooting. I trouble shooting SQL Server performance issues. And, you know, I walk into a lot of situations where maybe not a lot of things were done in a great way. And it’s my job to unravel them. It’s my job to figure out what, what, you know, not every single thing that is unaligned to best practices needs to be fixed. But the ones that are causing the problems certainly do.

So let’s just pretend that we have a store procedure that we’re troubleshooting. We’re not actually going to run this as a store procedure. We’re just going to run this as some ad hoc code because it’s quite frankly just good enough. All right. So we have two queries here. We have one that inserts some rows into a table variable called way pops, which if you’re not if you’re not hipping with it, like old E. Darls over here, that’s what the kids say for way populars. Way pops. They don’t. It’s not. It’s not my kids saying go away, pops or random kids on the street thing.

Go away, pops. You’re weird and covered in tattoos and kids of our generation don’t care for tattoos. Way popular. And then what we do is we do some work with that table variable. In this case, I’ve chosen to cross apply to it with the select top one query as our sort of villain.

And, you know, you can you can call this sort of stuff unrealistic or unreasonable and you can say, I know all this stuff. But not everyone does. So and you notice down here that I have for the moment. I have the optimizer compatibility level hinted to 140.

Of course, because I’m using SQL Server Developer Edition because this Microsoft promises is not production data. You know, don’t audit me and start trying to charge me seven grand a core for this. It’s not going to be worth your time. I’ll fight you.

Because under Compat Level 150 in Developer Edition, which is equivalent to Enterprise Edition, table variables do get treated a little bit differently. We’re going to look at both ways. Don’t worry. So I’m going to take advantage of kind of a neat thing that SSMS added where I can execute the current batch.

Let’s shift alt and F5 for anyone playing along at home. And watch what this does. It’s going to turn the two queries under this booger green.

That’s not even like go Celtics green. That’s just like you’re sick green. And this is going to run for almost to say about 10 seconds.

And here’s what the query plans look like. Let’s bring that up a little bit so we can see both things in full detail. We have an insert into the table variable that all in all, all told, takes about 1.6 seconds. And then we have a query down here that takes about 8 seconds.

And we can see there’s not a lot of time spent in this part, 51 milliseconds. But this is where SQL Server chooses to say, Ah, this is where we need an index on the users table.

That’ll solve all our problems. All right, we can reduce this query’s impact by 58%. If only we had this index on the users table.

We can see looking at this thing that we spent, you know, 8 seconds in here. A lot of it’s scanning our table variable. That’s not a good time, is it?

Why would we spend 8 seconds doing that? SQL Server, come on now. Come on now, SQL Server. What’s wrong with you? So let’s just look at that real quick under compat level 150.

So under compat level 150, one thing is going to change. Exactly one thing. If I execute the current batch, it’s going to go a little bit faster.

Not like, you know, saves the day faster, but about twice as fast. So like 4 seconds instead of like 8 seconds. And this query in here did get better, right? Like this query is still about the same at 1.6 seconds.

But this query all of a sudden has signs that, well, perhaps this missing index request is not where we should be focusing our time. Perhaps an index is missing on our table variable because we have an index spool coming off our table variable. SQL Server has taken its just, you know, magnificent big brain and said, I’m going to take this temporary object and I’m going to create a temporary index in tempDB after I’ve scanned all the rows from our temporary object.

And then we’re going to use that index instead, which is not a terrible strategy because we’re down from 8 seconds to 2.5 seconds, right? So, you know, in this case, index spool kind of worked out in our favor. But that’s not good enough, right?

We still have this thing that runs for like, I don’t know, let’s see, 1.6 plus 2.3. I don’t know, let’s put 4 seconds. We don’t, it’s not good enough for us.

We are professional performance tuners. I don’t know who you think you’re messing with here. So the eager index pool is certainly a sign that we should have an index around somewhere. So let’s try indexing our table variable, right?

So we’re going to, since we can’t create an index after the fact, we’re going to create one in line. So let’s just say index C clustered user ID and then date. And then since we’re ordering by stuff by date descending down here, let’s give it a shot with date descending, the clustered index.

And we’re going to switch this back to 140 because, you know, not a lot of people I know are operating under 150. But we’re going to look at both because we are, if we are one thing here at Darling Data, we are fair. We are fair to everybody.

Mostly. Except scale our UDFs. Kind of unfair to them. So now let’s execute this and see what happens.

And, you know, that’s two seconds. So something got better, right? So down in this query, I mean, SQL Server is still like, we need an index on users, which is, you know, stupid at this point. 183 milliseconds.

And we spend about one second in here. But now this got a little bit worse. This got about 300 milliseconds slower. And the query plan for the insert changed, didn’t it? Right?

So let’s, what happened? We now have this sort in here. And this sort spills a little. We can tell by the little, little bangy thing here that this sort, this sort spilled off to disk. Maybe that’s why it’s slow.

Maybe it’s not. Maybe that’s just, you know, maybe that’s just how much time it took. The spill wasn’t too devastating, right? If we look at what happened in here. We spilled 3,900 pages.

That’s not so much. You know, I don’t think, I don’t think that’s what’s, I don’t think that’s what’s slowing us down tremendously. So I think we’re going to need to rethink how this insert is structured.

Right? Like, what are we doing in here? Right? Because now all of a sudden, so we have this index on the badges table.

Should probably, probably tell you about that. We have this index on the badges table. Right? Name, user ID, and date. And since we’re seeking to the name in the badges table here.

Right? Name and yada, yada, yada. Yeah. Well, that index should present everything in a reasonably orderly fashion. So let’s take a look at why we are now sorting things to get, put data into the clustered indexes.

Remember, indexes sort data, and it’s the kind of thing that, the kind of thing they do that makes other stuff fast. Remember, the query we have down here now with all our data nice and indexed and sorted is pretty good. Like, we still have a crappy estimate, but in this case, like, it’s, you know, such a small number of rows that we just don’t, we just don’t need to care too much about that one.

So, all right. How can we rewrite this insert in a way that will maybe be a little bit less painful? So, let’s take this, and let’s actually do this twice.

Actually, let’s see. What’s the best way to do this? I’m going to type in a demo and screw with all your heads. So now I just need to figure out how I want to do this.

So, let’s take this part. Let’s get rid of this. I’m going to need this right now. We’re going to say equals, and then we’re going to put that there. We’re going to say equals, and we’re going to put that there and say equals, and then we’re going to get crazy.

No, not that crazy. Union all. There we go.

Let’s put a couple union alls in between these things. And now let’s dandy up our where clauses. I’m going to put one there, and then we’re going to put one here, and we’re going to forget an end quote, and SQL prompt is going to put an extra one in, and then we’re going to take this one and put that there.

Now we can finally delete this, this empty shell of a query. All right. Let’s just see what happens.

Let’s see. Oh, no. We should do. There we go. Terminate your queries properly, everyone. So let’s do this.

And we’re still at like two seconds. And now instead of one sort, we have three sorts. There’s one.

There’s two. And this one in the middle still spills. So maybe our index definition is working against us. So let’s actually take the descending out of here.

Let’s see how far this gets us. So rerun this. And now, well, things got a little snappier.

All right. So the index order that we have for the badges table, right, we are now fully preserving that index order. All right.

We seek to the name in all three of these things. And because we seek separately to the name in all three, we now have the data in order by user ID and date descending. All right.

So 1.6 seconds. It’s still not great. But, you know, like this query is still going to be fast. You know, 180-something milliseconds. So let’s think about this a little bit differently.

All right. Let’s maybe think about our good old friend the temp table. All right. So let’s leave this where it is.

But I’m going to steal this part. And what I’m going to do is just down here, rather than declaring anything here, let’s create a table here. And let’s replace that with the pound or hash sign, whatever you want to call it.

And now let’s do an insert into that. Oh, I hit the wrong button. I don’t know.

Got all ahead of myself. Now let’s try that out. All right. So now we have two inserts, right? We’re just testing the inserts right now.

We’re going to test the insert into the table variable versus the insert into the temp table. And now let’s see how these two. Let’s drag race these two.

Oh, man. What did I do wrong? I forgot to drop table if exists. What an amateur. Drop table if exists.

Way pops. Go away, pops. All right. Let’s try that again. Error free this time.

It’s running. It did its thing. And let’s look at the difference between these two. So the first query, the insert into the table variable, takes about twice as long as the insert into the temp table.

Now, if you’re like me and this sort of thing interests you greatly, you might go on, you know, your SQL Server and you might fire up PerfView or Windows Performance Recorder and you might grab CPU stacks for when this stuff is happening. And you might look at flame graphs and all sorts of other things to figure out why one thing is so much different than the other. But the short answer is that the insert into an empty clustered index on a temp table gets all of like the fast loading minimal logging stuff that if you are a big fan of wine distributors from New Zealand, you may have read various blog posts about.

And that’s something that the table variable doesn’t get. It has never gotten. So in this case, something that I’ve talked about many times in the past is that table variables make queries ineligible for parallel execution plans.

That’s true all across all of everything, unless you play weird tricks on your table variable like inserting from like executing dynamic SQL or inserting from executing the store procedure, which I don’t recommend doing the second one because that has some weird locking stuff that happens. I recorded a video about that that you should probably watch and catch up on if you haven’t done that yet. But there’s no parallel plan difference here.

These are both single threaded plans. The only difference is that the data load into the temp table is far more optimized than the data load into the table variable. So let’s just get rid of the table variable.

We’re just going to quote this thing out now because we have once again determined that temp tables reign supreme when it comes to performance. Don’t worry. They don’t always.

Certainly times and places for table variables. It’s up to you to figure that out. I can’t tell you every time you should use something.

But so let’s see here. We’ve got our drop table. We’ve got our create table. We’ve got our insert. And then we need to fix this query a little bit, don’t we? We need to make that point to the temp table that we created.

And now let’s start this over a little bit. And let’s do our fancy trick here where we execute the current batch. And now we have everything rocking and rolling pretty okay, don’t we?

I think we did a pretty good job here. We got the initial insert query down from a couple seconds down to just under a second. And we got the final select down from eight seconds to just under 200 milliseconds.

And we didn’t need to create this index on the user’s table to get there, did we? So this is sort of a stock and standard approach to query tuning.

You fix little bits until you’ve got a whole thing that performs pretty well. All right. And this is what I do all day, every day for money.

Weird, right? Get a piece of code, make it go faster. It’s a darling date away.

So anyway, I hope you enjoyed yourselves. I hope you learned something. I’m moderately amazed. It’s actually sort of a miracle that I have not had one single allergic symptom during this entire, so far, 17 minutes.

I have been holding back a little bit because I’m afraid that if I do anything too sudden, I might just break into a sneezing fit. And you wouldn’t enjoy that. You wouldn’t like that at all.

So this is just a couple things that you can look at and do. Hopefully some lessons learned in here. For all you aspiring performance tuners out there on YouTube. So yeah, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you like this video, thumbs ups are appreciated as are nice comments. Boy, that Eric darling sure is spiffy.

Wish he was my dad. I would never tell him to go away pops. Something like that. If you like this sort of SQL Server content about performance tuning, which apparently some people do, you can join nearly 3,609 other satisfied customers of a free product and subscribe to the channel and get notified every single time I post something.

And normally when I post things, my voice doesn’t sound like scratchy box and I’m not awaiting some terrible allergic disaster befalling me in the middle of recording. So I promise the other videos are a little bit more lively.

So yeah, so we covered the like, the subscribe, the thanks. I think that’s about it. I do appreciate you spending your time choosing to learn from me.

And I will see you in the next video. All right. Thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Performance Issues With Denormalized Data In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of database normalization and its impact on SQL Server performance. With a bit of a groggy start due to an early morning battle with allergies, I explore how a lack of proper normalization can lead to unexpected query performance issues. Specifically, we look at the Stack Overflow POST table, where questions and answers have different types of relationships and constraints that are challenging for the SQL Server optimizer to understand without explicit guidance. Through practical examples and queries, I demonstrate how these issues manifest and provide insights on optimizing queries in such scenarios.

Full Transcript

Erik Darling here with Darling Data, and boy do I have a big smile on my face today for no good reason. This Monday morning hit like an absolute ton of bricks. I woke up at 3.30 in the morning with every allergy in my head conspiring against me, and it took a very long time to get things calmed to a point where I could fall back to sleep. So if this video is a little weird or low energy, well, you have a pretty good reason why there.

My head is swimming with strange, my eyes have just all these weird floaty things, whatever those things are called. So in today’s video, we’re going to talk a little bit more about how a lack of database normalization can lead to some very weird performance issues. We’ve talked about it a little bit before in the Stack Overflow database.

Again, coming back to the POST table, and why the POST table is so important to this is because within the POST table, we have questions and answers and some other things, but only like certain things can apply to questions or answers, right? Like only answers can have a parent ID because the question is the parent of the answer.

And likewise, only answers in the POST table can be accepted as an answer to a question. Questions can get a different type of voting on them. They can get up votes and down votes just like answers can, but only answers have an additional attribute where they can be marked as the answer.

Get a little green check mark and 15 extra life points to use at the Stack Overflow company store to buy, I don’t know, windbreakers and kayaks and zippos and stuff. It’s like the Marlboro Miles of yore. So let’s look at voting in questions and answers because this is an election year, I hear.

So someone’s going to have to vote, unfortunately. And if we look at what we’ve got in the POST table, there are eight different kinds of POST that you can have in there. The ones that we care about are the ones that make up the majority of the table, which are questions and answers.

Now, like I said before, questions and answers can all be voted on in different ways, but only answers can get voted on with a vote type ID of one, right? So only anything with a POST type ID of two can get a vote type ID of one, right?

Ups and downs can apply to either one. So that’s fine, but it can make life a little weird when you’re querying data in there. Now, like we’re just running straight queries here. So I can show you, I can show you like an easy example of this. But imagine that you had a front end that called the store procedure and passed some parameters in.

You could end up in an even tougher spot than what I’m going to show you with parameter sniffing also involved in this situation. But even without parameter sniffing, you can end up with some weird query plan stuff. So this first query where we’re looking for, you know, anything, any POST type ID of one, which remember again, that’s a question, that has a vote type ID of two.

Right? So the top 2,500 POST ordered by creation date, this finishes nice and quick, right? We get a rather easy, simple looking query plan here. We seek into the votes table. We find some rows. We do a nested loops join to the POST table.

We find some rows and we return the top 2,500 ordered by creation date descending out to our fabulous, wonderful end users. Now, imagine a scenario where, again, if you had a store procedure and like, you know, you know your data, and you love your data, and you give your data big hugs and kisses, and ruffle its hair before it goes to school every morning, gets on the school bus, right, and lucky pencil behind its ear.

Like, you could like, put some safeguards in here. And even like, you know, if you were a reasonably gifted front end designer, you could put some safeguards in here, so that this didn’t happen to you.

But a lot of folks have no idea that, A, this can be a problem, B, that this is a problem that they should sort of guard themselves against. So now we’re going to look at where data doesn’t exist. So now we’re going to look for a vote type ID of 1, like there, and a POST type ID of 1.

And remember, that combination cannot exist across these two tables. Alright, so if we run this, this is going to be significantly slower than the previous query, because SQL Server is just looking and looking and looking for nothing, rows that can’t possibly exist.

They cannot possibly occur in that way, unless we had terribly wrong, erroneous data hanging out in our tables. I mean, God, I think we’re too smart for that, aren’t we? Right?

So if someone, you know, just like either entered the wrong thing, or, you know, like maybe just has no idea, again, most end users are not walking data dictionaries that understand all of these things. Like, like what the numbers are, and what they’re actually looking for.

Especially when it comes to, you know, the vote types, post types, I mean, it’s all weird. So, the query plan ends up looking like this, where we have to, our top spins up, and it starts asking for rows over here, and we have to keep going to the votes table.

We spend almost two full seconds going to the votes table repeatedly saying, okay, give me rows, give me rows, give me rows, give me rows, give me rows, give me rows, I need to look for stuff.

And then we spend almost, well, actually, this is a little over five and a half seconds, seeking into the post table, looking for rows and rows and rows and rows and rows, only to have nothing return. No rows met our criteria.

Right? And like the optimizer, I mean, you know, God bless it, it made some reasonable guesses about, like how many rows might exist and might come together, but it was wrong. Right?

There was zero of 7500. It’s crazy. Crazy. Silly little optimizer. How dare you? But like I said, this sort of, this sort of like domain logic is really difficult to express to the optimizer. Like, I guess you could create an index view, maybe, and use the index view instead.

But that might mess you up even still with all this stuff. But like, there’s no like cross table constraint that does a good job of this stuff. Because the only way to do that is to use a scalar UDF.

And if you put a scalar UDF into a check constraint, you’re an awful person. Your heart is just rotten garbage. It’s, get away from databases.

I’m going to send you the rock collecting book. Don’t do that. Scalar UDFs in computed columns and check constraints have this nasty habit of making, if not every, then nearly every query that touches the table ineligible for a parallel execution plan.

And that can have some really, really serious detrimental effects on the speed and efficiency of your workload. Alright? So don’t, don’t do that.

Don’t be awful in that way. Now, this is partially due to the fact that we are asking for a top. We have requested a top. We, the same thing would happen if we used offset and fetch, because under the covers offset and fetch is just, just a little topperoo.

Uh, but this is all because using top and, you know, a number of other things internally sets a little role goal, for the optimizer. And the optimizer, uh, sort of uses that role, row goal to hedge its bets against how hard it’s going to be to find this limited number of rows.

Remember, like a row goal means I don’t need to find all the matching rows. I just need to find the top X number of matching rows. Right?

Which, if we run this query with the optimizer row goal disabled, this comes back very quickly finding no data. But we choose a much different query plan. Right?

I mean, granted, it doesn’t run for like eight seconds, which is good, but it’s a much, much different query plan. Right? We still have some nice little index seeks here and here, and the whole thing takes around 470 milliseconds. And that’s not bad, but that row goal internally just kind of made things get, get wonky weird.

Right? All right. SQL Server chose a silly little navigational nested loopy easy. Well, you know, just, uh, I think this is going to be super easy to find this data.

All this data exists. I’m just going to find it real quick type bet. But that, that query plan was not a very good bet for the table. Now, uh, I, I’ve, I’ve, I’ve demonstrated this little trick before, but you know, it might be worth, you know, going through again.

If you’re, you know, the type of person who’s not allowed to use hints in queries, like disable, optimize, or row goal, you could do something like this, where you nest some tops, and you get a very similar execution plan. Right?

So this one again, about 400, 500 milliseconds. Same bigger query plan, parallel, lots of seeking and hashing and stuff. But, uh, it finds the lack of matching rows quicker. And if we were to, uh, use the original sort of argument of vote type ID two down here for post type ID one, we would still be able to find all the rows that we cared about relatively quickly.

Doing that. So, uh, this one goes back to using sort of the original plan with just the really easy nested loopy deal in here. But, you know, uh, either way we get, we get the results very quickly.

And the same thing would happen, uh, with the query above with this thing, uh, the disable optimizer row goal hint applied to it. This would also finish rather quickly because SQL Server is able to find this data very fast based on the indexes that we have. So, if there’s a message in here, it’s largely that well normalized data causes fewer problems.

Um, uh, in situations where I see any sort of, like, parent-child relationship or hierarchical relationship, uh, all designed into a single table, uh, the, the, the, the, like, the actual, like, stuff that you know can only happen for a parent or a child or a question or an answer, or the top of the food chain versus the middle and bottom of the food chain, uh, gets very difficult to, uh, get into a table. Get across to the optimizer as far as just like, you know, what data lives and can, is eligible for stuff in other tables, right? Because that’s what kind of happened here is SQL Server just doesn’t know that only these type of, uh, only answers can be accepted as the answer, uh, to a question.

Questions can’t do that. And the vote type ID of one and the post type ID of one, the vote type ID of two and the post type ID of one means absolutely nothing to the optimizer. It’s just making guesses based on what data lives in there.

So, um, if you need help with that sort of thing, I am, I am a SQL Server consultant. I can, I can fix these sorts of issues, apparently, that people pay me for, usually. Uh, they do not pay me for YouTube videos.

These are, these are free for you to, uh, you know, help you understand what’s going on with your SQL Server a little bit better. Uh, in that, uh, in that vein, I did, I do, I do hope you learned something. I do hope you enjoyed yourselves.

I do hope that my, my energy level was acceptable for, uh, the limited amount of highly damaged sleep that I got last night. Uh, and I hope that my, my, my, my, my wording and thoughts were clear to you. So, we have all that stuff going for us.

Uh, if you like this video, nice comments, especially about my new haircut, uh, are appreciated. Uh, if you like this sort of SQL Server content, well, golly and gosh, you can join. Look, let me get the, let me get the most up-to-date number here.

You can join nearly 3,600. We are at 3,599 subscribers to this channel who are blessed with magnificent, angelic notifications from YouTube. Every single time I post one of these free little gems for you.

Ain’t that nice? Anyway, uh, I’m going to go back to my natural state of being slumped in a chair. Um, maybe I’m going to drink some more coffee.

That might be a good idea. Espresso? I’m sorry. Not, not just coffee. Espresso. I go, I, I’m, I go for the hard stuff. All right? I’m a, I’m a hard caffeine user when it comes to that. So, uh, I’m going to go do that now.

And probably, I don’t know, do all sorts of other dorky stuff like take antihistamines and use nasal sprays. Maybe, maybe I’ll do a neti pot. But I’m, I’m not going to film me doing a neti pot because, uh, I know, I know, I know how many, um, sort of sensory issues exist in my, my audience.

So there’ll be no live neti potting. Don’t worry. Anyway, um, thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

The Unpredictability Of SQL Server Performance

---

Thanks for watching!

Video Summary

In this video, I delve into the unpredictable nature of SQL Server performance issues, sharing a real-life client emergency that occurred on a Saturday afternoon. This experience highlights how slight changes in data or workload can significantly impact query plans and execution, making it challenging to maintain consistent performance. I illustrate these points with an example where two nearly identical queries exhibit vastly different performance due to subtle differences in literal values and statistics sampling. By discussing the nuances of index usage, modification queries, and locking strategies, I aim to provide insights into why SQL Server can behave unpredictably and how understanding these factors can help in troubleshooting and optimizing your database environment.

Full Transcript

Erik Darling here with Darling Data, and in today’s video I want to talk a little bit about the unpredictableness of SQL Server performance issues. And this came up because I actually dealt with a bit of a client emergency a little bit earlier today, and if you’re looking at your calendar, if you’re looking at the published date of this video, you’ll see that it is indeed Saturday at 3.10pm, which means that it’s a little bit more than a client. It means Erik Darling did some work on a weekend, which is a rarity, but you know, nice people pay me money and I try to be responsive. So there’s a couple different angles to this, and a lot of it has to do with making sure that your environment is sort of set up and maintained so that SQL Server has, is future-served, chances to screw-up, and chances to screw-up as possible. SQL Server is a big, gigantic, complicated piece of software, and slight changes here and there can have weird effects.

And even if you’re not making changes, your data most likely is changing. And as data changes, well, SQL Server makes adjustments. Things like auto-update stats, compile new query plans, and all sorts of other sort of things that, you know, seem like a good, you know, dynamic, like adjustment to whatever is happening with your data and your workload, but can really kind of make things hard to keep stable. And, you know, not to plug away too much at, you know, your humble YouTuber here, but this is the kind of thing that I do like helping people with. So let’s, let’s just take a look at these two queries right here. There is a one minute difference in one of these literal values. And if we run both of these queries, and we look at the query plans, one of them is going to do a clustered index scan, which takes 175, well, 177 milliseconds. And the other one’s going to do an index seek with a key lookup that takes 18 milliseconds.

Right? That’s a one minute difference there. And there’s a one row difference. And one SQL Server decided that it was going to tip that point. The one that does the clustered index scan estimated 13,496 rows. And the one that does a key lookup estimated, well, that was an improperly aligned blob, but the one that does a key lookup asked for 13,000, estimated 13,495 rows. Right? And I know you’re looking at this, and you see, you see this green text here. And this green text is asking for an index that would fully cover the query. Right? So it wants to include display name, display name is not in the index up here that I created. So asking for display name is really the thing that tips SQL Server over. If we had that green text index, SQL Server wouldn’t really have much of a choice. Like that key lookup just wouldn’t be an option.

Or rather, it would, I mean, I guess it would be an option if there were another index. But SQL Server would narrow down its choices to that one index that has everything in it a lot easier. Right? So it would just say, well, there’s no lookup to be done. So this is clearly the cheapest way. There’s not going to be that costing choice. So that’s kind of like one part of it. And the other part of it is that when modification queries are on, modification queries need love and indexes too.

They need to have good ways to find data. And one thing that’s terribly frustrating, before we move on, one thing that’s terribly frustrating is like I’ve, I’ve, I’ve, I’ve gone over this demo a few times and every time that I have dropped and redone this index, that the time and date that I need to put in here to get that one row plan switch over has been different.

So like different stats, stats, samplings and stuff can really make a big difference here. Like if I, if I rerun all this stuff and I rerun these two queries, there is a very good chance that either they’ll both get this, that they’ll both get the same plan and we won’t see that tipping point difference like we did before. And we, we don’t with that, with that, with that index, right? Where we resampled statistics, look what happened.

Now for both of them, we get a clustered index scan. And now these numbers are still have a one row difference, but they weren’t quite low enough for a SQL Server to, to, to go for the key lookup plan. Right? Neither one of them met whatever internal tipping point threshold SQL Server had for itself.

And that, that’s terribly frustrating. Stabilizing stuff like this is hard. Right? Figuring out that this was the problem is hard. Figuring out how to solve the problem is hard. Right? Because there’s not all, it’s not always as obvious as an index when a plan completely and drastically changes.

This is just a simple example. There are, you know, the queries that I deal with are much bigger, much more complex. And there are much bigger changes that happen just because of random set standpoint. But for instance, like a query that used to have a fairly, that used to have a cost that made it eligible for a parallel plan.

Something happened. And all of a sudden SQL Server is like, well, this, this, I think this thing’s going to cost like two query bucks. And you can’t just go drop cost threshold for parallelism down to one so that this query plan, this new query plan is like parallel all of a sudden. That would drastically change the entire workload.

Another thing that gets weird is with locking. So with just this index in place, if I run this and I do this update, SQL Server on the first run chooses to lock the entire object. Right? SQL Server said, nope, don’t want to manage a bunch of little locks.

That’s not fun. We’re going to lock the whole object. Right? And if I run this again, SQL Server chooses a different strategy. I ran the same update twice in a row and now SQL Server said, oh, I changed my mind.

Now we’re just going to take a bunch of exclusive key locks. Right? And then if I, if I come down, if I come down here a little bit and I’m going to lose a little bit of how the sausage was made going out there. But if I, if I come and rebuild the, that users table and I run this again, we are back to locking the whole object.

Right? And then if I run that update again, we’re back to using the key lock. So it’s like just weird stuff happens constantly all the time. Weird, like weird little things change.

Weird little bids and bobs just line up differently. And plans change and locking changes and all sorts of other things just kind of get really weird. And you can just have oddball problems.

Of course, if we add in a good index for this modification to query, this modification query is, we don’t even necessarily need creation date in here. That’s just sort of an artifact of when I copied and pasted this index down lower. Then we’ll get pretty consistent results from this where we’ll always take the key locks.

Right? Because SQL Server will have a good index that it can seek to, to find the, the, the reputation rows that it wants to update. So if you’re out there and you’re, you’re having trouble with these sorts of tiny little things, having profound cascading effects on SQL Server performance, if this is really gumming up your workload, well, I’m, I’m, I’m here for you.

This is the, this is the kind of stuff that I like doing. You know, it’s like a, like, like a coach likes telling you what’s wrong with your squat form. I like what’s telling, I like telling you what’s wrong with your SQL form.

So if you, if you do need this kind of help with stuff, um, you know, I’m, I’m here. I’m ready to work. I’ve got my, got my hammers and shovels, uh, a couple of flamethrowers, you know, some grenades.

We got some, we got some stuff that, uh, we got some, we got some good hardware. So, uh, anyway, uh, I’m going to go not work on a Saturday now. And, uh, we’re going to, yeah, I think, I think, I do believe it’s martini time.

It’s close enough. So, anyway, uh, thank you for watching. I hope you learned something.

I hope that, I hope that you, I genuinely hope that you don’t have these kind of SQL Server problems. But if you do, uh, young and handsome consultants are standing by. And, um, yeah, uh, if you like this video, uh, appropriately placed thumbs are, are appreciated.

Uh, as are nice comments, especially if you like my new haircut. I got one. My head’s not big and fuzzy anymore.

My head’s nice and sleek and streamlined and sort of aerodynamic. I look like I’m, I look like I’m ready to, like, be a, be a general or something. I could lead an army with this haircut.

Uh, and if you like this kind of SQL Server content, or if you just need, if you, if you see this kind of SQL Server content, and you’re like, wow, I, I, I, I, I identify with that. Well, um, I guess you, you could subscribe to the channel, which is free, but, um, that, that, that might not solve all your problems unless, unless you, unless you have a lot of time on your hands.

So, anyway, uh, thank you for watching. And, and please, please do go enjoy, uh, your Saturday. Hopefully you didn’t have any weird emergencies today.

And, um, I, I do hope that if, if you are the type of person who, who partakes in, uh, alcoholic beverages, that, uh, your martinis are cold and stiff. Way, way God intended. All right.

Uh, thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Why Some Indexes Create Slower Than Others In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into why certain indexes in SQL Server can create much more slowly than others. After waking up feeling unusually sleepy despite my usual morning caffeine fix, we decided to use that extra energy to explore the nuances of index builds on different columns. I demonstrate how non-selective columns lead to uneven thread distribution and significantly longer build times compared to selective columns. By examining the properties of these indexes, we uncover fascinating insights into SQL Server’s indexing behavior, particularly when using standard edition versus developer edition. The video also touches on a humorous anecdote about Microsoft support, highlighting the importance of accurate information in troubleshooting complex issues. Overall, this session provides valuable lessons for database administrators and developers looking to optimize their indexing strategies.

Full Transcript

Erik Darling here with Darling Data and a little sleepy. I don’t know why. I woke up this morning, shot out of a cannon, ready to go. And for some reason after drinking my customary two double shots of espresso, I got to feeling sleepy. I’m not really sure what the story with that is. It’s kind of a strange thing when your body reacts. the opposite way to something that usually has a pretty good effect. Anyway, today’s video, we’re going to talk about why some indexes create far more slowly than others. Now, if you’re on standard edition, this likely does not apply to you because you cannot create indexes with parallel threads. You are limited to offline index builds with a single thread, and you’re not going to have these problems because you just don’t have multiple threads to see this sort of stuff. So, if you’re on standard edition, I don’t know, you can create your indexes really slowly with a single thread or you can just watch this video. I don’t know, like maybe install developer edition to see how grand being able to create indexes quickly or more quickly is usually.

Sometimes for the most part. Now, what we’re going to do is we’re going to start, well, I’ve already created them because, I mean, it does, if we look under the armpit down here, let me zoom in under the armpit. I want to make sure that you get the full underarm experience from me. Where’s that time thing? Where are you? Where are you hiding from me? There we go. One minute and 18 seconds to create all these. And I didn’t want to sit there and make you wait for these things to pop up. Before we look at the plans for these, though, what I want to point out is that there’s a link up at the top there at my website, different index build strategies for SQL Server. And that’s not a post that, I mean, I did write the post, but really it’s a collection of links from 2006, back when Microsoft actually wrote useful things about SQL Server, not just like bland marketing material about how new feature is going to drive modernization data, blah, blah, blah, blah, blah, blah, blah. This was actually useful technical information. Good stuff. So there’s like six or seven posts up there at that link to old Microsoft posts about different index build strategies for SQL Server. And some of them you’ll see in here if you read all that stuff.

This link will be in the show notes as usual. So I’ll stick that in the old YouTube description. And without further ado, let’s look at some stuff. So what I just want to get the first four things up here. There’s four in total. But we have four indexes that got created, two of them on the votes table and two of them on the post table. And what I want to show you really quickly before we move on is the columns that these indexes got created on. So the vote type ID column is very not selective. There are like majority of vote types or upvotes or downvotes.

There are some, there’s a decent amount of question marked or rather answer marked as green check mark the answer in there. But then there’s a bunch of other things for like spam, offensive, whatever. And so it’s just not a terribly selective bunch of data. The second, the second index we created was on a column called post ID and post ID is much more selective. Granted, there are some posts with way more votes than others. Right. So it’s like skewed data, but it’s pretty selective generally.

For the post table, we did almost the same thing. We created one index on a very not selective column post type ID because most, most post types are going to be questions or answers. And then there’s like a smattering of other stuff in the table as well. And the next one is on owner user ID. Now, owner user ID is pretty similar to post ID up here. And that, you know, there’s going to be some skew towards users who ask more questions or post more answers. But in general, this is a fairly selective column.

At the far outlier of this is John Skeet, who in the 2013 version of Stack Overflow has around 27,000 or so answers or questions and answers combined. I think mostly answers, to be honest with you. I don’t think John Skeet has ever asked a question from thinking about things logically. At least a question that was not rhetorical. He’s, you know, one of those. Maybe he’s asked, you know, maybe he’s asked questions of other people in interviews, but, you know, I don’t think he’s ever asked a question he didn’t know the answer to.

Fascinating, fascinating way to live life. So let’s look at kind of what happens in here. Now, we’re going to go get the properties of all these things because that’s where all the helpful stuff lives. And we’re just going to expand the rows red thing a little bit. We can expand this too, but it’s not going to really make much of a difference. If you’ve watched other videos of mine, you know that this is the number of rows that a thread handled, and this is the number of rows that a thread produced.

So if we had, like, we don’t have a filter on this index. We had a filter on this index. These threads might have produced much lower numbers. But since we don’t have a filter or anything on here, these threads up here will produce the same number of rows that were read down here. All right. So good to know. Good to know. Good things to know.

And I don’t know what that accent was. It was very nonspecific. I wasn’t making fun of anyone. It was just a voice that came out of my body. Maybe I’m possessed. Maybe I’m just exhausted. Who knows? But if you look at the sort for this non-selective query, this is where things get a little jangly.

All right. If you look at all this stuff, some of these threads handled way more work than others. All right. This one handled a whole bunch. This one handled, I guess, a decent one. This one handled the most, though. If we, like, drew a line down under this 8, let’s see if I can draw a straight line with this thing.

Pretty good. Not bad. I haven’t had my morning drink yet, so it’s a little squiggly. A little shaky. But if we look at this, like, this thread number one handled far and away the most.

Like, no other number is quite as long as thread number one. And then thread number three did, like, nothing. And some of these handled, like, way fewer rows than others.

And that almost matches the distribution of data in the column. And I’m going to show you that in a second. And then if we look at the, let’s stick with the sort, because the sort seems to be where the interesting stuff happens.

And here, if we look at the sort for the index that got created on post ID, the numbers are much, much more even in here. All right. This is a much easier distribution of data. And if we pay attention to the times.

Go away, tooltip. No one needs your nonsense here. It took 40 seconds to create this index that leads with vote type ID. And it took about 18 seconds to create this index that leads with post type ID.

This is the same number of rows going into there. Right. There’s no filter on either of these. And the only thing that’s really different is the distribution of data.

Right. Like, even if you think about it, vote type ID is an integer. But it’s only ever, like, I think there are only, like, eight vote types. Right. So, like, you really only have the number one through eight.

If you have post ID, it’s also an integer. But it’s, like, you know, far bigger integers. So it’s not like there’s a, integers are all four bytes anyway. So it’s not like there’s a big difference in, like, the type of data we’re creating the index on.

It’s just the distribution that makes creating some indexes a lot slower. Right. And we’ll see almost the same pattern if we look at what happened in the index creation for the votes, for the post table. Sorry. If we look at what happened over here.

Holy cow. We only use three threads. And look at this distribution. You could think of that and that as questions and answers. And this is everything else.

Right. So we have about six million questions in the post table. They all ended up on one thread. We have about 11 million answers in the post table. They ended up in one thread. We have about 50,000 other things in the post table. And they all ended up on a third thread.

Now, you might be looking at this and saying, why in God’s name did SQL Server only use three threads to do this? Why wouldn’t we break these things up further? Why wouldn’t we use more threads?

Why wouldn’t we do that? And so you might even think about doing something insane like adding a max stop 8 hint to the index build. The index create script, sorry.

And you would be sorely disappointed to learn that max stop is not min dop. Now, it’s a short digression here. I was on a customer call recently where they had a support ticket open with Microsoft.

And when I say with Microsoft, I say that very loosely. Because Microsoft support is not all just Microsoft employees. Microsoft farms out support to like two or three other companies.

And this was a gentleman who worked for one of those two or three other companies. And the problem generally was that, well, the customer really wanted to get a parallel execution plan for this one query. They didn’t want to change any settings.

They didn’t want to add any hints to the query. They kept, you know, seeing all this stuff. Well, we want to get a parallel plan. And the gentleman from the third party support group working for Microsoft kept telling them, well, just try it with a max stop 8 hint. And I kept having to tell this gentleman that max stop is the maximum dop, but it is not the minimum dop.

If you can add a max stop 8 hint to anything, it’s not going to make that query go parallel. It’s going to tell SQL Server that that query can’t go more parallel than 8. And he refused to believe me.

Now, I’m just going to throw this out there. If you work for a company that’s paying Microsoft for support and you’re unhappy with it, you should talk to me instead. Because, at least for the client that I was working with, they paid $75,000 a year to Microsoft for support.

And this is the type of person who they would get on a call with. Someone with about 18 months of experience with SQL Server doesn’t know their butt from their elbow. I’m going to keep that one family friendly just in case you want to show that to your boss.

And they just don’t know anything. They’re, again, like 18 months of experience max with SQL Server. So, for about the price of one, for a little bit less than the cost of one core of Enterprise Edition, you could have a whole lot of help from me who actually knows something about SQL Server.

Wouldn’t that be grand? So, if we look, so this query, sorry, this index create only uses three threads, which is a little depressing. Max stop 8 doesn’t help because it doesn’t, again, doesn’t set the minimum dop.

There’s no min dop hint, much as I wish there was a min dop hint. We don’t get that. There’s a trace flag and there’s a use hint.

They’re both still technically, like, legally unsupported by Microsoft. But they do work, but they still don’t set a minimum dop. You can use the trace flag or the use hint with a maximum dop, but there’s no, like, you must use eight threads for this.

So, that’s a little bit silly, ain’t it? Anyway, if we look at the second index create for the post table, again, much more evenly distributed. Right?

Everything in there, pretty evenly distributed. Look at all those 21s all the way down. Very, very nice. And there’s, again, a pretty significant timing difference between creating the index on non-selective data versus creating the index on selective data.

Right? Now, there’s a big difference between the votes table and the post table. The votes table is about 53 million rows. The post table is about 17 million rows. So, there are significant timing differences between the two tables.

But within the two tables, creating the indexes with a non-selective leading column can really increase the amount of time you spend building that index. First, creating the index on a selective column first because you just get better distribution in there.

Now, to kind of round things out with this, for this video, I want to show you a couple things down here. Now, when we looked at the thread distribution for the non-selective columns in both the votes table and the post table, this is what the runtime counters per thread looked like.

Right? And I’ve omitted thread zero here because thread zero did not receive any rows. Thread zero generally does not receive any rows. And if we run this query, and we tuck this down a little bit, you’re going to see some numbers that look kind of familiar down here that you also see up here.

Now, I don’t have it completely mentally mapped out in my head, as I probably should. Let’s tuck that up a little bit. Oh, not that high.

Come on. Get down. There we go. Now we can see everything. But if you look… Oh, that didn’t do it. Come on, baby. If you look down here, we can see 733 here. And we can see 733 here.

Let’s see. There’s a 3511 733 here. There’s a 3511 733 here. Let’s see.

Do we have a 41? No, we don’t. Do we have a 203? We do. There’s a 2039371 there. Yeah, there’s a 2039371 there.

And then, you know, there’s a… Let’s see. Do we have an 818? Do we have an 818? Do we have 16? We have an 818477 up here. Look, this is very exciting stuff.

We have an 818477 up here. We have an 818477 right here. So there are a bunch of these threads that only got to work on like a single vote type ID. Some of them spread out a little bit more.

And some of them, you know, just kind of did their own thing. I think… Oh, there’s another good one. Look at this. 3-5-7-3-4-5-0.

3-5-7-3-4-5-0. So you get… Some of these threads did work on just one specific vote type ID. Other ones, you know, again, SQL Server kind of spread that out a little bit.

So that was nice of SQL Server, I suppose. Now, where things get interesting, too, is… And I kind of…

I kind of spoiled this one earlier when I talked about it, but we’ll do it anyway. If we were on this query on the post table, and we look at the post type IDs versus the actual rows, here’s post type ID 2 with 11 million.

Right there. Those are all your answers. There’s post type ID 1 with about 6 million questions. And there’s that there.

And then if we look at this number, 50597, I bet that would just about add up to what you have in here. Right?

So we’ve got a couple 25,000s plus a little. We’ve got a 167, a 166, a 4, and a 2. We’re going to bet if you added those numbers up, they would add up to 50597. So the three threads in here did sort of an unfortunate amount of work.

Like, I think what’s unfortunate about it is, like, if you think about what happened up here, like, SQL Server broke up, like, some of the bigger vote type IDs across multiple threads. It did not do that down here.

Right? We only got three threads. Right? Maybe, like, a fourth thread could have evened this out. And then we could have had, like, a 50,000, and then, like, a 5.5 million, and another 5.5 million, and then a 6 million.

That would have been a little bit nicer. But SQL Server did not choose that. So anyway, if you’re ever creating indexes, and you wonder why some indexes kind of create slower than others, this might be why.

You might be creating some indexes with leading non-selective columns, which sometimes you’ve got to do. Sometimes that’s the wise thing to do. Sometimes that’s what your where clause is on.

You’ve got to respect that where clause. And other times you might be creating indexes on fairly selective leading columns, and you might think, wow, that index created a lot faster.

And this would probably explain why. If you’re ever incredibly curious, and you get the actual execution plan for your index create statements, you might see stuff just like this, very uneven row distributions across threads.

Maybe, like in the case of the post table, you might not see very many threads involved at all. And that could also be part of it. And remember, especially if you are working for a Microsoft third-party support vendor out there, MacStop is not MinDop.

And again, if you are overpaying Microsoft for terrible support, I’m your mans. I can certainly do better than add a MacStop hint to make a query go parallel, because that’s a sure sign of a lack of knowledge.

I hope you enjoyed yourselves. Lord knows I did. I think I kind of pepped up a little bit as I was talking.

Maybe just sitting at my desk was what was making me a little sluggish feeling. Anyway, I hope you learned something. If you like this video, I do like thumbs-ups in appropriate places, and I do like nice comments.

And if you like this sort of SQL Server content, if you like learning more, if you want to know more about SQL Server than Microsoft Support does, and you want to keep watching these videos, a great way to get notified is to subscribe to my channel.

If you do that, you will join… Hang on, I’ve got to get the official number as of this recording. You will join nearly 3,574 other people and celebrating every time I post a video.

That would be fantastic, wouldn’t it? Wouldn’t that be just lovely for you? You wouldn’t have to keep refreshing the page. You would just get a little notification that said, Erik Darling did a thing.

And then you would be able to watch the thing. And you would be a smarter, happier, more well-informed person for doing so. Anyway, I’m going to…

I’ve got stuff to do. Actually, I’m finally getting a haircut in about a half hour. So I should probably prepare myself for that eventuality. And in the next video I record, I’m going to not look like some sort of, like, I don’t know, weird nerd.

It’s a curly Q thing over here. I don’t care that I’m… I don’t care that my hair is thinning. I care that my hair waves when I don’t want it to. I’m in my mid-40s.

My hair is probably going to get thin unless I intervene in some way. And I’d rather just shave my head. And the reason I’d rather shave my head because when you’re a guy with a shaved head, when it’s not due to illness, there is a tremendous amount of responsibility on you to maintain a reasonable weight because you don’t want to have a big face with a shaved head.

At least I don’t. It makes me look very… I look like Dr. Evil if I get chubby with a shaved head. So you don’t want to see that. So anyway, I’m going to go do my self-improvement stuff and I will see you in the next video.

Thank you, truly, from the bottom of my heart, for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

How Poor Cardinality Estimates Can Lead To Worse Blocking And Deadlocking In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into how poor cardinality estimates can exacerbate locking situations in SQL Server, particularly when dealing with updates and modifications. I demonstrate this through a series of queries and stored procedures, showing how bad cardinality estimates lead to excessive locks on the entire table instead of using an index effectively. By creating realistic examples and explaining the nuances of cardinality estimation, I highlight the importance of ensuring good indexes are in place and the potential pitfalls of using local variables. The video concludes with practical advice on fixing these issues, including the use of option recompile hints and parameterized dynamic SQL.

Full Transcript

Erik Darling here with Darling Data, trying to talk in a way that it would be really easy to train the AI off of. Hopefully someday I will be AI training worthy. Training AI worthy? I don’t know. Whatever they call it when AI steals your stuff, I guess. And in today’s video, we’re going to talk about how bad cardinality estimates can make locking situations worse in SQL Server. Alright. So, hope you’re ready. I realized that I don’t have a good affectionate name for all my viewers out there, my watchers. Maybe watchers is the right word. All 3,562 of you as of the recording of this video. Who knows? Maybe that will go up while I’m recording. You never can tell. I don’t want to call you my data darlings. That might get me in trouble with the misses. And I don’t want to call you data heads because it sounds like I’m about to say something a little bit more rude. So, we’re going to, I don’t know, we’re going to have to think about that. If you have a good idea, leave a comment.

Because I would love to know how you would like me to refer to you. Because I can’t possibly learn all your names. So, we’re going to have to come to some sort of group descriptor. Anyway, I’ve got this lovely index on the post table. And, you know, it’s good enough to prove a point. It’s not anything overly fancy. As soon as you get too fancy in demos, they start failing and people start staring at you like you’re an idiot. And what I want to show you is, well, how bad cardinality can make locking worse in SQL Server.

So, this is an easy one for SQL Server, right? This query right in here, very easy. We’re going to run this and we’re going to roll it back immediately because we don’t need to keep any of these changes. And I’m going to run this update in the transaction. Within the transaction, I’m going to select data out of my little helper function there called What’s Up Locks. It’s available at my GitHub repo somewhere. And what this is going to do is show us all the locks that were taken by this update.

All right. So, we go and we run this. Lo and behold, it runs pretty quickly. And if we zoom in over here, let me frame that up real nice for everyone at home. We see this request mode column right here. But notice only one of these rows has an X by it in itself. All right. That means this is the thing that actually took the locks. And the thing that actually took the locks was on 167 keys.

All right. So, that’s pretty easy. It’s pretty low, pretty lightweight. All right. It’s a serviceable number of locks. This thing finished pretty quickly. We didn’t have to worry too much about anything at all. All right. Pretty okay in here.

All right. And let’s go back to the query plan real quick. SQL Server started with a seek over here. And started with a very good cardinality estimate of 167.

Now, the thing that is important to note here is that when you start with a seek, you are most likely going to start with key locks. You start with a scan, you are most likely going to start with page locks. If you, I don’t know, I don’t think you can really start with much of those.

Unless you’re using a heap or something. But rid locks, maybe. Get some rid locks in your life.

And so, sort of generalized sort of advice there. The storage engine sees seeks low number of keys. Says, hey, key locks. And then from row or page, you might move up to an object level lock, which we’ll see in a minute.

But you will not go from row to page to object. You just go from row or page to object. Assuming that SQL Server finds valid reason to engage in an attempt at lock escalation and is successful.

If there are any competing locks on the table, it may not be successful. Now, the thing that almost no one, well, let’s see, what’s a good way to put this? The thing that almost everyone takes for granted is that their end users are not walking data dictionaries.

They do not have numerical meanings for different things printed out at their desk. We’re going to just look stuff up to make your job easier. PostTypeID equals three isn’t going to mean much to anyone at home.

No one’s going to memorize all the different post types in the post types table. It’s just not a thing that they’re going to do. So what they do know, usually, is the type of post that they want to find.

It could be question, it could be answer. For the sake of this demonstration, we’re going to be looking for wiki posts because those hit a relatively small number of rows. Posts and questions are, like, most of the table.

All the other things are the rest of the table, but, like, there’s 17 million rows in the post table. Like, 6 million are questions. Like, 11 million are answers.

And there’s, like, a few hundred thousand of the other stuff. But look what happens with this query. This is a real gosh darn shame what happens here. This is not a very quick finishing query at all, is it?

Not at all. It’s just four seconds. Right? Well, actually, how long did that take? Well, it was 87 milliseconds.

What’s your problem? What’s your gosh darn problem? Right? 1.8 seconds in here doing all this stuff. And, of course, 1.8 seconds over here.

Notice that we did not use our nice narrow little index that we created on the post table, on the post type ID column. We ignored it. SQL Server says, no, I’m not using that index.

Because I don’t want to do key lookups. If you want me to use this index, you have to put the column that you’re updating in the index. Good luck with that later.

We’ll see how that goes. But the reason why SQL Server doesn’t use it is because SQL Server makes a real crappy guess at cardinality. Right?

If you kind of look a little bit more closely about what happens in this query plan, this parallel distribute streams uses a partitioning type of broadcast. What broadcast means is that this one row gets sent out to eight threads because we’re running it max.8.

Right? We get one row from here. This thing turns that one row into eight copies of one row. And then when we come down here in the clustered index, we have some…

Let me get both of these things open so you can see a little bit better. There we go. We have eight threads in here that act cooperatively to scan all 17 million rows.

Right? These numbers in here will add up to 17 million. And then up in this section, we have the actual number of rows that got produced by each of those threads after that post type ID filter was applied. So, you know, there’s a decent spread here.

Nothing’s too, too off. I guess the 11 is a little bit low. But 30, 11, 19. This will add up to the 175 rows that we get here. So, all well and good.

And then when we finally do our join over here, that gets whittled down to 167 rows. Right? So, a lot of extra work.

And what’s kind of funny is that even if we tell SQL Server to use our index, right? If we say SQL Server, we created an index. It’s perfectly usable.

You’re being a goofball. Use our index. We still get, well, actually, you know, I should repeat myself. But if you were paying close attention to the output of this from the first demo, this does indeed lock the entire table. Right?

So, we get still over here a rather poor cardinality estimate down here. 167 of 2142770. So, that’s a seven-digit number.

So, I think 2.1 million rows are going to get hit over here. So, we don’t use it. And, of course, when SQL Server is like, holy cow, that’s a lot of locks. It escalates those up to the object.

And I’m just going to, just to make sure that you don’t think I’m being goofy here. I’m going to, that query did run a lot faster. It still took a lot of locks, though. So, if we rerun this, this is the one that takes about two seconds or so, I guess. De-da-de-dee.

This one also locks the entire object. We have this X locked. Let X lock at the object level. We lock the whole gosh darn thing. So, in practice, a lot of people will experiment with joins, with modifications. And, that’s not maybe so great.

Xist tends to work a bit better, unless you need to, like, join a table to another table to update the columns in one table to the columns in another table. Then, Xist does you no good.

But, like, in this case, we could use Xist. Maybe it would turn out a little bit better. But, that’s usually not the way most people are going to write that query the first time. Now, going back to our users not being data dictionaries, right? What we’re going to do is we’re going to create a store, I’m going to say, an incredibly realistic store procedure.

It’s like uncanny valley levels of realism for this store procedure, where we’re going to ask our users to supply a post type. We’re going to look that post type up for them.

And, then we are going to do an update based on the post type that we find, the post type ID that we find for them, right? So, let’s create this store procedure.

Let’s make sure we have this created, because there’s another copy of that that has a little fix for it. So, if we run this, and it’s going to do roughly the same thing as all the other ones, what do we get?

We get this big honking object level lock with the local variable in effect. The reason why is because SQL Server makes terrible guesses when we use local variables. Whomp and whomp.

167 out of 2.1 million. And, again, we are not using our nice narrow nonclustered index. We are using our big honking clustered index. All right.

SQL Server has said no. No to the nonclustered index. Yes to the clustered index. And SQL Server is once-ing again. Once-ing. Ah!

It’s a good time. Once-ing. Where’d that come from? Once-ing again.

Asking for an index that not only leads with our post type ID column, but also includes the column we are attempting to update, which is maybe not the greatest, the grandest of ideas.

So, of course, you know, local variables cause all sorts of problems. You may run into cardinality estimation issues for all sorts of other reasons, but local variables are just a very easy and convenient way to show you how crappy cardinality can get when you use them.

Of course, there’s a very easy fix for this, right? And what we’re going to do is just create or alter our store procedure, and we’re just going to stick an option recompile at the end, right?

And I just want to show you the difference here. It’s not really anything incredibly groundbreaking. Yeah. Local variables, option recompile.

That’s like the first step in the decision tree. Figure out how bad this thing is. Just run and do that. All right. So if we run this, what we’ll see is the same behavior as the, when we used a inlined literal value where we have the 167 exclusive locks, sorry, 167 exclusive key locks here, and then, you know, some other intent exclusive locks and other places that don’t really do anything because they don’t actually take the locks.

The only one that actually takes the locks is the one that has X by it. So when you’re writing modification queries, be very, very careful. Make sure that they have good, make sure that you have good indexes in place so that your queries can find the data they’re looking for to modify.

If you find yourself needing to use local variables, some ways to fix problems with them are, of course, option recompile hints using parameterized dynamic SQL or an enter a store procedure call to a store procedure that will actually do the update because those will treat whatever local variable you create outside of them as a parameter when you pass it into them.

If you’re using table variables for whatever reason, you know, they’re in memory only, right? Just try using a temp table instead. Usually get better cardinality estimates.

Table variables don’t get any sort of local histogram to the data that shows the data distributions in there, and that can cause some pretty big problems when you start joining them off to other tables. If you have, I don’t know, poorly written queries, overly complex join and where clauses, maybe out-of-date stats, you can hire me to do most of that stuff.

I’ll even update statistics for you if you feel like you need me to. One thing that is sometimes good to mess with, it wouldn’t, I tried it every which way in this demo, but sometimes changing the cardinality estimation model where your queries can be useful.

You have the new one and the legacy one. I have a strong preference for the legacy cardinality estimator for most of the things that I do. A lot of the demos that I write are using the new cardinality estimator where things just, you know, fly off the rails in a lot of ways.

Then there are other things that you might be doing in your queries that the optimizer does not reason terribly well with. If you have scalar-valued functions in a where clause or a join predicate, or if you have multi-statement table-valued functions with the return-a-table variable, you’re cross-applying or joining to those, you can hire me to rewrite those because I do that for fun.

Money. I do that for money so I can have fun. Keep. And of course, if you are in the midst of a modification query, if you are shredding XML or JSON and attempting to use some sort of join or where predicate or some sort of isolating predicate, you can also hire me to fix that because I do that sort of stuff also for money fun.

So anyway, we’ve learned today that poor cardinality estimates can lead to more intrusive locking.

Don’t let the intrusive locking win. Fix your queries so that when you modify data, you take as few locks as possible. You don’t try to escalate those locks all the time.

And then you cause all sorts of blocking and deadlocking issues. Of course, if you have all sorts of blocking and deadlocking issues, you can also hire me with money to fix that so I can have fun doing this.

All right? Good deal. All right? Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll hire me.

I don’t know why I’m pushing that so hard. I don’t know. It’s like I have vacation coming up. The more people who hire me, the harder it is to take vacation.

If you like this video, thumbs ups are nice. Just make sure that you put them, you thumbs up somewhere appropriate.

Nice comments are nice. Do you like those? Kissy face emojis. Always a winner. And if you like this sort of SQL Server content, you can subscribe to my channel.

So that, hold on, let’s drum roll this. So that you can join nearly 3,563 other lucky people who get notified when these videos are published.

So, yeah, that’s all that. Anyway, I’m going to go work because fun is over. I’ve had my designated playtime.

I’ve got my yard time today. So now it’s time to go back to work. Thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.