Tag: how to create temp table in sql

A Little About Intelligent Query Processing Limitations In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into the complexities and limitations of Microsoft’s intelligent query processing features in SQL Server. Erik Darling shares his experiences working with clients where these advanced features often fail to kick in when they should, leading to suboptimal query plans. I illustrate this through examples involving batch mode on rowstore and adaptive joins, highlighting how various factors such as the version of SQL Server, compatibility levels, and even scalar UDFs can impact performance optimization. The video also delves into using extended events to track down these issues, offering a glimpse into the intricate heuristics that determine whether or not these features are utilized.

Full Transcript

Erik Darling here with Darling Data. I have no funny introduction to this video at all. Nothing. I got nothing. I am recording this video shortly before dinner, so two things are going to be true about it. One is I’m going to really want to get to the end. And two is I might be a little distracted because I’m thinking about some steak here. Once, once Erik Darling has steak on the brain. It’s tough, tough to get it, tough to get it off the brain, really. Unless you have a, unless you use a lot of butter. I apologize to any vegans in the house. I am not one of you. It’s, it’s cool that you’re, you’re, you, just not my thing. So we’re going to, I want to talk about some of the other things. I want to talk in this video a little bit about the fickleness and the limitations of some of Microsoft’s intelligent query processing features. You know, Microsoft loves to put its, put its hands on its tips and stick its chest out and act like it has this, you know, fully automated performance fixing database system. But in reality, there are a lot of limitations to these things. And a lot of the times, man, it’s, they just don’t kick in. These things don’t kick in when they should.

So, uh, we’re going to look at two things. Uh, we’re going to look at, uh, an example of where batch mode on row storm should have kicked in, but didn’t. And an example of, and then I’m going to show you like some of the extended events you can use, uh, just a short, um, a short list of things you can use to try and like, just to give you an, show you like the, the, the amount of stuff that has to go on in order for SQL Server to actually engage these intelligent query processing features. So, uh, for the first demo, I have, uh, I have a votes table indexed in two ways. Once on vote type ID and creation date, and then once on creation date and vote type ID. And I’ve hinted three queries to use, uh, the clustered index and then those two nonclustered indexes up there. And the idea of the query is, uh, there’s the index hint. That’s the only thing that’s different about the three iterations here, is to find the min and max creation date grouped by vote type ID, right? Right in there. And so we look at these query plans, uh, there’s going to be one, the, the one in the middle is different. And the one in the middle is different because, uh, it did not use batch mode on rowstore. The first one right here, uh, this is in batch mode, right?

You can see the batch right there, batch mode on rowstore. Good for us. This hash aggregate also executes in batch mode. And just as a sort of like SQL jeopardy note here, uh, in up until batch mode came along, the only way that you could do a global aggregate like this was with the stream aggregate. You couldn’t use a hash aggregate for it. Um, so that, that was written in like a whole bunch of SQL Server literature for like, you know, a very long time because most of it, or actually all of the good, all of the good SQL Server books were written way before batch mode came out. Actually, no, that’s not true.

The last good one was 2012 when batch mode first crept out of existence in SQL Server. It wasn’t, but batch mode wasn’t very good then. So, um, yeah, 2012 was about the cutoff for good SQL Server books with the notable exception of Great Post, Eric, available on Amazon. Um, I forget how much it is. It’s like, whatever. Don’t, it’s a good book. Buy it. If you buy it, I’ll sign it. How about that? Uh, but anyway, uh, if we look at these plans, uh, and this isn’t the end of the world for this query, right? It’s not, this doesn’t, it doesn’t make a huge difference here. Uh, the, what the query that doesn’t use batch mode on rowstore, uh, takes 2.1 seconds. The queries that do use batch mode on rowstore take about 1.8 and 1.7 seconds.

So it’s not a dramatic end of the world here. It’s just one of those crappy, it’s one of those crappy things where like, you know, you do your job and you create a good, useful index and SQL Server, like, because of, you know, various costing things, uh, does not choose like, you know, like what would be a, you know, overall like a time saving query plan. This is a very small example of this happening. Uh, I work with clients all the time where there are much, much bigger examples of, uh, these intelligent query processing features not kicking in when they should. And, you know, like me having to like, like to play all sorts of tricks and do all sorts of weird stuff in order to get SQL Server to, you know, intelligent query process something.

Right. Which isn’t a good thing because I dropped out of high school the day I turned 16. Uh, I, I, I’m turning 44 at past summit this year. It’s been a long time. This year I’m celebrating my birthday at past summit when I was 16, I celebrated my birthday by not going to high school anymore. Um, so it’s not a good sign when this guy has to go in, in, in an intelligent query process SQL Server because, uh, it shouldn’t be that way. I should be giving SQL Server wedgies and smoking cigarettes in a gas station parking lot. What should be happening?

So, uh, this is just like a, you know, kind of a crappy limitation here, uh, or kind of a crappy example, but SQL Server not, you know, doing something that it should because like we’ve got cost it out. Right. So like the, for the second query that doesn’t use batch mode on rowstore, uh, we use the even Steven index, right? And the even Steven index, if we look up here has vote type ID first and then creation date. And what having vote type ID first in the index does, of course, because you watch all my videos, I don’t want to say religiously, um, dutifully. No, no, that sounds gross too. Uh, uh, enthusiastically. Yeah. There we go. Um, cause you have a bad day if you didn’t spend it with me, wouldn’t you? Okay. Uh, so because this, this index puts vote type ID in order, right? The orders, the vote type ID column, leading column of the index vote type ID is in order because this column is already in order. It’s, it’s, it’s SQL Server thought it would be really cheap to just stream it right into the stream aggregate because the stream aggregate expects ordered data.

That’s why things were a little bit less things were a little bit choppier on these ones. And we didn’t choose batch mode or rowstore because it was just like, well, I, I, I, I, I stream aggregate. I have to sort 53 million rows. Not that SQL Server has been smart enough in the past to not sort 53 million rows. Uh, there was that merge join video from a little while back, but anyway, a digression here. Uh, so that, that, that, that’s this thing, right? And there are all sorts of, um, extended events related to, uh, to the intelligent query processing features to either tell you like sort of about what they do or like why they weren’t used or, you know, what, what needs to happen for, uh, for them to, uh, come into play. And you can usually track down these extended events. And, uh, you know, so this top one here is bat is the batch mode on rowstore heuristics.

And there are a bunch of reasons. There are a bunch of things that SQL Server looks for. And holy cow. What? This is when every other thing is true, but you still dinner. Ah, the Welsh keyboard kicked in, uh, uh, did not cross the, did not cross the road.

Oh, did not cost the, cross the cost threshold. Oh man. You should have stapled it to a chicken. Anyway, uh, so this is the list of stuff where, um, you know, that, that would lead SQL Server down the path to choosing, um, to choosing batch mode on rowstore.

Uh, the second query result is a list of, list of reasons why, oops, sorry. I touched the wrong thing there. Oh, dear me.

All right. Now, this is a list of reasons why SQL Server might skip using an adaptive join. Uh, these are not particularly, uh, legible to human beings. Um, I do have, uh, a DBA, uh, sorry, I have a DBA.

I have a, uh, Q, self-answered Q and A on Stack Exchange where I do give examples of a lot of the, what these things mean. Uh, because at the time I was quite fascinated by them. Um, and then, uh, this bottom section, uh, just talks a little bit about memory grant feedback stuff and, like, the steps it goes through.

Uh, and, oh, look, we have deprecated things in here. Um, replaced with current execution count and adjustment count. Okay.

So, so these things replace those two other things. So, don’t pay attention to these things, apparently. These things, no good. Uh, pay attention to the other things. They are not deprecated. Great.

Uh, but this, like, talks a little bit about, you know, the, like, like, the, the process that memory grant feedback goes through as it’s just in query memory. So, there’s a lot of stuff in here. And the, and these, these heuristics and limitations are, of course, hinged on, uh, or predicated on a bunch of other stuff.

Just, like, for example, you have to be on SQL Server 2019 or better or some cloudy equivalent. Don’t use, don’t use managed instance. Uh, some cloudy equivalent where, like, you’re on an enterprise-y thing.

You need, uh, to be in a modern compatibility level, like 150 or, uh, 160 for a lot of these. Uh, some of them are earlier, uh, like, adaptive joins are, like, 140 for SQL Server 2017. Uh, there might be a couple of few from 2016, but you do have to be in, like, 130, 140, 150, 160.

Something rather new. And each, and for each version of SQL Server that introduces new intelligent query processing features, you have to be in a higher and higher compatibility level in order for those to be unlocked.

That’s why for a lot of queries, when I want to differentiate things, you’ll see me use the, the use hint that sets the, the, the, the query compatibility level to a higher compat level so that I can experiment with the batch mode on rowstore stuff and see, like, the, like, the before and after. It’s a lot easier to do it that way than it is to, um, do it in the reverse, at least for me. So there’s all sorts of, like, so, yeah, back to what I was saying.

Compatibility, enterprise edition compatibility levels, version of SQL Server. So there’s, there’s, like, a lot of stuff that has to, you know, already be in place before any of these intelligent query processing features will even think about, thinking about anything. So if you’re, if you’re out there in the world and, um, you know, you’re, you’re having a hard time with SQL Server and you’re, you’re wondering why all of these, like, awesome intelligent query processing things aren’t just making your life so much easier and better.

It’s probably because either Microsoft doesn’t think you spent enough money on SQL Server, which, you know, if you’re on standard edition, you get what you get. You don’t get upset. You pick the cheap one, right?

Not me. You may not be on a modern enough version of SQL Server. You know, 2019 and 2022 have the most up-to-date intelligent query processing features. And then lastly, you, your database might not be in a compatibility level that allows for any of these intelligent query processing features to be used.

And all of these things are going to hold you back. And there are various reasons why you might be in that, that condition, right? It could be a vendor thing where they’re like, no, you can’t use a newer version of SQL Server.

No, you can’t use a higher compatibility level. We haven’t certified, blah, blah, blah. You know, there’s just all sorts of crappy reasons in the world for that stuff. You know, you might be able to affect some things by using query hints like, like I have on these queries to use a different compatibility level just at the query level.

That might be useful enough for you. That might get you across the finish line in a few places. But, you know, there are times when I see entire workloads where everything is right.

The SQL Server 2022, Compat Level 160, you know, the Enterprise Edition, not managed instance. And still, these heuristics, these intelligent query processing features are either, or rather, the IQP features do not meet the heuristic goals that they need to, to kick in. Or there is some limiting thing in the query itself that prevents any of the, some of the IQP features to kick in.

I think, you know, adaptive joins, we looked at a bunch of reasons there. But, you know, I think, you know, probably the biggest one for most people is the scalar UDF inlining, where the list and the number of restrictions on it seems to grow with every cumulative update. And that, you know, that’s one that I think a lot of people in SQL Server world had very high hopes for, because scalar UDFs really are the damn devil when it comes to performance tuning.

