Tag: DBA

Capturing Query Performance Data With sp_HumanEvents

Video Summary

In this video, I delve into using SP_HumanEvents to capture extended event information in SQL Server, specifically focusing on query performance data. I walk through setting up an extended event to track queries that run for over five seconds and demonstrate how to filter these events based on session IDs or specific stored procedures. The goal is to help you identify and tune problematic queries without overwhelming your system with unnecessary details. I also discuss the limitations of SP_HumanEvents, such as the lack of filtering in the actual execution plan view within SSMS, and highlight its usefulness for performance troubleshooting by providing detailed query plans post-execution.

Full Transcript

Erik Darling here with Darling Data. And this is going to be another video where I talk about how to capture different extended event information using SP underscore human events. I want to get a cool echo going, but I don’t quite have those audio engineering skills. So in the last video I talked a little bit about what SP human events is. is four, which is basically to make capturing performance related human events stuff easier. So that focuses on things like query performance, weight stats, blocking, compiles and recompiles, right? All sort of good things to be able to capture for your workload. I apologize for the fuzz over here. For some reason, my setup is acting a little weird today and I can’t quite figure out why the lights are being awkward on me. So anyway, there are all sorts of different ways to capture query performance data, including actual execution plans. So again, don’t run this thing for too, too long. Or if you’re going to run it for a while, make sure it’s in like a development, on a development server or stuff like that. But yeah, there are all sorts of different ways to do it. So for example, this execution would track query performance, it would capture any query that runs for over five seconds, and it would run for 20 seconds. If you’re on a really busy server, and you don’t want to capture absolutely everything, you could do that same setup, but also add this sample divisor here, which sort of acts like a little bit of a fizzbuzz thing. So you would only capture queries for with spids that are divisible by being modulo 5 equals zero. Another thing that you can do is if you’re interested in particularly interested in a single store procedure, you can do this, and you can track any execution of a particular store procedure. Now, what I find myself doing most often is sitting on a dev server, like doing client work, I find myself most often sitting on a dev server, with you know, just an SSMS tab open. And I want to find a particular I want to monitor my session and my session only.

for long running queries from, you know, whatever, right. So I find myself doing this is particularly useful for long store procedures where a lot of tiny little noisy things happen. And like, if you collect the actual execution plan for everything, that that sort of procedure executes, you’re gonna like crash SSMS, right? It’s like, it’s cool that you can turn on actual execution plans for a whole store procedure. But like, there’s no way to filter that like, it would be great if you could, like, when you clicked on, collect actual execution plan, there was like one of those little arrow drop downs, where you could like filter that stuff out and be like, look, I don’t care about things that run in like under five seconds, just show me the bad stuff, right? Because a lot of store procedures do a lot of tiny little work. If there’s like loops and stuff like that, you end up capturing just a bunch of nonsense that you’re never going to care about. And then you end up sifting through it all trying to figure out what you what’s good and what’s not. Um, uh, see, uh, the, well, God, I wish I wish it was still the SQL century plan explorer, not say the name of the company who owns plan explorer now.

Uh, but, uh, that does make, uh, you know, dealing with a long, big long store procedure execution plans easier, it breaks it down statement by statement. So it’s a bit easier to navigate in there. But, um, you know, uh, there’s also no filtering in there. So you can be a lot of scrolling through stuff looking for things that have high CPU and duration. Uh, there are a number of downsides to the product at this point. Um, uh, they haven’t yet added, uh, the per operator timing that SSMS shows you.

So it’s a little bit more work to, uh, get anything useful out of it. Now, um, I had this query run over here, uh, ran for about 13 seconds. Uh, let’s see if I can see if I can do this zoom it justice here. Uh, which way am I going this way? Oh, a little bit. There we go. You can see the 13 seconds right there.

You can also see exactly what time it is. So that’s a little freaky. But anyway, uh, this window did not capture an actual execution plan. Uh, just pretend this is a big store procedure. All right. Do me, do me a favor and go along with my fantasy where this is a big long store procedure, but this is the only query in it that stinks. If we, uh, come over to, uh, this window, which is where, uh, I have, uh, pulled up the data that my extended event collected. That’s this one here.

I turned it off just so I don’t forget to turn it off later and, you know, bog my own server down. Uh, but it was capturing from, uh, I’m looking at live data from this extended event. Uh, this is what, um, I had, what columns I’ve decided to show, uh, from the actual, from the extended event.

Uh, you get the query, uh, post execution plan, the SQL statement, timestamp, uh, duration, CPU time, uh, granted memory. It’s, it’s a little tough with extended events because not every event captures all of the same query metrics, which is a little, a little disappointing. Like, you know, you capture memory grants with this one, but not with this one.

Uh, also all the times, like you are in like microseconds. So like you have to do a lot of math in your head, you know, KB to GB is not like exactly my strongest suit as far as, you know, um, figuring out, um, uh, you know, like doing that conversion mentally. So anyway, uh, you get the point.

So there are, there are two things here. Uh, there’s the statement that finished, um, if this were coming from a store procedure, you’d have a lot of other information in here. But again, I mostly use this just to monitor a single speed for whatever it’s getting up to and like filtering that date, that data down to, uh, execution times that I care about. Right.

So like, you know, you might start at like, you know, 20 seconds or 10 seconds and then, you know, tune those queries, then move down to like five seconds or two seconds and tune those queries if you can. Uh, so all that. But the cool thing is for the, uh, post execution plan event, uh, you get a little tab over here for the query plan. And if you click on that, you’ll see what the query did, right?

Uh, we had a whole bunch of, uh, crazy stuff going on. So this, uh, just, uh, this just about confirms what SSMS told us. It ran for just a hair under 14 seconds.

And if we look through the query plan, we can see which operators, uh, had the most execution time associated with them. Uh, we have, uh, well, about a second and a half there. Uh, that adds up to a six seconds there for five seconds there.

Uh, we hit 13 seconds at this point and then another second over here. So, uh, that, that, that tells us, you know, which areas you might want to focus on. Maybe there’s some indexes we could add.

Maybe we could write the query a little bit better. Um, you know, things like that. Uh, this isn’t a query tuning video. This is just teaching you how to capture crappy queries. So you can tune them or you can pay me to tune them.

I do do that for a living. I’m told. Occasionally, I guess. So, uh, this is all the, the, the stuff that, uh, you can find from the actual execution plan. Again, newer versions of SQL Server, newer versions of SSMS.

Really handy to have these operator times in there. Uh, there are some weird caveats to the operator times thing. Uh, one of them specifically is, and this is probably a pretty good, um, probably a pretty good place to, uh, explain it a little bit. Um, each, so all of these operators in the, this section of the query plan, except for the nested loops join executed in batch mode.

So all of these three operators here, the timing that you see associated with them are individual. If this were a row mode plan, timing would be cumulative going from right to left. You would see, um, uh, all of, like, you would, like, uh, for a row mode operator, it collects, it, like, accumulates all of the time that its child operators used.

That’s a little more obvious when you get to the nested loops join that, that doesn’t support batch mode. I wish it did. Batch mode nested loops would be amazing.

But, um, this one does not support, uh, batch mode. So this runs in row mode. So this nested loops join does effectively add up all the operator times from the clustered index scan here, the clustered index scan here, and the hash match aggregate with the little spilly doodad here.

So, uh, this is usually how I use SPHumanEvents to, uh, help me with my performance troubleshooting. I find it very useful. Um, I use it quite a bit.

I hope you do, too. Um, there will be links in the video notes to the GitHub repo and all that good stuff. The readme over there has a whole bunch of information about how to execute it, how to use it, all that other stuff. But, you know, if you do use it, you hit any problems, you have any questions, anything like that, feel free to also use the GitHub repo to report those things, ask those questions, and all that good stuff.

Uh, thank you for watching. I’m going to be recording some, uh, other videos about how to use SPHumanEvents. So, uh, hope you, hope you tune into those, hope you watch those, hope you like those, and, um, I will, we’ll see you in that next video.

Like and subscribe and all that, right? Click the little bell for me. Thanks for watching.

Going Further

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

Capturing and Analyzing Blocking with sp_HumanEvents

Video Summary

In this video, I delve into how to log and analyze blocking using a couple of stored procedures that I developed from scratch—sp_human_events and sp_human_events_block_viewer. These tools are designed to make it easier for SQL Server users to set up important human events for different performance issues such as query performance, weight stats, blocking, compiles, and recompiles. The focus is on capturing these events over a specified duration or using the keep alive parameter to continuously monitor potential issues without causing unnecessary overhead. I walk you through setting up the blocked process report and demonstrate how to use sp_human_events_block_viewer to view detailed results from extended events, providing insights into blocking queries, wait times, isolation levels, and more. The full code is available on my GitHub gist, along with necessary scripts, and I encourage viewers to explore and contribute to improve these tools for everyone’s benefit.

Full Transcript

Erik Darling here with Darling Data Enterprise Edition. And today I’m going to show you how I log and analyze blocking using a couple of stored procedures that I wrote all by myself from scratch. The first one is called sp underscore human events. I wrote that one to make it easier for SQL Server users to set up important human events for different sort of performance issues. It does like a query stuff, weight stats, blocking, compiles, and recompiles. Those are the sort of important ones. The most common ones that I end up troubleshooting, so that’s what I geared it towards. I don’t really have any plans to add more because I don’t really end up troubleshooting more than that. I don’t care about your AG failover. personally. I don’t care about AG’s personally. So I’m going to show you how I do that. And all of the code from this section here, which is all you need, will be available at this GitHub gist. I don’t expect you to copy this URL from the screen. I’m going to include it in the video description. And I’m also going to link to the the necessary scripts in my GitHub repo. Of course, if you find any issues, you need support, you hit errors, you have problems, you have questions, that GitHub repo is also the best place to go and ask there. Alright, so let’s get down to it. I have this, these couple commands right here that run sp configure so that we can turn on the blocked process report. The block process report is what drives information being fed into the extended event that we’re talking about. So I would, I already set this up a little bit, so I don’t have to do a bunch of goofy crap on camera. But if I run this, and this sets up the blocking event type, and it also uses a parameter that I like called keep alive. So sp human events can do one of two things. We can either capture stuff from the stuff that I mentioned before, query performance, weight stats, blocking compiles and recompiles.

For a specified duration of time. Or you can use this keep alive parameter to set up a session that just keeps on running. So if you’re not sure when to expect something happening, that can be a good idea. I wouldn’t suggest leaving a lot of these on for a long time. Because extended events, you know, they can, they can cause some observer overhead. I know that Microsoft has taken some steps to alleviate that. But especially query performance ones, I’m capturing actual execution. plans. Because a lot of the times, because a lot of the times, that’s what I need to do. So I wouldn’t suggest leaving those running forever and ever. Could be, could be detrimental. But the blocking one is pretty straightforward and easy. So I already have this set up. If you come over here, and you look at all this stupid stuff I have on my server, and we go into management, and we go into extended events, and hopefully I don’t have anything nefarious, or weird, and these, in my extended event names. But if we come on, oh boy, zoom it just like, got weird on me. Alright, there. So I have one to capture deadlocks. I actually have a duplicative one up here. I guess I could have used that instead. I didn’t look first. But I have this keeper underscore human events underscore blocking session. That’s the default name that SP human events will set up for a blocking session.

that is being kept alive. And I’ve got a couple queries over in these windows. I just did a simple update for one row to modify the age column. And I can get rid of that because I’m done with that. And I had one query over here that was just doing a select star from users looking for that same user ID, so that it would get blocked under the default isolation level for every version of SQL SQL Server, other than Azure SQL DB, I guess. You have to be good. You have to be able to say something good about the cloud in these videos, right? Well, anyway, so SP human events does use the ring buffer as a target. It does not use file targets. I decided to use that because it seemed a little bit easier to manage in code. I know that Jonathan I think the highest, the man who always has great weekends, that is not the biggest fan of the ring buffer, nor am I, but it’s just a design trade off that I decided to make.

