This week, I’m dedicating all my blog content to PASS Summit. Why? Because I’m here and it seems stupid to not talk about that.
Oh dear, it’s Friday, and I’ve got a plane to catch. I suppose I should post some thoughts from the week. Hey, has anyone seen my sneakers?
All updates will be under this line. Hit refresh once in a while. Enjoy the show.
Livefeed!
What a great week here in Seattle. The conference was a grand whirlwind of catching up with old friends as usual, but I was really wowed by the number of first time attendees.
If you missed the announcement, Red Gate said during their keynote that there will be a bunch of mini-PASS events coming up too. If the main thing keeping you away from Seattle is travel, I’d encourage you to check out the others when they happen. They’ll be happening in Dallas, NYC, and Amsterdam. I’ll be at as many as they’ll have me for. Speaking to many attendees, they say that if they weren’t local to the area they’d never be able to go. This may be your chance to get involved in a far more convenient location.
It can be difficult to describe the value of attending these things, and I’m somewhat terrible at marketing pitches. But what has struck me over the years is that I’ve become friends with everyone I started off idolizing. It’s quite an exceptional community that has been built up over the years and events like these make me happy and honored to be a part of it.
Every time someone says hello, thanks me for something, wants to take a picture, and especially when they seek me out to ask me a question, it really reinforces the reach that being a part of this community gives. It doesn’t take much to share something that helps many people in great ways.
Anyway, my place is taking off. See you all next time around.
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.
That’s from 15:45 to 17:00PM in the Terrace Suite. Come by and laugh at Average NOLOCK Enjoyers with me.
All updates will be under this line. Hit refresh once in a while. Enjoy the show.
Livefeed!
Catch me live streaming!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
This week, I’m dedicating all my blog content to PASS Summit. Why? Because I’m here and it seems stupid to not talk about that.
Hey, it’s finally Wednesday. That means I can sleep in and have nothing to do but annoy people around the convention center.
All updates will be under this line. Hit refresh once in a while. Enjoy the show.
Livefeed!
Video Summary
In this video, I share my experience trying to conduct some fun interviews with people who just passed an exam, but unfortunately, many of them were in a bad mood today. Despite the initial setback, I managed to secure something special—an exclusive SSMS 21 T-shirt in Dirt from Aaron Stilato, which I couldn’t resist showing off. The shirt’s dark mode design caught the camera’s attention, and it really did look magnificent under the spotlight. It was a delightful surprise to see SSMS finally supporting dark mode, and I can only hope this feature brings more joy to developers who use the tool daily.
Full Transcript
So I spent some time trying to get the people that passed to do fun interviews with me, but a lot of people are in a bad mood today. So that didn’t quite work out. Maybe tomorrow will be better after people have had a chance to drink and whatever. But I did manage to score, I think, an exclusive T-shirt from Aaron Stilato. And this is an SSMS 21 T-shirt in Dirt. I just want you to take a look at dark mode. And I just want you to take a look at this beauty. Because for some reason, this camera just wants to look at the pillows on my hotel room bed. But take a look at this, the majesty of this shirt. Look at that. Who said SSMS could never be in dark mode? I don’t know. That’s just, that’s just silly to me. And I’m very happy that we finally got this going for us. I think maybe we can thank Visual Studio for this. But anyway, I’m going to go back. I’m going to go back in again and see if I can find someone, someone who wants to be fun with me. All right. Erik Darling, Darling Data, signing off.
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.
All updates will be under this line. Hit refresh once in a while. Enjoy the show.
Livefeed!
Alright, doing better on this today. The first step is remembering to do it.
Here are some fun pictures!
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.
All updates will be under this line. Hit refresh once in a while. Enjoy the show.
Livefeed!
It turns out that teaching all day doesn’t give you much time to update blog posts.
Day One Got Done
The class me and Kendra did on performance tuning internals went fantastic, and the nice folks at Red Gate remembered my birthday.
Sugar Buzz
We had about 70 people show up. Great questions (the answers were pretty stellar too, if I do say so myself), and absolutely no post-lunch nappers.
Anyway, it’s 6am here in Seattle while I’m updating this a day late. I have a second precon to do some prep work for.
Thanks for reading!
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.
More Of What Missing Index Requests Miss In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the world of missing index requests and why they often don’t provide the performance boost we might expect. Starting off by dropping all indexes in my database, I ran a query that SQL Server repeatedly suggested would benefit from various indexes, only to find it still running after 14 minutes despite adding these indexes. This led me to explore how certain query patterns, like distinct counts and batch mode operations, can actually run faster without any additional indexes at all. The video also touches on the importance of not always relying on missing index requests for performance tuning and highlights alternative strategies that might be more effective in specific scenarios. If you’re interested in diving deeper into SQL Server performance tuning or need help with your own database issues, I offer consulting services and training courses that can provide valuable insights.
Full Transcript
Erik Darling here with Darling Data. Bet you didn’t know that. It’s hard enough to get some people to read things like the video title before making weird comments. Even more difficult to get people to watch the video before telling you something that you said in the video like you didn’t say it in the video. So, you know, that’s fun. Not at all frustrating as a content creator, but what can you do? Hopefully everyone can take the time to read Darling Data and we can go from there. I think that’s a reasonable starting place for all you smart people, especially ones that have senior in your title. So, this is a little bit of an addendum and it only made sense to record this right after the other video because it is an addendum. Hopefully I spelled addendum right. I often don’t. I don’t have senior in my title. So, we do what we can here at Darling Data. Like check for Intel drivers and sometimes Intel times out and then it has that helpful little pop-up behind me, which I love.
Not at all distracting or annoying. Anyway, before we talk about our addendum to why missing index requests stink, we’re going to talk about business. Important business stuff. If you would like to support this channel for as low as $4 a month, you can do that by signing up for a membership via the link in the video description. If $4 a month escapes you, then you can of course, like videos, like videos, comment on videos, and even subscribe to the channel. All wonderful things to do that make my probably enlarged heart go pitter-patter. If you need help with SQL Server, if you watch the things that I talk about and you think, gosh, I’m having those problems and I’d like Erik Darling to come fix those problems for me, that’s the kind of thing that I do via my consulting.
Crazy, I know. Crazy, I know. I’m not just making this stuff up. If you would like training about SQL Server performance tuning, I have beginner, intermediate, and expert level stuff all available to you for life for 75% off, which means about $150 USD. Go get it while you can. Who knows when that might expire? Not the for life part, but I might actually put more work into that. Who knows? Crazy stuff. If you would like to see me live and in person. In other words, I might stop recording free videos and make you start paying for things.
Ah, I know. Some people, you do one free thing and they can’t stop asking you for more free things. It’s a wild world out there. But if you would like to come see me live and in person, well, the pre-cons are not for free, but actually none of this is for free unless you win something from Redgate or you sneak in. I’m not condoning sneaking in. I’m just saying it’s possible you could.
November 4th and 5th, I will be in Seattle with the lovely and talented Kendra Little co-hosting two magnificent days of SQL Server performance tuning. If there is an event nearby to you that you think the old Eric Darlingmeister might make a good addition to by way of a pre-con, let me know what that is and I might just show up and I don’t know. So, this stuff happens sometimes. So, with that out of the way, let’s go talk about why missing index requests annoy me a little bit more.
So, well, lucky me, this query is still running. If you look sort of, well, no, sorry, this one. If you look, you can still see the time moving a bit. 13, almost 14 minutes. So, when I started this query off, what I did was drop all the indexes in my database. And then I just didn’t even, like, run the query because I’m not waiting 14 minutes for anything.
Except, I don’t know. Okay, a few things I would wait 14 minutes for. Not much, not many things. I just got the estimated plan a bunch of times. And every time I got the estimated plan, SQL Server told me that a different missing index request would fix everything. If we scroll down a little bit, you’re going to see, first, we started with an index on the comments table on the score column that includes user ID, which I guess makes some sense because we’re filtering on score and we’re joining on the user ID.
And then it said, well, now we need one on the post table on score and then owner user ID that includes post type ID, which I guess makes sense because we’re filtering on score and we’re joining on owner user ID and we’re selecting post type ID. So, okay, fine, totally sensible. And then it was like, oh, well, no, we need one on the votes table.
We need one on vote type ID and post ID. It makes sense because, you know, we’re correlating to the post table on post ID and we’re filtering to vote type ID. So, okay, reasonable guess there.
And then, you know, SQL Server was like, whoa, whoa, whoa, whoa, whoa, whoa, whoa. Now we need one on the users table on reputation and display name. Okay, we’re filtering on reputation and we’re selecting display name.
So that’s okay, too. And then SQL Server was like, you know, now that I think about it, I was wrong about comments, votes, and posts. I would like three slightly different indexes on those.
And thankfully, that was the end of it. Unfortunately for us, and unfortunately for our query, that was not the end of the, sorry, I’m going to do that every time. That was not the end of our performance issue because this query has still been running for 15 minutes.
If we run SP who is active and we get the execution plan, we’ll kind of see what was happening here where, well, I mean, this part of the query was, I mean, you know, still, it got better, but it’s still kind of slow. We spend two seconds on the, two milliseconds on the users table, 374 milliseconds on the post table, 3.6 seconds on the votes table, getting a little yucky there, and then a minute and 51 seconds on the comments table.
And all this after adding in every single index that SQL Server assured us would make things better. Where we really start spending a lot of time in here is when we start getting into this part of the query. No indexing is going to help this part of the query.
There is no indexing to help anything from after this. Anything from this line on, indexing is not going to help you with. Spooling all this data into tempDB.
SQL Server is not going to fix that. Respooling that data out of this spool. So now this is sort of an interesting thing here. Because there is no child operator to this spool, we can infer that this spool and this spool and this spool are all the same spool, and we just need to get the data out of them once we have finished getting the data into this spool.
We just need to spool and spool again and again, or spool and spool alike maybe, I guess. So no index is going to help this. But SQL Server doesn’t really know that.
SQL Server also doesn’t really have a good view of what might be slow and all that. This is up to sort of the more experienced query tuner to figure out on their own. Now, one thing that SQL Server never told me was that certain things can make that query pattern faster, particularly where we are getting a couple distinct counts, which is why we had all that spooling going on.
So something like using compatibility level. Now, I want to be very clear about this. I am using compat level 140 currently, so I’m already using the new cardinality estimation model.
For example, me moving to compat level 160 is just to get batch mode on rowstore. And even with batch mode on rowstore in place, or rather, especially with batch mode on rowstore in place, this query will run faster.
At least, it did before. Whether it does now or not is up to the demo gods. But what’s even funnier about this, aside from the fact that this thing is still pretty slow, is if we get rid of all the indexes, right?
We have now gotten rid of every single index that SQL Server suggested and that I dutifully obeyed. If we get rid of all of those indexes and we run this without any indexes, right? We didn’t have to add a single one.
We needed nothing. This thing will finish. I think it was about 10 seconds. If not, I’m going to feel really embarrassed. Of course, you know, it wouldn’t be the end of the world.
There we go. Okay, I was off by five seconds. Sorry about that. This query plan might be slightly different from the last one that I got. Oh, it is.
Look. Yeah, SQL Server chose a nested loops join there for some reason. I wonder why you would do that. Why would you choose a nested loops join? Hmm. It’s a bit of rose to choose a nested loops join for.
But I don’t know. So actually kind of funny. This plan is a bit different from the query plan that I got before. But what’s different here is that a whole bunch of this stuff now runs in batch mode. If we look at this, we will see batch mode right above my beautiful gigantic head.
If we look at this hash match aggregate, even though it’s spilled, we will see batch mode floating around there. If we look at this hash match inner join, we will see batch mode there. We will see batch mode here.
We will see batch mode here. We might even see batch mode on a couple other of these index doodads, which is so nice to see. Oh, not post table. Post table is still rowstore. Sad for post table.
Hmm. But SQL Server doesn’t have to do all that crazy spooling when you do distinct counts and use batch mode. It only has to do that in row mode. Batch mode fixes a lot of stuff.
But notice SQL Server is back to saying, but you know, this might be better with an index. SQL Server just needs to learn when it’s wrong. It needs to learn when to stop suggesting things.
SQL Server is kind of like, you know, when you just want to like complain about something a little bit and someone keeps trying to like solve every problem. And you’re just like, no, just listen to me complain a little bit. I don’t need, you know, I don’t need like advice about this.
You know, like one time I was complaining about like importing an Excel file with SSMS and like 40 people were like, oh, just learn SSIS and do it with SSIS. I’m like, no, I just really, I just, I just wanted to complain about loading an Excel file in with this thing. I don’t know.
I’m not going to go learn SSIS. It’s a dead technology. Right? What do you want to go learn SSIS for? You out of your minds? Anyway, this is another reason why when you are tuning queries, missing index requests are not the end all be all of making a query go faster. Oftentimes, there are different and better approaches that you can take.
For this specific query that we’re looking at here, there are all sorts of things that we could consider that might make things better. Like we could, you know, put part of the query with part of the aggregate, like part of the aggregations already done into a temp table. You know, it’s a perfectly feasible thing to do.
Right? All sorts of stuff that we could have done. We could, I chose batch mode because that’s a real easy way to test if a query goes faster. If you get batch mode going on and a query goes faster, you’ve already solved all the problems without indexes, which is great.
Because everyone knows, or rather, everyone is terrified of indexes. Like, ugh, I can’t add another index. What if I slow down an insert?
God, what will happen? Let’s let every single read query that our application has suffer because I’m afraid that I might slow down an insert. What a great idea.
Right? It’s like LinkedIn-level advice on things. Oh, indexes. But they might slow down inserts, updates, deletes. Oh, no. Not that.
Anyway. I was going somewhere with that. But you know what? I think I’m just going to go get ready to record another video. So I think I’d rather do that instead.
So thank you for watching. I hope you enjoyed yourselves. I hope you enjoyed this addendum to why I hate missing index requests. I hope you learned something, and I hope that you will do all the stuff that I tell you to do in life because I’ve made enough mistakes that I feel qualified to tell you what to do.
So I think that’s good. Anyway, thank you for watching. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Writing boilerplate stuff that I’m bad at (sales and marketing drivel, abstracts, lists of topics)
But every time I ask it to do that stuff, I really have to pay attention to what it gives me back. It’s often a reasonable starting place, but sometimes it really goes off the rails.
That’s true of technical stuff, too. Here’s where I’ve had a really bad time, and if there’s anything you know deeply and intimately, you’ll find similar problems too.
I’ve asked it some SQL Server stuff, like:
Start this DMV query for me: It comes up with DMVs that don’t exist and columns that don’t exist.
Write me a restore command for a single table: It actually tried to do it. There is no such thing as object-level restores in SQL Server.
Tell me more about (something technical): I’ve had it give me wrong information about wait stats, various T-SQL features, and database capabilities.
This is one of my favorite interactions.
LLM garbage
In other words, it’s wrong more than it’s right, even about yes or no questions.
Worse, it’s confidently wrong. I need you to fully understand what a farce this all is.
Anyone who thinks it’s capable of taking on more complex tasks and challenges knows nothing about the complex tasks and challenges that they think it’s capable of taking on.
What’s The Point?
I see a lot of people using various LLMs to generate stuff to say, and it’s all the same, and it’s all wrong.
How many times can you post the same “10 things to avoid in SQL queries” list that starts with “Avoid SELECT *” and expect to be taken seriously?
Worse, there are people who post and re-post all manner of “SQL tips” that are blatantly incorrect, missing important details, or offer advice that ranges from doesn’t-make-any-sense-whatsoever to downright-harmful.
If these were people who knew what they were talking about in the first place, they’d be ashamed and embarrassed to read what they’re posting.
But they aren’t people who know what they’re talking about, and so the badness proliferates.
Somehow or another, these posts get tons of traction and interaction. I don’t understand the amplification mechanisms, but I find the whole situation quite appalling.
For all the talk about various forms of misinformation, and how the general public needs to be protected from it, I don’t see anyone rushing to plug the onslaught of garbage that various LLMs produce.
Seriously. Talk to it about something you know quite well for a bit, and you’ll quickly see the problems with things it comes up with.
If you want to mess with LLMs for language-oriented tasks, fine. Treat it like having an executive assistant and double/triple check everything it comes up with.
If you’re using LLMs to come up with technical content, you need to stop immediately. You’re lying to people, and embarrassing yourself in the process.
We all know you didn’t write that, and anyone with a modicum of sense knows just how little you actually know.
I was recently linked to this podcast about the whole situation, and I found it quite spot-on:
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into why missing index requests in SQL Server often end up being more frustrating than helpful. Erik Darling from Darling Data takes a humorous approach while discussing the intricacies of indexing and query performance. He highlights how SQL Server’s suggestions can be misleading, especially when dealing with simple queries that return results quickly but still generate missing index recommendations. I share tips on identifying truly problematic areas in your database through practical examples and explain why certain factors like trivial plans, non-sargable predicates, and existing indexes might prevent useful missing index requests from appearing at all. By focusing on real-world scenarios and query performance metrics, this video aims to provide a clearer understanding of when and how to address indexing issues effectively.
Full Transcript
Erik Darling here with Darling Data. Sorry, I’m going to straighten up my tie a little bit. Make sure that’s dangling in the right place. Someday I’ll come up with a better way of wearing this thing, but for now you all just have to deal with me having a strange lollipop poking out of my shirt. When I put it up here there’s just too much stuff. I want to avoid stuff. I don’t like the unpleasant stuff. No one likes the unpleasant stuff. Well, some people do. I suppose 4chan still has an audience. Anyway, in today’s video we’re going to talk about why missing index requests stink. And I think you’ll all enjoy this one. But before we do that, guess what? Yeah, it’s that time. If you would like to show your support for my humble channel with a small monthly donation, as low as $4, you can sign up via the link. down in the description below. If you don’t have $4 a month, I totally understand. Those bookies are bloodthirsty maniacs. Stop betting on football. Well, actually, baseball, I guess, is what’s wrapping up now. But it would make me very happy if you would like and comment and subscribe to the channel because that’s the kind thing to do. That’s the nice thing to do. If you need help with your SQL Server, for any of these things, that’s my job. I fix SQL servers. So give me a ring. You can contact me through any variety of ways. I seem to be everywhere. If you would like to get some high quality, low cost training, you can get that for life.
But for the for the for the low cost of around 150 USD with the discount code spring cleaning. If you would like to catch me live and in person, the only way to do that right now is to go to Seattle in November. If you would like me to come closer to you for whatever reason that is, please, please nothing nefarious. I I’m a nice boy. You can tell me what events might happen near you that might be good for me to go to because I like going to events. It gives me something to do other than be hung over every weekend. So I’m kidding. I haven’t had a hangover since 1994. I’m just I’m just I’m just that good at drinking. But anyway, let’s talk a little bit about why about why I have become disillusioned over the many years by missing index requests. So if all if all is gone, according to prophecy, I’ve already created this index, and I have just wonderful. What I’m going to do is I’m going to start off by running these two queries. And I think you might be somewhat shocked.
And amazed and amazed and astounded at what ends up happening when these two queries finish running. They’re not anything typically like particularly complex, at least as far as you know, like production queries go. Often I see production queries that are hundreds to thousands of lines long, with way more joins and conditions and sub queries, CTE stacked thousands of lines high. And, and, and, you know, there’s a lot to dig through with those, of course, but these are fairly simple queries. It’s actually the same query twice.
The only difference is one asked for the top 100, and one asked for the top 500. And at the end of the day, you know, they both return, you know, slightly different results, because from one, we asked for the top 100 and get 100. And for the second one, we asked for the top 100, and we get 158. But that’s okay, because that’s not really the point. The point is that this first query takes 15 seconds. You can see that there, spends 14, well, just let’s call it about 14 seconds, even creating an index for us, and doesn’t tell us that we need an index. Right? It doesn’t say this eager index pool means SQL Server created an index when this query ran. And notice that when we ran a query again, SQL Server was just like, well, I guess we’re going to build that index again. Right? It does, does the same thing. And it takes the same amount of time, right? Just about 14 seconds there.
What’s the most frustrating part about the whole thing, is that SQL Server only tells us that we need an index on the post table, on the score column, including the user ID column. The thing is, when we touch the post table, we spend 10 milliseconds here, and 91 milliseconds here, and 124 milliseconds in total, dealing with data in the post table. SQL Server does not tell us that an index would help down here, where we spent 15 seconds. SQL Server thinks that this part of the plan behind my giant head is where we need to focus our indexing efforts, which is just not true. It’s not, it’s not a good solution. Where we need an index is on the badges table, so that SQL Server doesn’t have to create an index every single time these queries run.
Notice that the index that we created happened a lot faster than the index that got created in the query plan. And now this all happens very quickly. And SQL Server is still insistent that we need an index on the post table. Right? And this is the kind of stuff that you have to watch out for. Right? SQL Server is going to tell you that you need indexes in places where you don’t need indexes. It’s going to tell you that you need indexes that almost already exist. It’s going to ask for very duplicative stuff. It’s not very good at this game. Notice these queries. They all have slightly different where clauses. And that’s okay. The important thing here is that every single one of these queries runs very quickly. We have four milliseconds. We have three milliseconds. We have three milliseconds again. We have two milliseconds.
And every single one of these queries tells you that an index would impact the query by some giant percent. We have 99.6 up there. We have 99.5 over there. I think there’s a 99.7 up a little higher. At least there was before. Yeah, look at 99.7% impact on what? On three milliseconds? Are you out of your mind? How would that impact three milliseconds in a meaningful way? You see, all these missing index requests are things that SQL Server comes up with based on its usual shenanigans with costing and estimates.
And it doesn’t take into account anything that actually happens when the query runs as far as figuring out what indexes are necessary. There are some things that will prevent missing index requests from even showing up in queries where they might help too. All right. So remember, all those queries it finished in two milliseconds, SQL Server was like big time index.
Add it now or else I quit. I’m going home. But certain things like trivial plans, if you look at these two queries, you can sort of see the signs of trivialness up here because I did not have any of this brackety or parametery stuff around my query. And I certainly did not ask SQL Server to treat my query as a trivial plan, but yet it did.
And we have this trivial plan where SQL Server tells us no index could possibly help this query go any faster. And then we have this query down here where SQL Server has now decided that an index would be so much more helpful than not having an index here. All right. So that’s one thing that can prevent missing index requests from showing up.
Other things that can prevent missing index requests from showing up are a lack of sargability or is a lack of sargability. We’re talking about one thing that spans multiple queries here. Now, the textbook definitions of sargability is wrapping one or more columns and functions, even these lovely built-in ones that turn pink.
Concatenating columns together to ask SQL Server for stuff, right, or even concatenating a value onto a column will do the same thing. And also doing anything kitchen sinky like this. This will happen with store procedure parameters too.
So if you need to figure out if, you know, a column is like a parameter or if that parameter is null, SQL Server will not be forthcoming about missing indexes, like being helpful for any query like this. Granted, that’s for sort of a good reason because you couldn’t seek into the index with any of these query patterns. But sometimes having a smaller version of the index to scan is helpful, especially if all you’re doing is scanning a clustered index and that clustered index is gigantic.
It still might be helpful to know, hey, you know what, an index would be pretty okay here. I wouldn’t mind that. Other times when SQL Server will not tell you that an index would be useful is when there’s a crappy index in place already.
Or maybe not even crappy. Let’s just say mediocre. Or maybe this index was great for a different query, but this index is not great for another query that ends up using it.
That’s something else where a SQL Server will be like, oh, but I got this index. I can just use this one. It’s fantastic.
I’m just going to stick with this thing. It’s the best index I’ve ever seen. If we run these two queries and we look at the query plans, we’re going to see quite different execution things going on. Now, granted, this isn’t terribly slow.
And we have an index seek over here. It did have to go parallel to be 227 milliseconds, and we did have to sort some data, which meant we had to ask for memory. This isn’t a very, very big deal for these queries.
But look what happens when we rearrange the order of the index key columns. In this one, we have creation date and then score. In this one, we have score and then creation date. Because the score predicate is much more selective in both of these queries than the date range predicate that we’re passing in, we’re looking for basically 11 years of dates.
And if you think this is abnormal, I don’t know, maybe you should try consulting because this stuff is everywhere. If we look at these two query plans, the second one that helps us seep to the scores first, way faster. Here, right, zero milliseconds this thing took to find that one row.
This one over here, 226 milliseconds to find that one row using DOP8 threads. Jeez, if this query weren’t at DOP1, how long would it take? I don’t know.
What’s 226 times 8? Bigger number, right? So often SQL Server will make up for shoddy indexing with parallel execution plans. But for that first query, you know, SQL Server is not going to sit there and be like, hey, good idea here. Index, please, now.
Sometimes the columns you select will have an impact on SQL Server giving you a missing index request, too. If we look at these two queries, and we look at the execution plans, the first query, which is almost identical to the second query, has no missing index request. SQL Server says, nothing would help here.
We’re doomed. All we can do is scan this clustered index. That’s our only choice. It’s our only hope. You’re just, this is it for us. For the second query, SQL Server’s like, wait, no.
We could impact this query by 91.2% if we had an index. Golly and gosh, what happened? Well, in the second query, all I did was quote out this body column.
Now, this isn’t always going to happen. Sometimes you’ll have a select star query and SQL Server will be like, well, they asked for the whole table. Might as well just put the whole table in the includes.
Smart, right? It’s a good index design pattern. Thanks, cookies. You know, it’s really, yeah. Why not? Go for it.
Disk is cheap. Why not just do that? But in here, the only difference is coding in or out the body column. And then SQL Server all of a sudden decides that an index would be great. Sometimes other things will impact whether missing indexes show up or not, like the types of predicates you use.
Sometimes SQL Server will look at range predicates and think, no way. Other times SQL Server will look at equality predicates and think, awesome, I can do that. Even though these both logically ask for the same thing.
This query just wants the parent ID 184618. And this one wants a parent ID greater than 184617 and less than 184619. So that can only be 184618.
These queries both want the same thing. But SQL Server only thinks that the equality predicate could be helped with an index. And gosh, look at the impact that that would have.
99.9969. I didn’t make that up. I couldn’t possibly make these numbers up. SQL Server comes up with these dirty, filthy numbers on its own. It’s got a nasty mind, SQL Server.
This query down here, SQL Server is like, nope, nothing would help. Again, we’re doomed. We’re just lost here. So missing indexes.
They often show up when they wouldn’t help. They often show up on tables in the query plan that would be of no value to add additional indexing to. And they don’t show up when they would help.
There are all sorts of situations that we looked at in this video where a SQL Server could have been like, hey, yeah, actually an index would have been pretty all right for this. There are all sorts of things that will prevent missing index requests from appearing, too. Eager index rules, trivial plans, non-sargible predicates, mediocre existing indexes, columns that you select sometimes, and the predicates that you use sometimes.
You will probably find some caveats to the last two points there at various junctions in your query tuning life. But these are things that can affect whether or not SQL Server gives you a missing index request. It gives you a missing index request, singular.
Now, I don’t want you to feel bad about this. I don’t want you to feel at all ashamed. I don’t want you to, you know, if this is your kink, if this is all you’ve got going for you, my rates are reasonable. But I used to love missing index requests because I really thought that SQL Server was trying and thinking really hard about what missing indexes would help for a workload.
It’s just like how I used to love the plan cache. I used to think the plan cache was full of all this fantastically useful information until I really started working with missing indexes and the plan cache. And the more that I worked with them, the less I liked them.
They have a lot in common as far as sort of, you know, unpredictability and instability and, you know, just not being able to really, like, tell you what would be the best thing to do in certain situations. But I really, I just end up getting misled by them over and over and over again. And the right set of indexes wasn’t obvious until I started really looking at the queries that were running and figuring out what indexes would actually help tune them.
If we even needed an index to tune them at all. There were lots of queries that I looked at where SQL Server was like, missing index, missing index. And I was just like, we’re good there.
There’s a different index we need. It’s elsewhere. You know, there’s probably a joke about princess castles or something that would be great if I was into jokes about princess castles. So what you should pay attention to when you are tuning queries or looking for queries to tune is you should look for queries that are slow.
That is wall clock time. That is a metric that you can meaningfully affect the amount of time from point A to point B. Right.
From hitting F5 until results are completely sent back to SSMS. You would want to tune queries that users run. Right. Because usually those are the ones users complain about. Now, there are going to be exceptions to that because you might find at certain points that there’s some other process that runs while users are trying to do stuff.
And maybe that messes up user queries. That can totally happen. And you should totally feel free to fix those queries, too.
And then maybe once you fix those, the user queries will kind of calm down a bit. And you can usually figure out which user queries still need some work and some help. Just an example.
Like you might have some background process that like does stuff and it might block user queries. It’s not the user queries that are slow. They’re blocked. They can’t make progress. Why?
Because read committed is a garbage isolation level. You’re welcome. So when you’re doing this, get actual execution plans. I have a video coming up about what to do if you feel like a query runs too long to get an actual execution plan. So just hang on tight for that.
Look at the operator times and those query plans to find the slow parts or the slowest parts and see if an index would indeed help in those slow parts. If you have a missing index request for a slow part of a query, you have been blessed like no other. Because you don’t have to actually type the index definition.
But you do have to review that index definition. Because SQL Server does not put a lot of thought into the order of key columns. SQL Server also does not put a lot of thought into how columns outside of the where clause might make good index key columns.
It’s up to you to figure out if columns that you’re joining on, ordering by, grouping by, partitioning by, all that other good stuff might make useful key columns too. And you should fix those issues. Don’t just create a bunch of missing index requests and think your job is done because it’s not.
And finally, don’t fall for scam imaginary metrics. Things like logical reads, missing index impact, and query operator costs will all lead you astray. They will lead you down the wrong path as far as which queries you should focus on.
You should avoid these things like the plague. Now, physical reads, of course, are a bit different than logical reads. And it is possible that a query that does a lot of logical reads will sometimes do a lot of physical reads.
But that would show up as duration anyway, and that’s the thing you want to fix. No user is going to come complaining to you about how many logical reads your query does. No user is going to thank you for reducing logical reads.
They’re going to thank you for making their queries faster. So stick to that stuff. And I think that’s about it. I’m going to, I don’t know, upload this as I am wont to do and schedule this. And then, I don’t know, record something else because I’m feeling real fired up today.
Feeling extra firecrackery. Me and Bats getting down. Bats doesn’t have, Bats is, Bats is running on empty. Not me though.
I’m running on love. Cool. Thanks for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I dive into the exciting world of Azure SQL DB and share some new transformation rules that caught my eye. After a bit of a technical hiccup with my computer, which almost derailed our session, we managed to connect and explore these rules in detail. Specifically, I highlight two query transformation rules: Logical to Physical Sort and Redundant CSE Spool, which are intriguing for their potential impact on query optimization. The video also delves into a left outer join to left anti-semi-join transformation, showcasing how SQL Server can optimize certain complex queries more efficiently. Along the way, I share some tips and tricks, like forcing a higher compatibility level with use hints, to make these new features work for you.
Full Transcript
Erik Darling here, live and in person with Darling Data and of course Batsmaru who is now my, just a permanent fixture in these videos. I hope that I don’t get sued for briefly having a candy dispenser with a licensed, copyrighted, trademarked image on it. But I just feel like the resemblance is uncanny when you get down to it. So anyway, I took a little break from recording because I had so much built up and when I came back to do this, there were like 40 things wrong with the computer, including that it couldn’t see my microphone anymore. So it’s a wonder that this is even happening for us today because it almost didn’t. So we’re going to talk about some new transformation rules.
I had a reason to use Azure SQL DB finally, not allowed to say what it is, but while I was using Azure SQL DB, I figured, what the hell, Erik, let’s get curious a little. Let’s see if there’s anything new and interesting and exciting alive in Azure SQL DB because oftentimes I do find it is the saddest place on earth. I realize that it’s also sort of a playground for Microsoft to test new things out.
So, you know, guinea pigs of the world, I guess, I guess we need them too. Anyway, before we get into that, let’s talk about you and I and our beautiful journey together. If you like the content that shows up on this channel or even if you don’t, I don’t care.
Leave me alone. You can like, you can comment, you can subscribe. These are all fine things to do.
If you feel that the content on here is of such value to you, such amazing, you know, mind blowing value that you want to support this channel, you can sign up for a membership for as low as $4 a month. There’s a little link you do in the video description that will let you do that.
If this thing will move forward. Yes, there we go. If you would like, oh, wait, that’s that went one too far. Ha ha.
See, I haven’t lost it all yet. If you need anything consulting wise for SQL Server, that is my current profession. That is my title. I guess, I guess, you know, if I want to be really impressive, I could say I’m a founder, but then, you know, you get a bunch of founder mode jokes made about you.
I’m not, I don’t really need that in my life. I’ve got enough problems. So, you know, if you need any of this stuff, here I am. And here you are. And here we are together with you paying me money to make your SQL Server faster.
If you would like some high quality, low cost training, good for life, you can go to the link up there, which is also quite coincidentally in the video description down below. And you can use this discount code to get 75% off.
Again, for life, SQL Server training. It brings it to about 150 US dollars. So, that’s a pretty good deal considering what other people charge you for far crappier training. If you would like to see me live and in person, I only have one event coming up through the end of this year.
November 4th and 5th, I will be at Past Data Summit with Kendra Little doing two miraculous, stupendous, fantastic days of SQL Server performance tuning, wizardry, witchcraft. Probably no miming, which is good.
If there’s an event near you, and that event could maybe use someone like me as a pre-con speaker, let me know what that event is, because maybe I’ll show up in pre-con there. Who knows?
Crazy things happen. With that all out of the way, let’s look at SQL Server stuff. Alright, cool. So, we’re going to come over here, and we’re going to hope that Azure did not have a weird connection thing, because sometimes if I leave this out of the box for too long, it takes a really long time to re-establish the connection, and that gets really frustrating.
And it looks like that’s what’s happening now. So, if you ever, you know, are tempted to use Azure for something, I don’t know what you might want to use Azure for. It’s not a fun place.
If you ever walk away from your computer for a little bit, and you come back, and you just want to run a simple query, sometimes it can take a really long time. And sometimes it’ll take so long that the connection will actually time out.
And sometimes it’ll take so long, you’ll actually have to go turn your Azure SQL DB back on, because it just, it goes away on its own sometimes. So, we’re at almost a minute just to run this thing.
And this is one reason why working in the cloud is a true misery for a lot of people. But, wow, at one minute even, that finished. Okay, great.
So, I’m going to talk about a couple other things that, I don’t know, myself and others have found poking around in Azure SQL DB. One of them is a new use hint. So, Microsoft, you know, those option, you know, max stop, option recompile, whatever.
Apparently, those hints just weren’t enough. So, Microsoft added these use hints. So, option use hint, something that, you know, get, well, I mean, I guess they get used to replace a lot of trace flags.
So, I thought this one showed up, abort query execution. Apparently, this one can be used to abort queries after a certain amount of time. I haven’t quite gotten the syntax on that working yet, nor do I plan to spend a lot of time on it.
Because, the other kind of funny thing about Azure SQL DB is that even though some of this stuff is there, it shows up, it doesn’t mean it’s actually implemented. So, even if you got the syntax absolutely right, SQL Server still might just not do anything with it. It might just be a no-op.
Another kind of funny one that showed up is this one. This is a database scoped configuration setting called Optimized SP Execute SQL. What this does, I don’t know.
I haven’t been able to get it to do anything interesting yet, but that’s there too. What we’re here to talk about, though, are some of the new query transformation rules that I found a little bit earlier today. Specifically, these first two.
Because the first two are the only interesting ones. Well, actually, I’m lying. They’re not the only two interesting ones. They’re just the only ones that I can say anything about right now. Because I haven’t quite figured out what to do with the other two.
Logical to physical sort, I’m not really sure on that one yet. You know, there’s this weird burning eternal hope that someday Microsoft will fix batch mode sorts. So that when they spill, they are as efficient as row mode sorts.
I don’t think that’s going to handle that. It’s a little weird. And then there’s another one down there. Redundant CSE spool.
I’m guessing that that’s a redundant common sub-expression spool. But I don’t quite know yet. But anyway, those first two caught my eye and were interesting. Because I can figure out a little bit what that means.
You have LOJ and ROJ. And that’s going to be left and right outer join. Right? To left anti-semi-join. That is LASJ.
So that is actually a really cool thing. Because what that is, is something that myself and others have been complaining about in SQL Server for a very long time. Now, if you’ve, but I don’t know, I’m going to guess that this might not be the first video you’ve ever watched of mine. If it is, welcome, of course.
But if not, you may have seen me complain in other videos and blog posts about when you do a left join. And so it’s like a very common thing in databases. You need to find rows in one table that are not in another table.
Probably the most common way that gets described to do that in various SQL tutorials is to do a left join from one table to another. And then use the WHERE clause to filter out where the primary key column and the table that you left join to is null. So you only, so that by doing that, you can find rows that exist in the first table, the from table that do not exist in the, the second, the join to table, the left join to table, because the primary key cannot be null when you actually have a join match.
Right? So good stuff there. And that’s what exactly what those rules do.
So let’s, now that we have reestablished a connection to Azure SQL DB, let’s create a couple of tables and put a little bit of data in them. This isn’t a big performance thing. This is just to show you that this does exist and can happen, but you need a little bit, you need to put a little bit of grease on the ball, but a little bit of grease on the ball.
Of course, I mean, you need to, you need to force a higher compatibility level. Now, when I first started messing with this, like it wasn’t happening naturally. Now my database in Azure SQL DB is a compat level 160, which is the highest compat level that Microsoft like has, I don’t know, for a database level setting.
I think I actually didn’t try to change this one to 170. Maybe I should. But anyway, the, the, the, that, that use hint right there that you’re looking at is the thing I was talking about these use hints.
Now, so this is the syntax for those. And with, by doing this, I can force the query optimizer compatibility level to use 170. Now I tried some other stuff first.
I tried the query optimizer hot fixes stuff. So that’s, well, that’s that use hint or trace flag 4199, which is the equivalent there. That didn’t get it to work.
And then I was sort of like, well, you know, these, these things are new. Why not? Why not? Why not see what compat level 170 does? Why not horse around with that, that little thing for a little bit. And what we’re going to do is just, just run these queries real simple, real quick.
We have query plans turned on. And we get zero rows back from both of these because, I mean, let’s be honest. I put the same data in both tables.
Right? So there’s not like the work we’re going to count. There’s no rows that don’t match between these two, right? It’s just row numbers one through 16,000 something. But if we look at the query plans for these, we’ll see up here, this is the query plan pattern that I see a lot when I’m working with clients that, of course, annoys the bejesus out of me.
You have a full scan of one table, a full scan of another table. You have a right outer hash join, which, you know, the right outer part doesn’t bother me so much. That’s the optimizer’s choice.
It can reorder this stuff and do whatever it want with it. And then we have this filter operator after that. Right? And, of course, in this filter operator, this is where we are going to be looking for where this ID is null. Right?
That’s exactly what our where clause prescribed in here. We are saying join I to O on these two IDs where this ID is null. Right?
So that’s exactly what I was talking about as far as the query pattern goes. But down here in Compat Level 170, SQL Server can take a different approach. So that’s the first thing that I’ve done with the query. Rather than use a right outer join and then later filter things out, what SQL Server can do now, or rather what SQL Server’s optimizer can do now with these two glorious, beautiful new rules in them, is it can convert that query to a left anti-semi-jub.
If anyone from the SSMS team is out there, please just put the full names of things in the query plan. It’s okay. It’s okay if you do it.
You can show us the whole thing. We don’t need to guess. We don’t need to tooltips for everything. Just show us the full name for things. Please.
I’m begging you. I’m begging you. Why won’t you give me this one thing? I’m not even asking for dark mode or debuggers like everyone who annoys you. Just show the full name of a query operator. We don’t need the suspense.
We shouldn’t need to hover over this to say, oh yes, that is a left anti-semi-join. How nice. So anyway, that’s about it for this one. I would like to personally thank and give high marks and just convey my personal esteem to whomever on the optimizer team.
It could be multiple whomevers. It could be a plural whomever. I don’t know what that is.
Got these two query rules in there. I do hope that they’ll be allowed outside of compat level 170. Otherwise, it’ll be 15 years before anyone sees them. So, that’s fun.
But yeah, good job, optimizer team. Bad job, SSMS team. The fish. The fish yearn for you.
Anyway. I’m going to go, I’m going to close this file out. You might see that I have several other tabs open here. And I do have some work to do in order to get all these recorded for you.
And so, I’m going to work on those. And that’s all you need to know is that they’ll be here soon. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I hope that, I don’t know, I hope that life is just going grand for you. I’m really, really excited for all the things you have to look forward to. So, anyway, that’s about it for me here.
Time to go record some other stuff. Thank you. I love you. Good night. Bye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Kendra and I both taught solo precons, and got to talking about how much easier it is to manage large crowds when you have a little helper with you, and decided to submit two precons this year that we’d co-present.
Amazingly, they both got accepted. Cheers and applause. So this year, we’ll be double-teaming Monday and Tuesday with a couple pretty cool precons.
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
Here are the details!
Day One: A Practical Guide to Performance Tuning Internals
Whether you’re aiming to be the next great query tuning wizard or you simply need to tackle tough business problems at work, you need to understand what makes a workload run fast– and especially what makes it run slowly.
Erik Darling and Kendra Little will show you the practical way forward, and will introduce you to the internal subsystems of SQL Server with a practical guide to their capabilities, weaknesses, and most importantly what you need to know to troubleshoot them as a developer or DBA.
They’ll teach you how to use your understanding of the database engine, the storage engine, and the query optimizer to analyze problems and identify what is a nothingburger best practice and what changes will pay off with measurable improvements.
With a blend of bad jokes, expertise, and proven strategies, Erik and Kendra will set you up with practical skills and a clear understanding of how to apply these lessons to see immediate improvements in your own environments.
Day Two: Query Quest: Conquer SQL Server Performance Monsters
Picture this: a day crammed with fun, fascinating demonstrations for SQL Server and Azure SQL.
This isn’t your typical training day; this session follows the mantra of “learning by doing,” with a good dose of the unexpected. Think of this as a SQL Server video game, where Erik Darling and Kendra Little guide you through levels of weird query monsters and performance tuning obstacles.
By the time we reach the final boss, you’ll have developed an appetite for exploring the unknown and leveled up your confidence to tackle even the most daunting of database dilemmas.
It’s SQL Server, but not as you know it—more fun, more fascinating, and more scalable than you thought possible.
Going Further
We’re both really excited to deliver these, and have BIG PLANS to have these sessions build on each other so folks who attend both days have a real sense of continuity.
Of course, you’re welcome to pick and choose, but who’d wanna miss out on either of these with accolades like this?
pretty, pretty, pretty, pretty good
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
See you there!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