And, you know, having those not be as big a devil as they are would be nice. But, you know, we can’t have nice things. So, anyway, that’s about that.

I do, I do hope that if you, if you, if you need help with these sorts of things, you will, you will, you will think of your, your humble, young, handsome consultant, Erik Darling. Because I, I do, I do, I do tend to help pretty well with these things, despite, despite being a high school dropout. So, because they’re tough problems, and it seems like every time Microsoft adds a knob, 17 things become far more complicated to track down and pinpoint.

And it gets, it gets to be rather cumbersome. So, anyway, I’m going to go eat dinner now, so I can get the steak off my brain and into my belly. And then I can record stuff that makes probably more sense and is more coherent.

And, uh, no, I’m going to go eat dinner now. A little more focused. That’s the word, focused. So, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope that you will like me. Love me.

I hope that you will like this video and subscribe to my channel. And join the nearly 4,000 other data darlings who have, who have, who have done that. Who have subscribed to my channel.

It’s probably the same, like, five people who like everything. That one, one, one person who, who dislikes everything. It’s okay. I still like you. I still like you.

I will still, still give you a hug. Just for, just for showing up. Just, just for giving me the view. Even though you gave me a thumbs down. Even though you gave me the view. I’ll, I’ll give you a hug for that.

Nice, big hug. So, anyway. Yeah, it’s steak time. I’m out. Goodbye. 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.

Building Reusable Queue Tables and Procedures In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of building reusable queues in SQL Server, a topic that is both fascinating and crucial for maintaining efficient data processing workflows. I explore various queuing strategies, including different ordering methods like FIFO (First In, First Out), LEFO (Last Entry First Out), and FAFO (First Available First Out). Additionally, I discuss the importance of choosing appropriate data types for your queue table, particularly emphasizing the use of `bigint` as an identity column to avoid potential scalability issues. The video also covers how to efficiently manage processing eligibility and track duration using computed columns, along with detailed explanations on locking hints and isolation levels to ensure smooth operation in high-concurrency environments.

Full Transcript

Erik Darling here with Darling Data, and in the interest of fiscal solvency, we’re going to talk about building reusable queues. What does one have to do with the other? Absolutely nothing. I just blurted that out and went with it. And I don’t feel like starting this thing over. So you’re going to just have to cope with that mentally somehow. So there are two aspects to building. There are a lot of reasons for building. queuing processes in SQL Server. And there are all sorts of different ways to do it. There’s like first in, first out. There’s, you know, first, last in, FIFO, LEFO, FAFO, last in, first out. I’m going to skip over the third one. And there are all sorts of like, you know, different ways you can choose to dequeue something. And by dequeue, I don’t mean Dairy Queen. I mean to like remove something from the server. Eligibility for queuing. For me personally, you know, it really does depend on why I am the intent of the queue. There are times when I would want to, you know, just delete something from a table when I have just when I have started processing it. That’s a completely legitimate way to do it. And this in this example, I’m just updating stuff. And sometimes the update thing is nice because it like rather than just delete an item from a queue table, you can have the queue table retain some information about the queue, the queuing process, right? So it’s like you can have some, you know, generally useful stuff in there. And that’s like, that’s the way that I kind of like to do it. You can kind of see that in the setup of my queue table here.

So I am going to do what everyone, every good little SQL Server DBA does. And I’m going to have an ID column. Mine’s going to be a bigint, because I believe strongly in using bigints as for identity columns. The process of having to fix that is a real nightmare. And I wish Microsoft would make it less of a nightmare. To me, that’s a pretty major scalability issue that they just ignore. And what do you call it there? Yeah, if like, I’m saying for like domains, like things that you understand can never go past a certain number, like, like, you know, dispositions of things like, you know, like, like, constants of the Stack Overflow database, vote types, or post types, you know, you can never hit, like, over 2 billion types of posts, you might hit over 2 billion posts, that’d be a lot of posts, that’d be a lot of questions, but you’re never going to have like more than like, you know, an integers amount of types of posts.

So for when I’m when I’m making a table with an identity column, I’m gonna I’m gonna suck up, I’m gonna suck up it, suck it up and use those four extra bytes. Oh, my God, the four extra bytes. And I’m gonna I’m gonna use a bigint because it’s the funny thing to me is that, you know, when when you start talking about data types, you have the absolute worst people on Earth will start talking to you about bytes.

and especially when you talk about in some big ends are like, well, integers are four bytes, big ends are eight bytes, you know, disk, disk, disk, like, well, okay, fine, it is legitimate. The thing is, about the point where you’re going to start maybe caring about the four extra bytes that you’re storing, is probably going to be right about the time that you’re starting to run out of integers.

And when you have those four extra bytes is available to you in bigint form, you’re going to be so much happier that you don’t have to fix that problem. So, you know, that’s that’s my thing. Right there. And so for this queue table, since we’re not doing a delete queue, we’re doing it when doing an update queue, we need we need a column like this to tell queries when when a row is being processed.

In this in this case, the thing that I’m using for the processing point is just the reputation column from the user’s table. It’s really just completely senseless. And then I have some this is this is what I was talking about where I said it’s nice to have some information about how long it took to process a thing.

So what we have is a couple couple three columns in total start date and end date and computed column that calculates the duration in milliseconds between the start date and end date. So, you know, I’m using milliseconds here because like this particular queue brought would process very quickly. But in real life, you might, you know, want to do like seconds or minutes or something, you know, get get more more human understandable numbers out of it.

And then the two two important things are when you for processing this queue table, this one probably the most important because what we what we need here is which will make more sense when you look at the queries that actually hit the table. What we need here is a really efficient way to find things that are not being processed or skip over things that are being processed and a way to order the results after that in a way that we can process the queue in order. If we were if we were going to process these things differently, for example, the lovely, talented Canadian Bertrand, Canadian Aaron Bertrand, this Canadian Bertrand is going to be his name from now on, has a series of posts about snake sorting.

And snake sorting is a way to is sort of a way to divvy up work equally amongst threads. So they all sort of finish around the same time. The context of his post was like distributing backups to threads. So like you wouldn’t have, you know, like, you know, one thread. Oh, I got an eight terabyte backup.

Oh, now I got a 10 terabyte backup. They would be like, I got an eight terabyte backup. Now you take that 10 terabyte backup and the other two threads. You take like the five, four terabyte backups and we’ll all chug through these things more equally.

So if you have that kind of knowledge about like the size of a process or the size of a thing that has to be written, you might not want to do something as naive as just say, like, you know, get just give me the next one in sequential ID order. You might want to use some additional logic like Aaron does to split things up to make the thread each each thread do a more fair amount of work. So depends on how you’re dealing with that stuff.

So here’s here’s the store procedure that I will the sort of a template store procedure that I use to run the queues. And it’s just a perpetual loop until we run out of work. So and something is pretty important in here.

I’m going to split this up into two parts. The locking hints in here are particularly important, regardless of which isolation level you’re in. Because, you know, if you’re using I mean, just like option wise, if you’re using read commit, just really, if you’re using pessimistic versus optimistic, using a pessimistic option up isolation level like read committed, you’re going to have a blocking nightmare if you don’t put stuff like this in.

Right. And if you’re using an optimistic isolation level, you’re going to have a pretty hard time with a time of things if you don’t add in some additional locking so that so that like, you know, in some cases, like you’re going to want read queries to not see version data. So you have to be just be be careful with that.

If you are using an optimistic isolation level, you will probably want to use the read committed lock hint for queuing processes that goes for queuing processes. Or if you’re doing anything like building a sequence table, Paul White has an excellent article on building sequence tables where the he uses the read committed lock to great effect in there. And I think that’s probably something that you would want in a queuing process like this.

If you were doing if you were using an optimistic isolation level and if you are a good little data darling out there in data land and you are being real smart, you are using an optimistic isolation level because pessimistic isolation levels are the turds. They are nothing but problems. So, yeah, there we go.

And you know what I had known I accidentally deleted something in here that I shouldn’t have deleted. I just realized it now because I saw some red squiggles at ID integer. No.

Reputation. Sorry about that. Integer. No. No. No.

That should be. No. Like that. And then. So what this code does is it’ll look for things that it needs to process. Right.

So while we have at least one row in the table that needs processing, we’re going to enter a loop that looks like this. And this is where the index thing makes a little bit more sense. So we’re going to use a CTE here just because it’s a little bit cleaner than using a sub query or any really.

I mean, if we could use a drive table here and it would be the exact same thing. But, you know, for this, it just kind of, it does make the, it does make the sort procedure a little bit more readable. When I use a CTE here.

So what we’re going to do is just select the top one row. And we’re going to hold on to that one row. We’re going to say row lock up D lock. We’re going to hang on to that one row.

And we’re going to, again, you know, like I said, this is just very naive sorting to just get me the next, next available thing in sequential order. You might need to do this a little bit differently depending on what your process is. And then we’re going to use kind of a neat thing where we’re going to use an update to not only set a couple columns in the table equal to something.

So what we, this update will start by changing in, in the in process column to one for whatever row that we find. And it will also update the start date to the time that we started this. So that when we’re done at the, at the end, we can add a, we can update the end date column.

So we know how long something took. And then we’re going to do something that I think is particularly clever. We are going to, in the same update, we are going to update our two parameters to be equal to the columns in the queue table that we found up here for that, for the one row that we pulled out up here.

We’re going to set those equal to the column values that we pulled out there. Then, you know, just what we’re going to do is we’re going to use those as part of our queue process thing. Uh, my pretend example is just selecting a count from the users table where reputation equals whatever reputation we pulled out there.

And we execute that SQL down there. And then we use the ID column that we fetched. If this zoom, it will listen to me.

Zoom, it will ever listen to me. I’m hitting escape like every three seconds and zoom it. It’s just like, I don’t care. Uh, come on, zoomie. There we go.

All right. That took a little bit more wrestling than I cared to do. And then the final thing that we’ll do down here is update the queue table to just set the end date to when the, when this thing finishes and the ID equals the ID of the queue item that we pulled out. And that would, that will be, uh, that will be really quick.

Um, there’s also just a little safeguard, uh, there, um, to make sure that we only, uh, would only ever update a role that’s in process and not a role that is not yet being processed. Even though that’s sort of technically impossible with this. I just like to be extra safe when I’m, when I’m running these things, when I’m automating tasks like this, because if you’re not, you can run into some really weird bugs.

Um, so all of, all of the code and, uh, a somewhat deeper explanation of some of these things is available on my blog. Um, I, there are two posts that will be in the, in the show notes, as they say, about building reusable queues. I originally wrote it as a two part series.