So anyway, I let that blocking situation proceed for about five seconds. And then I ran SP human events block viewer. And I got these results. Now, some of this, some of the details that come back are sort of at the mercy of what the block process report captures. For example, we get the event time, we get the database name, and a lot of the times this contentious object will resolve to the table or whatever that the blocking was taking place on. In this case, we got a really weird object ID back. It just doesn’t make a whole lot of sense. But we see in these lines here, who did the blocking, who got blocked, the SPIDs. I know that a lot of this stuff doesn’t exactly help you troubleshoot the blocking problem. I just wanted to get as much of the information out of the block process report as I could.

Like ECID, I don’t think I’ve ever actually used this to troubleshoot a blocking problem, but it’s in there, so I get it out for you. Just in case you might need it at some point. We get the query text of the blocking query in the blockhead query. You can see those there. Those are the queries that I just showed you in the other window. We get the total wait time, the status, the isolation level, the lock mode. Note that the query that does the blocking, I don’t think I’ve ever seen lock mode get populated there.

I get out the SQL handles because I want you to be able, if you need to, to track down the query plans for the queries that were involved in the blocking. So you’ll have both the, these lines are, I mean, they’re a little bit duplicative, but we have the SQL handle for both the blocking. You see the comma there? That’s both SQL handles for the blocked and blocking query.

If there’s a procedure name in there, I do try to resolve that as well. So transaction count, the transaction name. One thing that shows up in here a lot that can be really useful is implicit transactions, where, you know, you’ll like, you know, certain tools like the JDBC driver, the Python driver, use implicit transactions by default. You have to explicitly turn those off and like, not in the connection string, but like in the connection code kind of.

We get when the transaction started, completed. We have all the client options in here. Sometimes client options can lend some insight into, you know, just like, like a, like a weird setting, but these are both coming from SQL Server Management Studio. So there’s not really any weird settings.

We get the wait resource. If there’s a priority set, if there’s log used, stuff like that. We get the client app, host name, login name, transaction ID, which again, isn’t terribly useful, but it’s in there. And then over here, I give you the full block process report.

So we look through here, like this object ID is obviously nonsense. So, you know, whatever. This index ID of 256, I’m not sure.

That’s entirely accurate, but there we have it. And then coming down through some of the other stuff. So SQL Server 2022 added a lot of these stacks in here.

These are probably not terribly useful for query blocking, at least that I’ve ever seen. They’re probably more useful if you have like, you know, like background process blocking where you could really trace some of these call stacks to something a little bit more useful. And then we have the query that got blocked.

We have the SQL handles that I was talking about and the query that was doing the blocking down here. All right. So we got all that stuff.

And that can be really useful for figuring out what was blocking, you know, what your problem, what the problem was, all that other good stuff. And the bottom section kind of gives you a breakdown of all the like all the blocking that has happened in the database that was captured that is currently in the ring buffer extended event. So a couple in here that I think are particularly useful for the scenario that we’re looking at are lines four and five right here.

And these will tell you if you have blocking involving selects. So under the default isolation level read committed, write queries can block modification queries and modification queries can block select queries. And unless you, I mean, let’s litter all your all your select queries with no lockends and whatnot, which, you know, is not a good idea.

Please don’t do it. I would much prefer that you need use a good old fashioned optimistic isolation level. So multi version concurrency control like using read committed snapshot isolation.

And the second one is that there is a sleeping query doing the blocking. If you have queries that are regularly sleeping and still blocking, there is often signals some sort of application error where you are not closing out connections when you should. This will also warn you about implicit transactions.

A lot of other stuff. There’s a ton of checks in there for various like different things that are weird. Like if you have oddball isolation levels like repeatable read or serializable getting involved, you know, it can be a good idea to warn about those because at least I want to say with entity framework, when you use the transaction scope, I don’t know, is it method or whatever? That sets a pretty strict isolation level.

I can’t remember if it’s repeatable read or serializable by default, but it’s one of those. So it’ll give you all that information. It’ll show you all that good stuff.

And this, I don’t know, I use this quite regularly with client work to try to help me, you know, be able to troubleshoot blocking issues and all that. And I don’t know, that’s about it there. You know, it’ll tell you how much blocking there is and, you know, for the entire database and then per object and all that other good stuff.

So, you know, pretty good. All right. Pretty good.

All right. Cool. So I’m going to keep recording some more of these videos. This is going to be pretty short. I’ll probably be sort of repetitive because I don’t know who’s going to watch what when. You might find one video and watch that. You might find, you might watch a whole series of videos.

I don’t know how it’s going to work. I don’t know. I don’t know what you’re going to do with your life or your day or your great weekend. But anyway, I will catch you in the next video. Again, please use this stuff.

I spent a lot of time writing it, not just for me to benefit, but for everyone to benefit. It’s open source hippie stuff. So, yeah. Enjoy. Use it.

Report errors to me on GitHub. Good stuff. All right. Thank you for watching. Goodbye.

Going Further

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

A Little About Memory Grants In SQL Server Query Plans

Video Summary

In this video, I delve into memory grants and SQL Server execution plans, explaining how they work and how operators can share memory within a query plan. I also discuss how SQL Server decides on the size of a memory grant for a specific query, focusing on sorts and the impact of string data types, especially those with large maximum lengths like `VARCHAR(MAX)`. By running through various examples and queries, I highlight how memory grants are calculated based on row count and estimated data size, and how they can vary depending on join algorithms. Additionally, I touch on parallel query execution and how memory grants are adjusted for such scenarios.

Full Transcript

Erik Darling here with Darling Data, the most widely used SQL Server consultancy in four towns. What, what, you’re not impressed by that? I don’t know what else to give you. This video is going to be focusing on memory grants and SQL Server execution plans. And a little bit about how they work, a little bit how operators are able to share memory within a query plan, and how SQL Server decides on the size of a memory grant for a specific, for a given query. Now, this is not going to cover memory grant feedback or any of that other stuff. This is just going to cover how SQL Server, out of the box, decides on a memory grant. So we have query plans turned on because, believe it or not, this is not my first take recording this.

Something stupid happened, and I had to start over again. Namely, my printer, which is apparently possessed, just started making all sorts of weird noises, and I found it distracting, and I found it detracted from the, the normally very high quality of my recordings. So, let’s run this query, which is just selecting the top 1000 IDs from the users table ordered by reputation, an ascending order because we have not specifically asked for descending, and of course, every sane database in the world defaults to an ascending sort when no direction is specified. So, let’s run this query, and let’s look at the execution plan. We have a finely ordered result set. Amazing.

Now, since we don’t have an index that leads with the reputation column at the moment, if you watched the last video, which I hope you did because you like all my videos and you subscribe to my YouTube channel, the thumb and the bell button, we don’t have that index anymore. I got rid of the execution plan. But if you look at the execution plan, we have a query plan. And because we have a sort in the query plan, SQL Server needed to ask for some scratch base in order to write the results that we’re selecting down in the order that we are asking for, the presentation layer order that we are asking for in the query.

That means we got a 182 meg memory grant to sort that ID column by that reputation column. Because that’s all we did. If we look at this arrow going into the sort, the estimated row size is 15 bytes, the estimated data size is 35 bytes. And since we’re selecting a whole bunch of rows, well, since we’re, you know, trying to get the top 1000 rows out, SQL Server does some multiplication and says, all right, 182 megs, that should do it.

Should set the boat straight. It does. We don’t spill. We don’t get a warning that we use too much memory. So apparently, this memory grant was Goldilocks, right in the middle, perfect memory grant for this query. If I run this query, where I join one result set of that select top 1000 to another, the memory grant here isn’t going to double.

And it’s not going to double because SQL Server is going to choose a hash join algorithm to make this query to join those two results together. Because we have the hash join in the query, SQL Server is able to share memory from one sort operator to another. So this portion of the query plan goes, gets all the rows, inserts them into the hash table that SQL Server creates to implement the hash join.

And then when this portion of the query plan starts probing in down here to get the rows that we care about, then this portion of the query plan runs and this sort uses memory that this sort operator gave back. So if we look at the overall memory grant for this one, it only went up by about a meg.

And it only went up by about a meg because that hash, so apart from sorting data, hashes also require memory to do their thing, to create their hash table and do some work. So this only goes up by about a meg.

So we know that those two sorts shared memory because neither one spilled, neither one seems particularly starved for memory. And again, we don’t have any warnings that we asked for too much memory.

Again, this is a pretty good Goldilocks zone grant for this query. If I change the query a little bit and I force a loop join, rather than letting SQL Server’s cost-based optimizer decide on the join algorithm, then the query plan in this one will show a memory grant that just about doubles here.

Actually, I think it exactly doubles here, if I’m remembering correctly. Rather than the hash join, we have the nested loops join. And if we look at the memory grant for this thing, we have a 364 meg memory grant, which is 182 times 2.

So I think anyway, at least from… Ran out of fingers at 5, 10? I don’t know. Thumbs are fingers.

Hot dogs are sandwiches. The world’s a strange place. But because the nested loops join operator doesn’t offer what my dear friend Paul would call any phase separation in the query plan, it’s all sort of streaming, then that memory grant can’t be shared.

And we need twice as much memory for each side of the query to do its sorting. So we’ve learned so far. SQL Server, to calculate a memory grant, looks at the number of rows and the size of those rows in order to figure out how much memory to be granted.

It also has to decide the memory grant on how many memory consumer… How many memory consumerating? Wow!

What a time to be alive. How many memory consuming operators are in the plan? And if memory can be shared across different portions of the query plan based on the operators that get chosen in there.

Now, where things get more interesting, I think, is when we start looking at string data. Now, you’ll notice that I have some numbers listed over here.

This should be 182. Don’t tell anyone. Forgot to update my entire script. But if we look at this query, the memory grant for this query, Now, these numbers are cumulative.

So if I just did web display name or website URL or location, we would get different memory grants. But if we sort of just quote columns in until we get down to about me, which is a max column and varchar max, then this will all lead up to about a 9 meg memory grant.

Some of the other memory grants in compat level and 160 have been inflating a little bit on me. So these numbers might be off by a little bit, but they are going to be large enough for you to get the general idea.

And of course, this one went up by quite a bit. This went up 14 gigabyte memory grant to sort this result set. The reason why we get a 14 gig memory grant here is because of the way that results get sorted by SQL Server.

Now, the easiest way for you to visualize this is to picture an Excel file, right? And that top left-hand corner of the Excel file, you have that magic button that highlights all of the rows and columns in the sheet.

And then you have the little sort button up at the top, and you hit that, and you can choose a column to sort by. And unless you are a deranged person, and you don’t choose to sort your entire spreadsheet by that column, if you just sort one column independently, you’ve ruined your spreadsheet.

If you sort all of the columns by that column, you’re probably in okay shape. But what happens is the entire spreadsheet, all the columns and whatever, and the spreadsheet will flip to match the sort order of the column that you chose.

That’s a lot like what SQL Server has to do when you ask for ordered data without a supporting index. You have to sort, you have to write down in sorted order by whatever column or columns you’re choosing to order by all of the columns that you are selecting.

So you write down reputation in order, but you also need to write down the rest of the attached row in that order as well. So all of these columns, all of the data from these columns is being sorted along with reputation.

That’s why the memory grants for string columns, especially big max data types, can get so wild and out of control is because SQL Server, when it needs to figure out string columns, it guesses that they’re all going to be half full.

So if you have a, let’s to make it easy, let’s just say you have a VARCAR 100 column, SQL Server is going to guess that every single row in that column is 50 bytes full.

So it’s going to guess at the half full mark because some might have less or fewer bytes in them. Some might have more bytes in them. And so it does that to sort of get right in the middle and just sort of give itself some fudge room on either side of that.

