Query Plan Archives | Page 82 of 147

A Little About How Overindexing Can Hurt SQL Server Performance

Posted on May 31, 2023January 21, 2026 by Erik Darling

A Little About How Overindexing Can Hurt SQL Server Performance

Video Summary

In this video, I delve into three unexpected ways that over-indexing can negatively impact SQL Server performance—far beyond just slowing down inserts and updates. I explore how having numerous unused or redundant indexes can lead to inefficient buffer pool utilization, causing more frequent disk I/O operations as the same data is repeatedly cached in memory. This not only wastes space but also introduces variable query performance due to constant swapping of data pages between memory and disk. Additionally, I demonstrate how extra indexes can trigger earlier lock escalation attempts during transactions, leading to increased locking overhead on both tables and indexes. Lastly, I showcase how transaction logging can be unnecessarily burdened by redundant indexes, resulting in more extensive log activity for the same operations. With practical demos, I aim to provide clear insights into these often-overlooked performance pitfalls.

Full Transcript

Erik Darling here with Darling Data, of course. And today we are going to talk about three ways that over-indexing can hurt SQL Server performance. Now, I don’t mean it in the sense that a lot of folks think about it. We’re like, oh, I added too many indexes. Now my inserts are slow. Or now my updates are slow. Or now my deletes are slow. Because sure, that can happen. But that’s the first meme that everyone throws out whenever they think about over-indexing. Now, by over-indexing, I don’t necessarily mean the quantity of indexes. What I mean is the indexing strategies that I see a lot while working with clients, where you’ll either have, you know, like a single column index on every column in the table. Or a bunch of indexes that have been sort of ad hoc over the years that no one has ever sort of reanalyzed to see if they are still of any use or validity. Some indexes could have been created to support parts of the application that aren’t parts of the application anymore.

Or someone could have added another index later on that SQL Server started choosing and using over another index. And you just see either index usage patterns, like drop off for some index, like have totally like zero read indexes. Like these indexes just aren’t helping queries go any faster.

And you still have to keep those indexes in sync whenever you modify the base table, because if you didn’t do that, your data would be corrupt. And also creating indexes on sort of like the same sets of columns over again, over and over again, rather. You know, like, you know, column A, column B, include D, then like, you know, on column ABD, and then, you know, column ABC, and then, you know, you know, stuff like that, where you just see like the same columns indexed over and over again.

And, you know, like index on column A, index on column A, B, C, index on column A, B, C, include column F, G, H. I know all the letters of the alphabet. I can do this all day, all day, standing on my head. But three ways that are sort of unexpected when I talk about them with the folks I work with are how having lots of unused and duplicative indexes can hurt the buffer pool.

How it can lead to earlier lock escalation attempts. Remember that lock escalation is something that is attempted but not always granted. And, of course, transaction logging.

And I have demos to prove it all. I brought receipts. So, the first thing we’re going to look at is how indexes, how indexing, rather, can have you make less efficient use of your buffer pool in SQL Server. The buffer pool, of course, being where SQL Server stores all those lovely data pages that queries want to work with.

SQL Server being a mildly intelligent database. It doesn’t work with pages directly on disk. Any query that wants to read data or modify data, those data pages have to go in memory before SQL Server will start dishing them out to queries to work with.

So, and this goes for even unused. So, this is more about indexes that are read from. So, if you’re indexing sort of the same columns, like, over and over again with the, like, slightly different includes, stuff like that.

It even, like, you know, the same key columns just sort of, like, tacked on at the end over and over again. You will end up with a lot of the same data in memory over and over. Now, this is a problem because most of the servers I look at, and I’m talking specifically to you, I’m pointing at you, I’m saying you, probably do not have enough memory in your SQL Server to cache the relevant data to your workload in memory.

And having the same data effectively, remember, every index is a completely separate structure on disk, and so it’s a separate structure when you read it up into memory. Having the same data indexed over and over again means that you have the same data up in the buffer pool, which means you have less room for other things in the buffer pool. And that can generally lead to a pretty bad situation.

We are constantly going out to, you know, those, well, I’m sure your SAN disks are great, but maybe your SAN network isn’t so hot. But you’re just constantly sort of, like, hot-swapping data for one table or index and for data in other table and indexes constantly. And that just introduces weird variable performance issues because you may have a query that runs very quickly when everything is in memory and then a query that takes, you know, a lot longer when you have to go out to disk and read a bunch of pages in.

So the first thing that I do for all of these demos, because sometimes I don’t always do them in the same order, is rebuild the user’s table so that all the data pages for it are nice and densely packed. I do some updates and other stuff across these that sometimes leads to the user’s table being in a strange state when I go to run the actual demos. So the first thing I’m going to do is tell you a little bit about the scripts that I’m using here.

So let’s actually create these indexes while I talk through those. I get those off and running. This shouldn’t take too, too long to create.

The user’s table is rather small, and I have a great home computer for this sort of stuff. So I have two helper scripts that I’m using for this one. One is called What’s Up Indexes.

It is not a terribly expansive piece of code. It just gets some general information about tables and indexes for a specific database. These are available on my GitHub repo.

The link to that will be in the description of the video. So if you feel like also clicking on that link, as well as clicking on like and clicking on subscribe, you can also go get the scripts to do this stuff at home. So if I run this, we’re going to get some very basic information about the indexes in the database that I just created here.

So we are in the Stack Overflow database. We’re looking only at the user’s table. And we have four indexes currently on the table, one being the clustered primary key, which is 348 megs and about 44,000 pages.

And then we have the three nonclustered indexes that we created, which, since they’re only on a subset of the columns from the clustered index, they are all smaller than the clustered index. But they add up to around like 212 carry the thing, maybe 213 megs total. Again, not huge, but, you know, I’m trying to do some quick demos here.

In real life, we would be looking at much, much larger things. But about 212 megs for all of the indexes. Now, the indexes that I have sort of display a pattern that I see quite a bit in client work, where we have one on reputation include display name website URL, one on reputation creation date include display name location, and one on reputation creation last access date include display name upvotes downvotes.

So three indexes that, I mean, they have slightly different includes, but the key columns all share, like, you know, some commonalities as far as, like, column order and what on all that stuff goes. So another help review that I have in here is called WhatsApp memory, which looks at pages in the buffer pool in which objects are responsible for them. And since I just created these three indexes, the entirety of the index is going to be in memory.

And two, since I had to read from the clustered primary key to create those indexes, like that was a data source for the indexes to create from, that whole thing is currently in memory. At the start of this test, what I’m going to do is run a checkpoint and drop clean buffers. I do it twice because I am a superstitious man.

But if we run that twice and we look at what’s currently in memory, there is absolutely nothing, which is great and wonderful. And boy, I just couldn’t ask for a better demo so far. So I’m going to run this.

I’m going to look at what’s in memory before I run my queries. Then I’m going to run these three queries that all hit the user’s table in a different way. And then I’m going to look at what’s in memory afterwards.

And the execution plans for this are not terribly important because you’ll see at the very end that we ended up reading good chunks of all three of those indexes up into memory. So the first one uses the index U1 right there. I guess I did win, didn’t I?

I won the SQL Server lottery. The second query uses U2, which is a band that I absolutely hate. And they are not New Wave, no matter what anyone says.

And the third query will, of course, use U3 right there. And note that even though we seeked into all of these indexes, we still had to read a pretty significant amount of the pages in. So this is what’s currently in memory.

We got, let’s see, a 53 plus a 47. So that’s 60, 100, about 130, 130 something megs of the indexes are currently in memory of the 212 megs that the indexes make up altogether. So we have three different indexes, nearly the same data in there.

And we have, you know, what I would consider to be sort of a wasted buffer pool space because of that. Now, when I see this when working with clients, usually I consider it part of my job to start consolidating indexes. I mean, like obviously dropping off unused indexes if the server’s been up long enough to make a sort of confident call on that.

But then, you know, consolidating duplicate and, you know, close by duplicate indexes so that we have fewer objects competing for space in the buffer pool. So how I would do that here, I mean, I wouldn’t run drop indexes on a client database unless they were really mean to me, didn’t pay their bills. But what we can do is we can consolidate those three indexes into one index that has the key columns that we care about and then the included columns that we, that sort of made, that all the three different indexes had in there.

Again, include column order doesn’t matter. They’re just window dressing for the indexes. We can put them in any old way we want.

And let’s run that same experiment now with just the single index and see how things turn out. So we start off with nothing in the buffer pool because we cleared everything out. All three queries run and use the u4 index, which makes me euphoric.

Kill me now. And all three are going to use that index because that index has all of the same stuff in there. Note that we did a seek into this index for all of these, starting with the first one.

Let’s just zoom back in on this. All three of these do a seek into the u4 index. That joke was u4 icky.

But now, rather than having, you know, 100-something megs from three different indexes sitting in the buffer pool, we have one index in the buffer pool that takes up about 70 megs. So obviously, we’re doing much better buffer pool utilization because of that.

Now, if you go look at this, remember that the three indexes combined were 213-ish megs altogether. This index all consolidated is 101 megs altogether. And so we have one much smaller data source that can service all three of the queries that we care about.

So, yeah, I don’t know. I guess that’s about that there, right? Cool.

So now we know that consolidating indexes, and that would include getting rid of unused indexes, can help us save space both on disk and in the buffer pool, right? Those precious disks that your SAN administrator gets paid billions of dollars to manage.

Billions. It could be an Oracle DBA making that kind of money. All right.

So the second thing that index overindexing, having too many unused and duplicative indexes around, can hurt is lock escalation. So we’re going to start off.

We’re just going to rebuild the user’s table real quick. And the first thing we’re going to do is in the transaction here, we’re going to update this table. I don’t even know why I keep that in there.