Uh, I forget why, probably cause I needed an extra day of blogging to, to, to cover something. Uh, it’s hard, hard to tell sometimes, but I, I think it’s a very interesting, uh, problem when you have to tackle this sort of thing and doing it correctly and doing it in a way that, uh, is, is effective for high concurrency and not having a bunch of awful blocking problems, uh, is especially interesting. And that’s where, uh, table design is really important and, uh, making sure that, you know, your table is set up in a way that allows you to find the rows that you need to process in the order you need to process them as efficiently as possible.

Uh, so like, you know, if you’re doing something where there’s going to be like, you know, four or five worker threads and they’re going to like chug along on like huge tasks for a long time and then come back, the locking and blocking thing probably isn’t going to be all that important. But if you need to orchestrate like really high concurrency queuing, uh, and distribute those queues without running into a blocking nightmare, that’s where this kind of stuff becomes much more important. So anyway, thank you, you, especially you, the good looking one sitting right there, uh, for watching.

I appreciate you more than words can, words could ever possibly describe. Uh, I hope you enjoyed yourselves a little bit. I hope you learned something.

Uh, if you like this video, um, thumbs ups and helpful comments are, are always nice. I do enjoy those. Uh, if you like this sort of SQL Server content, uh, please subscribe to my channel.

You can join the nearly 4,000 other data darlings out there in YouTube land who, uh, who, who, who have done, who have done that and, uh, who, who make me feel like a, a special, helpful, useful, good person by, uh, allowing little bells to go off in their head every time I publish a video. So, uh, yeah, you can, you can, you can like, and you can subscribe. You can do, you can do them both.

And, uh, then, then you’re, then you’re extra, extra data darling-y. And I owe you a hug. So, anyway, uh, I got some more of these to record.

I got to bulk up things, uh, before I go away for a little bit. So, uh, this one’s getting canned right about here. Thank you.

Thank you.

Going Further

---

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

Spring Training

---

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Register for Data Saturday, on September 7th here!

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.

Date Math Annoyances In SQL Server Queries

---

Thanks for watching!

Video Summary

In this video, I delve into common pitfalls and best practices when working with dates in SQL Server queries. Erik Darling from Darling Data shares his insights on avoiding implicit data type conversions by using explicit date values instead of relying on zero as shorthand for 1900-01-01. He also highlights the importance of performing date math operations on parameters or expressions rather than columns, which can significantly improve query performance and accuracy. Additionally, Erik discusses the nuances of the `DATE DIFF` function and emphasizes the need for precise assumptions when using it to ensure correct results. By sharing these practical tips, I hope viewers gain a deeper understanding of how to handle dates effectively in SQL Server queries.

Full Transcript

Erik Darling here, Darling Data, doing Darling Data stuff with SQL Server. Apparently I have to get on an airplane today. What day is today? I guess we’ll find out because we’re going to talk about dates. I’m dumb. So, I’ve talked about some similar stuff on this, on the Chan before. I hate myself. But, I’m going to talk about things in a slightly different way here. Mostly because I still see a lot of screw-ups and weirdness and sort of buggy-ness with date math and date function queries. And I just want to kind of talk through a little bit of what goes on with date math and date functions. So, first and foremost is a giant pet peeve of mine, at least in recent years, because for a long time I kind of had no idea what what an issue this sort of thing could cause. But, more recent years and experiences have taught me that relying on implicit conversion of data types can really cause strange incorrect results bugs. And you don’t want to do that. For you or anyone, really. Why would you do that to a person? Be nice. Be kind. So, I see a lot of date math queries where people use zero as shorthand for 1900-01-01. And of course, there’s a lot of data in very simple circumstances. But, in other circumstances, you can run into some big issues with this. It would be a bit too much to gin up a demo to show you what those issues are. Just trust me, do yourself the favor of not being lazy and using this shorthand of zero to replace 1900-01-01. And we should probably make a note here and say, just so there’s no confusion, do this instead. Yeah.

There we go. That looks better now. So, these two queries will do about the same thing. They’re going to give me adding 45,477 days to 1900-01-01, which gives us the exact same value for both of them. We get the same results back here. But, like I said, there are more complicated circumstances where you might hit some weird bugs with relying on this implicit conversion from zero to 1900-01-01-01. So, always, please explicitly do this. Maybe even, like, say, convert date or date time 1900-01-01 to make extra sure. I think I’ve gone through all of my GitHub repo and replaced any place where I may have made that error in the past. But, if you see anything in there, please let me know and yell at me.

So, another thing that I also see quite a bit of is when people need to do things like compare, like, you know, they want to go back a certain number of days or months, and they end up doing some really shoddy date math like this. Now, this won’t cause incorrect results, but, you know, in the presence of a half-decent index, performance is not going to be great with this because you’re performing the date math functionality on the column, and you’re comparing that to just, like, this scalar thing over here.

And your life for the SQL Server query turns out a whole lot better when you do something like this, where you compare, like, the column in the table to the result of date math operating only on whatever parameter or variable you pass in. So, if we just look at the results of these two queries, and I stuck recompile hints on there so we don’t have to deal with the local variable effect with anything. So, you know, the first query where I do the date math on the column, that takes about two seconds.

We have to scan the whole column. No one is happy. On the second one where I do the date math on the, right, there’s a column compared to the date math. This does an index seek and runs for about 100 milliseconds.

So, that’s a much better way of doing things. Where it gets a little tougher is when you need to compare columns that are in different tables, right? So, saying where p.creation date, the date dip between, let’s say this query, well, this query specifically is looking for posts where, like, someone came along, like, a year later and commented on them, right?

So, someone, like, just wandered in drunk and was just like, this is out of date now. Something like that. And, like, if these were just two columns in a single table, you could, of course, what do you call it, create a computed column on them, index it, and be fine.

But, since they’re in different tables, really the only way to do that without, like, you know, creating a new table and storing both sets of data or completely screwing up the design even more of the post table and somehow adding comment dates in there, which blows my mind how little sense that would make. Like, give me a twitch. You know, like, the only realistic thing you could do would be to create an indexed view that would, you know, give you the results of this back all at once.

Now, this, I get it. I’m with you. This is not a very useful indexed view, right?

Like, because we’re just, unless you just need the count, we should probably involve some other things in here, like, probably, like, the ID column from the post table and the comments table, so we could, like, look those things up in the base tables, something like that. But this is just one quick way of showing you that, you know, an indexed view can be a pretty good way of making the non-sargable a little less painful. Another thing where date math gets sort of weird on people is the date diff function.

So what a lot of people are surprised by with the date diff function is that it does not actually look for, like, the duration necessarily. Like, the date diff in years does not look for, like, a 12-month difference. The date diff in month does not look for, like, a 28 or 30 or 29 or 31-day difference.

The date diff in days does indeed look for a one-day difference, but that’s the, but, you know, if, you know, we were to get into a situation with, like, you know, let’s say that we were looking at, like, 12-31 at, like, 11-59. And 2020-01 at, like, 0-0-01. Or even just, like, 0-0-0.

Actually, I don’t even know if that’s going to work. We’re going to find out together. Yeah. So, like, you know, there’s a one-minute difference between these, but SQL Server’s like, no, it’s a full day. And there’s, like, you know, a one-day difference between these, but SQL Server’s like, nope, that’s a full month.

And, you know, again, there’s a one-year difference between these, but SQL Server’s like, no, it’s a full year. So, the date diff function is a little weird in that way. And so, you have to kind of be careful about being precise about what it is you’re looking for.

All right. So, if you look at, like, this query, and you’re trying to figure out, like, all you want to do is get, like, this count that we were talking about. That’s about 20,000 rows.

If you wanted to write that using different logic, right, if you wanted to write that in a different way, you would have to do something like this. Where you would have to add a year minus one and then flatten the year, flatten the creation date column to the year. And it just gets very, very complicated to try and replicate that logic with, like, explicit query syntax.

Like, just doing something as simple as this just doesn’t work, right? If you look at this, the results of this, that’s a whole bunch more rows than we got from either one of those. So, this isn’t, like, you know, depending on what exactly you’re looking to count up and return in these things, you need to be really careful with how you write these queries.

The performance, of course, doesn’t matter, but the results here are different, and they do matter. Now, if we were to look at some of this stuff in here, the results wouldn’t look too weird for the most part. But if you kind of start scrolling down a little bit in the results and you look at sort of really down at the bottom is where the interesting stuff is.

So, where SQL Server is telling you that there’s a one-year difference between things, it’s also telling you that there’s a 23-month difference. So, there’s, like, almost two-year difference there, right? And there’s the 723, and I’m not going to figure out how many 30s go into 723, but it might start to creep up above these things.

So, you know, I realize that these things are documented, and, you know, if you’re keen on reading the documentation, you might think this video is stupid and be like, oh, the video is documented stuff. A lot of people don’t get that involved in this, not until someone tells them that stuff gets weird that they start to worry about stuff getting weird.

So, at the end of the day, when it comes to doing date math, one, please use explicit dates. Do not use numbers like zero to sub in for dates and rely on an implicit conversion for that. Please don’t do date math on columns as much as you can avoid it.

Do date math on, like, whatever parameters and expressions you need to and compare the column to that. If you need to do date math across tables, index views can be a good way of making that a little bit less painful. And if you need to use the date diff function to figure out what span of time exists between things, just be very careful in what your assumption is about exactly how much time has to be between two things in order for date diff to tell you that, like, you know, there’s a month or a year and a day between them.

Because, again, going back to this, you know, there’s a whole bunch of stuff in here where, you know, you know, like, yes, I agree. Go away, GitHub Desktop.

There is, you know, 2020 is a year ahead of 2019. But, like, there’s really a one-day difference between these dates. You know, and, you know, there is a, the first of the year, the first month of the year is a month ahead, is a month different from the last month of the year.

But, again, there’s only a one-day difference. And, you know, I agree that, you know, the first of the year is indeed a day ahead of the last day of the year. But there is only a one-minute difference between these.

So just be careful, like, be very specific about exactly how much time has to exist between two things before it is considered a one, like, a month or a year or a day difference. You might need to use, like, seconds to figure out it.

Like, you might have to do the date diff in seconds and then figure out how many seconds are in the span of time you care about, whether it’s a day or three days or a month or, like, how many days in a month you care about or, you know, like, the number of seconds in a year.

You might need to do a lot of that math to figure out exactly what it is you need to be precise about it because date diff on its own is not very precise. Anyway, thank you for watching.

Hope you enjoyed yourselves. I hope you learned something. I hope you read the documentation in the future so you can call me stupid for explaining parts of things that, how things work or something like that.

Of course, I thank you very, very deeply for watching. This is my last day on Earth. This is the last thing I record. Gosh, that’d be sad.