So if you have like a VARCAR 1000 column and everything in there is like a state code, like M-A-N-Y-C-T-N-J, other northeastern states, SQL Server is going to guess that they are all 500 bytes full even though you never cross like the two byte mark.

So that’s real depressing. And VARCAR columns, Unicode columns, of course, since they’re double byte stored, it’s just basically going to be whatever, whatever size you choose for the column.

SQL Server is going to guess half of, like half of that. So if you have like a, like in VARCAR 200, it’s going to guess, I guess it’s still like 200, but anyway.

String columns were a mistake. We should just have numeric codes for everything and translate everything into binary and never have to deal with this string. Now, one thing that, important thing that I want to point out is that memory grants are not, for parallel queries, are not the grant times whatever the degree of parallelism for the query.

The memory grant is divided evenly amongst parallel threads. The reason for that, of course, is that every plan starts off as a serial plan. Even if the cost is, like SQL Server has a bunch of like initial costing things to figure out the initial cost of the query.

But the initial plan that you’re going to get every single time is a serial plan. SQL Server only starts exploring parallel plans if you have a cost that breaks your cost threshold for parallelism, which I’m going to have a video about that soon, and related to that soon.

And if there is nothing in your query that would inhibit parallelism in any way, non-inlineable scalar UDFs, inserts into table variables, things like that.

Those are the two most common ones. Different cursor options will do it too. But if we run this query at max.8, we will see that the memory grant for this query does not get multiplied by 8 because that would be insane.

It gets divided by 8. So what you get is a memory grant of about 197 megs. Now, this goes up a little bit from before. It was like 182, 183 before.

Just because we do have some hashing in here. We have some sorting in here. And there’s some parallelism going on. So things do go up a little bit, but they don’t get multiplied by 8.

So what SQL Server does is it takes that initial serial plan, comes up with a memory grant for that. That’s why in the query plan properties, you will see under the memory grant info, you will have the serial desired memory, which is 188.032, which just about would have been what we got for the 183 plan.

Divide by 1024. That should get you pretty close to 183. So SQL Server comes up with the serial plan, a memory grant for the serial plan.

And then if a parallel plan is chosen down the line in the optimization process, the memory grant will get divided evenly amongst the number of threads or the degree of parallelism for the plan.

That is a little bit about how SQL Server comes up with memory grants for plans based on the size of the data that is getting passed into memory consuming operators and other stuff like that.

This video mostly focused on sorts because sorts are the operators that I see responsible for gobbling up the most memory. Bad estimates for the number of rows can over inflate memory grants.

Bad estimates for the size of data. Again, this comes back to like how you designed your schema. If you, you know, again, VARCAR 1000 column for, you know, very small bytes of data, you know, like one, even if everything was VARCAR, even if like you had a VARCAR 1000 and there were only under 100 bytes in there, you’ll be overestimating that by 400 bytes a row.

So the worse your estimates are for the number of rows, the worse your sort of schema design in for how big a data type you have assigned to your string columns, sort of the worse those memory grants can get on the high side.

And of course that sucks because if you’re, you know, if your server isn’t set up from a hardware perspective to, you know, cache like, you know, your, like your, like most important data in memory and also service queries, then those big memory grants are going to steal space away from the buffer pool where SQL Server stores all those precious data pages.

And it’s going to, you know, knock those back out to disk. And, you know, it’s just going to make things worse. You know, weird variable performance issues in there because, you know, sometimes you’ll be going to disk, sometimes not.

And of course you can hit issues with, you know, query, like, like, you know, like just like you can run out of threads and hit thread pool weights, you can run out of available query memory and hit resource semaphore weights.

So we’re going to, we’re going to talk more about that when we talk about how I use SP pressure detector. I’m going to do some videos on that shortly, sometime this week. And then, you know, that’ll be fun.

Yeah, more stuff. But again, kind of calling back to the last video, there are times when you can create indexes that put data in the order you care about so that when you choose your order by columns, then you can have data physically sorted by the, or logically sorted by the index ahead of time so that you don’t need to ask for memory for separate scratch space to write all that stuff down.

But that was the last video. Go watch that one. Anyway, that’s about all I have to say about this. I forget what the next video is going to be about. We’ll figure that out in a minute. But anyway, as usual, hope you enjoyed yourselves.

Hope you learned something. And, you know, do the old like and subscribe. It’s the thumbs up and the bell. So, appreciate that. Anyway, I’m going to go figure out what’s next. 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 How Indexes Store Data In SQL Server

Video Summary

In this video, I delve into how indexes store and sort data in SQL Server, focusing on B-tree rowstore indexes since they are the most commonly used type. I explain that creating indexes helps speed up searches by organizing data in a way that makes locating specific records more efficient. The video walks through visualizing index storage with query results to illustrate how data is sorted within an index and why this matters for query performance. I also touch on inequality predicates, which can cause sorting issues when crossing boundaries between index values, impacting the need for sorts in query plans.

Additionally, I discuss the implications of sorts on memory grants, noting that they can lead to large memory requests or spills to disk, potentially affecting parameter sniffing and overall query execution efficiency. The video aims to provide insights into why proper indexing is crucial for optimizing SQL Server performance and sets up the next segment by previewing upcoming discussions on memory grants and their impact on query plans.

Full Transcript

Erik Darling here with Darling Data, the most sought after SQL Server consultancy on the planet and known universe. And I’m re-recording this video because it was pointed out to me by the ever vigilant Randolph West that my audio sync was off quite a bit in the last one. I’m not sure why. I recorded the isolation level video right before this one. Everything was fine. Walked away for a minute, came back, let the other video upload and start processing on YouTube. And this one was a disaster area. So we’re going to do this over again because, you know, I guess if I’m going to be pitching these videos as a reason why you should either, you know, buy my training or, you know, purchase consulting from me, I should not look like a badly dubbed Kung Fu movie when I do it. So, um, that’s fun. Anyway, uh, this video is going to talk about how indexes store and sort data because of course the reason we create indexes is that they put data in an order that we care about. And of course, uh, when we create indexes and we put data in order, it is to make searches of that data faster.

And of course the fastest data to search is data that is sorted in a way that makes locating the data that we care about as efficient as possible. So, uh, let’s make sure that query plans are turned on because I have a helpful note to do that right here because sometimes I am a bonehead and I forget to do that. There’s nothing worse than running a query that takes like 30 seconds and then finding out that, uh, you didn’t turn on query plans.

I think maybe the only thing worse than that is recording a video for 15 minutes and then realizing that you looked like a badly dubbed Kung Fu movie. So we’re gonna, we’re gonna try that again. Um, and of course we care about, uh, sorted data because when we have sort operators in our query plans, bad things can happen. Uh, we can have rather large memory grants and we’re gonna talk about memory grants in the next video.

So I’m not gonna spend too much time explainifying them here. I’m just going to say that sometimes sorts ask for big memory grants because those are, uh, size of data operations. Uh, if we don’t get enough memory, we might spill to disk.

If we ask for way too much memory, we might, uh, you know, knock a bunch of data out of the buffer pool. We might clear out other memory consuming caches and memory managers in order to loan memory out to query so that they can execute. Um, memory grants and sort of things like that can, uh, make queries more sensitive to parameter sniffing.

Um, even with the memory grant feedback, uh, intelligent query processing, uh, feature in newer versions of SQL Server, we can still run into situations where, uh, it does not quite work out once you start sort of mixing, um, different performance issues with those features. It’s just, you know, parameter sniffing is sort of a classic one. I know we have the parameter sensitive plan optimization, but, um, you know, most folks not on SQL Server 2022, aren’t gonna feel a lot of, uh, uh, pain relief from that one.

And, um, even like when you have a normal parameter sniffing situation, uh, the memory grant feedback thing, um, kind of kicks back and forth a lot. Even if it settles on like a middle ground value, that middle ground value can still not be all that great. Um, you still end up with a fair amount of spilling and a fair amount of overestimating memory grants.

And, uh, you sometimes have to override that when you can with the, uh, the, the max grant percent query hint. Uh, so let’s look at the index that I have created here. Uh, it is on the users table and the columns that make up the key of the index are reputation, upvotes, downvotes, and creation date, which I just stuck in descending order for a little bit of flavor.

It’s not gonna make a whole lot of difference to our query. Uh, down in the included columns, we only have display name. Um, and, uh, the thing about included columns is that they are just window dressing for your query.

They are only stored in the data pages. Uh, they are not sorted the way that the key of the index is. And since they’re not in the key of the index, they don’t offer the storage engine, any sort of, um, uh, like optimized way of locating exactly where things are.

So if we had a query that was like where reputation equals one and display name is like capital A percent, like, like we couldn’t like seek to any values for display name. Cause it’s down to the includes the order of included columns doesn’t matter for the index definition. Key column order matters a whole lot.

Included column order does not matter one single lick. So the first thing that I want to do is kind of talk you through how, uh, you can visualize the way that indexes store data. Uh, that makes the, uh, makes finding data in them more efficient.

So I’m going to run this query and notice that I have an order by reputation upvotes downvotes and creation date descending. I have a bunch of columns in the where clause. That’s sort of less important, but what I want to do is run this.

And show you the query plan and show you that there is no sort in the query plan because we have an index that exactly matches the ordering. The present presentation level ordering of our data. Uh, so we don’t have to sort that, but what I want to show you in the results to sort of help you visualize the way that indexes store data.

When you have a multi key column, nonclustered index is by showing you the results over here. So for reputation, we only have the value one 24. Uh, of course, the reputation column is going to be ordered from low from an ascending order from a 124.

From lowest value to highest value. But just for the chunk of this index where reputation equals one 24, we can sort of get a sense for how other, other data in the index is sorted. Now reputation, I’m going to call it the primary sort of the index.

I don’t want you to get that confused with the primary key. I just want you to know that because it’s the leading key column of the index, every, it is the primary sorting of the index is by this column. All the other columns are sorted within a duplicate value chunk by it, like within that.

Um, we’re going to talk about what happens when you cross boundaries a little bit later when we talk about inequality predicates. But with it within reputation one 24, up votes are all sorted in ascending order from one down to 10. Down votes are all sorted within any duplicate values in up votes.

So we’re, we’re up votes is one down votes is one one two. And that pattern kind of carries on except for poor up vote number four that only has a one and a two. But for six, we have one two three for seven, we have two four.

So it’s ascending order within every range of duplicate values. Um, for unique, you know, if this was a unique index, you know, uh, where we had like, you know, if this is like an identity column or something, there wouldn’t be a lot of room for that.

So, um, it would look a little bit, it would look different, of course. But if, you know, if we had a unique leading key column, we wouldn’t have this nice visualization here. And then within, uh, any, uh, duplicate values for down votes, uh, creation date will be sorted in descending order for those.

All right. So like, that’s how you can visualize this index data being stored, right? Whether, wherever there’s duplicate values, uh, values for the column in the key of the index after it will be stored in ascending order within that duplicate range.

And that’ll go for any other key columns that come along. So I’m going to run these two queries back to back. And we’re going to look at the query plans in a second.

But what I want to just, you know, bring up is to point out kind of the obvious what’s on your screen. Uh, this, this query, uh, is just the top 1000 ordered by upvotes. And since upvotes isn’t the leading key column, we don’t have that.

We don’t have that column nicely ordered from a, an ascending order from smallest to largest. We have reputation ordered that way, but not upvotes. For the second query, we’re only looking for where reputation equals one.

So we’re only going to return the top 1000 rows for reputation equals one. Now that the query plans look different. And, uh, well, I guess at least half an important way.

The top query, uh, goes parallel and has a top end sort in it to put data in the order that we care about. So if we look at the details of that top end sort, uh, we’re going to see an output list and we are going to see an order by upvotes in ascending order. Right.