We don’t even look at the execution plan of this one. It’s a habit, I guess. And then we’re going to use a different helper object called what’s up locks. Since we’re not doing anything right now, there are no locks to assess in here. But once we’re in a transaction with this update, boy, howdy, there’ll be fireworks.

So if we run this with just the clustered index, and we look at what locks gives us, we can see that SQL Server has, well, like one intent exclusive lock on the object, which isn’t like a locking locking thing.

It’s like a pre-lock weight kind of. This weight would be blocked by other locks or by an object level shared lock on the table, on the object. But the real thing that we care about here are the granted exclusive locks on pages in the primary key.

So that’s just with one index. We end up with 3,394 pages locked. If we add in this index, and we run through the same thing again, we will see that SQL Server now has a new object to lock, and it takes out more locks, not like the time machine villains, but more locks as in like more locking on the index.

And we end up with some thousands of locks taken and granted, both still on the primary key and still on, come on, mouse cursor, do what I want. And now also on the non-cluster index that we just created.

Those are 4,002 key locks there. So we have key locks here and key locks there. Now, if we add in just a second index, which is, again, a situation I run into with clients a lot, just the same columns in a slightly different order.

And like they both kind of get used, but, you know, no one really knows which queries, you know, hit what, which queries are more important, all that stuff. You know, and this, I mean, this takes a little bit more analysis and domain knowledge to figure out, like if you can get rid of one of these.

But if we add in that third index, and then we run this, rather than locking a whole bunch of stuff separately, we now have one exclusive lock on the entire user’s object, and that has been granted.

So, again, lock escalation is only attempted. It is not guaranteed to happen. If other competing locks get in there, you may not see lock escalation happen. There’s some counters in some of the index DMVs with lock escalation attempts in there.

So those are, you know, interesting things to keep an eye on. But this is one way that, you know, having, you know, additional duplicative or even unused indexes on your table can hurt performance.

Because even, like I said before, even unused indexes need to end up in the buffer pool and need to be locked to modify when you modify the base table. SQL Server can’t play favorites and be like, well, you haven’t been read in a while.

We’ll just update you later. We’ll defer that update to some other time. It doesn’t, that doesn’t happen. It does not happen. And so that can end up being sort of a bad time. All right, cool.

So the third and final way that, you know, you know, over-indexing can hurt SQL Server is with transaction logging.

So what I’m going to do is just create all three of these. I’m going to do all this stuff in one big swoop. And then I’m going to show you what an update looks like from the transaction logs point of view.

So two things I want to show you. One is that there’s a reason for the checkpoint here. And the reason for the checkpoint is because after we create indexes, we have data about creating those indexes in the transaction log.

If I checkpoint everything in there, go away, SQL prompt. If I checkpoint everything in there and I go look back at the transaction log, we’re not going to, it’s going to be empty, right?

So we don’t have anything in there for this table. If I go and do this, so again, we’re doing the transaction in an update so that I don’t, I don’t, you know, we’re doing the update in a transaction rather so that I don’t have to worry about undoing any evils that I’ve done.

If we run this, we’re going to see all three of the nonclustered indexes that I created end up with a bunch of locks in the transaction log and also with a bunch of additional records in the transaction log.

Now, this will be true for, since this is an update, only indexes that have the columns that are being updated will end up in the transaction log.

If this was an insert or delete, then every index would be affected because, you know, deletes are every row in the table, inserts are every row in the table, even if they’re null or something, a new record is added, so you would see records in the transaction log for that.

Now, of course, if you have filtered indexes where, you know, the insert or delete didn’t touch the data that was, you know, not part of the filter, it wasn’t like inclusive of the part of the filter, then you wouldn’t see anything there, but, you know, that’s a little bit more of an edge case.

So, you know, we have all that. Anyway, those are three ways that I find people are rather surprised by that can hurt SQL Server performance by having too many unused and overlapping indexes hanging around in your database.

If you want to analyze your indexes, I, of course, recommend SP Blitz Index. It’s an open source tool that I contributed. Well, still contribute once in a while, too. That one’s sort of set in stone at this point, but I contribute a lot of stuff to that in the past that I’m rather proud of.

Of course, originally written by the lovely and talented Kendra Little, wrote a great post today about SQL Server Management Studio being the best SQL Server monitoring tool on the planet, which I heartily agree with because the rest of them are a bit iffy.

But anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And again, please like and subscribe and all that good stuff.

And I will see you in another video where we will talk about, well, I guess more SQL Server stuff. It’s the way this seems to go, isn’t it? Wish I knew more about something else so I could talk about that.

Anyway, thank you for watching.

Going Further

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

A Little About Views, Parameters, and Local Variables in SQL Server

Posted on May 30, 2023January 21, 2026 by Erik Darling

A Little About Views, Parameters, and Local Variables in SQL Server

Video Summary

In this video, I delve into the complexities of parameters and local variables in SQL Server views that contain windowing functions. Exploring how these elements interact with query plans under different compatibility levels and settings, I provide a comprehensive overview of what to expect and how to optimize performance. I cover the nuances of simple parameterization, the challenges it poses with windowing functions, and the impact of various trace flags and database configurations. By walking through practical examples and discussing the limitations of local variables, I aim to equip viewers with the knowledge needed to navigate these tricky scenarios effectively.

Full Transcript

There are some levels of exhaustion that are just unfathomable. I’m Erik Darling with Darling Data, and in this video I’m going to talk about how parameters and actually to some extent a little, just a tiny little bit about local variables and how they’re behave with views that contain windowing functions. This is sort of a wrap-up video because I’ve talked about some of these things separately, but with the advent of SQL Server 2000-2022 and a brand new compatibility level of 160 available, there is a little bit more to talk about. And boy is my heart tired. So let’s dig right in and have some fun. Now words of wisdom about windowing functions, they are generally aided and assisted from a performance perspective. And all those P’s are really going to test if my audio is synced up with any degree of accuracy. If we create them, well let’s look at our windowing function and let’s look at our windowing function.

We are query generally. We are generating a dense rank. We are partitioning by owner user ID and ordering by score descending. Aside from those two columns that are also in the select list, we also have creation date and last activity date. And so our index for this windowing function and the query more generally is going to be keyed on owner user ID and score descending and include creation date and last activity date. I like to include when reasonably possible all of the columns in the select list to cover the query so that we don’t have to deal with SQL Server thinking about key lookups because SQL Server often seems to make counterproductive choices when it comes to doing key lookups.

Either not doing them when it comes to key lookups. Either not doing them when it would be a great idea or doing them when it is a pretty terrible idea. So we are just going to make sure that we don’t have too many choices to worry about here.

I am going to worry our pretty little heads about this one. So I am going to create this view called Pushy Paul Error Free Professional Presenter over here. And we have got query plans turned on because believe it or not I have had this script file open for a while making sure that I have a reasonable number of bases covered when I give you all of this information that you are going to forget about mere moments from now.

But let’s run this query. Let’s select from our view and we are going to use a literal value here. Now various things could trip this up.

Simple parameterization would be one of them. And the database level setting. And the database level setting.

Well, let’s backtrack a tiny little bit. The query optimizer. Well, part of the query optimizer choices. One is to add simple parameterization to a query.

That could potentially trip this up. Another is the database level setting. Force parameterization that could also trip this up. But when we use a literal value and we run this query, everything returns nice and fast.

Right now we are in compat level 160. And even though this looks like we got a simple parameterized query, we didn’t. It was attempted, but it was not actually used.

If we look at the index seek over here, we have a literal value. I don’t know what a value is. It’s not a town in Colorado.

But we have a literal value down here. We do not see a parameter down here. So simple parameterization was not successfully applied to this query, though it was attempted. Surprise, surprise.

There are weird things in query plans that no one should ever have to explain to anyone. But we do get a quick index seek. We do get an accurate cardinality estimate.

And why wouldn’t we? It’s true, right? And obviously it’s true to me. Let’s try to say, sorry,win I’m going to kill. We’re trying to see the first demo protocol right here. SoHello. If I were to stuck in and try to estimate them. I would do, umm.