So I think I better not. Maybe I should record something else real quick after. Anyway, if you like this sort of SQL Server content or you like this video, you can like and subscribe to me and my channel, and you can get notifications when I drop the SQL Server content, and you can leave me likes and comments when I do, and then everyone’s happier, right?

It’s cool stuff. 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.

Performance Tuning GREATEST And LEAST Functionalist In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into the exciting world of SQL Server’s new greatest and least functions introduced in version 2022. Erik Darling from Darling Data shares his insights on how these functions can be used effectively, especially when working across two columns in a single table or joining tables. While demonstrating their usage, he also highlights some performance considerations and suggests ways to optimize queries for better speed, such as using early aggregations. The video is packed with practical examples that showcase the functionality of greatest and least, along with tips on how to emulate these functions if you’re not running SQL Server 2022 yet. With a touch of humor and self-deprecating remarks, Erik keeps things entertaining while delivering valuable information for database professionals looking to improve their query performance.

Full Transcript

Guess who? Erik Darling, Darling Data, recording a video about something in SQL Server. Shocking, I know. Shocked me every day that there’s stuff to talk about still. Uh, you know. Maybe someday there’ll be something new, or maybe someday there will be nothing left to say, but until then… Gotta keep going, I guess. Until I rub the eyebrows off my face. Uh, alright. So, uh, in this monster of a video, this absolute savage unit of a video, we’re gonna talk about, uh, well…

For the… for the sake of demo length, we’re gonna just talk about the greatest function, which is new to SQL Server 2022. But the greatest function is… was accompanied in… in release by the least function. So you can do greatest and least, uh, right? So, uh, every other database platform for, like, 20 years has had the greatest and least functions. And in… sometime in 2020, 2021, uh, Microsoft said, Oh, yeah, we better play catch up on this… this basic database functionality stuff that, uh, other databases are wiping the floor with… floor with us with.

Um… Yeah. So, great. Um… SQL Server 2022 has the greatest and least functions. If I had a kazoo, or a party… one of those party things, I would… I would… I would do that right now. Uh, but I don’t have either one, so just use your imagination. Pretend I am, uh, interacting with, uh, with party noise-making, uh, devices. Assuming that you’ve been to a party and you know what I’m talking about, um…

Maybe, maybe, maybe, maybe, maybe one time you saw a presentation about parties and, uh, noise-makers were part of… part of the presentation. Uh, you could… you could fill in that blank if you… are… are… are not a… hyper-social being. Who works with databases would be shocking, wouldn’t it?

Uh, it’s like… it’s like that joke about, uh… Well, uh, I don’t know. I guess IT people in general, it’s, uh… Uh, how do you tell the difference between an introverted IT person and an extroverted IT person?

And it’s that, uh, an introverted person stares at their shoes while they talk to you, and an extroverted IT person stares at your shoes while they talk to you. So…

Uh, yeah. There’s that. So, uh, we have the greatest and least functions. And I’m gonna show you two examples of them. Uh, one is just across two columns in one table. And the other is across two columns, uh, in join tables.

And, uh, honestly, for the single table thing, it turns out fine. Uh, but for the double… the join table thing, uh, I am a little bit less in love with the query plan. Um, it’s… it’s a bit slow for me.

Right? I don’t… don’t enjoy slow queries. Uh, and this is… this is gonna be true of… Uh, well, other examples. Well, I’m gonna show you. Well, I’m gonna show you.

So, if you’re not on SQL Server 2022, right? And you’re… or whatever other problems you have in your life, uh, there is a way to emulate the greatest and least function… greatest and least functionality.

Whew! Um, it’s… it’s too early for me to be drunk, so don’t… that was just a mouth malfunction. A mouth function. Uh, you can emulate the greatest and least functionality, uh, by doing the old cross-apply trick.

Um, supplying the… the columns that you need to greatest and least as values, right? And then aliasing, uh, that as a single column to reference. And then, um, from there, uh, finding the max of whatever is in that aliased column.

So, whatever comes out of combined date. You can’t emulate greatest and least functionality doing that. Uh, the problem is that, uh, you know, performance for this sort of thing is not all that hot.

Um, you know, again, for the single table query, it’s not that big a deal. Uh, but for the multi… for the join and if you’re, you know, doing other stuff, that’s a three and a half seconds. The greatest version of this was, like, was only about 500 or so milliseconds faster.

So, performance isn’t necessarily great for that. Uh, and, uh, batch mode does change the picture a little bit, but not enough to, um, not enough to make me happy. So, if you do need to emulate the greatest and least functionality in SQL Server, you may want to write in, you know, you’re unhappy with the speed of the query.

If you write it the norm… the way that I just showed you and speed is fine, like, you’re not worried, like, like, performance is good enough, don’t worry about this. But if you’re unhappy with performance, uh, you can always rewrite the query a little bit to do some, uh, earlier aggregations. Um, I have another video, uh, about SQL Server performance where, uh, it just… the optimizer just doesn’t spot an opportunity for, uh, earlier aggregation and, um, performance sucks.

And we have to kind of tell SQL Server what to do. Uh, so we have… we’re gonna… what we’re gonna do is we’re gonna do… we’re gonna aggregate early on our own the max score from posts and the max score from comments. And then we’re gonna use, uh, the… the cross-apply just to continue with the emulation of the… the greatest and least functionality.

We’re gonna… just wanna do this. And, uh, this will be a lot faster. Right, the early aggregation allows us to kinda hit these two tables, uh, bring the rows that we need to figure out the… the max of, uh, a lot faster, right?

So we… oh, geez louise! Zoom it! I hit control! Why are you messing with me? Why? Uh, we do this. We do the early aggregation here. Uh, and then we do a secondary aggregation to bring things, uh, down to one row even further here. And, uh, this all takes about… well…

It’s another real curious case of… uh, uh, operator times and execution plans. Looking real stupid. Real bad. Uh, kind of embarrassing, actually. Uh, this… this query doesn’t take two seconds, as the gather streams operator might have you believe.

It really does take 1.059 seconds. Uh… Yeah. No. Summer interns. Uh…

Well, yeah, that’s about that. Alright, well, now I’m just depressed. Um, thanks. Thanks, SQL Server, for that. Uh, let’s just wrap this up with the demo to show you…

Uh, that that can also work well for a single table query. Uh, not that this was slow before, but, uh, you can… you can express, uh, the… logic in many different ways that can… they can help with performance in many different situations.

Uh, usually, I find, when I’m query tuning, that, uh, I… I need to guide SQL Server towards doing aggregations a lot earlier, uh, than it sometimes does. And, you know, usually, uh, that… those… those, uh, those… as long as there is a meaningful reduction in the number of rows via those aggregations, uh, you should see pretty decent performance improvements. You know, like, comparing, um, this plan up here…

with the greatest and least. Alright, and… Run this again. Uh, this is real goofy, because SQL Server scans both indexes. Uh, there is no early aggregation before the join.

And the aggregation only occurs at the very end. Uh, for some reason, SQL Server doesn’t think to do anything between… getting…

17 million rows and 20… almost 25 million rows, and… joining those fully together and then aggregating stuff. The optimizer’s like, no, this will be fine! Alright, so we have our initial aggregation here, and then our final aggregation here.

Uh, but, um… Like I said, my general experience is that it is a lot better and faster if you guide SQL Server towards the correct… uh, the correct early aggregation strategy like this.

Um, even though this filthy idiot liar of a gather streams operator is still trying to convince us that our query… took two seconds to finish. It really took one second.

So… Alright, well, I wasn’t drunk before, but I think I’m gonna go start on that now, cause… I’m gonna go start on that now, but…

I’m gonna go start on that now, and I’m gonna go start on that now, and I’m gonna go start on that now. Well… I mean, look at that. Look at that thing in front of you. I don’t know…

The thing… The thing is real ugly. Uh, we’re gonna… We’re gonna… We’re gonna have to… Have to drink to cope with that. Sorry to say. There’s no other alternative. Uh, I’ve…

I’ve jumped out of every single window that I have available. The glass is shattered. Uh, it is no longer as dramatic and exciting… An exit, as it was when the glass was there. Uh, so now…

Now we’re just down to drinking. To deal with… These inconsistencies. So… I’m being dramatic. Calm down. Uh, anyway. Thank you for watching.

I hope you enjoyed yourselves. Hope you learned something. I hope that… Uh… You will not… Be as offended as I am at these operator times and query plans.

They are… Truly… Truly awful things… Sometimes. And, um… If you like this video…

Thumbs ups… Are good. Um… Inspirational comments. Live, laugh, love. Um…

Add years to your life and life to your years. Peanut butter and onion. Uh… All nice things to hear about. So… Um… If you like SQL Server content… Uh…

Despite… This monstrosity that you see before you… Uh… You can subscribe to my channel. And we can at least laugh at this stuff together. Uh…

I probably won’t be drinking on camera. Cause that would be very unprofessional. But… Um… What… What happens below this line… Stays below this line. Lucky for you.

So… Um… Yeah. Anyway… Uh… Subscribe. Like. Comment. What else… What else can you do on YouTube? I don’t know. Anyway… 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.

A Little About Parameter Sensitivity And Startup Expression Predicates In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into the world of parameter sensitivity in SQL Server stored procedures, focusing on a common coding pattern that can lead to suboptimal performance. Erik Darling walks through an example where a procedure uses startup expression predicates based on dynamic parameters, which can cause significant issues with query plans and execution times. I explain how these predicates can make your job easier but complicate SQL Server’s optimization process, leading to poor estimates and wasted resources. The video also covers the use of `OPTION (RECOMPILE)` hints as a potential solution, while emphasizing that this is not always the best approach. By sharing practical examples and insights, I aim to help you understand how to write more efficient stored procedures and avoid common pitfalls in your SQL Server coding practices.

Full Transcript

Erik Darling right here with you, cranking away, having a great time, trying to figure out where to put stuff so it’s less distracting than my desk. One of the most distracting things on my desk is the lens cap to my camera because it’s not quite a fidget spinner but it has this very pleasant squeezy motion about it so you can really get a pretty decent two-finger workout. on something there. I don’t know. I don’t really know what good that would do you. Picking up Legos. Pick up those Legos. In this video, we are going to talk about parameter sensitivity in the context of startup expression predicates. If you don’t know what those are, don’t worry. I’m going to answer that question. Relax. Calm the hell down. crazy. Alright. So this is a coding pattern that I see at a lot of client installations. I don’t know what the hell else to call them. Client sites. Like every client is just a website I visit. And they’ll, you know, coming back to my favorite SQL Server adage that I’ve ever said, is anything you do that makes your job easier, makes SQL Server’s job harder. And this is one of those cutesy, like, you know, got your nose optimizer things. Maybe that’s what this is. Got your nose. And this, this will just do you no good. So I see this pattern a lot where they’re like, oh, we’ll only join if this thing is true. Problem is, sometimes this thing will be true. Sometimes it’ll not be true.