Cause we had to, we don’t have upvotes perfectly in, we don’t have upvotes is like the primary sorting of this index, in this index or any other index. So we need to sort that we, the SQL Server needs to get all those rows and ask for memory is scratch space, memory grant, and write down all of the columns that we’re selecting in order of the column that we’re asking to be ordered by. The second query plan where we’re only looking for reputation equals one, we don’t have any sort at all.

There’s no sort operator in this one because we have that equality predicate on reputation. We have upvotes in perfect ascending order for just that chunk of reputation equals one. Remember when we looked at that first query where we had reputation equals one 24 and upvotes was in like, like one through 10 after that.

It’s the same deal here. So we don’t have to physically sort data because we have data in, in index order for reputation equals one, right? So we use that leading key column.

We have an equality predicate. We only search that chunk of data and upvotes is in perfect order within that chunk of data. We can expand that across the entire key of the index and we can order by creation date descending. And we will still not need to sort in our query plan.

Our query plan is sort free, right? We have a seek, we have a top. And because the equality predicates preserve index order across the key of the index, we don’t need to sort data for that. Included columns are useless for that.

If we look at this query with an order by on display name. Remember display name was in the include section, include region of the index. If we look at the, we get one row back, one single solitary row, but our execution plan decides to sort that one row.

So even though we seek to all the values we want, that gets us down to a single row. SQL Server is like, ah, well, let’s make sure that row is nicely sorted for them. They asked so nicely for that order by, I guess, I guess it has to follow the rules.

Otherwise we’d have mungodb. So something that I brought up a little bit earlier, inequality predicates do not work in the same way. So if we look for where reputation is less than or equal to one or greater than or equal to 1 million.

Again, we’re only going to get one row back for this, but SQL Server is still going to choose to sort data. Well, SQL Server is still going to have to sort data for these two queries. So if this thing will cooperate and we can get these things nice and close to each other.

So when I zoom in, they’re not 10 million miles apart. It looks like a moon mission. So this top query, we have to sort data. SQL Server chooses a parallel execution plan to do it.

And for the second query, even though we get a single row back, we still need to sort that single row because we have a top and order by. So crossing boundaries presents an issue for these queries because every time you cross a boundary, so like let’s say we went from reputation 124 to 125, the upvotes column would reset the ordering.

So let’s look at how that we can visualize that. So this query is almost the same as the first query, except we’re doing in 124, 125. If we look at the results, the important thing that I want to get to is where this boundary changes.

Because now we’ve crossed from 124, we’ve hit upvotes equals 10. Now we get to 125 and the sorting for upvotes resets. So the same thing is going to happen here where we’re not going to have downvotes like perfectly in order, even after the upvotes, right?

So like that this boundary resets. If we did where reputation in 124, 125 and upvotes equals zero or one or something, then we would have a different sort of set of data there.

But you can see all the sorting resets every time you cross a boundary like that. Now that matters less as long as we are sorting by the leading key column. If we search on other columns, like on other key columns in the index, upvotes is the second key column in the index.

So we can cross boundaries in upvotes or downvotes. And as long as we’re ordering by the leading key column in the index, then sorting this data is free, right?

Looking at these two, we have two nearly identical query plans. They both scan the index because again, we’re not searching on reputation. We’re only ordering by reputation.

But so the order by is free, but the search does result in a scan because we’re not also searching on reputation. All right. So that is how a little bit about how indexes was Btree rowstore indexes in SQL Server store data.

columnstore indexes are of course a little bit different. By that, I mean a lot different. And they are not a topic in this video because they are so different.

And most of y’all are just using the Btree rowstore indexes anyway. So that’s why I chose to talk about it. Anyway, again, we care about sorts.

Sorts ask for memory grants. We’re going to talk about those next. You know, sorts can spill the disk. Sorts can also ask for way more memory. Steal a bunch of pages from the buffer pool. Clear out your plan cache.

All sorts of nasty stuff. And they just kind of generally make code a little bit more sensitive to parameter sniffing issues on account of the memory grant thing. And in parameter sniffing scenarios, the memory grant feedback loop gets thrown off a little bit. So that is all I have to say about that at the moment.

Next video will be more about memory grants. We can talk about those. And yeah, I don’t know. Thanks for watching.

Please, pretty please, like and subscribe so that you can keep seeing these. And if you like and subscribe in the next five minutes, I’m timing you, I will send you an Adidas t-shirt. Leave me your address in the YouTube comments.

All right. Good enough. Thanks for watching.

Going Further

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

A Little About Why I Don’t Like The Read Committed Isolation Level

Video Summary

In this video, I delve into the reasons why I believe that the read committed isolation level is not ideal for most SQL Server databases and advocate for using an optimistic isolation level instead. Starting with a simple example in my Stack Overflow database, I demonstrate how read committed can lead to inconsistent results due to its lack of a consistent view of data. By switching to read committed snapshot isolation, we achieve consistent query results every time. Additionally, I explore the blocking and deadlock issues that can arise when using read committed, especially with concurrent read and write queries. Through an example stored procedure, I illustrate how these queries can block each other, leading to deadlocks. The video concludes by recommending read committed snapshot isolation as a better default setting for all databases, offering practical advice on how to implement it effectively.

Full Transcript

Erik Darling here with Darling Data, fresh off the heels of a great weekend. And I’m going to duck down a little bit so that you can read the full screen here. It says, why I think that read committed… I messed up my green screen a little bit. That’s how fragile these things are. Why I think read committed is not a very good isolation level, and why I think you should be using an optimistic isolation level instead. So, we’re going to start off with a pretty simple case, where you can get strangely incorrect results. Now, the problem that the main three problems that I have with read committed, the default isolation level for all the sorry souls, except the sorry souls who have to use Azure SQL DB. The main problem with it is that it doesn’t give you a consistent view of the data. So, if you have a scan that’s moving along a table, and some modification comes along and updates something over here, that row can move to this side of the scan and the scan will completely miss it.

Likewise, you can have something that the scan reads here, and then after the scan reads it and releases its locks because reads don’t hold on to locks for anything. That’s why you can’t have lock escalation with the read. So, if you have a scan read a row here, and then an update says, oh, I need to change something with this row, and that row moves over here, you can read that row twice. So, there’s not good side effects from not having a consistent view of the data. This isn’t to say that you should haul off and use repeatable read or serializable, but it is to say that there are better options out there in the world, read committed snapshot isolation and snapshot isolation being the two of them.

I do have a slight preference for snapshot isolation, but as far as ease of use and getting queries to be agreeable, read committed snapshot isolation is the easier choice there. So, we’re going to create, oh, actually, you know what, we’re going to go back and we’re going to use the right database first. I just realized we’re in the wrong one for me to show you what happens when things get weird.

Now I have these two tables sitting around in my stack overflow database, look very unprofessional. So, I have this query set up to use this read committed lock hint because I already have read committed snapshot turned on for the crap database. That is already done, that is already in there.

And if we run this query and we look at the results, we’re going to see 110 for all of them. That’s because there is no other concurrent activity going on in the database. And if I take this tiny little query and I put this in a different window, this is just going to update things in a loop forever and ever so I can keep running this query and showing you how goofy things are.

So, I’m going to kick this off and we’re going to come back over here and we’re going to look at what happens when I start running this. So, just on the first execution there, you can see that not all these rows have the same value in them. All right, these all changed.

All right, that one’s 10, this one’s 30, 50, 70, 90, 410, 430. So, and if we keep running this, we’ll see that consistent inconsistency every time this executes. All right.

And if I just quote out this read committed lock hint and I allow my query to use the read committed snapshot. Oops. I allow the query to use the read committed snapshot isolation level.

We will get consistent results on every execution. All right. So, every time you run this, the numbers are the same down the line. We don’t get inconsistent results from our select query.

So, we don’t get weird results moving up and down, which is good. What we want is consistency in our data. All right.

So, that’s the first reason. The second and third reasons are things that I have talked about a few times before. Let me just fix that up a little bit.

Sorry about that, folks out there. Like I said, it’s an early morning for me and my third tiny cup of espresso is still brimming. So, please, please do forgive me. It’s a little bit like being jet lagged.

Except it’s just the entire weekend catching up with you at once. So, that’s that one. The other problems that I have with the read committed isolation level are because of two things that I run into when helping clients with deadlocks and blocking problems quite a bit. And that is when you have read queries, read queries and write queries executing concurrently in the same database where you’re using read committed as the default isolation level.

They can block and deadlock with each other. This is something that not a lot of people realize. This is also something that not a lot of people…

Well, I’m not going to say it. Many people have avoided these scenarios by slathering no-lock hints all over their queries. Even their modification queries.

Just update with no-lock. Okay. That’s a joke. But when you use no-lock, you don’t run into the same necessarily locking problems that you do when you use the default read committed isolation level. This isn’t an argument in favor of no-lock.

This is me making fun of people who use no-lock. Ha ha ha ha. You fools. And this is me saying that you’re better off using an optimistic isolation level where you won’t run into this sort of thing. So the first thing I’m going to show you is this.

Nice. So we have a store procedure here. And we are going to intentionally parameter sniff our store procedure. The first execution we’re going to do of it is going to be for a very small date range.

And let me turn on query plans. See, this one finishes quickly. Right here.

Look how fast this one finishes. All right. Some seven milliseconds or something. Pretty good. Query tuned to perfection. Except now we are going to get parameter sniffed to perfection.

And I just want to point out really quickly that this is SQL Server 2022 running in the 160 compatibility level. But the parameter sensitive plan optimization doesn’t kick in because our only predicate that involves a parameter uses an inequality predicate for the search. So the parameter sensitive plan optimization is only available for a quality predicate.

So we don’t get any help there. So one thing that I want to do is grab this and stick that in this window. And we have to go back over to Stack Overflow.

And I’m going to kick off a very slow version of this store procedure. So doing this for, I think, like a day, let’s call it. This is like the final day of data that exists in the Stack Overflow 2013 database.

If we do it for this date, we don’t find a lot of rows. If we do it for this date, I mean, technically we only need to go back to like 2007 or something. But this is just funny to me.

And it’s also a nice piece of SQL trivia. If you ever want to know why the low value for some date types in SQL Server is 1753.0101, you can search for the name Philip Stanhope. And you can find out way more than you ever wanted to know about calendars.

So I’m going to kick this off. And it’s going to run terribly slowly because it’s going to find a lot more data. And I’m going to run that update query. And we’re going to go look at SP who is active.

And we’re going to see that our select query is blocking our modification query. Here’s our select running happily. And here’s our update sitting around waiting to get these intent exclusive locks so it can do its updating.

This happens because of a sort of funny thing with the query plan. And this thing is done now, so I can show you that. And if you look at the execution plan, you’re going to see that this thing did run for 15 seconds.

And the reason why this blocked the modification query is this portion of the plan. We have an index seek. We have a nested loops join.

And then we have a lookup back to our clustered index. I recorded a video pretty recently about key lookups. So if you’re really unfamiliar with what those are, you’ll want to go watch that video. I promise it’s not hard to find.

It’s the one where I look really, really cute in an Adidas t-shirt. Anyway, let’s get back to business. So what happens is our modification query wants to update the votes table.

And it needs to update, at minimum, the clustered index so that we can change data there and then, you know, push those changes out to any other nonclustered indexes on the table that have the column in it that is being updated. So we have an index seek where we find the rows that we care about here. Every time we find a row that we care about here, we pass it to this nested loops join.

And that nested loops join goes and looks in the clustered index for those rows. Now, remember when I talked about the read committed isolation level not providing a consistent snapshot of the data? So you could miss or see duplicates of rows, right?

So what happens here is SQL Server does some locking magic in order to prevent those inconsistent phenomena or phenomenon, depending on how you speak English. I speak it incorrectly, so I’m actually not sure which one to use there. And what it does is it uses an object level shared lock to lock down the clustered index so that no data can change in that clustered index while it’s trying to locate rows that it’s finding from the index seek.