I would $100, $200, $ président, $! differences in the query plan under compatibility level 150. So let’s run these and well these run we’ll say that if you were to use the global trace flag 4199 if you were to use trace flag 4199 as a query in here or you were to use the database scoped configuration query optimizer hot fixes you would not run into this in compat level 150. Now you would also not run into this in compat level 140 I’ll tell you exactly which cumulative updates that applies to towards the end of the video but if we look what happens here the top query no longer features an index seek the top query now features an index scan then a window aggregate and then a filter whereas the bottom query just features the index seek and then the window aggregate the filter is of course going to be the parameter for user ID because that is the only filtering element in the query the main message here is that when you have a windowing function in your view and you use parameters under certain conditions that I will tediously explain over and over again I’m going to feed that dead horse a full meal the local variables and parameters can’t be pushed past either the window aggregate which is just a batch mode sequence project or the row mode sequence project plan operator fun right it’s exciting it’s great stuff under compatibility level 160 that story changes a bit because there was a more recent optimizer hot fix that allows for that to happen so if we switch this to compat level 160 we will see both queries um use well slightly different execution plan but here is our sequence project and in parentheses compute scale R thank goodness it’s not just a compute scale R so that we could tell that we are projecting a sequence here because everyone who looks at a sequence project operator immediately understands that they are projecting a sequence of some sort not just computing a scale R but both of these queries now uh get index seeks even though one of them this top one has a parameter and this one here is still lying to us about uh getting a simple parameterized plan well there’s not much you can do there is there could throw a one equal select one on there to clear things up but then y’all would accuse me of cheating or something so let’s switch back to compat level 160 and uh talk a little bit about why this happens um views of course can accept a parameter when you create a view there’s no parameter list for passing into the view uh you just create the view and then you call that view from somewhere where you decide what you want to filter things on uh the optimizer uh except under conditions that we will discuss uh quite a bit in this video uh cannot push parameters past sequence project uh compute scale R or window aggregate operators which in my earpiece I’m being told that uh window aggregate operators are really just batch mode uh sequence well I forgot a word in there P-R-O-G project there we go uh it only works with uh constant values like literals right um unless you are in compat level 160 or compat level 140 or 150 with certain uh patch levels and uh database level setting settings uh query optimizer hotfixes trace flag 4199 enabled globally or trace flag 4199 enabled at the query level uh recompiling would also allow for this because it would reduce your parameter or local variable to a literal value uh another way of getting around this limitation is to create a uh inline table valued function in place of a view because inline table valued functions uh can accept parameters inline table valued functions also prevent simple parameterization and uh even under compat level 150 this works out pretty well all right there’s our inline table valued function and even if we stick that inline table valued function inside of a store procedure even in compat level 150 we will get the execution plan that we’re after where the parameter value is pushed past the sequence project compute scale r and we get an index seek rather than a full index scan that takes six seven seconds like we saw in other uh in other demos run during the video our seek predicate here is not on a literal value it is just it is on the parameter value passed into the inline table valued function so sql server 2017 cumulative update 30 uh sql server 2017 of course maxes out with compatibility level 140 uh sql server 2019 cu17 which of course maxes out at compat level 150 uh both have this query optimizer hotfix available which again trace flag 4199 uh the database scope configuration query optimizer hotfixes and of course uh sql server 2022 rtm and beyond uh without making either of any of those changes either the trace flag and either uh uh either method or the database scope configuration will fix uh the issue with parameters local variables and views without a recompile hint fantastic you don’t need to go rewrite all your views as inline table valued functions though um i don’t know maybe i don’t think that’s such a terrible idea maybe maybe i think that’s actually an okay idea uh one it fixes a lot of weird problems like this and two it gets you in the habit of writing inline table valued functions instead of crappy functions like scalar udf’s or multi-statement udf’s but uh you could fix this with uh trace flag 4199 as well do this and trace flag 4199 would address the issue good for us uh so one thing that i do want to uh uh talk about a little bit is local variables because um even though uh the uh even though the query optimizer hotfixes will address uh the the issue with not being able to push the predicate past the sequence project compute scalar or the batch mode window aggregate which is just the batch mode version of the sequence project uh they do not help improve uh none of that and it helps improve cardinality estimates at all so even we’re in compat level 160 and we run this even though no did i switch that to 160 oh no i’m i’m i see now i’m forcing compat level 150 in here that’s silly me like i said it’s all so tiresome so if we look at this query plan uh uh we are forced to again scan the whole index and apply the filter later for that for that local variable now uh not now if we uh still use compat level 150 but we enable uh trace flag 4199 we’ll get the seek plan that we’re after but even doing that we get a the really crappy local variable density vector guess there of 12 rows out of two thousand seven twenty seven thousand nine hundred and one uh i apologize for causing you pain but i just want to make sure that you all caught that on this one as well it’s only another six seconds of your life what were you doing anyway can’t even say i love you in six seconds but um we continue to get the crappy estimate here right the estimates are over here because we have to grab every row the estimates only bat at the filter where there’s actual cardinality estimation to be applied to a filtering element in the query uh uh and that also goes for uh if we hint to use compatibility level 160 we run this and even though we get the seek plan that we’re after we still get the terrible guess so even though um some some effort was made by the uh by the the nice folks who brought you dot feedback uh to fix this issue uh we still do not get uh any sort anything resembling accurate cardinality estimates from the local variable so uh i would advise you except under uh closely monitored circumstances to avoid local variables where possible um pain swelling itching redness nausea vomiting diarrhea and possibly even death have occurred when using local variables so anyway uh the larger point here is that uh microsoft does do some valuable things across uh versions of sql server of course uh and even in some cumulative and sometimes in cumulative updates of course we don’t really have much choice anymore since we we will never see another service pack again we will only ever see cumulative updates from now until forever for sql server matter maybe there’ll be a different word for it at some point later but um anyway uh the the main point here is that uh there there are some things that can be fixed by upgrading there are some things that can be fixed with trace flags and this is one of them this is a pretty good example of it uh i’ve seen performance issues related to this crop up oh i don’t know probably a dozen or so times in my time consulting so this is a good thing to keep an eye out for in query plans of course i do generally recommend keeping an eye out for filter operators in query plans because they often indicate that you have done something monstrous not always of course but they are a good thing to keep track of because uh they usually mean that uh there was some predicate that possibly could have been pushed down closer to when you touched uh and table or index uh and it wasn’t and you could have been dealing with a lot fewer rows traveling throughout uh the course of your query plan if you would apply if you were able to apply those predicates earlier on in the query plan i think uh probably the the best example of that is when someone does a left join to a table to find rows that don’t exist and they filter on where the left join to table is null those tables have to be fully joined before that filter can be applied to rule out null values even when you’re using a not nullable primary key you have to deal with that of course that situation is usually better dealt with by using not exists which can eliminate those rows uh at the anti-semi join rather than fully joining the tables and looking for nulls after the fact uh some things like windowing functions like you know if you want to run a query and look for where row number equals one i mean that’s an expression that you just have to gin up at runtime that’s not something that you can uh not something that you can really help but that’ll that would something like that would also result in a later filter operation because that value wasn’t stored anywhere where you could you know persist it index it and filter on it uh earlier in a query plan than after you have generated the row number so anyway uh i think that’s about it for this one i think that’s also about it for me today i’m gonna i don’t know i guess eat a dinner and uh try to forget the computers exist for a little while so thank you for watching uh i hope you enjoyed yourselves i hope you learned something i hope that this wasn’t too tedious for you and uh remember to uh do the old like and subscribe one two shuffle for me anyway uh thank you again for watching

Going Further

A Little About Improving Modification Query Performance In SQL Server

Posted on May 29, 2023January 21, 2026 by Erik Darling

A Little About Improving Modification Query Performance In SQL Server

Video Summary

In this video, I delve into a common performance issue that can arise during modification queries, particularly focusing on how certain query plan patterns might impact overall performance. I share practical examples using the Stack Overflow 2013 database and demonstrate how to mitigate these issues by employing techniques such as temp tables for manual phase separation and leveraging automatic phase separation through hash joins. By walking you through these scenarios, I highlight the importance of understanding query plans and optimizing them to enhance efficiency without compromising data integrity or concurrency.

Full Transcript

Erik Darling here with Darling Data. And in this video, returning to my normal form after a nice haircut. Round of applause for my haircut. Feeling extra cute in my Adidas T-shirt today. Returning to my normal form and talking about a little bit of this and that, we’re going to talk about sort of a general query plan pattern that you can watch out for in modification queries that can have an impact on query performance. I don’t remember why I called it this. It just seemed funny at the time, I guess. I don’t know what to tell you. Thanks, SQL prompt. I needed that. We have that healthy list of things. So we have, let’s make sure query plans are turned on. Stick to the script, pal. And that should be good there. Now, make sure everything’s nice and saved and pretty. All right. So what I’m going to start with is sort of a silly looking update. And this update is going to use an auxiliary table that I create in the Stack Overflow 2013 database just to do some dirty work for me.

I end up doing stuff like this a lot because I really get sort of nervous modifying the user queries. I mean, the user tables. I have like a backup stage copy of the Stack Overflow 2013 database where like I’ve never run in like any sort of modification so that if I need to like, like emergency change some data back, I can do that. But I do like these auxiliary tables because I don’t really care what happens to really care what happens to them all that much. I can just, you know, drop and repopulate them and, you know, move on with my day. Whereas if I screw up a modification to one of the user tables, it can have a, let’s call it a profound impact on other demos that I might run. So let’s update, let’s run this update query. And I only have that one equals one there so that Redgate SQL prompt doesn’t keep throwing messages at me while I’m running the demos to make sure that I have a where clause.

I do find that warning generally helpful. So I don’t want to turn it off. But I also just don’t need to hear any guff. But apparently the old where one equals one gives SQL prompt enough of a, enough of a warm, fuzzy feeling that it doesn’t say, hey, you don’t have a where clause. Which is funny because I do have two where clauses elsewhere in there. But I understand that linting T-SQL is a difficult task. So if you look at this query plan, a few things stick out. One, it is single threaded. That is probably obvious from the lack of parallelism operators or parallelism icons that show up in the query plan. But looking at this, right up until we hit this merge join, our whole query only takes about 1.2 seconds.

And then we hit this table spool. And let’s see, 3.5 minus 1.2. That’s a two, let’s call it 2.3 seconds inside this eager table spool. And then we have another two and a half seconds doing the update. Which, you know, doing the update, like I do expect that to take time. You know, we are updating 1, 4, 3, 4, 7, 2, 6, 1.4 million rows. So like, I expect that to, you know, take a little bit of time to do the update. But what I don’t love is that eager table spool. Many times spools are useful, or at least SQL Server trying to be useful and helpful by caching data. In this case, it’s there for Halloween protection, which is a very useful thing. Strange that Halloween protection was discovered on Halloween, right?

But the spool is there to keep track of rows that have been modified so that we don’t, you know, end up in some awful endless modification loop or some other weird, you know, edge condition where we keep trying to modify rows that have already been modified. And, you know, the old pipelined execution thing kind of gets, kind of gets icky in that regard. Now, everything’s a cursor behind the scenes. So if you’re going to keep complaining about cursors, you should probably just stop using databases.

Anyway, spools, despite their best intentions, best efforts, are not really highly optimized data structures. Rows get fit into them one at a time. You know, they don’t have any of like, like the optimizations that like temp tables and stuff have gotten, even though they exist in temp DB, spools live in temp DB. So even though spools live in temp DB, they don’t get a lot of the optimizations that temp tables and other things have over the years to make putting data into them any more efficient, which is kind of a bummer.

So we can see where the time really starts to accumulate in this plan, right, across these three operators. We have the 1.2 seconds, jump up to 3.5 seconds, jump up to 7 seconds. So I’m not in love with this.