And when you ask SQL Server to create query plans based on this type of logic, you will be quite unhappy in the long run. Things will not go well for you. So let’s make sure that we have the correct version of this procedure running. It happens, it happens a little too often to me where I forget to create the crappy original version of the procedure. And I go execute it and I’m like, wow, that was amazingly fast. Did the optimizer change? No, I just, I just didn’t overwrite my fixed version of the code. So we’re going to make sure the crappy version of the code, we’re going to make sure your version of the code is in there, not my version. My version’s good. Yours sucks. Stinks. Look at this thing. Ugh. Disgusting. Throw it in the trash.

And so we’re going to just, you know, because we are incredibly superstitious people, we are going to recompile this procedure. And what we’re going to do is execute this. Once to look for posts and once to look for comments. Now I realize, a lot of you are probably sitting in the back screaming, yelling, ugh, but what if you check for posts and comments? Or what if you check for neither? Or what if this, blah, blah, blah, blah, blah, blah, blah, blah, blah. Well, you know what? This is, at some point these demos have to fit on one screen. And at some point these demos have to fit in one video.

If I go through every single iteration and permutation possible of parameter combinations, not only are we going to be here for a long time, but no one’s going to watch it because it doesn’t get to the damn point. All right. So just keep that in mind that none of the demos I’m going to show you have proper handling for both and neither. Right. It’s either one or the other. Okay. That’s just what we’re dealing with in this one.

So here’s the execution plan. And what we have is the first one that finished relatively quickly because SQL Server cashed an execution plan for the first execution of this procedure where we were hitting the post table. All right. And everything turned out pretty okay for the post table. But if we look down here, we still have the comments table in the mix. All right. And if you look at what the estimates are for the comments table, we have all, well, I mean, it’s all one row.

All right. So this is not a very good, not a very good estimate at all for future executions of this store procedure. Now, again, you may be out there in the world and the void, the void beyond the screen screaming. You can just throw an option recompile hint on there and you absolutely can.

And if you do that, I’m not going to argue with you. It’s a fine choice. It’s totally okay with me. I am okay with option recompile. Do it. I don’t care. If it fixes things quickly for you, go for it. Right. Do it. Right.

What am I going to do? Spank you? That costs extra. All right. Family friendly SQL Server consultant. If you want that kind of action, big bucks. All right. So the second execution of this query takes about nine seconds.

All right. 8.7, which is close enough to nine. And the thing down here is that that bad, that one row estimate on the comments table from the first execution really hemmed us up bad there. All right. Because we got a lot more rows. We got a lot more than we bargained for in here.

All right. So this is probably a good point to tell you about startup expression predicates. All right. Because that’s what I said was going to make this thing parameter sensitive. And that is what’s happening here.

So in both of these query plans, you’ll notice we have these filter operators. All right. There’s one up here and there’s one down here. Usually when I see a filter operator, I get nervous.

Well, I mean, yeah. Usually when I see a filter operator, I get nervous. Unless I know that it’s there for a reason, like we have a having clause on some aggregate, or we have a windowing function where we’re filtering to, like, for example, row number equals one, where you need to generate that expression at runtime and then filter on it later.

Whenever I see a filter without one of those things, I get a little antsy, because that can often mean that we have written a predicate that is very complicated or very non-sargable, the opposite of sargable, anti-sargable.

And usually that means performance is going to suffer in some weird way. It can also mean that we’ve written a left join with some sort of null or not null check on the left join to table, in which case we should be using not exists or exists or whatever would logically fit the scheme of the query.

But in these cases, what we have here are these startup tooltip. You know, you show up when I don’t care about you and when I don’t need you, and then as soon as I need to show you, you disappear on me.

So in these filters, we have a startup expression predicate, which means that only rows that pass through this filter will touch the table, right? So if we can just keep hitting this filter with nothing, and eventually SQL Server will say, okay, well, nothing passed, so we’re not going to do anything with that table.

There should be no I.O. for the post table in this execution, because nothing passed this predicate, right? We had a whole bunch of rows try, but nothing did, right? They all failed.

None of them passed the predicate. For the comments section, where we have check comments equals one, well, a whole bunch of these do pass, way more pass than what happened before, and this is part of the cardinality estimation process.

SQL Server comes up with an execution plan based on these parameter values, and it says to you, well, comments score, sorry, if check comments is zero in this procedure, then I don’t expect any rows to pass that.

Sorry, you’re going to get a plan for that, and performance will inevitably suffer. So what a lot of people think is also a good and cute, well, cut your nose, that’s why we do our squeezy finger exercises, so we can be really good at grabbing the optimizer’s nose, right?

Boom. So what a lot of people also do is something that they think is clever and cute and will work like this, where they’ll separate these things out.

And again, I know, I don’t have a code blocking here that checks for both equals one or both equals zero. I get it. I don’t have any handling in here for those scenarios.

You’re just going to have to live with it. You’re going to have to take these two fingers and pinch whatever part of yourself helps you cope with the fact that I am not writing the most robust implementation of this stored procedure that I possibly could.

All right? Deal with that fact for a few more minutes. Thing is, this doesn’t help either.

Bare naked if blocks suffer a similar fate as the sort of startup expression predicate type code, where, and I’m just going to show you an estimated plan real quick. All right?

So SQL Server will compile and figure out a query plan for both of these branches on the first compilation, regardless of if both branches are explored or not. You get a fully-fledged execution plan based on whatever parameter values get passed in the first time, and you end up in a situation that’s quite a bit like the non-if block startup expression predicate portion.

All right? So if I execute these two things, we’re going to see a very similar outcome to the above query, where the one for the initial compilation for posts, totally fine.

The second run for comments, not so hot, right? It’s about 9, 10 seconds again. So if we look at these two query plans, almost identical outcomes just in two execution plans rather than one. All right?

We have 741 milliseconds here and 9 seconds here. So clearly the if block thing does not work so hot either. Now, again, if you want to throw a recompile hint on the first query, you can do that. If you want to do the if branch thing and throw a recompile hint on both queries, you can do that.

I don’t care. Whatever you want to do, roll with it. One way that you can solve this problem without recompile, you can even keep your if blocks, is to just use dynamic SQL parameterized, of course, so that we don’t get anything terrible happening to us.

And you don’t even have to, like, start declaring variables and all sorts of other things. I took an incredibly lazy way out of doing this. And I just, instead of setting a parameter to this, I’m just putting the whole string in there.

Screw it. If I’m going to make you angry with not having error and code checking for every potential combination of check comments and check posts, well, I’m just going to do this too.

All right? And if we create or alter our procedure, this is the one that would have run the first time and made me look somewhat foolish. But if we run these now, SQL Server is able to cache and reuse these execution plans over and over and over again.

And performance for the comments run no longer sucks because this is now treated like its own individual query. And whatever happened before doesn’t actually matter all that much. For the post run, this is just about as fast as it’s been for anything else.

Is there more that we could do here? Yes. Absolutely.

There are indexes we could add, tweaks we could make to the query. Sure. There’s probably lots of stuff we could do. There’s probably thousands of things that we could do. What they are, I can’t even possibly begin to enumerate the billions of alternatives we have for tuning this query.

It’s just an insane amount of, insane variety of things that we could do to fix this thing up. But that’s probably for another video. So in this video, we learned that anything that we do that makes our job easier, like writing shortcut code like this, makes SQL Server’s job harder.

We end up with startup expression predicates, and these startup expression predicates are based on the passed in parameter values. And if we do a big switcheroo and we sometimes join to one table and sometimes join to the other table, whatever table we compile the plan for first is going to be okay.

Whatever one we didn’t compile the plan for first is probably going to suck. If you want to throw an option recompile hint on this, you have my blessing. I will write a letter to your employer.

Say option recompile is okay for a small fee. If you want to do this, it will also not work without option recompile hints. You will have typed more.

You will have looked busy for another 30 seconds, but you will not have solved a problem because bare-naked if branches like this where you just execute raw queries in them do not actually separate, does not actually give you any sort of performance fencing.

The logical fencing is still there. The performance fencing is not. SQL Server compiles a plan for both of these on the first execution. If you want true separation, you can execute something else, like sp-execute SQL with dynamic SQL.

You could put each of those queries into a store procedure and execute that store procedure within that if block, and that would give you true separation. You could also add option recompiles to absolutely everything.

I won’t care. I like option recompile a little bit. It’s kind of a good thing. So, if you’re going to write this kind of code, you can always also hire me to fix it.

Thank you. I love you. I hope you enjoyed yourselves, as usual. You might be able to tell I enjoyed myself a little bit. I do hope you learned something, or maybe picked up on something new.

What else? Gosh almighty. So many things to say. So little time. Sometimes I just want to pull up a chair and talk to you all day.

If you like this video, thumbs-ups are nice. Comments are nice. Smiley faces. Emojis.

Lots of emojis. Just a crap ton of emojis. Totally cool. If you like this sort of… If you like SQL Server… I don’t know. This sort of content. If you like SQL Server content, because I have a wide variety of SQL Server content, most of it is performance tuning.

None of it is backups, like maintenance, high availability, disaster recovery, because what a snooze fest that stuff is.

It’s all pretty much like performance tuning, query writing, stuff like that. Subscribe to my channel. Like almost 4,000 other people have. Because it’s a nice thing to do.

For a handsome young consultant, so we can continue to afford getting haircuts to look good in these videos. We can’t have ugly consultants on video, can we? Bad idea.

Anyway. I’ve got things to do. I’m going to go do them. Before my wife yells at me. 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 Index Design Patterns In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into index design strategies and their impact on query performance in SQL Server, specifically focusing on how indexes affect filtering and ordering. We explore why missing index requests often fall short of expectations and delve into the nuances of creating effective indexes for complex queries. By analyzing two sample queries and experimenting with different index configurations, I demonstrate that while some index designs can significantly improve query speed, others may not yield the desired results due to data distribution and query specifics. The video also touches on common misconceptions about index leading columns and the limitations of SQL Server’s missing index feature, providing valuable insights for database administrators looking to optimize their indexing strategies.

Full Transcript

Erik Darling right here with you, live, sort of. I mean, I feel pretty lively. Today, what are we going to talk about? I don’t know. How about some index design strategy? All right. We’re going to talk about how indexes affect filtering and ordering, and we’re going to talk about why, I think for the 10-bajillionth time, why SQL Server’s missing index requests are quite often underwhelming in their very existence. And how there is only one person to blame for that. He doesn’t care. All right. So, let’s make sure we got nothing going on here. Oh, God, I had some statistics. Look at these awful things. I bet they’re duplicates. I bet someone should delete those for performance. That was a callback to another video. Welcome. Welcome to my brain. It’s awful. It’s not a good place to be. It’s treachery. All right. So, we got two queries here. Let’s make sure query plans are enabled because those are important for everything that we do. They mostly function correctly, which is nice. Sort of like my brain. Mostly functional.