All right, so every row that comes out, we go look. And the entire time that’s happening, the clustered index has an object level shared lock on it, which is preventing those intent exclusive locks from the update from coming in. Cool.

We got that. So that’s how read queries can block write queries in the read committed isolation level. Another thing that can happen is we can run into a situation for absolutely similar reasons where read queries and write queries can deadlock with each other for sort of similar reasons. Now, I have a select query that runs in a loop and just looks for anything with the vote type ID of 8 in the votes table.

And what I’m going to do as soon as that index is done is, well, I can do this before the index is done. I’m going to stick this into this window here. This window is getting a lot of reuse, right?

Doing a good job recycling things here. So we have this update query that is going to just flip the vote type ID column back and forth for a single row in the votes table. Just one row.

All it’s doing is changing vote type ID from 8 to whatever. And because our select query has a semi-covering index, it will have a key lookup in that plan and we’ll end up with the same object level shared lock issue that we saw before. Now, sometimes we end up with a little bit of getting lucky with the timing.

So I got to kick that off and kick that off. Oops, I didn’t select the whole thing. And I probably have to go back over here and rerun this now.

There we go. All right. So while this update is executing, we ran the select query and the select query ended up in a deadlocked state with the other query. All right.

So that update query and this select query have deadlocked with each other and we have, well, our select query has been murdered, essentially. That happens again because there’s a key lookup in the select query, the object level shared lock, and the locks that the modification query are trying to take are fighting with each other. And then, like, we have the nonclustered index and it needs to update that and read from that.

And this one’s trying to select this and lock this. And just because of the way that those things sort of do their little murder dance, this select query ends up being the victim of a deadlock. Usually, SQL Server will try to choose who should be the deadlock victim by looking at which transaction would be the easiest to roll back.

And typically, select queries are the easiest to roll back because there’s no transaction logging to deal with. So this is obviously not ideal. Again, you know, a certain level of no lock hinting would prevent these issues from happening.

And also, sort of interestingly, there are other things that would fix it. You know, we could use an optimistic isolation level, again, read committed. If we had a covering index so that we didn’t have to do those lookups, then we could avoid that situation as well.

The slight danger here is that sometimes, you know, these select queries are doing, you know, select star or the equivalent of select star. It would be really onerous to create very wide indexes to solve all those scenarios when there are typically better options that fix it without you having to, you know, adjust many indexes, add lots of columns to them as include, stuff like that. There’s also some other things that we could do.

If we wrote the query and, you know, sort of like wrote our own sort of lookup and we used a, like we did a self join or used a hash or merge join hint, then we would be able to avoid that scenario because it’s really the lookup in there that causes the problem. Anyway, that’s a little bit about the read committed isolation level, why I sort of don’t like it very much, why I think read committed snapshot isolation is a better isolation level. It should be the default, it should be your default for all your databases.

If it’s not, just flip the switch, it’s the worst that happens. Everyone loves you, just like they love me. Anyway, if you turn on read committed snapshot isolation today, I will send you an Adidas t-shirt.

Promise. It’s only good for May the 1st of 2023. So I don’t want to get an email in like a year saying I did it and I want my Adidas t-shirt because the offer has an expiration date.

Anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. Remember, like and subscribe and all that stuff.

I do appreciate likes and subscribes. Views are also nice, but, you know, I want to make sure you keep viewing. So there’s that.

Anyway, I’m going to can this one, upload it, and start talking about something else because I have agreed to go roller skating today. And this might be the final piece of work that I do for SQL Server because I don’t have a lot of confidence in me on wheels. That’s why I don’t have a driver’s license either.

Anyway, time to go. Goodbye.

Going Further

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

A Little About Trivial Plans In SQL Server

Video Summary

In this video, I dive into the fascinating world of trivial execution plans in SQL Server and how they can sometimes lead to unexpected query behaviors. I explore why certain queries might be considered “trivial” by the optimizer and what that means for performance tuning. By walking through specific examples, I demonstrate how even simple changes in query structure or indexing can significantly impact whether a plan is marked as trivial or fully optimized, revealing the intricate decision-making process behind SQL Server’s cost-based optimization.

Full Transcript

Erik Darling here with Darling Data. Here bright and early on what I presume is a Friday morning, but could be temporally inaccurate depending on how much you actually think time is real. Might not be. Might not be. I’ve heard various thoughts on the subject recently that really made me question the whole of existence. Alright, so I’m going to switch things up. a little bit. I’ve been recording like 10 videos recently about stored procedures that I write, how to use them, how to get information from them, stuff like that. And I wanted to do a few more videos just purely about SQL Server interestingness. Things that, I mean, I’ve generally have found interesting over the course of my long and storied career with SQL Server. And things that I like sharing with people. This is a material that I do during, you know, conferences and stuff like that. But, oh, you know, just trying to democratize things a little bit, make sure, make sure everyone can see it. You know, it shouldn’t just be, no, it shouldn’t just be limited to people who can take a week off work and spend five grand showing up somewhere. I don’t know. I guess I’m like Jesus in that way. Leave any further jokes about that alone. Alright, so let’s actually read the script.

Let’s turn reading comprehension up to 11. Read the script and turn on query plans here. Oh, this video is going to be about trivial execution plans. Trivial execution plans are a thing that have been not solely, but certainly partially responsible for a lot of strange things that I’ve seen over the course of my query tuning life. And not only query plans, but sort of by extension, simple parameterization, which can occur when a plan is marked as trivial. So first I’m going to show you exactly where you can locate information about a plan being trivialized by the optimizer. This is sort of like the optimizer just saying, yes, dear. I don’t, I don’t, I don’t really have time. This is, you know, agreeing to agree. Yes, I’ve got your query. It’s very important. Super important. I just, yeah, we’re going to take care of that.

So if we look at the properties of this right here, floating above my head is the important part. We’re going to try and move this thing around a little bit so that we can get to where it should be. Look at that. Oh, beautiful. All right. It’s going to put that right next to me. There we go. So this plan’s optimization level was trivial, not full, just trivial. Just, we don’t really have any choices to make. SQL Server is driven by a cost-based optimizer. And if there are no cost-based choices to make, like which index to choose, which operators would be best to use in the query plan, right?

There’s a whole bunch of different operators that are possible of, they’re capable of doing the same job. And sometimes SQL Server has to think about how much each one would cost and say, oh, well, I choose any, me, me, me, you’re the cheapest. There are no, there are no choices like that for this execution plan. This, it’s the user’s table. All it has is the clustered primary key on there. And because that is the only source of data and because we are only asking for 100 rows unordered, all SQL Server has to do is say, okay, give me those first 100 rows.

You can do some pretty funny looking things that are still considered to be trivial. Now, if we look at this query, one might think that generating a row number would make SQL Server have to think long and hard about the best way to generate that row number. But it doesn’t because in this case, at least, and let’s just make this super clear here, do some formatting and live formatting. Make sure our query is nice and pretty for the camera.

In this case, we are partitioning by and ordering by, in all cases, the ID column. And the ID column is the clustered primary key of the user’s table. So, it is unique.

So, partitioning by this will not really do a whole lot. Ordering by it is free because it is the leading key column of an index. Right?

So, like, just without a where clause or anything else to pollute this thing, we have everything that we want in order in both of these cases. So, if we run this query, and again, we come back to the execution plan, even though it looks like SQL Server, well, I mean, it doesn’t just look like SQL Server, even though there are a couple more operators between here, there was no cost-based choice in generating these operators.

This is how row numbers and many other windowing functions are produced in row mode. In batch mode, you might see a window aggregate. Sometimes you might even see, well, not in batch, but in general, you may also see a window spool for some windowing, windowed, windowing, window, whatever, some windowing functions.

And so, these two operators are responsible for generating that row number, but since we have an index that completely supports the partitioning and ordering of our windowing function, SQL Server has no cost-based choice to make when it comes down to this.

Likewise, since we have a clustered primary key in the table, and that’s going to be part of the select star here, you can actually see it right there up, oh, it disappeared on me, you bum.

There we go. And I don’t know why, but sometimes zoom in is a little bit laggy when I’m recording. It’s kind of strange, but whatever. Can’t have everything, so they say.

But since the ID column is our primary key, if we had another column that was marked in unique some way, like a unique nonclustered index, constraint, something like that, we might see something similar happen.

But if we select the distinct top 1,000 everything from the users table, because we have that ID column in there, SQL Server has a pretty good guarantee that absolutely everything will be unique because of that index, and we don’t have to do any work to try and produce a unique result set.

And so if we do a little flip here, and this is something that annoys me quite a bit about SSMS, is that this context is just not this context, and I can’t click on this context to get this context.

I have to click over here to get this context, and then I have to click back to get this context. But in case my beautiful hair, which is long nearly to the point of a pompadour at this point, I’m getting a haircut in a couple of days, don’t worry.

If we look over here, and let’s put that right next to me again, because I want to be the star of the show. Trivial Eric, they call me. In the pursuit of all things trivial.

So this is, oh, wrong hand. So this is, again, a trivial plan. We didn’t have to do any work to find a plan for that. Where this changes is when SQL Server has choices to make.

So I’m going to run all three of these queries in a row. And while those run, which, I mean, they’re already done, but I just want to explain a little bit up front.

And the reason that I’m forcing compat level 140 here and here is to avoid the automatic batch mode on rowstore feature from kicking in. I have that in developer edition.

So lucky me. And the reason why I’m using this hint down here is because I want to show you what a stream aggregate plan would look like for this query. Because these are the three choices that SQL Server gets to make when it runs this query.

After this, I’ll show you what happens with batch mode on rowstore. So if we look at these execution plans, the first execution plan chooses a hash match flow distinct.

Why did it choose that? Good question. Because the top that we chose completely met SQL Server’s cardinality estimate for how many unique rows we might get out of here.

This next one, or we were asking for a number of rows less than or equal to the number of rows that SQL Server thinks will be distinct in the table is the more correct way of putting it. Every time I say something, I picture people tutting at me for saying it incorrectly.

So I do try to correct myself when I catch myself. Since this query is asking for more than the cardinality estimate of the grouped or the distinct tuples in this index, SQL Server chooses a full-on hash match here.

See the little difference with the flow distinct and the aggregate here. Now, good job us. If we look down at the bottom query where I told SQL Server, I really, really want you to use a stream aggregate, this order group hint will do that.

SQL Server had to sort all that data because stream aggregates expect data to show up in order. So we had to sort the whole table, do a stream aggregate, do more parallelism, do some more stream aggregating, and then produce those rows.

So we had sort of a double aggregate on that one. It’s a bit strange, but we got the result back. But this was a cost.

So like this is the stuff that the optimizer thought about while it was coming up with execution plans. If we just compare the cost of the top 5,000 plan right above it without the flow distinct choice being in there, we can see the estimated subtree cost for this query is 47.5-ish query bucks.

And we can see the estimated subtree cost for this query was 94.7 some odd query bucks. Not that I care so much about how much a query costs.

This is just how SQL Server chose the plan it was going to use. If you’re the kind of person who cares about query costs, you should really stop.

They are not good things to measure a query by. So if we had an index on reputation that put reputation in order, then all of this stream aggregating would have been a whole lot cheaper to the optimizer because we wouldn’t have had to sort the data.

It would have been sorted for us in the index. Now I know I promised you, I’d show you what happens and why I have this hint in here. So just to remind you, if I run this query, we get the hash match flow distinct operator up there.

But if I run that query without the hint, then I get a different query plan. I get a hash match aggregate right there, right?

That’s the full hash match aggregate. The real reason that I get that is because SQL Server has chosen to execute this operator using batch mode. So if we highlight that, this happens in batch mode.

Batch mode on rowstore kicks in, thinks that this query is going to be, is heuristically expensive enough to require batch mode to be quick. And so it kicks in and we get it. And does it make a huge difference?