And the reason I’m not in love with this is because there are ways that we could get the same sort of effect as the table spool with a whole lot more, a whole lot better performance. Now, this is a sort of small update, so the performance gains aren’t going to be like, like 7 seconds to like 1 second, but we can shave a bunch of time off this. And in larger scenarios, you could likely shave more time off of this sort of thing.

So the first way that we can introduce this sort of phase separation here is to use a temp table. And the reason why that works is because if we come back and look at the query itself, the total score by user table is the source of the data that we are selecting in order to update the total score by user table. So we want to find a user’s highest score across questions and answers.

And so to do that, we find the question score and we find the answer score and we get the max here. Now, I get it. There’s probably a way you could use a computed column or something else to get this. In case when question score greater than answer score, then question score, case when answer score greater than question score, whatever.

If they’re tied, you can stick that in there too. But in this case, we’re going to pretend that we’re doing this update for very good reasons that we cannot possibly overcome by other means. So one way that we can do this is with a temp table.

Because if we put everything in the temp table that we care to use for the source of the update, then we are no longer using the table itself to update itself. Stick with me on that. The thing is, if we’re going to do this and there’s any sort of concurrency in the database that might be changing data underneath, we need a transaction and we need to make sure that the total score by user table doesn’t change while we’re doing this.

Because anything that we put in the update in the temp table is going to be a snapshot of what the total score by user table looked like when we got the data. But then if something comes along and inserts, updates, or deletes data afterwards, then we could have some wonky results. So in this case, I’ve opted to use the hold lock hint.

And I’m going to partially run this to show you why. So if we grab this chunk of the query, let me say drop the temp table if it exists. We don’t have to do this in a store procedure where you’d most likely be doing this sort of thing.

But we do have to do this in the script itself. If we run this, we are going to utilize an inline table value function that I wrote. And it’s in my GitHub repo.

And what we’re going to do is use the hold lock hint. Alternately, we could also set the transaction isolation level to serializable for this. And what we get back from WhatsApp locks there is we see that our query has been granted 29,830 page locks with a shared lock on them.

Which, if you’ve watched other of my videos, you would know that the shared lock prevents modification queries. As the shared locks are not compatible with modification locks. So if we come over here and we try to insert a row into total score by user, this thing will be hung up, getting blocked by the…

Well, it says set statistics XML off, but I promise you that’s our user session 67. That’s 67 up there. That’s the queries with the hold lock.

So we can’t insert into the table. We see that insert waiting on an intent exclusive lock. If we cancel that and we get rid of useless prompts, we will see that we can’t update the table either. All right.

We come over here and look at this. We’ll see our update. Also going to be looking for an intent exclusive lock, but the update is stuck on waiting on those hold lock hints. Likewise, we will not be able to delete any data from the table because of those locks.

So this delete would also be blocked while this is running. Now, of course, we wouldn’t really want to have a serializable lock on the table for that long to preventing other updates, but we would need that for data correctness. Of course, we are allowed to select from the table.

So if we run this, we can still… Select queries can still run against the table. The only locks that are taken and held are there to prevent modifications. So let’s commit this because it’s just some selects.

And now let’s run the whole thing in one go. All right. So we’re going to drop our temp table. We’re going to insert data into the temp table that we care about. And then we are going to run the update from the temp table.

The update from the temp table is just what happens down here. Right. So I am indexing the temp table in this case because I did find it useful in my performance testing. I do suggest you also test indexing temp tables in those scenarios or in any scenario, really, where there’s performance as a thing.

So what we get back here is 418 milliseconds to dump data into the temp table. This query down here is what’s up locks running. So we don’t have to care about that time because that wouldn’t be part of our normal transaction.

So we have 418 milliseconds there, about 328 milliseconds to index the temp table, and then about 2.7 seconds to do the update. And in this part, in this query plan down here, the majority of the query time just is the update. Right.

Like we don’t spend a lot of time up until then, 643 milliseconds total. So, you know, about two seconds doing the actual update, about 643 milliseconds leading up to it. But the update is the majority of the work in there.

And this is, you know, better performance than we got from doing the straight update from the, like to update the table using itself as a source. So 418 plus 328, that’s 7 and change. And then 2.7 here, that’s 3.4.

So that’s three and a half, about three and a half seconds down from seven seconds. We cut that time in half. Good, good, good. These are all positive things, right? Breaking things up a little bit is a grand way of doing things.

Another way that SQL Server can perform what my dear friend Paul refers to as phase separation. What we did up there is manual phase separation. What we’re going to see in the next example is called automatic phase separation.

And what we’re going to do is instead of do the update and, you know, set the news that like sort of like that sub query to do the update. Instead of doing this, we’re actually going to join to the result of that. And what I had to do in this case was to hint for a hash join.

The reason for that, the reason why this gets a little bit tricky is because the ID column that we’re joining on is the clustered primary key of the table. And when SQL Server has two clustered primary keys, it generally tends to favor either nested loops or hash joins or merge joins rather. Because we have ordered sets, we have, you know, well-defined indexes.

So SQL Server is generally tends to lean towards those. There are plenty of circumstances where it would choose, might they choose hash joins. Otherwise, in this case, I just used a hint rather than fiddle and faddle with a bunch of other stuff.

But if we run this query and we use this update instead, this will finish in, I forget now. I did this late last night, so my mind’s a little less useful than it used to be. But this finishes in about 4.6 seconds total.

And again, most of the time in there is spent in the update, right? So we had 503 seconds up until the update and then 4.6 seconds after. It was about 4.1 seconds.

Not only about 400 milliseconds difference from the 3.7 when we did the manual phase separation thing, but still better than the 7 seconds total there. And this is, you know, without having to, you know, do a transaction, set serializable, because all the locking is performed, right, just within this query by itself.

But notice in this query that, I mean, A, we get a parallel plan, which we did in the original one. So that helps things along. But also there’s no spools in here.

The reason why we don’t need a spool is because we have a few stop-and-go operators that pause output for rows to be kept track of. We have a hash join here.

The hash join is a stop-and-go operator. People call it like an internally blocking operator because all the rows have to get to the hash join. The hash table has to get built and other stuff has to happen.

We also have a sort in the query plan. The sort is another one of those stop-and-go internally blocking operators. All the rows have to arrive before they can come out. So in this case, because we have operators like this in the query plan, we don’t explicitly need a spool to keep track of which rows have been hit or not because all the rows that come out of that are going to be rows for the entire query, right?

All the results have to hit operators like that and then be passed along. We don’t have just an entirely streaming plan that can just pass rows along as it goes and may end up rereading rows on the other side, right?

Because in the case of the first query plan, and I’ll just get the estimated plan for that again so we can talk a little bit about why that’s a thing.

Part of the reason for that is, like when I talked about how read committed is kind of not my favorite isolation level, all of these operators, none of these operators aside from the table spool will cache all the rows from the query.

So that’s why this gets inserted here. SQL Server could have chosen a sort or a hash or something else to do it. It just didn’t. Cost-based optimization chose a spool. So what could happen is, as we’re doing the update here, other operators could end up rereading rows in here.

But because we have this spool as a caching mechanism, because we use a temp table as a caching mechanism in the second example, and because we had hashes and sorts in the other query plan that can be used as caching mechanisms, the spool becomes not needed explicitly to do this because we have other things that keep track of the rows.

So anyway, if you’re looking at modification queries and you’re trying to tune those, and you find that performance is subpar, you now have a few, or actually you have a couple of things that you can try to improve the performance of the updates.

Of course, if most of the time is spent in the update operator, like if, like, you know, let’s say the entire query runs for 10 seconds, and like nine and a half seconds is spent in the update, you may have a different problem to solve, either like the number of indexes that you’re modifying, you know, stuff like that.

You know, sometimes, you know, putting locking hints on there to skip like row or page level locking, jump right to a table level lock can be useful. But this is just kind of a good example of how you can rewrite queries in different ways to improve performance of the entire operation.

Anyway, that’s about it here. Thanks for watching. As usual, I hope you learned something. I hope you enjoyed yourselves thoroughly, thoroughly enjoyed yourselves.

And remember, do the old like and subscribe dance for me, and I will see you in another video probably pretty shortly. Thanks again for watching.

Going Further

SQL Server 2022 Degree of Parallelism Feedback Is Wack

Posted on May 26, 2023January 21, 2026 by Erik Darling

SQL Server 2022 Degree of Parallelism Feedback Is Wack

Video Summary

In this video, I dive into my thoughts on a feature in SQL Server 2020-22 that I find personally disappointing: the degree of parallelism feedback. I explain why I believe it’s not very useful for most users and discuss how it only tests downgrades to DOP 2, which is often unnecessary given that many servers have their DOP configured correctly. Additionally, I suggest a better approach would be to allow SQL Server to explore parallel plans for queries that do not break the cost threshold for parallelism setting, helping to mitigate risks associated with adjusting this setting. The video covers four main reasons why I find this feature lacking and concludes by recommending improvements or alternative settings that could make query optimization more effective.

Full Transcript

Erik Darling here with Darling Data, and I’m taking a little break from my recording about various community tools and my little bit about series, which, I don’t know, I was going to call that Tip Jar SQL because it’s sort of like small little tidbits about things, but got lazy. Just sort of forgot to go down that path. Anyway, I’m here to talk a little bit about, I think, a really disappointing feature in SQL Server 2020-22, and that is the degree of parallelism feedback. I’m going to talk about the four reasons why I find it personally disappointing, and then I’m going to talk about some things that I think would make better additions to the intelligent query processing set of features that have the intelligent query, and that have the intelligent query, and that have permeated SQL Server since 2016 or so.

It’s a really interesting feature that have permeated SQL Server since 2016 or so. Maybe 2017, who knows? One of those years.