So, we’re going to run these. And we got no indexes right now at all. Zero indexes. Both of these queries return the required 5,000 rows. The problem is that neither one of these queries is great and fast and amazing. When we search for a sort of small amount of data, we scan a clustered index. We sort all our data, and it takes about 600 milliseconds. Not the end of the world, but you know what? Not every query to any extra exercise needs to start with something that takes 35 minutes and ends with someone being like, dude, I added an index. It was three seconds. It’s not every story. Not everything ends that way. It’s annoying. And when we search for more data, and we have to do some more sorting, we end up spilling a little bit here. Ouch. Ouch. I chihuahua. And then this one takes about 1.1 seconds. Almost twice as long as this one. Monocounted this sorty spilly thing over here. And I guess there’s like 100 milliseconds on that. Now, these queries have both asked for the exact same index. On post type ID, last activity date, as the key columns, because they’re in the where clause. And then include score and view count, because they are in the query, but they are not in the where clause. Our only columns in the where clause.

Right here. Right here. Post type ID and last activity date. We are ordering by score descending right here. Which seems important. It seems like something that an index would help with. Doesn’t it? Seems like we maybe had an index. Maybe we wouldn’t have to sort that data. Maybe we wouldn’t have to risk spilling that data to disk. Maybe we could have these queries run and not ask for any memory. Wouldn’t all of these things be grand? The thing is that the index that SQL Server asks for, which is just about this one. If you remember the green text above, here’s our where clause columns. And here’s our oh, they just happen to be there columns.

So let’s create this one. And I’m going to name this index whatever, because I’m allowed to do whatever I want. It’s my computer. And in the words of John Crook, I’m taking my ball and going home. So this index takes a minute to complete because, well, you know, there has been some chatter recently. I had a video about why some indexes are slow.

And of course, Paul White saw that video and absolutely dumped on me with the technical details for why the actual technical details for why it’s slow. Not just me being like, yeah, because they ended up on like three threads and it stunk. He like broke down the entire algorithm of parallel index creation. I will find the link to that and put it in the show notes because, gosh, gosh, is it impressive.

All right. So now we have the index that SQL Server asked for. Both of these queries will use that index. And both of these queries will, well, sort of benefit from it. This query does spectacularly with it. Does great. Four milliseconds in a seek, six milliseconds in a sort.

This one gets about, this one does better too, right? It’s about twice as fast as it used to be, right? It went from 1.1 seconds to about 600 milliseconds. So now this query with the index is about as fast as this query was with no index, just the clustered index on the post table.

So, and this is a, this is the sort of mixed bag that you get from most of Microsoft’s advice. Some things, yeah, they get better. Other things, not so much. It’s not great. It’s not fantastic. It’s just okay. So let’s, let’s try an index that I would, I see a lot of people go for when, when they’re dealing with this stuff.

Because a lot of people have this strange misconception about how indexes work. And a lot of people will think that if they have to sort by something, that that sort column has to be the leading column in the index. Right? So because we’re ordering by score descending, they think, whoa, well, we’re ordering by score descending.

That better be first so that we have that index sorted by that. So let’s, let’s recreate the index with score descending first in the index. And we’re going to see what happens in this case, which is, well, I should probably not hover down too low.

Because that’s going to completely spoil the final index that we’re going to create. And, I don’t know, ruin the whole video. Way to go, me. All right. So let’s run these two now.

And with score very first in the index. Well, it kind of flip-flops here, doesn’t it? Ugh. This one slowed down a lot.

This one is slower than it’s ever been. Now, both of these, both of these queries are single-threaded now. And the second query, well, this one, holy cow, zoom it. What are you, what is on your mind?

This one, really fast. Right? This one’s crazy fast now. This one, crazy slow now. The reason for that is because, well, to talk about post-type IDs a little bit, post-type ID 4s are pretty rare in the data.

There are not a lot of post-type IDs 4. Most of the post-types in the Stack Overflow database are, one, where there are about 6 million, two, where there are about 11 million, and then 3 through, like, 8 or something, which there are, like, a few hundred thousand.

So post-type ID 4 is rare. So once we sort by score, it still takes a whole lot of, like, it still takes a whole lot of, like, scanning through to find all the, find post-type ID 4 columns in that index.

So that’s not a very good index choice either. So we still have very mixed results. We have, we’ve created two indexes now. The first index helped the first query and sort of helped the second query.

The second index completely just neutered the first query and really helped the second query, right? The second query is now as fast as the first query was with the first index.

Promise that makes sense. So let’s go with the third index here because one thing that a lot of people overlook about B-tree indexes is that when you navigate them correctly with equality predicates, order is preserved throughout the, throughout the, throughout the, the second, the column after the equality predicate.

So where, you know, columns for, like, post-type ID where there are a lot of duplicates, you’re going to have, like, a whole lot of the number 1 and then all the scores and, like, for that range of number 1 are going to be ordered descending in this case, because we said descending in the, in the index definition right there.

Look at us, look at us go. And so that, that helps, right? Because we seek to the number 1 first in the index and then we have score in the order we care about and then last activity date, I mean, that’s an inequality predicate over here.

So, you know, it’s, it’s maybe not the most ideal situation to have score gatekeeping last activity date. Maybe some situations where I wouldn’t want that.

But now, with that index in place, both of these queries are equally as deliciously fast. Now, the important thing is, let me bring these up, let me get it in there, squeeze in there, everyone hug.

Big hugs for everybody. So the two important things that this index did for the, for the query that we’re running here. One, it allowed us to seek to the post type ID that we care about.

Two, notice that there’s no sorting in this index, in this query, query plan rather. There’s no sort operator. There’s a top, but we don’t need to sort the data because after we seek to post type ID, we have score in the order that we want it to be.

Then we’re able to evaluate our last activity date predicate as the final part of the seek. But it is a residual predicate, right? It is a predicate way up here in the residual nosebleeds.

And if Zoomit would listen to me, we would have a much better video. We would have high quality videoing. So last activity date is a residual predicate up here.

Post type ID is a seek predicate in here. And yeah, we don’t do any sorting. So without needing to, without actually having to sort data, again, the stuff that we avoid, you know, if this were a properly parameterized query, we would avoid, like, you know, parameter sensitivity issues with memory grants.

We would avoid, you know, potentially spilling the disk, potentially asking for way too much memory for some executions. We would not avoid green screen fuzzies over there. Those are annoying.

Anyway, a little bit about index design there. A little bit about how to approach different index design choices. Remember that the missing index request feature has a lot of blind spots and does not aim to please every area of your query.

It only aims to please the where clause. Other relational areas that are great, relational areas, yuck, that are good to index for, like order by, group by, joins, things like that.

It just ignores them and sticks those columns in the includes, which isn’t cool at all. So careful, careful with those. Careful with the green text, as they say on 4chan.

Anyway, I’m going to go do something with my life. I don’t know what it is. Might be good, might be bad, might jump out a window again.

We’ll have to wait and see. Anyway, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. If you like this video, compelling comments, thumbs ups, are great, great ways to tell me.

If you don’t like the video, and you feel so utterly driven to click the thumbs down button, I mean, you can tell me why if you want. I don’t care.

You can also just click thumbs down, and I’ll just say, oh, you’re that one person again. But you can also share your thoughts and feelings with me. I want to know. I want to know what love is.

Actually, for you, it’s probably hate or something, right? You’re giving me a thumbs down. If you like SQL Server content about performance tuning, indexing, query tuning, what other spam SEO words can I throw in this?

I think I’ve run out. Subscribe to my channel. A lot of other people do. We’re coming up on like 4,000 people. 4,000 people get notified whenever I say something, which is strange, to say the least.

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.

Spring Training

---

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Register for Data Saturday, on September 7th here!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Stop Worrying About Duplicate Statistics In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the peculiar behavior of SQL Server statistics and why deleting duplicate statistics might be a pointless exercise. I share my observations on how people often get fixated on minor performance tweaks that don’t significantly impact their server’s overall performance. By creating a simple utility table and running queries to generate various statistics, I demonstrate that SQL Server only updates one set of statistics even when multiple are created for the same column. This video aims to provide clarity on why focusing on duplicate statistics might be more about performing a micro-optimization than addressing real-world performance issues.

Full Transcript

Hey, it’s Erik Darling with that Darling Data Company who does the SQL Server Consultant Training and Education. And that’s him over there in the corner, losing his mind. In today’s video, which might be the second video I’ve recorded today, there’s no way for you to know. Good luck with that. We’re going to talk about, I mean, this is really just sort of like a funny behavioral thing. I see a lot of questions out and about in the SQL Server internet where people have this very strange fixation with deleting statistics. They’re like, I want to delete old statistics or I want to delete statistics based on something. Right? Like systems, duplicates, duplicates, like this thing, this problem they have where they just refuse to like, uh, buckle down and like do anything that might meaningfully help their server go faster. They get like micro fixated on these dumb things that aren’t going to help you. Right? It’s like when, like, when people have like the most basic ass performance problems and they’re like, gonna look at spit locks. You don’t like for what? Like you haven’t added a single index to any of these tables. Your queries don’t have where clauses. Like, uh, your SQL Server is a single core with four gigs of RAM. Uh, your VM admin hates you. Like you, you just don’t, you’re not, you’re not really gonna help anything by doing that. Um, if you’re, if you’re SQL Server is, uh, is tuned up to the point where you have time to sit back and think, I wonder if duplicate statistics are dragging me down. Uh, I want you to go on vacation or get a new job or maybe learn about a different database thing in the world. I don’t know. Like there, there are a lot of things that I would do, uh, rather than go looking for duplicate or I don’t know, like somehow try to figure out if statistics are going to be a little bit better.

or if the statistics are unused or not. And, um, and delete them because it’s, it’s, it’s just such useless performative garbage that, uh, I, I can’t take it seriously. Every time I see that question come up, I’m like, oh, you just don’t have no idea what you’re doing then, do you? You just, you’re just clueless in the world. It’s floating, floating on the ocean, wherever the breeze takes you. Um, so, uh, this is just kind of a strange little video about, um, like how SQL Server doesn’t update duplicate statistics. All right. So we have that to look forward to. So what I’m going to do, I’m going to create a little utility table called user stats. Uh, the definition of the table, um, the contents of the table have very, very little, uh, importance. Um, it’s just a, it’s just a table that I can kick around without worrying about like messing up data in the actual users table.