No, not for that, not for that query. Makes a big difference in some other queries. This one, not so much. So, there are some perhaps obvious things that will get you full optimization.

In this case, we’re going to try, we’re going to, if we order by a column that has nothing to do with any index currently on the user’s table, if we ask SQL Server to give us the top one row ordered by account ID, this will be a fully optimized plan.

The reason that we can tell it’s a fully optimized plan without even digging into the details of it is that it’s parallel. If a query’s cost to the optimizer is greater than the cost threshold for parallelism setting, the plan will not be trivial.

There are a whole bunch of heuristics and other things that kick in. You know, SQL Server looking at like the logical trees and stuff for a query to figure out like an initial cost so it can figure out some other stuff.

It’s a little bit beyond the scope of what I can cover in this video, but it’s, it’s definitely, there’s definitely a lot going on in there. A lot of math. I don’t know math. There are also some not very obvious things that might get a full optimization.

So let’s just run these two queries. They’re both logically identical. The only difference that we have is this little set of parentheses around a select for the ID column here.

But this little nudge is apparently just enough to make SQL Server think real long and hard about how it wants to run this query. So if we look at the details of this query plan, this optimization level is trivial there.

And if we look at the details of this query plan, this optimization level, which I believe is the first time we’ve seen, oh, actually no, because it was in, uh, it was in those distinct queries.

This optimization level will be full. So SQL Server had a, um, a cost-based decision to make apparently, like maybe where to put the compute scale are.

I don’t know. Um, couldn’t tell you on that one. So let’s try an even simpler subquery here. Let’s just, uh, let’s run these two queries.

We’re just getting a count. And then this count where one equals select one, one will always equal select one, but SQL Server is kind of, I don’t know, uh, kind of picky about these things.

And if it’ll let me grab it, no, that just moved that query plan. There we go. All right. So if we look at these two query plans, they are absolute, well, they’re not absolutely identical.

Um, this is another one of those funny things with batch mode on rowstore. Uh, and, uh, and prior presentations of this, where I wasn’t using, uh, the, the newer compat levels, uh, these plans would be exactly the same.

If I, if I set this back to, uh, the, what do you call it there? The one 40 compat level, we would not get batch mode here, but, um, this is, uh, perhaps, a cost-based decision that is of some, uh, interest to you because, uh, one thing that cost-based optimization will do is figure out if batch mode on rowstore would be a good choice for your query.

Uh, in this query, we do not get that, uh, this query, if we look at the details over here, you can probably see it right over my head.

It is trivial. Uh, and this query down here, if we look, if I duck down a little bit, it has full optimization. So, cost-based optimization hides a lot of stuff, right?

Like, a lot of stuff is just, like, on the other side of that door. If you get a trivial plan, you don’t go through that door, you just, you know, get whatever gets handed out that door. Uh, there’s a lot of cool stuff behind it. It’s like the, the backstage area, right?

All the good, that’s where all the good stuff happens. So, the brown M&Ms and pharmaceutical grade cocaine are. So, one other thing, and this is, this is going to lead into a minor tangent, uh, is if we run these two queries, and we look at the query plans for them, whenever my, RDP decides to become responsive, maybe my RDP needs some pharmaceutical grade cocaine.

Uh, so if we look at these two queries, this one up here, scans the clustered index, this one down here, scans the clustered index, but only the query that will get full optimization decides that an index might be useful.

Now, I bring this up, uh, because I want to show you other things that are on the other side of, in the backstage of SQL servers, I’m trying to, the full optimization party, uh, and SQL servers optimizer, but also because, uh, I, I, I need y’all to chill with adding every missing index request that you see, or that you might find via scripting, because a lot of the times, uh, they are not quite, uh, go, they’re not quite going to do as much, uh, good for your query as they, you might think they’re going to do.

You might see this show up in front of you someday. You might say, wow, this thing has a cost of 36 query bucks. That sure sounds pricey. Uh, and you might look at this and say, wow, I can make this query cost 99.2491% less if I had this index.

The thing is, uh, do we really need to improve a 288 millisecond query? Is that really important to you?

Is that worth it? I don’t think so. Don’t believe so. But anyway, back to, uh, why, and this is something that I, I, I, I foreshadowed ominously earlier in the video, back to sort of why this query, uh, does not have a missing index request.

Is, part of, part of it is the, uh, simple parametization. Part of it is the trivial plan. And part of it is, uh, that SQL Server just didn’t care all that much about our query.

It did not, did not break, not, not, did not get full optimization, not break any boundaries.

So when trivial plans, uh, can be a real letdown, is when we have a constraint like this, where we are telling SQL Server, exactly the upper and lower boundaries of the reputation column.

And, uh, this constraint is trusted, right? If we look at, if we look at the, this, this data, right?

This is our, this is our index. This is our constraint rather. And we can see that this can, this constraint is trusted because there’s a zero for is not trusted. If it were one, then it wouldn’t be trusted.

But zero for is not trusted means it is trusted. All right. Cool. So if we run this query and we look at what happens, and, uh, I got ahead of myself a little bit when I was talking about the last thing, because it’s really more pertinent to when I talk about this thing, uh, is the reason why, uh, this query, despite knowing the upper and lower boundaries of, uh, the, the reputation column, and despite us looking for a value in the reputation column that, uh, falls within, falls, uh, out of those boundaries and would not possibly exist in the table.

Because SQL Server gives this thing a trivial plan. And because SQL Server chooses to, uh, simple, simple parameterize this plan, the simple parameter plan has to be safe for any other, uh, value that gets plopped into, uh, the where clause and gets also simple parameterized.

We could search for a value that’s within the boundary and we would have to scan the clustered index. So this is one of those funny things about, you know, like, you know, all the moving parts in SQL Server that can certainly make things, uh, tough to, um, tough to deal with a little bit.

But if we, so actually one thing I want to look at real quick, we should go over here and we should actually validate that SQL Server read every single row, uh, in the, in the user’s table when it did that work.

And it did, it read all of them. Good for us. We figured it out, cracked the case. I’m going to make, make detective sergeant major, whatever.

I don’t, I’m not good at ranks like that, but, uh, this query, if we look at this, if we run this query and we get it to full optimization, this query will, will bail out, uh, because all we get, uh, we can see the full optimization again, floating over my head right there.

Full. Uh, the reason why we get a constant scan rather than a clustered index scan is because rather than, um, being simple parameterized and SQL Server having to come up with a plan that’s safe for any other value that might go in there. Uh, we have a literal value here and SQL Server is able to evaluate that literal value, uh, not have to come up with a plan that’s safe to reuse for that literal value and say, no, we don’t have any rows for that.

Thanks for asking though. So anyway, uh, trivial plans are kind of weird. Um, you know, I don’t, I don’t like them very much.

In, in a lot of cases, uh, I find that they pop up at very inopportune times. I find that they are not, um, they are used when they shouldn’t be.

They are misused, uh, quite a bit. And, um, the only thing that you can do is, is look at your query plans. Uh, look at the, uh, look at the, uh, the operator properties of the root operator.

See if your plan is getting full or trivial, uh, optimization and then, uh, work from there. Uh, the one equals select one thing is a really cheap and easy way out.

You know, I’ve, and if you are able to modify any queries that, um, that, uh, that are not running well because of the, the trivial optimization level, then we might, you might want to do that.

Anyway, uh, I’ve been talking long enough. I got stuff to do. Uh, I’m going to try to upload this video. This is my longest video yet. I’m surprised the camera is still, still operating.

Uh, and, uh, yeah, uh, I’m going to, I’ll record another one in a little, in a little bit. I got, I got some, I got some actual work to do after this. Anyway, thank you for watching. I got some quite strong Раз soloing rhythm. I got some good heart.

Going Further

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

Why You Probably Don’t Want ISNULL Or COALESCE In Your SQL Server Queries

Going Further

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

A Little About Parameter Sensitivity In SQL Server

Video Summary

In this video, I delve into two key SQL Server scenarios that highlight the importance of avoiding overly clever solutions and understanding parameter-sensitive plan optimization in SQL Server 2022. First, we examine a situation where attempting to outsmart the optimizer can lead to suboptimal performance. Specifically, I discuss how using bit parameters in stored procedures can backfire, causing SQL Server to generate plans that are not as efficient as they could be. This leads us into an exploration of why parameter-sensitive plan optimization doesn’t always come to the rescue, especially when dealing with startup expression predicates that lack histogram support. By rewriting a procedure using dynamic SQL, I demonstrate how making life harder for yourself can actually simplify things for SQL Server, resulting in more efficient query plans. Throughout the video, I also share some light-hearted moments, like my new microphone pop filter, to keep things engaging and relatable.

Full Transcript

Erik Darling here, Darling Data, and recording a rather quick one to talk about, well, a couple things. One is a situation where you shouldn’t get cute with the optimizer. And two is a situation where SQL Server 2022’s parameter-sensitive plan optimization will not come to your rescue. Now, there’s a good reason for that, and we’ll talk about it. Now, if you’re a loyal subscriber to my channel, you’ll notice something brand new in today’s video. This little pop filter right here. Yeah? Apparently, this little microphone came with a set of three in case you get hungry and want to nibble because it’s right near your mouth. And I just sort of forgot about them. So now, here’s wishing you a pop-free experience listening to me drone on about SQL Server. Sorry. So we’re going to start off with the indexes. We got two of them. One called BudLight and one called CoorsLight. This is not a taste great, less filling situation. This is just two indexes that make good company. So, let’s focus in on the right window. That’s a good start to the video, right?

So we got these two indices here. By the way, if anyone says indices, I will punch you in and sue you at the same time. One of them is on the post table. And it’s got owner, user ID, and score in the key of the index. No included columns here. And the other one is on the comments table, which has user ID and score. Now, it’s a little strange to have in the stack overflow schema in the post table. It’s called owner user ID. And in the comments table and the votes table, it’s user ID. But we’re actually in the badges table, too. So that’s a little weird. But we’re just going to run with it for now. Owner user ID. Now, those two indexes are here to help our store procedure. So I’m going to talk through how they help and why they could be more helpful. And then kind of walk through what else the store procedure does.

So what we have are two potential left joins. I say potential because you’ll notice it is these things here, which we’re going to come back to. But the indexes are here to try and help us make this faster. Now, is this perfect? No. In a perfect world, I might put score first, depending. You know, the score column is not terribly selective. So range predicates can still acquire vast swaths of data.

So maybe not maybe not the greatest idea there. But, you know, one of those things you kind of have to A, B test a little bit, see where you see where it gets you. Now, coming back to the store procedure itself, we have two bit parameters. We don’t mean that they are two bit parameters. I mean, we have two parameters that are bits.

One of them is called check posts. The other one is called check comments. Now, what these do is direct SQL Server to maybe do something or maybe not do something when we get to these joins. All right. So having these parameters in here, well, they’re just like any other parameter.

They are sniffable. And just like any other parameter, SQL Server caches a plan for the first compiled value and then reuses that plan. But we can tell SQL Server whether to actually do a join with parameters like this.

So I’m going to run this. And the first iteration is going to be to check the post table. And then the second one is going to be to check the comments table.

Now, on the first run, I have query plans turned on. I don’t have to worry about that. This runs pretty quickly.

All right. 640 milliseconds. Okie dokie. No, it’s OK. It’s not great. It’s OK. But this is one of those things I see developers do quite a bit.

They try to get cute with SQL Server’s cost-based optimizer. And what happens is that they sort of end up falling flat on their faces. It’s going to come back to my copyrighted adage about SQL Server.

And that is, anything that you do that makes your life easier makes SQL Server’s life harder. And this is one of those things. So let’s slide this plans DMs over just a little bit.

So I don’t lose my finger in a tragic green screen accident. And let’s first look at this part of the query plan. And let’s nuzzle that.