Sometimes it’s hard to tell the difference between 2016 and 2017 in retrospect. So, the first of the things that I dislike about the feature is that it only tests the data, and it’s not just the data, but it’s not just the data, but it’s not just the data. Where you have datatrue that knows that folder has its protection for its operational assessment that has done which changes when the database can IRS Liaultにな place.

Debenstrom app has five, seven views as the data, and all of those areas are connected to that. So, you know, that’s useless at best for most people. Most people have DOP moderately correctly configured on their server, and testing minor downgrades to that is quite a waste of SQL Server’s time and of your money.

You’re paying $7,000 a core for SQL Server to figure out if a query can run at the same speed at a lower DOP by like 2 or 4 or something. It only tests downgrades to DOP 2, which means that you will have at best a minorly parallel plan. SQL Server will not test if going down to a single-threaded plan would not detract from query performance at all.

So that’s kind of silly. And I’ve been given, I think, some reasoning on that, and that would require a recompile, which is just nonsense because when SQL Server comes under sufficient CPU pressure, you will see a parallel plan, but the query itself will only be running at DOP 1.

I have plenty of demos that show that. They do not require a recompile. You still see a parallel plan for it, but behind the scenes, a query only gets one CPU thread. Its DOP is indeed one.

It doesn’t test DOP upgrades, meaning that if you have, let’s just to make numbers easy because I am not particularly good at math. Let’s say that you have a query running at DOP 4 that’s hitting a 100 million row table. Let’s go to 100 million rows.

Let’s really scale this thing up. You have four threads suffering with 25 million rows apiece. It does stand to reason, at least in my experience with query parallelism, that if we were to alleviate some of the stress from those four cores and maybe bump it up to eight cores, and we would have roughly 12.5 million rows per thread, each of those threads would be under far less stress and strain.

We could probably have a more efficient query, but degree of parallelism feedback will not test that for you. You don’t get that. You get the same dumb DOP 4 plan without any hope of a parallel upgrade, which is pretty goofy.

Want my honest feedback? That’s just plum goofy. It also won’t test upgrades from a serial plan to a parallel plan. That doesn’t happen.

Probably gets it more malarkey about needing to recompile. But we all know that’s not particularly true. Yeah.

And so, like, if you have queries that are running for, I don’t know, 5, 10, 20, 30 seconds, and there is nothing specifically inhibiting them from going parallel, then why not test and upgrade to a higher DOP to bring the duration of those queries down?

Since this is something that is not permanent, it is impermanent, as some might say, why not just test it and see if there’s any dramatic decrease in overall query duration at higher DOPs, like, say, 4 or 8?

8’s a pretty good number for most DOPs, as long as you have the CPU cores to back it up. So, that’s a pretty disappointing thing there. Now, this feature really feels like there was no adult in the room at the time when it was decided on.

This feels like developer pizza, where, you know, they decided that they wanted to, you know, pick out, like, the grossest pizza, and you’re like teenagers sitting there with a menu being like, ew, tuna fish and gravy or something, like, just like picking out intentionally gross pizza toppings.

This is just not a very good spec for this thing. I can’t imagine that there was very good telemetry involved in deciding on this one. It just kind of feels like a what if.

Like, developers get one wish per release, and this was the wish. Now, for a very, very long time, SQL Server has had the cost threshold for parallelism setting, and this setting has led many, many people to think that query cost is a very important metric.

And I guess it is in the, you know, because it does, you know, if a query costs under your cost threshold for parallelism setting, and it would be reasonable for that query to go parallel, but it does not get considered for parallelism because it does not have a high enough cost to go over your cost threshold for parallelism setting, then, you know, you could have some issues.

But quite far more often, I find that, you know, servers running at the default cost threshold for parallelism setting have far more problems than folks who have raised it up and might have a few straggler queries that could use some help. Now, way back when, the cost threshold for parallelism setting is, or rather, meant seconds on one guy’s computer named Nick in, like, 1997 or something.

That’s the, at least that’s the folklore around it. And there are still people I talked to today who think that cost threshold for parallelism means seconds, which is kind of weird. But I think that, well, two things.

One, a nitpick is that, you know, the cost threshold for parallelism setting should be part of the SQL Server installer. MacStop is in there, has been in there for a few releases now. You know, and I think it would be a generally useful thing since most sane and rational people, after they install SQL Server, will set cost threshold for parallelism away from the default.

Whatever they set it to is better than the default unless they set it lower for some reason. That would be a weird choice. You know, most folks I know will start at 50 and then adjust as necessary from there, assuming that 50 is not a successful change for them, or not a completely successful change for them.

I think that a much better addition to SQL Server than degree of parallelism feedback, which only focuses on downgrading parallelism, would be a setting that would allow you to allow SQL Server to explore parallel plans for queries that do not break the cost threshold for parallelism setting, and look at their overall duration, perhaps their weights as well, if that’s the kind of thing that you care about, and figure out if a parallel plan would be better.

It would also be good if we could test parallelism at higher DOPs up to some upper limit, you know, whether that’s configurable or that’s just based on, you know, some internal math, and look at the number of cores and all that good stuff.

Cores and sockets and things, things that people really think hard about when they’re setting maxed up and all that, figuring out NUMA, getting crazy in there. But I think a much better setting would be, you know, to figure out if, figure out what threshold you would want to explore a parallel plan for a query that was deemed not expensive enough by the optimizer to consider a parallel plan for, because that would take a lot of the risk out of adjusting cost threshold for parallelism.

You know, just to throw a number out there, let’s say that you change cost threshold for parallelism to 50, and there’s some really important query that has an estimated subtree cost of 49.9 query bucks, does not quite break that threshold, and you have a regression to a parallel plan.

Well, this would take a lot of the sting out of that, because if that thing ran for, let’s just say, 10 seconds, the optimized, you know, the SQL servers internals looked at that and said, oh, 10 seconds?

Well, that doesn’t seem very, that’s not very fast. Let’s try .4. Okay, you know, now let’s just say that it, like the parallelism scaled linearly, and now the whole thing runs in like 2.5 seconds.

Well, that was, that was a successful introduction of parallelism to a query. I realize that there is inherent risk with introducing parallel queries or upgrading parallelism. Downgrading parallelism does not, does not have, you know, the whole issue of, you know, parallel queries and worker threads and all that stuff, you know, there is risk in there.

But I think that is a risk that most workloads do not have. And the fact that this setting is one that you can turn on and off is one that you can use to mitigate those risks a bit for anyone out there who has a workload where they are at risk of that.

But I think most people who have workloads where they are at severe risk of that are at least fairly well managed as far as DBA staff and all that stuff goes. So anyway, those are my thoughts on the degree of parallelism setting.

It’s not a terribly good feature. I’ve seen the demos for it. It’s not very interesting.

DOP goes down, doesn’t go up. And that’s about it. I don’t know. Maybe this will be one of those things that just no one ever turns on and it dies on the vine and that would probably be the best thing for it unless significant improvements are made or other settings are introduced to allow you to better control parallelism on the server, specifically going from a serial plan to a parallel plan where it would be more efficient to do that.

Anyway, that’s it. Thank you for watching. You may not have learned anything, but hopefully you enjoyed yourselves anyway.

I’m going to go eat lunch now, I think. Brush my teeth after that. Make my dentist happy.

I don’t know. Maybe then I’ll go for a walk. We’ll see what happens. Anyway, thank you for watching. Remember, like, subscribe if you’re into that sort of thing, if you’re that kind of YouTube fanatic and also that kind of SQL Server fanatic.

And I will see you in another video that will be back to more of the regularly scheduled content. Anyway, thank you again for watching. Thank you.

Going Further

A Little About Views, Parameters, and Local Variables in SQL Server

Posted on May 25, 2023May 3, 2023 by Erik Darling

A Little About Views, Parameters, and Local Variables in SQL Server

Going Further

A Little About Improving Modification Query Performance In SQL Server

Posted on May 24, 2023May 3, 2023 by Erik Darling

A Little About Improving Modification Query Performance In SQL Server

Going Further

A Little About Why Parallelism Doesn’t Make Every SQL Server Query Faster

Posted on May 23, 2023January 21, 2026 by Erik Darling

A Little About Why Parallelism Doesn’t Make Every SQL Server Query Faster

Video Summary

In this video, I delve into an intriguing scenario where parallelism in SQL Server doesn’t always yield the expected performance benefits. Starting off with a view called “parallel but serial,” which gets amusingly abbreviated as PBS by Redgate’s SQL Prompt tool (a nod to Sesame Street), I run through its execution plans side-by-side to highlight the nuances of parallel query processing. The key takeaway is the concept of skewed parallelism, where all rows end up on a single thread despite running in parallel, leading to suboptimal performance. This video serves as both an educational exploration and a reminder that while SQL Server’s parallelism can be powerful, it’s crucial to monitor row distribution across threads to ensure optimal query execution.

Full Transcript

Erik Darling here with Darling Data. Exciting news on top of Scotch Sniffer Magazine wanting to partner up with me. I’ve also, I guess Wine Wanker Magazine also watches my YouTube channel and they’ve put in a competing offer to partner with me on future endeavors. This video was of course sponsored by Fiji Water. I guess not really because I can’t, I don’t know if this is actual Fiji Water or if this is the Fiji Water bottle that I refilled with New York tap water. Maybe, maybe it says something about my palate that I can’t tell the difference between a fresh bottle of Fiji Water and New York tap water, but quite frankly, water doesn’t get me drunk. I’m not going to invest that much time in figuring it out. This video, video is a little bit about why parallelism does not help every query in SQL Server. And what I have set up over here is actually a view called parallel but serial, which is oddly enough gets aliased by Redgate SQL prompt is PBS, which I find amusing because this is supposed to be a very informative and entertaining Sesame Street video.