Because I need to run some updates. All right. So we just created this table. We just inserted rows into it. I think anyway, we should check the query plan to make sure something actually happened there. And it did. We put all 2.4 million rows in the users table, uh, into the user stats table, but just for a few of the columns, right? We only have ID downvotes, upvotes and account ID, right? That’s all we need for this one. So, uh, let’s look at statistics currently, right? Because this table has a clustered primary key on it. The thing is, we have not yet run a query that would cause SQL Server to generate statistics. Now, we created a table, and we created an index, and we loaded data into that index. If we had created the index after the fact, it would do a full scan, stat sampling of the data in that column, and we would have something here.

But, because we have not queried the table in a way where we had the index first, the data load second, we haven’t run a query to say like where ID equals one. SQL Server has not generated statistics for this index. All right? So, but this is not the, this is not the statistics object that we care about. We’re going to mess with a different column, and we’re going to run this query, where we’re going to be looking for, uh, account IDs within a certain range, and we are going to get this incredibly lucky number back. All right? Incredibly lucky. Uh, Chinese stuff, eight is, eight is a lucky number. Uh, when, when I lived in Chinatown, it was very funny, because like in, in American buildings, uh, the, we always skipped the 13th floor, and the buildings that were like built in Chinatown, they always skipped the fourth floor, because four is unlucky, but eight is very lucky.

So we have a very lucky number here. We have four eights. The only thing that would make this luckier is maybe a fifth eight, but I don’t know if there are rules around how many eights would be an unlucky number of eights, because I’m just not that culturally aware. Um, it was never really explained to me. It’s a little, a little strange. But anyway, uh, with that query run, um, and, uh, we can come back and look at this. And now we see that we have, we still have nothing on the primary key, which is okay.

We are never going to have anything on the primary key, because we’re not going to query and filter on the primary key. But now we have this system statistic called WASIS-OOF-47-BIF-90. Uh, it has all those rows in it. Uh, not all of those rows were included in the sample. Right? That’s a much smaller number than that. And, uh, it has had no modifications against it. All good there. All Gucci all the way down.

Now, I’m going to run, uh, a couple updates against the account ID column, and I’m just going to mangle the hell out of it. All right. And that’s, it’s all well and good. This is going to run for a few seconds. And now we’re going to look at the same statistics thing. And now we can see that we have a whole bunch of modifications.

Now, the only reason why I have where one equals one at the end of these is because if I don’t do, like, I know that there’s an option in SQL prompt to, like, not get yelled at. But when you have, like, a modification query without a where clause, uh, I just think it’s funny to leave that on and have SQL prompt say, oh, where one equals one. Cool where clause. No warning for you. That’s just kind of amusing. Anyway, uh, where was I?

We have now a bunch of modifications against that statistics object. Right? So if we rerun that query, SQL Server will re-update. Oh, sorry. I don’t need to show you the query plan for that. I’m, it’s such a weird, uh, muscle memory thing that I look at the query plan for everything.

I didn’t need to show you that query plan. But if we look at the stats now, uh, we will see, um, some, some rows sampled. Uh, I think that’s a smaller number than before. And, uh, now we’re back to zero modifications because we just, uh, updated those stats, uh, when that query ran. And I guess if you needed any proof about when this video got recorded, there it is. Um, the time is a little bit off because of my server.

For some reason, all my Windows servers installed in Pacific time. I have no idea why that happened. I didn’t choose it. I know I could change it. I just choose not to. Okay. Great. So here’s where the duplicate stats thing comes in. Right? So let’s say we have this index. We’re going to create an index on account ID. Right? We already have a statistics object on account ID, uh, because that’s the thing that we’re querying. Right?

So SQL Server created a system stat on account ID. And now we’re going to create an index on account ID and a statistics object on account ID. We’re going to do both, both things. Right? And now we’re going to run this, uh, not that query. We’re going to run this query again to look at these statistics objects. And I want you to just note, uh, that, you know, we have, uh, now three duplicate things. Right?

We have the system stat on account ID. We have an index on account ID, which produces a statistics object. And we have a custom user statistic on account ID. Right? And, uh, the rows sampled for these are both going to be equal to the number of rows in the table because they were made with, I mean, the index is full scan by default.

And these create stats thing, I added the full scan option to that. So we got that there. Okay. Cool. Let’s run these updates again because these updates are going to take a little bit longer than, uh, before, because now we have, now we have to update the index on account ID and that slows us down a little bit.

Actually makes it, it makes it go twice as slow or half as fast. However you want to, however you want to, uh, however you want to call that. And now if we look at this, we’re going to see all three of these stats objects have a whole bunch of modifications against them. Right? All three of them have modifications. All three.

The thing is if I run this query, you get a count, which finishes pretty quick. And I’m going to remember here that you don’t need to see the execution plan for this count query. Good job, Eric, darling. You did it.

And now we go look at this. Well, what do we have here? SQL Server only updated statistics for one of those.

Great. Great. Love it. Love it. Love to see that happen because we know that SQL Server didn’t update three separate statistics objects, uh, on, on, on the same, the same duplicative field.

Uh, we, we are a little, we, we are allowed to be a little bit disheartened that, uh, SQL Server, we, we, we used to have this nice full sampling of the statistics down here, but now we have this, this boohist, uh, default sampling of the, of the, of the, of the, for the statistics object there.

Not that that’s the end of the world. You know, it just kind of sucks that you go from like the big full scan of stats to like the little default sampling of stats. Now I know that there are settings in SQL Server where you can say, no, no, no.

I want you to maintain like whatever sampling percent I choose every time stats are updated, even if they’re auto stats, uh, because, no, I, I demand that level of control. No.

So you do have that option available to you, whether you use that or not. It’s up to you. You might find a great use for it. You might never find a use for it. Uh, I don’t care unless you’re paying me. Then I care a lot.

It’s like faith no more. So when might duplicate statistics be something that you care about? Well, it’s, it’s really hard to figure out from a query optimization standpoint, um, why or when you might care about, uh, duplicate statistics existing. Uh, it doesn’t really add that much, really doesn’t, it doesn’t add a whole lot to the, the, the query optimization conundrum.

Uh, the one thing that might be kind of interesting is, uh, you know, doing statistics updates maintenance, uh, where, you know, I, I, I do, you know, reasonably agree that it would be maybe not the most beneficial use of time for you to update. Uh, so like, you know, like if you’re, like you’re using maintenance plans or older scripts, you know, and you say, hey, well, you know, look for things with modifications. These are modifications and these would get updated, whether they get updated and used or, uh, whether they take a long time to update and they mess up your maintenance plan window.

I don’t know for this table, definitely not, but in general, uh, it’s just, it’s just really not going to make a difference, uh, to, to the general, general query performance on your server. If you go around and start getting rid of duplicate statistics, SQL Server doesn’t, is, is a little bit smarter than that.

Uh, at least, at least in this one regard, which is, which is, I guess, a nice regard to be smart in. Um, I guess if you’re, if you’re really concerned about maintenance, um, you could, if you wanted to, um, well, I mean, I guess, I guess you could delete duplicate system statistics, let SQL Server recreate any that it might need.

But, uh, if, if, but that would only really make sense on, like, gigantic tables where, you know, um, you know, updating those statistics with, uh, with, with, with, even, geez. The, the, if it’s, if it’s still really slow with the default sampling. Yeah, sure.

I guess. But, like, if you’re using full scanning, you’re like, well, it’s slow. You’re like, well, maybe, maybe if you’re going to be such a control freak that you need to do a full scan on everything, you should be picking specific statistics to do the full scan on rather than just saying, hey, store procedure, go find anything that’s been modified and update it.

Take a little control. Take a little more control, you freak. Take a little more control.

And maybe, maybe I’ll just take the weekend off. Wouldn’t that be nice? Me just hanging out.

So anyway, uh, thank you for watching. Um, I hope you enjoyed yourselves. I hope you learned something. And, um, I will see you in another video at another time.

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.

What’s The Point of 1 = (SELECT 1) In SQL Server Queries?

---

Thanks for watching!

Video Summary

In this video, I delve into the age-old question of why one might see `1 = SELECT 1` in SQL Server queries, a topic that garners about 70 to 80 comments per week. I explain that this snippet is often used to avoid trivial plans, which can hide important optimizations or cause confusion due to simple parameterization kicking in. By incorporating `1 = SELECT 1`, we ensure the query optimizer makes full cost-based decisions, leading to potentially more optimized execution plans. The video walks through examples where `1 = SELECT 1` is crucial for demonstrating certain behaviors and clarifying complex queries, especially when presenting or debugging issues. I also discuss how this technique can help in maintaining clarity during demos and presentations by ensuring the exact query text used matches what was executed, avoiding misunderstandings and confusion among viewers.

Full Transcript

Erik Darling here, fresh from a full day of celebrating freedom, and back to work. For you, because, I don’t know, do I work for you? I might. I might work for some of you who watch. Maybe not enough. Maybe I should work for more of you who watch. That’d be nice. Then I could just do this all day, and I wouldn’t have to, like, do stuff over there on the computer you can’t see. So that’d be cool. I don’t know. Anyway, we’re gonna, in this video, I’m gonna answer a question that I answer 70 to 80 times a week. And it is, why do you have 1 equals select 1 in your queries? And the funny thing is that everyone who asked me that question asked me that in a comment. What amuses me, I suppose, is that if you were to type what’s the point of 1 equals select one into any search engine, even dumb Bing can find it. You would find my New York Times bestselling blog post called, what’s the point of 1 equals select 1 in SQL Server queries? And you would see, you would see, you know, the same thing.

nearly the same text instead of demos appearing in this SQL Server Management window in handy, easy-to-read blog format.

So in this video, I’m going to read my blog post to you, and hopefully you will watch it, and hopefully this will be available as a secondary resource for anyone who looks at one of my demos and is puzzled by the presence of 1 equals select 1.

So here we go. We are already using the correct database. I believe we have already dropped all of the indexes we can possibly drop, so we’re good there, right?

That’s excellent news. We’re in good shape, you and me. So the main reasons for using 1 equals select 1 in SQL Server queries is to avoid two things.

One is a trivial plan, because trivial plans can hide all sorts of, or preclude the inclusion, academics, of certain optimizations that you only get when the optimization level is full.

So that’s one good reason. And I often use it in my demo queries because I want to write the simplest possible demo query to show the behavior I want you to see is possible.

The trouble is that sometimes when the simplest query doesn’t work out, either because the trivial plan does not get me the optimization thing that I want, or people see the simple parameterization thing kick in and get very confused.

Like, is that forced parameterization? Like, what’s wrong with your database? Is it broken?

Why is that parameter there? And it’s kind of funny. In some ways, I think that writing slightly more complicated queries would be less distracting to the casual viewer than just putting 1 equals select 1 in there to do what I want.

The trouble with writing more complicated queries is they become more prone to failing. The demo gods are harsh gods.

They, I don’t know, they hate me sometimes. So, yeah, there we go. Anyway, so some examples of, you know, things like I’m talking about.