Ah, it’s in my ear. Let’s nuzzle that right here. We’ve got an index scan that doesn’t actually do anything. And we’ve got a filter operator right after that. Now, usually, when you see a filter operator after some sort of index or table access, it means you did something real dumb.

In this case, SQL Server did something pretty smart. So let’s look at the details of the filter. We’ve got something called a startup expression predicate.

Meaning this filter, rather than acting as a filter after the fact, it acts as a filter before the fact. So this filter actually prevents touching that table because the startup expression predicate, right, to like move past the filter is not met, right?

So we did not check the comments table in this one. For the post table, it’s a different story, of course, because we’ve got, believe it or not, another startup expression predicate.

But this one is met, so we do actually touch the post table, right? So la-di-da there. Now, what happens if we run this in reverse?

Let’s execute this. Or not in reverse. Let’s just run this one next. That’s sensible, right? All of a sudden, this takes a little bit longer.

The last one took about 640 milliseconds. This one takes nearly four seconds to finish. 3.8 seconds as the crow flies, as they say in my country. And if we look back way…

No, not you, tooltip. Not you either. Go away. You’re not my friend. Let’s slide this back over. And you know what? I think that’s probably good enough.

Now, this time, the startup expression predicate in the filter operator is met. But guess what? We retained the cardinality estimation from the first query, right?

SQL Server is still guessing that only one row is going to come out of there, but we actually get like 4 million rows. 401, 647.

That’s a seven-digit number. Seven numbers means millions. So, the reason why the parameter-sensitive plan optimization does not work here is because the mechanic behind that is when you run a query that’s parameterized and SQL Server looks at the histogram for various equality predicates and decides that one of them has skewed data in it.

It will mark the query plan. It will give it… It will get that multiple plan operator. And SQL Server will decide to give you up to three variations of an execution plan for your query.

There’s like a small, medium, and large. There’s no extra small or extra large. I’m not sure what that says about Microsoft. It’s not very welcoming. You could use some more plan variations, maybe.

Anyway, the reason why this doesn’t work is because there is no histogram for this startup expression predicate. It is either yes or it is no.

There is no bucketized values that this thing could possibly work off of to figure out when you need different execution plans. Maybe it could in the future because this is a, you know, I think this is an addressable case with the parameter-sensitive plan optimization.

I can understand why it’s not in V1, but I do think this is something that could be addressed by it just because it is a, I think, common enough issue and it is a probably simple enough issue.

But what do I know? I’m just a bouncer. So obviously, that didn’t work out so well. Now, coming back to my copyrighted adage about SQL Server, anything that you do that makes your life easier, makes SQL Server’s job harder, we took the easy way out.

And now SQL Server’s job is harder because it has to come up with one execution plan that’s fast for both being true or either one being true. Now, I rewrote the store procedure in a way that made my life harder, but makes SQL Server’s life easier.

And I used dynamic SQL. So just a fair warning here. I did not write this to take care of the both use cases just because we’re not testing that.

So, and for the sake of brevity, I am only writing this to address one or the other, not both combined.

Now, because of that, I had to change the queries a little bit. I can’t do the isNull on p.creationDate and c.creationDate because we have no p and no c depending on which one of these executes.

So we cannot bind a value that does not exist. All right, because up here in the original query, we’re doing this. All right, and I just chose sysUTc date time because, I don’t know, it just popped up.

Honestly, it was autocomplete. Thanks, Redgate. Just got to work that way. Anyway, let’s change our procedure to use this lovely dynamic SQL that we have done all the right things for.

And, you know, it is a bit longer. All right, because now we have to check if check comment equals one. We could do this a little bit differently, I suppose.

We could make the string itself more dynamic and like throw a case expression in here. So if like check post equals one, then we’ll insert this. And if check comment equals one, then we’ll insert this.

But no, I just decided to do this because I didn’t feel like doing it. I decided to make my life a little bit easier in this case. So now let’s execute this first one and look for check posts.

And this returns again pretty quickly. It’s actually got about 50 milliseconds faster, just not dealing with all the other stuff, right?

Not dealing with the maybes of the check comments thing. And that’s cool with me. I’m into that.

I mean, I’m not going to brag to anyone about making something 50 milliseconds faster unless it was running for 51 milliseconds. But it did improve slightly. Not a very dramatic narrative there.

But now if we run this second one where we only check comments, it will be just about as fast at 610 milliseconds. But it was much, much faster than before when it took four seconds to run because we had all of these startup expression stuff in the parameters sniffed startup expressions and the sort of poor estimates that come from that.

Anyway, I have to get out of here and go do some actual work. Thanks for watching. Hope you enjoyed yourselves. Hope you learned something.

And I will see you in some video in the future. If you’re the type of person who celebrates Fridays and you’re watching this on a Friday, then happy Friday or happy Friday if you’re from a part of the country that pronounces it Friday or part of the world.

Actually, probably nowhere else in the world is going to say Friday. That’s an American thing. I don’t know. If you’re from somewhere outside of America and you call it Friday, well, you can leave a comment for me.

I’d love to hear from you. Anyway, smell you later. Stink bombs.

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 Common Table Expressions In SQL Server

Video Summary

In this video, I dive into the world of Common Table Expressions (CTEs) in SQL Server and share my perspective on why they often don’t live up to their hype. I explain that while CTEs can be useful for certain complex queries, such as cascading filters or generating row numbers, they are not a silver bullet for improving query readability or performance. Instead, I emphasize the importance of proper formatting and logical query processing in making your SQL code more readable. The video covers various scenarios, including how CTEs interact with indexes, top syntax, and multiple references within queries, ultimately concluding that while CTEs can be handy in specific situations, they should not be relied upon for significant performance gains or to make queries magically easier to read.

Full Transcript

E-Dot Darling here, it’s my cool new rap name, with Darling Data. And today we are going to talk about CTE in SQL Server. The reason we’re going to talk about CTE in SQL Server is because I am absolutely sick of CTE in SQL Server. They don’t make queries more readable. Formatting does. Formatting makes queries very readable. especially if you don’t put leading commas in your queries. And they don’t have any magical performance, impact, benefit. They are not good, great, wonderful, spectacular, superb, extraordinary. They are none of these things. There is not a… they’re just exhausting at this point. They… they… they… they tire me. If you ever wonder why I look or sound so tired, you can probably blame CTE. All right. So let’s run this query and let’s get a query plan just to set a baseline, right? So we get the estimated plan for this. And now this is what I would call a fairly typical user query where we access one index on a table. You may have seen other videos of mine recently where we talked about how multiple indexes on a single table can be used and can be used all together.

to make a query happen. All right. You can have key lookups. You can have index intersection. You can have index union. And all of those things can use multiple indexes on the same table to make… make… make it a query happen. But in this case, we’re just touching one index to get our data for this query. All right. Now, where CTE don’t completely befuddle, baffle, and… what’s another good B word? I can’t think of one. If you think of one, and leave one in the comments, where I don’t hate them is when you sort of like cascade stuff down through a CTE. So like in this case, SQL Server is smart enough to sort of optimize away the fact that we have this thing in the middle. All right. So if we run this query, or let’s just get the estimated plan, we only touch the user’s table once in here. Okay. Fair enough. SQL Server is pretty smart with that.

You can even do slightly more complicated things and still have SQL Server be fairly smart about it. So here we’re going to have one CTE where we select this, another CTE where we select this from the preceding CTE, and then a third CTE, which I just noticed I have misaligned slightly, where we select from the preceding CTE and apply another filter. So did I do that? All right. I did that wrong. Let’s see. Actually, no, that looks a little funny. And we’ll fix that later. So if we get the estimated plan for this, SQL Server is again, smart enough to sort of abstract away those useless abstractions that you have stuck into your query, and just give us one single solitary index seek, where all of the predicates that we are, come on, tooltip, you are being so good there, where all of the predicates that we care about, are applied to that one axis of the index. We have our seek predicate to the ID, right, because that’s the clustered primary key of the table, we can seek right to that. Then we have a couple residual predicates before I lost my finger in a tragic green screen accident, where we look at the date filter, where we look at the date filter, where we look at the date filter and the display name filter that we applied in the other CTE.

Where things change a little bit with CTE is when we introduce the top syntax to our CTE. Now, this one here isn’t going to force SQL Server to rerun the initial syntax multiple times, but it is going to change the query plan in sort of in a weird way. So, if I get the estimated plan for this, it looks kind of weird now, doesn’t it? Looks kind of funny, a bit unexpected. We have this series of top syntax. We have this series of filters where different predicates are applied. Now, when we touch the clustered index, we still apply that same initial seek predicate, right?

We still seek to where id equals 1. But because we introduced top and we set what’s called a row goal inside of each one of the CTE that we executed, or that we, I mean, they all execute together, kind of. We have a top and then a filter and then a top and then a filter and then another top. So, we have the three tops and two filters. The two filters are going to be on the additional predicates that we applied.

And thank you, SQL Server Management. So, I must have made Aaron Stellato a real mad SQL Server Management Studio is stabbing me in the back today. Today, we have the first filter, which hits the predicate on creation date. And then we have another filter, which applies the predicate on display name, where that equals community over there. So, introducing top into your CTE sort of introduces a bit of a fence because of the row goals that get introduced there.

A slightly more verbose example of that would be these CTE that we’re going to look at next. Now, in the first one, we are joining users to comments right there. And then in the second one, sorry, not the third one, there is only two. There is no three, there is only two. We are joining the post table and the votes table, right? And the post table gets joined both to the comments table and the users table from the first comment table expression.

And then the votes table we’re joining to the post table, right? So, if we look at the query plan for this, we’re going to see that SQL Server’s cost-based optimizer, cost-based query optimizer, was free to rearrange the joins to suit its own cost-based needs. All right. So, have this, it joins comments to votes over here. It joins the post table down over here. And then it joins the users table up over here. All right. Now, if we were to go and stick a top in our first comment table expression only, all right, because I don’t want to add a bunch of tops in, too many, too many tops, spoil the broth.

If we add a top in here, but not here, we’ll see the query plan change and the join between users and comments will be fenced off. And what I mean by that is now we have this top operator here and we have the join between users and comments behind that top over here. Now, sometimes it’s tough to get behind a top, but in this case, it worked out pretty well.

All right. So, we have our top operator and we have users and comments over here. And then SQL Server was free to join votes and posts and whatever order it chose in the second comment table expression. So, the top operator will fence off, at least in the current iteration of SQL Server’s cost-based optimizer.

The top operator will fence off things inside of a comment table expression, but neither a comment table expression nor a comment table expression with a top inside of it will materialize the query inside of the comment table expression. Where comment table expressions start running into problems is when you start referencing them multiple times in your query.

Now, I showed you where, like, if you stack them and sort of run down through them, things can turn out okay. But let’s look at what happens when you reference CTE multiple times, sort of without that stacking effect. All right. So, the first execution plan that we get here, back to baseline, where we only seek into the user’s table once.

Now, if we join that CTE to itself, we are now going to have two seeks into the user’s table, right? Because we had to rerun the expression inside of that common table twice. It’s actually not really a table at all.

It’s more like a tabular, right? It’s a common tabular expression because we are not materializing this result anywhere. And if we do that a third time, we will now see that we touched the user’s table three times.

We have the one, we have the two, we have the three. I think that a kick in the head. I think an easier way to sort of get across what happens when you do that is if we were to just union all and sort of explicitly select from the CTE twice, we would go back to seeing the two accesses of the user’s table with the concatenation operator over here, which unioned all of those two results.

So the same thing would happen if we did union. But just think of it as just like this query, union all, this query again, right? Because even though you make this reference once here, each reference out here means you have to rerun this expression.

All right? Now, where CTE or derived tables or anything like that can be useful is when you need to do something in a query that you can’t do in a single step. A pretty common thing would be to generate, like if you wanted to find duplicate results or you wanted to find like the first result, using something like row number is a really common way to do that.