But what I’m going to do is I’m actually going to run these. I’ve got query plans turned on so I won’t waste anyone’s time. And while that happens, it’s going to take around 45 or so seconds. So I’ve got a lot of dead space to kill here. Maybe I should have talked about the magazines and whatnot while these things were running. Might have been smart of me. Poor time management skills, I guess you could put in my yearly review. But while these things are running. But while these things are running, this is the definition of the view. I’m actually going to just take that thing off for a moment so that the code is a little bit more clear and easier to read. Reading a wall of green text is no easier than reading a wall of red text. But anyway, what we’re doing is flattening out dates into years and months, because that’s all we care about. And we are joining the post table to itself to find accepted answers and accepted answers and what not. We’re also sort of doing a third kind of weird cross join to find the max creation date of all posts, where the score is greater than zero. And we’ve got a little bit of filtering at the end to find dates that are less than the max creation date in the table, which I know looks a little bit silly. But I deal with a lot of reporting queries that hit like date, dimension tables kind of things like calendar tables and stuff.

that do things like this that end up with weird execution plans. And then of course, we have to group by because we are we are getting a count. So we have to aggregate this expression as well. But anyway, our queries finished, it took about 41 seconds, my guess was between 40 and 45. So I was close on the low end. That’s a nice change of pace for me. But if we look at the execution plans, we’re going to have the parallel plan up top, which takes 21.525 seconds, 21.525 seconds, 21.525 seconds. And we’re going to have the serial plan down the bottom, which takes 20.170 seconds. So the parallel plan takes about a second longer, for some reason, I’m not going to invest too much time in figuring out where that extra second goes. But what I do want to point out is that the plans have the same basic shape, right?

They both start with an index seek into posts down here. Now we have our index scan of posts that goes into a loop join to seek into posts, which is the same thing that happens here. We have a key lookup, we have a key lookup, we have another nested loops join back to posts again. And the only thing that’s really different are some of the repartition streams operators that come along with a parallel plan. All right. Now this is an important thing that you need to check when you have a parallel plan. And you’re like, wow, this parallel plan is running a whole lot more slowly than I think it should. What the heck is wrong with this parallel plan? One thing that you want to check and make sure is not happening to you is skewed parallelism. So if we right click on this nested loops join, and we look at the actual number of rows, we’ll see that absolutely all of the rows in this query end up on a single thread. And this will continue throughout the query.

If we click on different operators, all of the rows will be on one thread going throughout. I mean, clearly, like the worst of it is in here, where we’re dealing with a lot of rows. But you know, the story doesn’t get much better. Well, I mean, the story does get much better as rows get filtered out. But the story doesn’t get much better as far as like row distribution goes. When when when even even after the number of rows gets narrowed down, they’re still all on one single thread.

Now, the way that SQL Server assigns pages to parallel threads in a query is via a mechanism called the parallel page supplier, which uses a sort of a hashing function, which is like like modulus dop, you know, like modulus, you know, eight, or whatever for this query, since we ran it at max dop eight. And it starts assigning pages by looking by by using that and saying, well, this row goes to this thread, this row goes to this thread, this row goes to that thread. But in this case, we get incredibly unlucky and all our rows end up on a single thread.

Now, there are various ways to solve this. Sometimes actually forcing loop joins can be a good way to solve it. Sometimes forcing different join types like hash or merge joins can solve it, especially changing the plan shape and, you know, all that other good stuff. But a lot of the times, you know, you do like, you know, you may be using something like a temp table to pre stage some of the data would have been useful, like maybe instead of cross joining to this, maybe if we just stuck the max creation date into, into a temp table and then, you know, use that to filter things, that would have been more appropriate.

But this is something that I actually ran across it twice in the last, I don’t know, well, actually not this month, because it’s the first of the month. But I ran across this exact thing a couple times last month, that would be April for anyone counting. And there were, well, I mean, the stuff that I just talked about was kind of how I ended up solving the problems in those cases.

But yeah, that’s just sort of an unfortunate side effect of parallelism that not all row distributions are created equally across your parallel threads. There have been a couple of times in the past where using an odd DOP number like seven or three or something has helped with this a little bit. But in other cases, you do have to use different methods of solving the problem.

Anyway, this is a short video because I just need today to be done with so I can start resting my brain for tomorrow’s recording. So I’m going to say goodnight here. I’m going to say thank you for watching.

I hope you learned something. I hope you enjoyed yourselves. And of course, a reminder to like and subscribe. Like this video and subscribe to my YouTube channel so that you can keep learning all sorts of fun and interesting things about SQL Server. Anyway, that’s about it.

Again, thank you for watching.

Going Further

A Little About Date Math In SQL Server Queries

Posted on May 22, 2023January 21, 2026 by Erik Darling

A Little About Date Math In SQL Server Queries

Video Summary

In this video, I delve into the nuances of proper date math in SQL Server queries and demonstrate how it can significantly impact query performance. After a rather unconventional breakup with BeerGut magazine, I’ve found myself in an interesting new partnership with Scott Sniffer magazine. While this venture might be more refined and elegant than my previous endeavors, it does come with its own set of challenges—like dealing with fancy Scotch sniffers who, despite their title, may or may not actually drink the stuff. Focusing on practical examples, I show how moving date math operations to the correct position in your queries can drastically improve performance by allowing SQL Server to utilize indexes more effectively and avoid unnecessary scans. This video is a reminder that writing well-structured queries isn’t just about getting results; it’s also about making sure those results come quickly and efficiently—leaving you with more time to enjoy a well-deserved glass of Scotch, or at least the virtual equivalent!

Full Transcript

Erik Darling here with Darling Data. After my prolonged, painful breakup with BeerGut magazine, I’ve been approached by Scott Sniffer magazine about forming a partnership. It’s going to be a little bit harder on my liver, but, you know, maybe a little bit more refined and elegant. Just like, just like my T-SQL and BeerGut magazine. Finally, work with a higher echelon of individual, not lowly beer drinkers. I’m going to hang out with a bunch of fancy, pinky-out Scotch sniffers. Actually, it’s unclear to me if they actually drink the Scotch or throw it in. They just sniff the Scotch and tell you what they think it smells like. So, all things I’m sure that I’ll learn during our, uh, conversations. So, uh, this video, uh, like the helpful ASCII text is telling you is, uh, where we do date math and how we do date math in our where clauses, and I guess occasionally in our join clotters, clotters, uh, to avoid those, uh, clauses matters for performance. So, right now, the only index that I have on the post table is on the ID column, and that’s a weird bit of format. What, what, what, quite what possessed me to do that. I think maybe I just got drunk and hit the wrong button. Uh, the only index that I have on the post table at the moment is on the ID column and with the owner user ID column included.

And what this query, well, I have two queries, one of them written the wrong way and one of them written the right way. Uh, in this join, I am doing date math the wrong way. Let’s frame that nicely. There we go. Let’s see, uh, I can draw a pink box around my head kind of. Ah, the things you can do with computers. So, in this one, uh, we are doing date math on the column that we care about. We have the v.creationDate wedged in the middle of, uh, our dateDiff function.

And in this query where we’re doing things the right way, uh, we are comparing the creationDate column to a date math expression here. So, I’m going to run both of these queries. And we got query plans turned on, so that’s a good thing.

And, uh, the reason why, of course, this makes a big difference is because when, uh, SQL Server has to perform date math. Um, um, on a column directly, uh, it has to do a lot more work than if it, if we, if it can do the date math on, uh, an expression and apply that to the data in our table. So, just moving, like, we don’t have, we don’t even have a useful index on the votes table right now to, uh, to, to find data in the creationDate column.

But even without that, even without that, we are able to make the, our query go nearly twice as fast. Well, actually, that’s a little bit better. The shave is about two seconds off the whole thing.

Go from 3.6 seconds when we write our query the wrong way to about 1.4 seconds when we write our query the correct way. So, there’s about a 2.2 second difference just making that change, just pushing the, uh, the, the date math from being, uh, from having the creationDate in the dateDiff column. And, uh, you know, comparing the creationDate column to this expression.

Now, if we go back and look at the query plans, I mean, aside from the fact that, you know, when you use non-sargable expressions, like when you put a column in a function and compare it to something, uh, SQL Server doesn’t bother to tell you that an index might help. Because you’re going to have to scan that entire index anyway. SQL Server’s like, well, what’s the difference?

I have to do, I have to do the same amount of work. You know, I’m just not even going to bother with that. Like, it’s not a cleanly, enough, not a cleanly written expression. So, uh, sorry, it’s not a cleanly written comparison. So, you can’t really, you don’t really get anything useful out of an index there.

Like, you might have a smaller index to scan, but it’s not going to be like a seek or anything. You’re not going to be able to seek to the data that you care about. So, you know, if you go look at this, uh, we have this predicate here where we have all sorts of stuff going on.

Uh, we, we compare, we, ooh, look at that. That’s a fun one. Uh, we have a convert implicit to date time offset three.

That’s fun, right? So that, that’s going to be part of, that’s going to contribute a bit to our, our woes. And then, uh, in the, there’s a helicopter going by.

Uh, I’ve been told by. The ever vigilant Randolph West that, uh, the, the New York City noises in my videos are part of the charm. I wonder if part of the charm is also the fact that I’m standing in front of the thing I want to show you.

So, let’s, let’s reframe that a little bit. Let’s move that over. Uh, actually, let’s bring up the tool tip first. And let’s get that where we want it, where we can see it mostly. Good enough.

Uh, nope. Stay. Stay. Good boy. Sit, Ubu, sit. Is there old enough to remember that reference? Uh, I owe you a, an Adidas t-shirt and a high five. So, in this one, even though we, we scan the scanner index in the same way, the predicate we apply doesn’t have any implicit conversions. Uh, we are able to get a bit more performance out of our query just by moving the date math a little bit.

Uh, and, uh, of course, SQL Server. And this one helpfully suggests an index on creation date that includes post ID. Right there.