1 equals select 1. Important stuff. Things you should know. And I’m not suggesting that you should put in 1 equals select 1 in all of your queries, but if you’re writing demos or you’re just testing stuff out, it can kind of be a neat thing to see if it changes anything.

So, here’s an example where I’m going to run these two queries, and we’re going to look at these two execution plans. And, of course, as promised, this query up here is simple parameterized.

You might be able to tell by looking at some of this stuff and realizing Erik Darling is not a dork and does not put square brackets on around absolutely everything in his query. And Erik Darling is the kind of guy who properly uses as when aliasing.

Aliasing things. Asleucing things. So, this query is clearly not exactly the one that I wrote.

It’s also got a little parameter over here way at the end called at 1. Fascinating. Absolutely fascinating stuff.

You might be even more fascinated to learn that this is a trivial plan. All right. You can see the optimization level trivial here. And we can see kind of a strange thing with the parameter list where SQL Server inferred the data type of the number 2 as a tiny int. If we were to write queries with a number 1 higher than the max of tiny int, small int, int, and big int, we would see the data type change for the parameter of each one of these.

At some point with the int max and the big int max, it starts using weird decimal types, though. It doesn’t explicitly use big int.

Sorry. So, that’s one reason why. Right? So, we can see that SQL Server clearly does slightly more thoughtful optimization with the second query that has 1 equals select 1 on it because the second query, of course, SQL Server says, Hey, have you thought about adding an index to make this faster?

Now, you know, 188 milliseconds isn’t terribly slow. Fine. I know.

But sometimes it’s the thought that counts. You might also notice that this query is written much more in the style of Erik Darling, where we have a proper as, for our alien, as-lesy-fiziting, and we don’t have dorky square brackets around things that don’t need them.

Right? So, cool. SQL Server gave me my query back. Stop enforcing its stupid query formatting on my beautifully written and formatted query.

Bug off, SQL Server. Sought off, Swampy, as a wise man once said. So, what gets fully optimized?

Right? Aside from, like, you know, 1 equals select 1, all sorts of things get fully optimized, but generally they require SQL Server to have to make some sort of cost-based decision about what the cheapest way to do something is.

So, join, subqueries, aggregations, ordering without a supporting index, lots of stuff that, you know, where all of a sudden SQL Server has to do more than figure out, I just have to select some rows from one table where this column equals a thing.

Easy peasy. I don’t have to, there’s not a lot of cost-based decision making in that process, unless there are multiple indexes involved. Of course, your tables all have multiple indexes involved.

So, the likelihood of you needing to write 1 equals select 1 and, like, a production query are pretty low. So, let’s look at these two queries. Right?

We’re going to select the top 1,000 IDs grouped by ID, which, of course, is meaningless because ID is all, what do you call it, unique values. It is the clustered primary key of the table.

And the reputation column, of course, is very ununique. Very ununique as a column. And so, you know, these obviously return different results because we’re doing different things.

But this query right here, if we look at this, we are with a trivial plan once again because there is no cost-based decision to make. This one down here is not a trivial plan. This is a fully optimized plan.

And the reason this one is fully optimized is, of course, because SQL Server had to choose what to do in here. Right? This operator represents a cost-based decision. And this operator is why.

SQL Server was like, oh, I’m going to fully optimize this thing because I need to think about how to group this column. Am I going to use a stream aggregate? Am I going to use a regular hash match?

Do I want to use a partial aggregate first? And at the end, it shows a hash match flow distinct. Right? That was apparently the cheapest one. So, happy times there.

Happy, happy times. So, one reason, or it’s a good way to put this. One situation where using 1 equals select 1 isn’t necessary is if you have an index, if you have multiple indexes on a table.

Now, this index right here has absolutely nothing to do with this query. We’re not.

Like, this is just on creation date. And the rest of these bottom two queries have nothing to do with the column creation date. For the first two queries, it do have a lot to do with the column creation date. If we run these, we will see the first query.

Again, look at this ugly, awful, square bracket, dork formatting. And no as with the alias. Shame on you, SQL Server.

And the bottom query, of course, does. With the 1 equals select 1, this looks more like what I wrote. Right? We can see the literal for the date. We don’t have this thing get substituted with a parameter.

And so this is one of those things where 1 equals select 1 takes a little bit of the confusion out of either me zooming in, doing a video like this, presenting live, taking screenshots for a presentation. And when I zoom in and show the query text of a query, sometimes it’s kind of confusing when people don’t see the exact query that they just saw me run. And so a lot of times, just for clarity, it makes a lot more sense.

Even if I don’t include the 1 equals select 1 portion in the screenshot, it makes a lot more sense for me to take a screenshot of just this part so that you can see that it is actually the query that I was just talking about running with the literal values. If I told you I was running a query and then you saw this in the screenshot, you’d be like, where the hell did that come from? Is that in the store procedure now?

No, Eric, that looks nothing like the query you executed. Are you insane? Right? So there’s reasons, right? There’s reasons for these things.

Some of these reasons are presentation layer reasons. Others of them are truly query optimization reasons. And now, if we look at these two queries, now look, I agree that the second query is absolutely, absurdly ridiculous, right? There is absolutely no reason to ever use this index for the query that we’re running because it’s only on the creation date column.

But having this superfluous nonclustered index around actually makes, right? Because this is a valid plan choice, right? SQL Server would cost this choice and say, oh, maybe no.

But having that around is a reason why this top query, now to make things even kind of weirder. Here, this is where your noodle is really going to get baked because look what we have here, right? This looks like simple parameterization.

But over here, we have full optimization, right? So sometimes, even when you get full optimization for a query, sometimes you still need 1 equals select 1 to get rid of this ooky query text with the terrible dorky square brackets and the lack of an as in the alias. So 1 equals select 1 has some extra powers to it that even getting full optimization for a query doesn’t have for presentation stuff like this.

So that’s another good thing to keep in mind. Now, the other problem that you might run into with trivial plans, and this is something that I see a lot. So, like, you know, I think they used to be a lot more common.

I forget exactly. There were a few people who would always write articles comparing the query optimizer, the query optimizer’s abilities with, like, MySQL or Postgres and SQL Server or Oracle or DB2 or, like, you know, a whole bunch of different relational query engines. The problem is that, like, they may have had some specialty in, like, MySQL and or Postgres and or Oracle and or something else.

But they were pretty stupid about SQL Server. There were things that they didn’t know to look for and there were things that they just didn’t have the expertise in to, like, understand what, like, why things were different between certain engines. Now, granted, you probably shouldn’t need a very, very deep understanding to understand why SQL Server might look at a check constraint in one engine but then not do it in SQL Server.

But that was the case for a lot of things. And this is a pretty good example of that. So if I add this constraint to the users table, right, which just validates that every reputation in the users table is greater than or equal to one and less than or equal to two million because at this point in time, John Skeet still does not have two million reputations.

I forget what he’s up to. It’s been a while since I looked. Maybe I’ll check in after this video.

But then if I run these two queries and look at the execution plans, both of them return zero rows. And, of course, here’s where the demo gods have absolutely betrayed me because you know what I didn’t do? I didn’t drop this index on creation date.

So let’s remember to add that to the demo script next time. And let’s make sure that Erik Darling does 100 push-ups. Ah, my own petard.

There we go. That’s what I wanted. So this first query obviously scans the entire clustered index looking for where reputation equals this substituted parameter. Now, SQL Server needed a plan, right, since this is a trivial plan with simple parameterization.

SQL Server needed an execution plan that would be safe, that would be cacheably safe for any other execution of this query where it would maybe hit a rep. Maybe it would be looking for a reputation where, you know, what do you call it? Like it might exist in the table.

So, like, I’m searching for zero here, right? My search is for someone with a reputation of zero, and this query rightly does a constant scan because this query doesn’t get simple parameterization. This query doesn’t get a trivial plan.

And so SQL Server can logically detect that this query is not going to return any rows. We can just skip the whole thing. Again, with this query, of course, it can’t do that because of the parameter substitution over here. It has to say, well, if someone searches for reputation equals one or two or three or ten or five million next, we might need to actually look and see the return rows from the table.

We have to go figure that out. So if SQL Server wants to cache and reuse this plan, it can’t be the constant scan because the constant scan doesn’t touch the table, doesn’t return any rows, and blah, blah, blah, blah, blah, blah, blah, blah. Yeah. It’s a lot like how, well, I mean, not a lot like how, but it is reasonably close to sort of the neighborhood of why if you create a filtered index on, let’s say, creation date, and then like a stored procedure or in an entity, like an ORM query, you pass a parameter to search for creation date.

SQL Server can’t use that filtered index because, of course, you know, like it has to cache and reuse a parameterized plan where some parameter values might qualify to use a filtered index and some might not, right? So like it’s sort of similar to that where like the cached and reuse plan has to be safe for anyone, but the cached and reusable plan has to be safe for everyone. But, you know, a more specific, like, you know, more optimized for the literal value plan, like if you put option recompile or something on it, like that would be like a more, a plan that’s more specifically geared towards the query you’re running than a good, than a general plan that would work for any set of parameters.

So, I’m glad I got that off my chest, finally. It feels good. Feels real good. I feel like I stretched. I don’t know. I feel like I slept 18 hours. I’m just kidding. After my July 4th, it’s going to be a while before I feel like I slept 18 hours.

There was a lot of mezcal and brisket, which is a bit of an odd combo, but trust me on this one. They go well together. I am a fan. I am a newfound fan of mezcal and brisket in one mouth. All in the same mouth.

So, with that additionally off my chest, Eric’s cooking tips. Put mezcal and brisket in mouth. Mix, stir thoroughly.

Thank you for watching. I’m glad you made it to the end with me. I hope you enjoyed yourselves. I hope that you learned something. And of course, you know, as usual, if you like this sort of SQL Server content, please subscribe to my channel.

Join the nearly 3,824 other data darlings who get notified when I present these little bits of my love to you. When I show you my love.

If you like this video, comments, thumbs ups, things like that are nice. And as promised in my last video, I got a haircut. The whole thing.

Whole head. Granted, it’s looking a little dicey up there. I might do something about that. Because that’s a little bit much for a man of my incredibly young age. If I want to retain my beer gut magazine accolade of being the youngest and most handsome SQL Server consultant in the known universe, then I might want to put some serum on that.

Maybe grow a little bit of that hair back. Also, like, if I don’t do that, you know, maybe my hair guy will, you know, go out of business and starve and lose his house and his car.

That’d just be depressing. I got to keep getting haircuts to make the world keep going around. You know? That’s why we all do the things we do. Keep the ball spinning. So, anyway.

I’m going to go now. Thank you for watching. And I’ll see you in another video shortly. Thank you. Goodbye.

Going Further

---

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