The thing is you can’t filter on row number here, right? You can’t say something like where n equals zero because n is in the select list. And the select list, if you’re familiar at all with logical query processing, happens way at the end.

And the where clause, join clause, stuff like that occurs way earlier when the query is built, when the shape of the query is generated. So we can’t filter on this here, right? We’ll just get an error, an invalid column name n.

So we have to do stuff like this outside of a CTE, right? So if we run this, it is perfectly valid syntax. Now, this sort of gets into other, you know, sort of query tuning dilemmas and things that you might run across in query plans.

But this is one of those cases where when I see a filter operator, I understand why it’s there and I’m not angry at it. Because we have to generate that expression for the row number before we can filter on it. So we have to do all this work to get all the data we want.

And in this case, well, I mean, for various reasons. I’m on SQL Server 2022. I am in compat level 160. And so I’m getting the cool feature batch mode on rowstore automatically.

I mean, that’s been available since SQL Server 2019. But it’s only Enterprise Edition. I’m using Developer Edition.

So I get all the Enterprise Edition features, which is why Developer Edition is like a great gateway drug for Microsoft. Because you start doing all this development in Developer Edition. And you’re like, wow, what great performance I have.

And then you move it over to standard, like your production environments in Standard Edition. And all your queries are complete stink bombs. And Microsoft’s like, well, okay, well, just give us $5,000 more core and you’re all set. Our dreams of a feature flag for Developer Edition to remove Enterprise Edition features is long in the making.

It doesn’t exist, but I’m sure they’re working hard on it so you don’t run into that. This awful conundrum, quandary, conundrum. It doesn’t matter.

Anyway, comment table expressions can be useful when you have to do stuff like this. Because you couldn’t do this all in a single step. Just don’t expect doing this to have any magic performance benefits for you.

Comment table expressions just aren’t helpful in that way. All right. Cool.

Hopefully, I have absolved and alleviated you of all your misconceiverations about CTE. They truly do not help performance in any way just for existing. They don’t make queries any more readable just for existing.

Again, query formatting is more important for making a query readable than jamming a completely unreadable query inside of a CTE. Anyway, I hope you enjoyed yourselves. I hope you learned something.

I will see you in the next video, which temporally could happen at any moment now. It may have already happened by the time I say this, which is really crazy. It’s just impossible to tell.

Anyway, thank you for watching. And I’ll catch smell you later, stinky pants.

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 Sorts In SQL Server Query Plans

Video Summary

In this video, I delve into the fascinating world of SQL Server’s cost-based optimizer and explore where it decides to stick sorts in query plans when you might not expect them. We discuss various reasons for these sorts, such as key lookups, stream aggregates, merge joins, distinct operations, and windowing functions. Each scenario highlights how SQL Server optimizes data retrieval but also introduces the challenge of memory grants, which can lead to a tug-of-war between buffer pool space and query execution efficiency. By understanding these intricacies, you’ll be better equipped to write more efficient queries and manage your database resources effectively.

Full Transcript

E-Darling here with Darling Data on sort of a sleepy Brooklyn Friday. And today we’re going to talk about places where SQL Server sticks sorts and query plans during cost-based optimization, even if you don’t necessarily ask for data in a particular order. Now, there are all sorts of reasons for this that we’ll talk about as we go through things. Some of them are requirements. from certain query plan operators like stream aggregates and merge joins. And the reason why we care about them is because sorts require memory. That memory is often called a query memory grant. And that query memory grant has to come from somewhere. So unless you have a perfectly honed, artisanally crafted set of hardware for your SQL Server, there’s going to be a bit of a tug of war between the buffer pool and query memory grants. in order to get enough memory for both of them to do what they got to do.

We’re going to talk more about memory grants in a separate video. There isn’t going to be a deep dive into that at all. This is just to kind of talk about where you might, why you might see a sort in a query plan when you didn’t say, order by some data. Now, before we do that, I want to talk about something related but not quite on topic just because there’s not enough to talk about with the other thing. to justify an entire video. And that is when you’re ordering data. Now, there’s no such thing as guaranteed ordering from a query unless you order by a column. And not unless you have a unique column in the mix to order by. Now, by that I mean, if we look at the results of this query, you can see that the first line up there is the only other options I see, blah, blah, blah, some other stuff that probably sounds really smart.

If you read the, if you were to read the whole thing. But if we look at the execution plan, we have a parallel execution plan for this query. Alright, takes about two seconds to finish running. And if we rerun this, we’re just ordering by this score column right now. If we’re just ordering by this score column, there are a lot of duplicate values in this score column. So if we run this query again, we get the same result back that time. But if we keep going and say, ah, what sorry, regex engine doesn’t support plus?

It’s a fair question. And if we just keep running this, well, we’re back to the only other options they see being something. And now we have to clarify the cigar API does something else. And oh, well, we got the cigar API again. And let’s see if we get lucky on this last run. Nope, we got the cigar API again. So we ran this a few different several times, we got a few different results back in a few different orders. Now, you might be tempted to stick a max stop one hint on your queries thinking that you’ll get reliable results back that way. The thing is that you won’t.

That they’re not guaranteed in this one either. And on top of them not being guaranteed, you also can end up with a much slower query. That last query that we were running took about two seconds to finish. This one is just about at the seven second mark to complete. And also, you still don’t get the guaranteed ordering from this. So it’s probably a little bit more rare that you get mixed results back just because you have a single thread working rather than dot threads working on the query. And the, you know, just various timing differences will have more effect on multiple CPU threads than on a single CPU thread.

But you can also end up with a query that takes a lot longer. The only way to get consistent results back is to also order by a unique column. Most, well, actually, all of the tables in the Stack Overflow database have a clustered primary key on a column called ID. It’s also an identity column. It’s a pretty popular choice for a clustered primary key these days. But this is the only way to get back truly guaranteed results. And if we look at this, we also get different results back than we did just ordering by that score column. So we need to think very carefully about how we’re ordering things and what we’re actually looking for when we’re dealing with data from a column that is not completely distinct across.

Now, to illustrate the sorting stuff from some of these other queries, I’ve created an index on here. And I did that ahead of time, you can tell, because IntelliSense has inserted this really helpful Excite Byte track under the index name. So lucky for us, we know that that index already exists, we don’t haul off and try to recreate that index, do something foolish. But one place where you might see SQL Server decide to stick a sort in your query plan for what might appear to be no reason whatsoever, is to do a key lookup. So in this query plan, we have this big thick line of stuff coming out of our clustered index. And then you can see a key lookup. And SQL Server has decided to sort that data prior to doing a key lookup via a nested loops join back to the clustered index on this table. I recorded a video about key lookups recently. So if you need to refresh your memory, go look at the list of videos in this channel and you’ll see that one in there.

Now what are we ordering by? Well, since key lookups are essentially a join between a nonclustered index and the clustered index on the table, when we go and touch that clustered index, we need to go get the ID column, which is again the clustered primary key. All right. All on board there. And because we don’t have that column in order in our index, well, we sort of do, but we also have an inequality predicate on the creation date column. So we don’t, we’re going to cross all sorts of boundaries there. And our data is not going to be in a helpful order leaving the index seat with the ID column.

And so SQL Server sticks an order by the ID column into our query plan in order to optimize IO when we go and do our lookup back to the clustered index when we have to find all those ID columns. Now the next two are single query plan operators that expect sorted data. First one is a stream aggregate. So if we look at this query plan, we have a sort and then we have a stream aggregate operator. That’s because a stream aggregate operator requires sorted data coming in.

If this were a hash aggregate, we wouldn’t have that requirement. We could just bring data in however we wanted. But here we are with the stream aggregate and needing to sort our data. Merge join is another one. Now I’m just joining the post table to itself on this owner user ID column.

But since I don’t have an index on the owner ID, owner user ID column to support that merge join, I have two sort operators in here going into the merge join here. I just want to take a quick moment to say that parallel merge joins are one of the most offensive things that you can ever see in a query plan.

If you see a parallel merge join in your execution plan, you should question the foundations of your existence. Or just fix it. I don’t know. You can do both. I find that questioning the foundations of your existence is often a pretty good thing to do.

You can answer a lot of questions about yourself that way. Now another place where we might see a sort operator get stuck in our query plan is if we ask for a distinct set of data. So if we run this, or we just get the estimated execution plan for this, we will see a distinct sort down here.

This is how SQL Server has chosen to figure out a unique set of data for this query is via the distinct sort operator. Now you’ll notice that this query and the one below it, both are using a semi-undocumented hint called disallow batch mode. We just get a different query plan with batch mode involved.

If we run this query without the disallowed batch mode hint, we get a hash aggregate operator. That hash aggregate doesn’t expect sorted data, and so we don’t get a distinct sort. And you can see that this does happen in batch mode, and you know, wouldn’t that just ruin the whole demo?

That’s that old CAD batch mode. Now another place where you might see SQL Server stick a sort in your query plan is when you use a windowing function. Now every windowing function is going to have some kind of order by in it.

Unless you do like a count over or something that doesn’t really require, have an order by requirement in there, but a lot of them do. But the point here is more that SQL Server needs to order not only by the order by column, but also the partition by column. That’s also something that SQL Server will need to sort if you don’t have an index that handles that sorting for you.

Now again, we are disallowing batch mode here just to get a couple more query operators in the plan. Now just so you have a sense of what changes, under batch mode we still have a sort. That doesn’t change.

We still sort data here, but now we just have a window aggregate here. Right? So this window aggregate doesn’t really tell us all of the stuff that we would see, that we would like to see from the row mode execution plan. But the sort operator is still going to be, oh, let’s come back to that.

No, let’s get that tooltip back. There we are. That’s what we wanted. This order by is still going to be on owner user ID and creation date in ascending order. But if we run this query again without batch mode involved, we disallow batch mode, we are still going to have a sort operator.

All right. Come on, come on, come on, bring it on back. Zoom it quit early on me.

Started drinking. We still have the order by on owner user ID and creation date. But now we have a couple additional things here. Instead of having that single window aggregate operator, now we have these three things. And these three things are all happening in a serial zone.

Right? This parallelism gather streams here gathered all our data up, stuck it into a serial zone, a single threaded zone in the execution plan. And now we have to just cope with that somehow.

Anyway, that’s not really the point. The point is that we have, I mean, we have the initial sort on owner user ID and creation date. We already looked at that. But now we have this segment operator.

And this segment operator first does a group by on owner user ID. All right. That’s our partition by element. And then we have another segment over here. And that tool tip just did a good job of disappearing on me.

Come on, pal. You can do it. I believe in you. You’re the best SSMS and the best SSMS that has ever existed. We also have a group by on owner user ID and creation date.

And then our sequence project, which, again, sort of like the window aggregate, not really a helpful set of details there. So, again, these are places where SQL Server’s wonderful cost-based optimizer might decide that your query plan needs to sort data for various reasons, key lookups, operators that expect it, like stream aggregates and merge joins, or because you don’t have an index that supports the ordering that you’re asking for.

So these are all reasons why SQL Server will need to sort data. And, again, we care about sorts because sorts require memory grants. And memory grants can steal data from the buffer pool.

And the more data that we steal from the buffer pool, the less buffer pool space we have. Then we have to retake this back into it. Then we have to touch disk. And it’s all just a horrible, horrible cycle. Gas station sushi.

Just round and round we go. So easy in, easy come, easy go, I guess. Anyway, I’m going to go, I don’t know what I’m going to go do, actually. I guess I’m going to go work on that video about memory grants so I can record that one.

Anyway, thanks for watching. I hope you learned something. I hope you enjoyed yourselves. I hope you have a great weekend since it’s Friday here. I guess that’s a little bit non-deterministic.

Just like ordering by a non-unique column is a bit non-deterministic what day it is when you’ll see this. So happy whatever day it is to you. All right.

Thank you for watching. Thank you. Thank you. Bye.

Going Further

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