Up in the pink. Oh, uh, fragile green screen. Uh, but that, that is, uh, you know, uh, sort of secondary. I mean, anyone, uh, who’s been working with, uh, SQL Server or databases in general for any amount of time could probably figure out, uh, that an index on a where clause is a useful. That’s a helpful thing.

Remember, we put data in order, it gets really easy to find. Uh, the only thing that I would probably disagree with, and another big reason why I tend to, uh, try to dissuade folks from listening to the missing index request, is because, uh, if you, we go back to the missing index request, uh, SQL Server only wants to include post ID in the index.

It does not, uh, does not think that our join column would be useful as a key column, which is, um, in most cases, a fair bit of malarkey. So, I’m going to be honest with you. Those missing index requests, not terribly valuable things.

Not terribly valuable at all. Kind of, kind of dislike them. Um, used to like them a lot. Now I hate them.

Anyway, um, you know, they’re, if they’re okay if you’re looking at the query itself, and if, you know, they’re okay if, you know, uh, you’re looking at the actual execution plan. And, uh, the operator, uh, in this case, the clustered index scan is the longest running thing in the query. And, you know, uh, you might say, oh, well, uh, like, you know, it might, it might be useful.

Might not. You know, uh, shaving 863 milliseconds off here. Not, not sure that that’s going to be, you know, the, the big winner.

Uh, you know, it might help a little bit more. But, uh, you know, this one up here where it’s, you know, three seconds of execution time, that might be a little bit more compelling. But, you know, um, also, if you write your queries correctly, uh, they tend to run faster anyway.

So, uh, this is about all I had to say about this one. Um, this is me urging you to write date math in your queries correctly and improve performance. And, uh, I don’t know, maybe you’ll get to see more of your friends or family or just get to do the things you enjoy better than sitting around scratching your head about why your SQL Server queries are all slow when you have poorly written date math inquiries like this.

So, I don’t know. You’re welcome. Um, you’re welcome in advance, I guess.

For all that. Uh, I’m going to, I don’t know. Maybe I’m going to go do something I enjoy now. Maybe I’m going to go sniff some scotch.

Sounds like a pretty decent idea. It’s 6.28 p.m. here at the moment. Sitting here recording videos instead of smelling delicious scotch. That seems like a bad choice.

Maybe you shouldn’t listen to me about anything. Maybe I’m wrong. Maybe, maybe I’ve got my life entirely wrong. Oh, anyway. I’m going to kind of go re-evaluate some things. Thank you for watching.

Please like, please subscribe. It’s the thumbs up and the bell. Uh, if you don’t, I’ll just have to sniff scotch even harder. I might sniff it so hard that it goes right up my nose.

And who knows what will happen then. Get scotch in my brain or something. Anyway, uh, thank you for watching. I hope you learned something.

Hope you enjoyed yourselves. Uh, and I hope that you start writing date math correctly in your where clauses so that you don’t end up with poorly performing queries. Thank you for watching.

Going Further

Formatting, Debugging, and Troubleshooting Performance Of sp_QuickieStore

Posted on May 19, 2023January 21, 2026 by Erik Darling

Formatting, Debugging, and Troubleshooting Performance Of sp_QuickieStore

Video Summary

In this video, I delve into some useful features of QuickieStore, a handy tool for extracting data from SQL Server’s Query Store DMVs and exporting it to formats like Excel. I highlight how QuickieStore can help you format output in a more readable way by adding commas to large numbers, making it easier to understand the scale of your query statistics. Additionally, I explain how to use parameters like `debug` and `troubleshoot performance`, which provide detailed information about parameter values, temporary tables used, and execution plans for each query run through QuickieStore. These features are invaluable for debugging issues or troubleshooting performance problems when working with Query Store data.

Full Transcript

Erik Darling back again, still with Darling Data, at least for the time being. Who knows? Maybe I’ll be with MongoDB in six months. See what happens. Just kidding. I’ve heard it’s not a real database, despite all their best efforts. Just kidding. My dear friend Joe Sack works at MongoDB, and I would never besmirch the product he works for, because I did enough of that when he worked at my Microsoft. He worked for it worked on SQL Server. So in this video, what I would like to talk about is some different ways you can use QuickieStore if you need to put the data into something like maybe Excel, or if you run into problems with SP, QuickieStore. So again, all of this stuff is detailed under the help parameter. If you scroll down a little bit, we’re going to be dealing with things sort of towards the end, around formatting output, debugging issues, and troubleshooting performance. So, what we get, if we run QueryStore, sorry, if we run SP QuickieStore, by default, normally, without any interventions whatsoever, you’re going to see something startling.

That’s just a real deal. If you want to see something like that. Now, if the people who create and work on SQL Server Management Studio cared about you in the least, they would put commas into numbers.

because when you put commas in numbers, you can very easily judge their scale when they get really large. And that can be pretty important if you’re the type of person who deals with big problems. So let’s scroll over to the right a little bit, and let me start showing you some numbers with commas in them.

Starting here with weight stats, we can see that there were 186,915 milliseconds of parallelism weights in this query very easily. If this were all smushed together, it might be a little bit harder. This will happen whenever a query breaks the thousand, or sorry, whenever a number breaks the thousand mark.

You can see all that in here. Where things are in the thousands, we have a comma, so we can see that very easily. Where things are under the thousand mark, we don’t need a comma, because there’s no such thing as 3,49 unless you’re dealing with weird currency issues. But we are not. We don’t talk about query cost because query cost is a meme, and query cost should be ignored to the fullest extent of the law.

So, scrolling over a little bit further, we have lovely, lovely commas in all of these columns, so that we can see exactly what kind of numbers we’re dealing with very easily. Right over here, this logical read, look at this, 1.1 million logical read, very easy to tell what that is. Now, I’m going to tell you something about milliseconds.

It’s going to bake your noodle. If you chop off the last three digits, that’s how many seconds it was. Pretty impressive, huh?

Average duration, 24 seconds. Total duration, 48 seconds. Because there were two executions. Pretty sweet. So, while we wait for the lovely, hardworking, intelligent people who keep SSMS afloat for us to put commas into numbers, SP Quickie Store is out there in the world doing it for you.

You’re welcome. All of you are welcome. Now, let’s say you run into a problem with SP Quickie Store. Let’s say you run into some issue executing it, and you get an error, and you want to figure out what’s going on.

A great way to do that is to use the debug parameter. So, if we use the debug parameter, up front, we’re going to get a bunch of stuff. We are going to get all of the parameter values that were passed in to the stored procedure.

All right, it’s going to show us what the starting value was for every parameter. It’s going to show us what the declared variables were set to inside of the stored procedure, which can be very helpful for figuring out if anything got set incorrectly along the way.

All right, lots of good stuff in here. Lots of helpful, useful things in here. And then if we go down a little bit further, we’re going to get the contents of all of the temporary tables that were used to filter, join, get data out of our Query Store DMV.

So, this distinct plans table is the one that drives most of what we pick up out of Query Store. I use a lot of temp tables in this store procedure because I found that just naturally querying the Query Store DMVs directly led to a lot of performance issues.

I can still run into performance issues querying them in this way, but I run into far fewer of them. And I’m going to tell you something here. There is almost nothing more embarrassing than talking a client into turning on Query Store because it’s nice, lightweight, almost no overhead.

You won’t even notice it. And then you open up Query Store, and like the first three queries you see in it, there’s Query Store querying itself to populate the Query Store GUI.

It’s real tough. It’s a real egg on face moment. So, if we scroll down, we’re going to see all of the temp tables that got used.

Maintenance plans is what I use to screen out, you know, non-query activity that can be harsh on a server. You know, create table, alter table, not really create table, but like alter table, index maintenance, stats maintenance, stuff like that. I use that to filter out stuff here.

I use this to figure out what the Query Store options are for a database. This is sort of the raw query store data or some of the raw data that I output and format in different ways and other result sets. You know, just sort of like how each temp table was populated along the way.

So, there’s lots of useful stuff in here to figure out. Like, wait, well, this should have been in there. Why wasn’t it in there? You can kind of track down and figure out why things didn’t end up where they should have or why you’re seeing what you’re seeing in the results.

That’s the first part of it. The second part of it, over in the Messages tab, prints out every single query that gets run by Dynamics SQL. It’ll print it out here.

Before each one of these queries is a number. This number signifies the number of characters in the query. So, if you see something that is, you know, cut off or you see something that, you know, may have been some dynamic SQL that did not concatenate cleanly.

Perhaps there was some implicit conversion that happened that, like, caused the string to truncate. This is a good way to troubleshoot some of that stuff. There isn’t a lot of terribly long dynamic SQL in SP Quickie Store.

But, you know, there was enough for me to care about exactly what was going on in here. So, like, this is, like, 3,000, 2,800 characters. And this is the query that got executed in there.

And this is all the stuff that it did. And this can also be very useful because if you hit an error, it will tell you which query through the error. And then you can rerun that query to see if you can reproduce it, see if you can figure out exactly what’s going on.

That’s what I do with a lot of this stuff when I’m working with, when I’m trying to debug things. A lot of this was in here just during development just to make things easy. But I kept the debug thing in because, you know, if you run into anything while you’re using it, you know, I want to make it easy to troubleshoot for you.

So if you go ahead and open up an issue for me in GitHub, you can tell me exactly where the problem is, what you ran into. And we can, I can try to help you fix all of that stuff. Or I can help me fix all of the stuff that I need to fix in my query.

So good stuff there. Some of these, because of the way I print things out, like some of the strings are quite long and they don’t print in one thing. Print is a little more forgiving than raise error.

Raise error has a bigger cutoff. So you might see some points where the query text is not exactly the way it should be. That’s not me being bad at my job. That’s just me not having any control over how print sticks things into the messages tab.

So, excuse me. That’s a good way to troubleshoot if you run into any issues with SP Quickie Store. If you start running into performance issues, you can use the troubleshoot performance parameter.

And this will do some interesting stuff. So if we say troubleshoot performance, the first set of output is going to be every query that runs, that hits the query store DMVs, and the actual execution plan for that query.

So that’s all this section in here. I’m going to talk about what’s in there in a second. But you can see there’s a whole bunch of queries in here. This thing finished in zero seconds because, you know, I can use temp tables and the GUI can’t, I guess.

But anyway, what comes out of here is kind of cool. So there’s a pair for just about every line here.

We’ll get a query plan back that’ll show us exactly what the query was, what the execution plan was. I mean, this all finished in zero everything seconds.

So we don’t need to worry about this one. So we have that, which is cool. But then we’ll also have, also have for every query that, that ran and produced a plan, this information, which is very, very useful.

We have how long it took. We have what the current activity is. So we can control an F for this in the script. And then we have the query that executed so that we can look at it and say, hmm, maybe, maybe, maybe I could have done better here.

I don’t know. Maybe I could, maybe I couldn’t. I just don’t know. It’s impossible to tell. Pretty sweet. Huh? All right. So, and you’ll get that for every query that executes.

And, you know, you’ll see, again, the query information about how long it ran for, how long the dynamic SQL was. So you’ll see some of this, some of this information is a little repetitive, but it is all very useful when trying to figure out if there’s a performance issue with one of these queries that is fixable.

There’s a lot, a lot, a lot, a lot, a lot of times querying these DMVs, especially on very busy, very active servers, where, or servers with a lot of query store data in them, where this thing can slow down a little bit because there are just some things that do not perform well on those types of servers.

Specifically hitting some of these, some of these table valued functions that are, that sort of are masked by the dynamic management views.

A lot of the times trying to get stuff out of there, like you can’t just filter it directly. Like you can’t push predicates to some of these query table valued functions directly. You end up with like, you know, scanning a whole lot of rows and then filtering stuff out later.

That’s what this kind of combo is showing you. And does it kind of give you an idea of the kind of stuff that gets filtered in here? Sometimes it’s not even a predicate that I pass in. Sometimes it’s just something like this, where like, I didn’t, I didn’t ask for that.

I didn’t, I didn’t ask for this. This is just part of the built-in view definitions or the table valued function definition. So all good stuff to be aware of, all good stuff to try and help you if you run into performance issues while using SP Quickie Store to query the query store DMVs.

If you find anything in here that you think, you know, you can, you can show me and it’s something that is fixable by me. I’d love to look, but a lot of the times it is background stuff that I just can’t do much about.

Anyway, I think that’s probably good for this one. Again, formatting output. If you, if you want to, you know what?

I don’t think I ever talked about what happens if you put the format to zero, it takes the commas out. So if you need to like paste this into Excel or something, you can turn format output off and get the numbers without commas.

So yeah, there was that. I got so excited about debugging. I forgot to tell you that. So anyway, that’s my video. I do, I do hope you enjoyed yourselves.

I do hope that you learned something. I do hope that you will like and subscribe to my cough-free channel. Not, not throat-clearing-free channel because there’s still pollen in the air and my face feels like a brick, but that’s okay.

Do it all for you anyway. Thank you for watching. Take care of yourselves or else, or else you won’t be around to watch more videos from me.

What would be the point of life if you were dead? Thank you.

Going Further

Getting More Details About Queries With sp_QuickieStore

Posted on May 18, 2023June 30, 2026 by Erik Darling

Getting More Details About Queries With sp_QuickieStore

Video Summary

In this video, I delve into the expert mode feature of SP Quickie Store, a tool that provides detailed insights into query performance in SQL Server. By enabling expert mode, you get access to numerous result sets that are typically omitted from the default output due to potential information overload. These results include memory grant feedback and compilation stats, which can be invaluable when troubleshooting complex queries or optimizing performance. I also discuss the new DMVs introduced in SQL Server 2022, such as query store hints and query variant view, offering a deeper understanding of how these features can aid in diagnosing and resolving issues related to query optimization and parameter sniffing.

Full Transcript

Erik Darling here with the wondrous, illustrious, eutiferous Darling data. Everything but cruciferous. We do not aid in digestion. We are not a fibrous vegetable in the least. We’re going to talk about today continuing in the same vein as some of the other videos that we’ve recorded recently or that I’ve recorded recently. There is no way. no we in Darling data. It’s just me alone doing Darling data things. We’re going to talk about how you can get more detail about the queries that come back in the default output for SP Quickie Store. So what we have is a parameter called expert mode. And what expert mode will do is return a whole bunch of result sets to you that it would not have made sense to put in the default output because it would have been information overload. And this is really only stuff that you want to dig into when you start dealing with other problems, deeper problems with queries. If I put this in the default output, it would mostly go unnoticed. You would have a scroll bar with a slider like this big and you would scroll for a country mile till you lose your hand in a green screen and it wouldn’t be enjoyable for anyone. I promise. It would not be enjoyable in the least. All right. So when we use this parameter and we say expert mode, please. I’m going to get back some results that you might not get back because some of these results only show up in SQL Server 2022 because SQL Server 2022 had a bunch of new DMVs diagnostic management user, whatever you want to call them added to the query store family of views that help you figure out other stuff. So we have the default output up here. All right. Down here we show or I show rather if there is any feedback happening via different various intelligent query processing features passed on to passed on to the optimizer from query store. So in this one that right now we only have some memory grant feedback, so SQL Server is adjusting the size of memory grants between runs, we can get some information about the feedback data here, which is kind of nice to have if you want to dig into exactly what the feedback is telling the optimizer to do. We see here if the feedback is good or bad, right? It could say feedback valid, it could say feedback invalid.

And then we have the created and then we have the created and then we have the created and last update times. Now there are a couple DMVs that are included in the results that right now I don’t have any data for. So there’s the query store hints DMV. So if you apply any hints to queries via query store, which is a new feature 2022, pretty cool. Actually, I think I love this feature because I can finally fix entity framework queries. And then there’s the query store. So this is a new feature. So this is a new feature笑. So this is a new feature and then there’s a new feature. So this is a new feature. And then there’s also the query store query variant view. And if you have queries that are getting parameter sensitive plan optimization feedback, those results would show up in here. Down a little bit lower are results that you will see in most any version of SQL Server, as long as it has Query Store in it.

And that is going to be stuff around compilation stats. So compilation stats is going to tell you about everything that happened during query compilation. How many times it compiled, how long the compilation took, memory bind and all this other crazy stuff.

You can kind of understand why this is not part of the default output, but can be kind of important stuff to look at if you’re dealing with, say, long compile times, other stuff like that. Now, this is one of those bum out things that I’ve talked about in other videos. Query Store does not store memory grant information the same way that the plan cache does.

Query Store only tells you how much memory a query actually consumed when it ran. It does not tell you what the full memory grant was. So there’s no way for you to say, oh, you asked for 30 gigs of memory, but you used 30 gigs of memory and you only used like 2 megs of memory.

So maybe we ought to fix something in there. There’s perhaps some poor cardinality estimation happening in there. Perhaps some parameter sniffing issue.

Who knows? Could be anything. So for this, unfortunately, there’s nothing in my plan cache. So I can’t tell you all these other juicy details because the plan cache is a horrible place. It’s volatile.

It clears frequently. I don’t know what happened to it. I didn’t change anything. My plan cache was gone. Real sad story. There’s some other stuff which I actually think is pretty cool is getting weight stats back for specific queries. So this section in here will tell you per plan ID what the top weights were.

So in here we have a lot of parallelism in the CPU. That’s probably going to be the story across most of these things. Now, one thing that I do want to point out, and I’m going to probably skip over this because this is just the total weights for all queries.

And then the next one down is your current query store settings. So this will tell you like whatever, you know, however you configure query store, all the configuration options will be in here. So one thing I want to point out is that query store weight stats, the stuff you see here, is aggregated.

Like there’s a weight category called parallelism. We don’t know if it was CX consumer, CX packet, CX sync port, CX whatever. We don’t know what it was in there.

We only have a parallelism grouping. So if we go to the help section, down at the bottom, there is a little decoder ring for which weights are included in each one of those categories. I may need to update this a little bit for 2022.

I think I checked in on that the last time I did an update on this. But now that I’m looking at it, I’m going to check it out again to make sure I did my work right. So in here is where you’ll see the types of weights that are included in each one of those.

So if you have a question or you’re unsure about what weights might be involved when you see them show up in the weight section, then this is the place to look because this will tell you which weights are included in all of the roll-ups. So that’s just a quick way to get more detail about the queries that you find in the top 10 in Query Store.

It can be very useful for troubleshooting deeper issues with a query, getting more information back about queries, especially if the plan cache is not being a useless sack of potatoes. So there you go.

Anyway, good video. Good video all around. Didn’t cough once. It’s been zero days since I didn’t cough in a video. And I guess, you know what? I’m going to record one more on Query Store.

I just have to decide what it’s going to be on. It’s going to be a surprise to everyone, myself included. If I could tell the future, I would tell you what it was going to be about. But I had to say something, didn’t I?

Anyway, like, subscribe, send me cough drops, something. Seasonal allergies. It’s a terrible time of year here in New York.

Anyway, that’s enough of me. I’m going to go figure out what to do next. And I’ll record that video and upload that video. And you’ll watch that video and you’ll like that video and you’ll subscribe to my channel. Because even if you mute me and you just want to like turn this into a weird OnlyFans thing, I don’t mind.

I don’t mind. That’s how much I love you. I don’t mind what you turn this experience into.

All right. Cool. Thanks for watching. Hope you learned something. Hope you enjoyed yourselves. I hope my allergies go away.

I hope for many things. I hope someone signs a contract for a million dollars. You know, just manifest things into our lives. All right.

See you next time.