Tag: SQL Server Training

Improving The Parallel Query Processing Documentation For SQL Server

Video Summary

In this video, I delve into the parallel query processing documentation in SQL Server, pointing out where it’s incorrect or misleading, and highlighting some dead-end links that don’t provide additional information. I walk through several examples to demonstrate how certain constructs, such as recursive common table expressions (CTEs), scalar user-defined functions (UDFs), multi-statement table-valued functions, and the `TOP` keyword, can actually support parallel execution in ways not fully captured by the documentation. By creating detailed demos using the Stack Overflow 2013 database, I show that some parts of these queries can indeed run in a parallel zone despite what the docs suggest, while other parts remain single-threaded. This video aims to provide clarity and practical insights into how SQL Server handles parallelism for these constructs, helping developers make more informed decisions about query optimization.

Full Transcript

Erik Darling here with Darling Data. And today’s episode is going to focus on the parallel query processing documentation, some places where it’s wrong and some places where it’s misleading, and some places where the links to get more information sort of lead to a dead end. There isn’t actually any more information at some of these links, which is kind of funny.

Now, the docs start by talking about constructs that inhibit parallelism, which is this list here. You have scalar UDFs, which is mostly right. You have remote queries, which is like half right.

You have dynamic cursors, which is right but incomplete. You have recursive queries, which is half right. You have multi-statement table-valued functions, which are also half right.

You have the top keyword, which I’m actually going to call half wrong, because it is, again, not really accurate. And what’s funny is that for a lot of these things, there’s all these things that say for more information, you go over to whatever link. And for a lot of them, there’s not actually any more information about parallelism at the other docs.

And that’s fine. Maybe it’s not the right place for it. But it’s also not very helpful to folks who are trying to find things if there is nothing additional at those other links about parallelism. Now, if you scroll down a little bit further, there’s this really exciting table of non-parallel plan reasons.

The non-parallel plan reasons are, they’re right for the most part. But the thing that kind of gets me is that there’s actually better detail in this table than there is up there at the top, or up there in the list of things that might inhibit parallelism. And for some of them, it’s kind of funny because you’ll see reasons that queries can’t go parallel here that don’t actually show up in here.

Like there’s nothing about top in here, there’s nothing about multi-statement table valued functions in here. There is, of course, something about table variables not supporting parallelism. But there’s nothing in the documentation up here that says the problem with the multi-statement table valued function is the table variable, not the function itself.

So I’ve written a series of demos that go through and either complete or debunk some of the information in the docs. So let’s start over in the lovely Stack Overflow 2013 database. And the first thing that we’re going to look at is recursive CTE.

Now, what I’m going to do is not actually run this whole thing because we don’t actually need to run this whole thing. And for a lot of these queries, I have some hints on there to force parallelism because it wasn’t naturally occurring. But what I want to show is that some part of the plan can indeed go parallel if need be.

So we’re just going to get an estimated plan for this recursive query. And just to make sure everyone understands a recursive CTE, a CTE is when you start off with a CTE. And then inside of that CTE, you execute some recursive doodads.

I love the doodads. So here we’re just selecting from post where the ID is equal to some post value. And then you union all and you join the recursive portion of the CTE.

You join that sort of back to itself, right? Because the name of the CTE up here is P. And we’re selecting from that part of the CTE.

This is the anchor. And this is the recursive portion. So if we look at the plan for this, now, if we remember what the docs said, one thing that would happen if we had a recursive query query is that we would have parallelism inhibited for that query.

And we can clearly see the execution plan that a large chunk of the query plan, after the recursive portion, does execute in parallel. We have a parallelism distribute streams.

And that’s where we join off to the user’s table outside of the recursive portion. So after we select from the recursive CTE, we join to the user’s table.

That whole portion of the query is free to run in a parallel zone in the plan. But the recursive CTEdoes have a restriction where this part has to run in a serial zone in the plan.

So the recursive CTEdocumentation, about half right. Just about half right. And have like the recursive CTEportion, can’t go parallel, but other parts can. The scalar UDF version is, again, right, but incomplete.

So I’m going to create this UDF, scalar UDF here. And this UDF, even though I’m on SQL Server 2022, and I am in compat level 160, 160, one restrict, so like some functions could potentially be inline, scalar UDF inlining, pretty cool feature.

Started with, you know, SQL Server 2019. But there are a lot of limitations to it. And one of the limitations to it is that if your scalar UDF has a CTE in it, then it is disallowed from scalar UDF inlining for some reason.

Why? I don’t know. It just is. So if we run a query that calls that function, we’re going to run the whole thing once. And then we’re going to run, actually, you know what?

We’re not going to run this whole thing because I didn’t create indexes to support this, and it will run for four hours. That’s a different demo. But what I do want to show here is why the documentation is only half right. So the initial call-in query up here is forced to run single-threaded.

If we grab the properties of the select operator and we look at the details in the properties pane, we are going to see the infamous non-parallel plan reason. And since we are on SQL Server 2022, we actually have the filled-in reason here, the t-SQL user-defined functions not parallelizable, which is right.

They’re not when they can’t be inlined. Inline functions might not go parallel even. But what you do see in the part of the plan that calls the scalar UDF, right, in here, where the UDF is executed, we have a parallel plan.

If we look over here, we can see that the body of the function was indeed able to engage a parallel execution plan. And part of the reason why this occurs naturally is because I didn’t create indexes.

So good on me for remembering that at the last minute and not sitting here for four hours waiting for results to come back. And the scalar UDF thing can be expanded out to two places where I see UDFs pop up quite a bit, where I really wish they wouldn’t.

One is in computed columns and the other is in check constraints. So if we create this function, actually I already created this, so doing this is really just for show. Create this function, oh, what did I do?

Oh, oh yeah, okay, I can’t alter it because it’s already referenced by the table. Good, good, good. So we don’t actually have to do that for show. But what I have here is a table created called serial. And the definition of the table has a computed, persisted even computed column in it that calls that function.

I’ve already stuck some data in the table and I’m just going to run this and get the estimated plan. And this will, because we have a scalar UDF in the computed column on this table, helpfully called serial, we are going to have our non-parallel plan reason yet again.

All right, so even SQL Server, SQL Server’s scalar UDF inlining abilities cannot defeat a scalar UDF in a computed column even if it’s persisted. It would be the same deal even if we indexed it as a parallelizable thing.

Check constraints are a little bit more complicated in the restriction. So I’ve got another table where I have this as a check constraint and where I already have some data in there. And the main difference in what’s parallelizable when you have a check constraint is in which columns are referenced.

So in the first query, I’m just, I’m just like basically cross-joining these tables together. But when I do a cross-join, the plan’s a little weird. So I’m just doing a left join on 1 equals 1, 2, I’m self-joining this table to itself basically.

And I’ve got the query trace on 8649 here to force a parallel execution plan. And when you look at these two plans, the first one, actually let’s go back to the queries real quick. The first one just does a count big.

We’re just doing a count of everything everywhere. Which means that the check constraint column doesn’t have to be projected out anywhere. It’s not involved in the query.

So the first query plan actually does, is fully parallel. This is all parallel up until the very end of the execution plan. And the second one, where we select the column B, which is what the check constraint is on, and where we also group by B, because we have to group by B if we’re getting a count, that query plan is forced to run fully single threaded.

And if we look at the non-parallel plan reason, again, we’re going to have the non-parallelizable function in the details there in the properties. So a little bit more complicated of a story with the check constraints.

And so it really is the column that’s involved in the check constraint that causes the problem. So if I created this table differently, and I put the function on the ID column, then doing anything with the ID column would, if I had to join with it, selected any of that stuff, then that would cause the plan to run single threaded.

Now, multi-statement table value functions are also somewhat restricted in parallelism, but not fully restricted in parallelism the way that the documentation kind of indicates. So if we create this function here, it returns a table variable, we do all this work in here.

The part of the query plan that is initially forced to run single threaded is going to be the modification to the table variable. Modifying table variables can’t run in a parallel zone.

The other part of the query plan that’s going to be single threaded is going to be reading from the table variable that the multi-statement table valued function returns. There’s all sorts of intricacies here where if you had table variables, like since multi-statement table valued functions report, like basically support, you like multiple steps, if branching, all that crazy stuff in there that uh, inline table valued functions don’t.

So if you had table variables declared, inserted to read from, and internally inside of the function, uh, table variables could theoretically be read from in a parallel zone, but the return table variable, the table variable that you return data from, cannot do that.

So if we run this query, or just get the estimated plan from it probably, uh, we’re going to see the estimated plan both for the query that calls the multi-statement table valued function, and for the multi-statement table valued function itself.

So when we looked at the scalar UDF, the body of the scalar UDF was allowed to run in a parallel zone. It’s not the case here. If we look at the properties of the table insert for, uh, the, uh, multi-statement table valued function, we are going to have, uh, that’s sorry, this is, this is a long, this, this one’s a mouthful.

Uh, we’re going to have a non-parallel plan reason that table variable transactions do not support parallel nested transaction. I’m not sure if that should be transactions in there. Uh, it seems a little weird that that’s singular, but whatever.

Um, I’m not the grammar police. Uh, don’t, don’t engage in that sort of fascism. Uh, but, uh, up here in the, uh, the query that calls the, um, calls the multi-statement table valued function, we do indeed have an entire parallel zone in that query plan.

The part of the query that is not allowed to run, uh, in parallel is reading from the multi-statement table valued function, returning that table variable. That is, that is a restriction, uh, across the board.

Uh, another place where the documentation is, uh, quite short on detail, and where, you know, if we remember, like, come back, coming back over here and looking, we see the top keyword mentioned here, right?

Trying to give tops a bad name. Big mistake. Uh, and then we look down in the non-parallel plan reasons. There’s no mention of top anywhere in here, right?

And top isn’t mentioned as a non-parallel plan reason. Uh, and this is where it’s a little misleading, because it says top will restrict parallelism, but then if you look in the details down here, it doesn’t actually do that. Uh, there’s no, there’s like no mention of it here.

And it’s, it’s only even partially true that it, because it only happens sometimes. So if we look at this query, where we have, uh, two select top ones, one here and one here, and we just sort of join those together.

Uh, and we look at the estimated execution plan for this. We are going to see, uh, two branches of the parallel query plan, right? Because we have the two top ones.

Uh, they can’t share whatever they were doing. Uh, and we do have SQL Server coming into a serial zone after the initial parallel zones, right? So like we have the gather streams operators here, which is clinching our parallel zones together.

Uh, these two things happen completely in parallel. But then as we get into the top operator, this is where the serial zone is, right? So everything basically from here on over happens single threaded, going into the top.

Where that diverges from being the God’s honest truth is when we use apply. So, uh, when either when we use the cross apply, uh, language element here, or if SQL Server optimizes a nested loops join to use, uh, apply nested loops rather than regular nested loops where the apply pushes predicates down to when we, we talk to the table on the inner side of the join.

Uh, then, then we can get a fully parallel zone there. I’m just going to get an estimated plan for this. And you can see that in this case, we have a fully parallel zone.

Well, let’s sorry. Let’s frame that a little bit better. Uh, there we go. We have a, uh, the end of the parallel zone gathering streams going into the final top, but we do not have, but we, we don’t have any restriction on this part of the query.

If you can even see this top end sort is, uh, fully inside of a parallel zone. There’s no clenching or cinching of the parallel threads there. Uh, the other thing or another thing that can, uh, cause a parallel, or sorry, a serial zone in a query, uh, is a global aggregate.

So, uh, I’ve got quite a bit of fancy, um, query stuff, query hints in here to get exactly the plan that I want. Uh, sometimes it’s kind of tough to get exactly the plan you want, just writing the query, uh, with, you know, some of the tables that you have available.

Uh, but if we look at this estimated execution plan, we have, uh, two parallel branches of the query. Uh, we have this part, which is fully parallel. And then going into both of those, uh, uh, I have to remind myself what those were.

There are two counts in this case. If we look at this going into each one of those counts produces a, um, produces a serial zone in the plan. We can see the gather streams happen there.

And the stream aggregates that do the count here. Uh, and then the global sum, right? The getting the sum of the two counts, adding those two counts together is also in a serial zone. This is would also qualify as a serial zone in the plan.

If we did some, if we, if our query looked a little bit different, uh, you can kind of, uh, see how that works. If you, well, actually, that doesn’t even go parallel. So forget it.

Um, and that’s not true of, uh, queries that have a grouping element, right? So global, global aggregate is just like select count, select sum, select average, min, max, all that stuff. Uh, for queries that, uh, have a, uh, grouping, uh, element to them, those queries, uh, those aggregates are allowed to happen fully inside of a parallel zone.

All right. So this one, this whole query runs in parallel. That’s fine. Uh, another thing that the documentation is only half right about is linked servers. So, uh, and I, I realize that this is cheating a little bit, but it’s the best that I’m going to do, be able to do quickly.

Uh, so I have a linked server here that I create. It’s a loopback linked server, meaning the server basically points to itself. And what I’m going to do is, uh, clear out, uh, wait stats and latch stats.

Uh, I’m going to query, uh, wait stats for the server. Uh, and then I’m going to run, I’m going to query the linked server here using open query. And then I’m going to select, uh, from the wait stats DMV here.

So if I clear everything out and I’m the only person on the server, it’s just me, nothing else is going on. And this happens quickly enough that I’m confident that it’s my query doing the parallelism. Uh, the first run through of wait stats after I clear everything out is all zeros.

And then the second run after I execute my query and re query the wait stats, I do have some parallel query weights and that’s pretty consistent across like every execution of this seems to end up with just about the same amount of CX sync port, CX sync packet, all that stuff.

If I keep executing this, the, the, these weights stay close enough that I’m, you know, I can confidently say that’s my query doing it, right? It stays pretty, pretty close to, uh, what it is.

Uh, if we kind of go back up a little bit here to where it’s quoted out and the reason I have this is just to show that one query, like the query that I’m executing does go parallel is, uh, well, actually, you know what, we’re not even gonna, we’re not gonna bother running, uh, oh, actually, I finished.

What, what the hell? So if we look at these two, uh, query plans, the version of the query that just touches the base table on the, on the server itself without using the remote query stuff, uh, that does go fully parallel.

So, um, this is about 238 milliseconds of, uh, execution time. So I do believe that the 300 or so milliseconds of parallel query time is close enough to this, that that would, that would indicate that it was my query that was responsible for it.

But if we look at the query plan for the, uh, remote server query, uh, we can, we do see that locally, the query that calls the remote server can’t go parallel. That restriction is local to where you call the linked server, but on the linked server, the query over there can, can run in parallel.

So again, only about half right on that one. Now, uh, I think the final thing that I want to talk about are fast forward cursors. Now, this is one part where, again, the table is more helpful than the documentation source, because we have, uh, where is it?

No parallel fast forward cursor is a reason in the documentation here, but it’s a little weird that the documentation up here only mentions dynamic cursors. All right. So that’s a, that’s a little strange, especially because when you go to that, uh, go to the documentation about cursors, there’s no mention if a fast forward cursor is a dynamic cursor, which might be something helpful to mention. So if I run this and everyone can be impressed by how blazing fast my query, my, my cursor was look at, look at that fast cursor execution. Uh, and we look at the properties here. Uh, we will see the, the, the, uh, warning that I highlighted before the no parallel fast forward cursor that does indeed restrict a parallel plan, but, uh, that is not really, uh, that is not really documented well over on the, uh, the learn site. So, uh, that that’s pretty much the end of it there. Um, you know, uh, there’s a written version of this post with all the demo scripts in it coming out, uh, around the same time that this will, this, this will be coming out. So, uh, if you want to dig in further on any of the scripts, see how things work there. You can, um, you can, uh, you can follow along that blog post. This is just the video version of it, which, uh, walks through stuff.

I don’t know. Some people like videos better. Uh, me, I’m, I’m just mostly trying to get the, uh, the video set up exactly how I want it here and also, uh, get, get back into getting used to recording things because, uh, I let that slide for a really long time because, uh, various reasons that I’ll hopefully get to talk about it past some. But anyway, uh, that’s it for today. Uh, I’m going to, um, go drink some water. Parched. Dry in here. Hear it. Anyway, thanks for watching. Hope you learned something. Hope you enjoyed yourselves. Uh, and I will see you in another video. Uh, I don’t know.

Maybe, maybe tomorrow, maybe later today. We’ll see what happens. See how, we’ll see how, uh, see how motivated I’m feeling after some nice, nice New York tap water. All right. 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.

Cardinality Estimation For Local Variables And Parameters

I’m working on ramping up my recording and streaming setup now that life has settled down a little bit, and publishing the results to YouTube while I work out the wrinkles. Enjoy some free video content!

Video Summary

In this video, I delve into the nuances of local variables in SQL Server stored procedures, offering a fresh perspective on their usage and potential pitfalls. Typically, my discussions focus on how local variables can affect cardinality estimation when used instead of parameters or literal values for filtering queries. However, this time around, I explore scenarios where local variables are mixed with parameters, highlighting the impact on query plans and performance. By running through several examples using parameterized dynamic SQL, I demonstrate how local variables can lead to poor cardinality estimates, especially when dealing with large result sets. The video also touches on a new optimizer feature in SQL Server 2022 called parameter-sensitive plan optimization, explaining why local variables can break this functionality and suggesting that they should not be used as a workaround for parameter sniffing issues.

Full Transcript

Erik Darling here with Darling Data. I’ve been busy the last few months fending off various hostile takeover attempts by BeerGut Magazine. Despite their best efforts, I don’t think that a six-pack is quite enough compensation for the glorious Darling Data brand, despite what GoDaddy tells me my domain is worth, which may be about the price of the six-pack, depending on local factors. Erik Darling here with me, I’m recording this video today to talk a little bit, well, talk about local variables in a slightly different way than I usually talk about them. Usually when I talk about them, it’s in the context of, like, there just being one local variable, how SQL Server deals with cardinality estimation when local variables are being used in place of parameters or literal values for filtering queries. Erik Darling here with me, I see this a lot when helping clients tune store procedures where, and again, the clients are nice people who keep my lawyers afloat so that I can fend off BeerGut Magazine hostile takeover attempts. But I see them a lot in store procedures to do something like this, like, where someone just didn’t feel like typing data at day minus one over and over again because they’re going to use it in a bunch of different queries to filter stuff out. So the query, having a good time. The query is end up looking something like this, where on one side you have a local variable like this being used and on the other side you have a parameter that gets passed into the store procedure being used. So I’m going to use a slightly different example to demonstrate what happens sometimes when you mix local variables and parameters in your where clause. Now, for these queries, I’m going to use the nice, safe, happy parameterized dynamic SQL that will keep your SQL injection attempts at bay. So everyone can rest safely and soundly knowing that. And I’m going to start off using values for my filtering that only get one row back from the user’s table. I’ve got two parameters that I’m going to pass into my dynamic SQL here. I’ve got one for upvotes and one for downvotes. And I’ve got a few different iterations of the dynamic SQL that I’m going to run to sort of use as examples here. Now every time I run this, the code in this, I’m going to clear out the plan cache. So they’re going to get a brand new query plan for each time this group of queries executes. But we’ve got this first one here. And this one is going to use parameters for both filtering on upvotes and downvotes. So the values that get passed in from here are what get get get get uses parameters in here.

And then I’m going to switch off having one use upvotes as a local variable as a filtering parameter. And then another one where I do the same thing with downvotes. So I get that there and we can see the local variable being used there. And then I’ve got a final one where both of them use local variables. So these both get the local variable doodad cardinality estimation used. Doodad. Professional, right? So I’m going to run this. And we’re going to look at the query plans together. And what did I do? Oh, did I have something highlighted? I did probably. There we go. Second time’s a charm. Much like me.

So let’s look at these query plans. And hopefully my zoom it skills are still fairly sharp. So we’ve got not too bad. So we’ve got this first query up here that uses parameters for both upvotes and downvotes. And we see that we get a guess of one for both of these. We also get a guess of one for both of the two, both of the queries where one of the parameters was substituted with a local variable. So we’ve got one here and we’ve got one here, but we’ve also got a regular parameter passed in on the flip side. So like there’s downvotes as a parameter there and there’s upvotes as a parameter there.

Only the ones that end in underscore v are the locally declared variables to the batch. Cool. So these both also get a correct guess of one because both of, because SQL Server is able to use the upvotes and downvotes. So it’s a cardinality estimation for the parameters to kind of guess one row and everything goes okay because of that. There’s not too much room for error when you have a single row that gets evaluated. But the last one, all the way down at the bottom where local variables get used for both upvotes and downvotes predicates here, you get a guess of 45.

So SQL Server where it no longer uses like the good part of the histogram to make a cardinality estimate. It uses the density vector stuff that the density vector math for both local variables. Things get thrown off a little bit. In this case, it’s just not the end of the world. Getting one row back when you expect 45 rows, it’s not that big a deal. Go away, zoom it. I’ve got a couple posts that talk about some of these things. They’re in this script file, but they’re here to remind me to put them in the show notes.

So those will be links in the YouTube video blog post wherever this ends up. We’ll see. See how wild I feel later. But those will be available there. Anyway, let’s change this a little bit because when you get when you’re just getting one row back, pretty rare for everything to like fly really badly off the rails. But what I’m going to do is I’m going to replace both of these with zero. And the reason I’m going to do that is because there are a lot more rows where upvotes and downvotes are zero for users because a lot of folks join stack, stack overflow, stack exchange, ask a question, get downvoted into oblivion, never come back, never cast votes, never do anything.

So there’s a whole lot of people qualify for these zero filters here. And this is where things get a little bit more dangerous, right? So like for low numbers of rows, almost any plan is going to be okay. For a big number of rows, it’s less okay. And I see people use this a lot and try to say, well, I did it to fix a parameter sniffing problem. Like, well, if all you’re ever getting back is a small number of rows, you probably don’t have much of a parameter sniffing problem anyway, because the parameter sniffing problem comes from when you have giant variations and how many rows might come back depending on the search argument.

So if you’re just always working with a relatively small number of rows, like you can’t just say, blanket, I have a parameter sniffing problem, because most of the time parameter sniffing is a good and fine thing. You get plan reuse, SQL Server uses the same plan, doesn’t set your CPUs on fire, generating execution plans over and over again, much like a recompile hint would or something like that. So let’s look at these queries now when we are dealing with more rows than just one.

Now, we’re going to get all four results back. And since I don’t have an order by on any of these queries, like we don’t like we don’t, we’re not going to get results back in the same order for any of them. This is actually also kind of a good lesson, like aside from, you know, the local variable thing is this one, the reputation is all scrambled here.

And this one reputation is in fairly decent order. This one reputation is a bit more scrambled here. And in this final one reputation is back to sort of being in order.

And we’ll talk about why when we look at the query plans. But let’s go look at how these performed. Now, the top query makes a startlingly good cardinality estimate only off by 10%. Not bad SQL Server, right?

Good for you SQL Server. But this all finishes pretty quickly. Since I’m using SQL Server 2022, I’m in compat level 160. I get and I’m using developer edition, which is the equivalent of enterprise edition.

I get batch mode on rowstore for some of these queries here. You can see the actual and estimated execution modes are both batch. So batch mode for rowstore kicks in and SQL is like, oh, I have to do some extra work.

Let’s batch mode this baby up. Talking more about batch modes a little bit further than I want to get in this thing. But, you know, whatever.

We can talk more about that later. But what happens is for the other queries SQL Server makes less and less of a good guess for what’s going to come out of these operations that do the filtering. So the second query, the second query plan rather, this is an astoundingly bad guess, right?

SQL Server kind of only made an okay guess for one of the parameters, not for both. And when you combine the cardinality estimates that it came back with and SQL Server was like, well, there’s two of them. I carry the two.

450. But 450 is an astoundingly bad guess. And because of this, we get a less than ideal query plan that takes round about three seconds to finish. Now, there is no mix of batch mode and rowstore.

All of this thing runs entirely in rowstore. I believe one of the seeks might be batch mode, but it’s really neither here nor there whether that happened. But the important thing is that the reason why this second query result set, which is this one.

Well, it’s identical to this one, but we’ll see they have identical query plans. The reason why this comes back in order is, of course, because SQL Server performs a sort by reputation to satisfy the stream aggregate that happens next. The hash match aggregate returns rows back whatever.

There’s no ordering, nothing like that. So you just get rows splatted back to you and you can sort them in the application. Right? That’s what all the smart people say to do.

This third query where, again, result sets were a bit scrambled. We also get a pretty lousy cardinality estimate on this one of 852 rows. But we can see very clearly from the results that we got 1782285.

So 1.78 million back from that. It’s a seven digit number, right? Yes.

That many. Seven. 10 minus three. Got nervous there. I thought maybe I was missing a finger or something. Anyway. So again, we get the hash match aggregate back. We get the hash match operator in this query plan.

So this one, again, was another one that came back with the sort of scrambled results because there’s no ordering to what comes out of a hash match. The third query, which uses both, or sorry, the fourth query plan, which uses a local variable for both upvotes and downvotes, gets, again, a pretty astoundingly bad cardinality estimate of 45 rows out of 1.78 million. Things fall apart a little bit more here.

Sorry, I don’t know why this thing keeps jumping around like that. I’m going to have to highlight. Just the magic of highlighting for context. The sort spills a little bit here, and this thing ends up taking around about three seconds. So even when we use both local variables, that’s what happens in this fourth one.

Things are obviously at their worst because we get this sort of lousy cardinality estimate for both of the local variables. But even for when we mix and match or mismatch or whatever, however you want to talk about, we still get pretty bad cardinality estimates. We don’t get very good query plans.

The top plan, even though it’s a clustered index scan, makes the most sense here because we don’t have to do a bunch of lookups to get a bunch of other rows. And especially because the cardinality estimates for all of these lookup plans are so low. We also have relatively low subtree costs.

So this one, SQL Server thinks this one’s going to cost about $1.50. SQL Server thinks this one is going to cost about $2.8 query bucks. And we get like just low costs on all of these.

So SQL Server is not even choosing like a parallel lookup plan, which probably would have helped here. But again, is that really what you want all the time? Maybe, maybe not. Probably a little bit more than we can talk about here.

Anyway. Yeah. So using local variables often results in pretty bad cardinality estimates. Sometimes you will not notice them because other parameters or other arguments might filter, either filter out enough rows so that a bad plan choice might not matter.

Or you might just get like you might just be working with a generally small set, small number of rows anyway. So you don’t really have all the problems that you run into when you start working with a larger number of rows. One small thing that I want to point out here before I sign off is that when you use local variables, you also break a new optimizer feature in SQL Server 2022 called the parameter sensitive plan optimization.

And I’ll show you real quick what I mean by that. All three of the queries that have at least one local variable involved just sort of end where the query text ends. This top query plan, return control ever comes back to me, has a little bit more stuff in it, right?

We can see all this plan per value object ID, blah, blah, query variant ID. If we click on this little ellipsis over here and we get all this stuff back, we’ll see what SQL, this is what SQL Server injects into the query text to enable multiple plans for the same query so that we get, we get a better optimized plan for different sets of parameters that have different cardinality estimates involved with them. But using a local variable, even just one of them does break the parameter sensitive plan optimization.

So just something to be aware of there. If you’re using SQL Server 2022 and you expect this magical parameter sensitive thing to kick in and fix all of your bad parameter sniffing queries, using local variables will mess that up. So please don’t do that.

Please don’t fix parameter sniffing by using local variables and then expect the parameter sensitive plan optimization to kick in and do any more work. For you, it just won’t do that. It does not, does not work that way. Anyway, I think that’s about good for today.

It’s about 15 minutes on this thing, which is about five minutes more than I was aiming for. So, yeah, I don’t know. I’m just gonna, I’m gonna go, I’m gonna go now and think about my life. Think about why I talked for five minutes more than I should have.

Hopefully you didn’t find it to be a waste of time. But anyway, thank you for watching. I hope you learned something. And I will see you in the next video. I’ve got a bunch of stuff lined up to record. So, yeehaw.

Hopefully that’s… H

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.

Mediation

The really annoying thing about foreign keys is that you can’t do anything to affect the query plans SQL Server uses to enforce them.

My good AND dear friend Forrest ran into an issue with them that I’ve seen played out at dozens of client sites. Sometimes there are actually foreign keys that don’t have indexes to help them, but even when there are, SQL Server’s optimizer doesn’t always listen to reason, or other cardinality estimation issues are at play. Some can be fixed, and some can’t.

When they can’t, sometimes implementing triggers which are more under your control can be used in place of foreign keys.

The following code isn’t totally suitable for production, but is good enough to illustrate examples.

If you need production-ready code, hit the link at the end of the post to schedule a sales call.

Setup

I’m going to use some slightly modified code from Forrest’s post linked above, since that’s what I started with to see if the foreign key issue still presents in SQL Server 2022 under compatibility level 160.

It does! So. Progress, right? Wrong.

CREATE TABLE
    dbo.p
(
    id int
       PRIMARY KEY,
    a char(1),
    d datetime DEFAULT SYSDATETIME()
);

CREATE TABLE
    dbo.c
(
    id int
       IDENTITY
       PRIMARY KEY,
    a varchar(5),
    d datetime DEFAULT SYSDATETIME(),
    pid int,
        INDEX nc NONCLUSTERED(pid)
);

INSERT
    dbo.p WITH(TABLOCKX)
(
    id,
    a
)
SELECT TOP (1000000)
    ROW_NUMBER() OVER
    (
        ORDER BY
            (SELECT 1/0)
    ),
    CHAR(m.severity + 50)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2;

INSERT
    dbo.c WITH(TABLOCKX)
(
    a,
    pid
)
SELECT TOP (1100000)
    REPLICATE
    (
        CHAR(m.severity + 50),
        5
    ),
    ROW_NUMBER() OVER
    (
        ORDER BY
            (SELECT 1/0)
    ) % 1000000 + 1
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2;

For Fun

With the boring stuff out of the way, let’s skip to how to set up triggers to replace foreign keys.

For reference, this is the foreign key definition that we want to replicate:

ALTER TABLE
    dbo.c
ADD CONSTRAINT
    fk_d_up
FOREIGN KEY 
    (pid)
REFERENCES
    dbo.p (id)
ON DELETE CASCADE
ON UPDATE CASCADE;

While it’s a bit contrived to have an update cascade from what would be an identity value for most folks, we’re gonna run with it here for completeness.

Here at Darling Data we care about completion.

Inserts

I’m a big fan of instead of insert triggers for this, because they take a shortcut around all the potential performance ramifications of letting a thing happen and checking it afterwards.

Now, you’re going to notice that this trigger silently discards rows that would have violated the foreign key. And you’re totally right! But it’s up to you to decide how you want to handle that.

• Ignore them completely
• Log them to a table
• Correct mismatches via lookup tables

Anyway, here’s the basic trigger I’d use, with hints included to illustrate the points from above about you getting control of plan choices.

CREATE OR ALTER TRIGGER
    dbo.instead_insert
ON
    dbo.c
INSTEAD OF INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0
    BEGIN
        RETURN;
    END;

    SET NOCOUNT ON;

    INSERT
        dbo.c
    (
        a,
        pid
    )
    SELECT
        i.a,
        i.pid
    FROM Inserted AS i
    WHERE EXISTS
    (
        SELECT
            1/0
        FROM dbo.p AS p WITH(FORCESEEK)
        WHERE p.id = i.id
    )
    OPTION(LOOP JOIN);
END;

Updates

To manage updates, here’s the trigger I’d use:

CREATE OR ALTER TRIGGER
    dbo.after_update
ON
    dbo.p
AFTER UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0
    BEGIN
        RETURN;
    END;

    SET NOCOUNT ON;

    UPDATE c
        SET
           c.pid = i.id
    FROM dbo.c AS c WITH(FORCESEEK)
    JOIN Inserted AS i
      ON i.id = c.id
    OPTION(LOOP JOIN);
END;

Deletes

To manage deletes, here’s the trigger I’d use:

CREATE OR ALTER TRIGGER
    dbo.after_delete
ON
    dbo.p
AFTER DELETE
AS
BEGIN
    IF @@ROWCOUNT = 0
    BEGIN
        RETURN;
    END;

    SET NOCOUNT ON;

    DELETE c
    FROM dbo.c AS c WITH(FORCESEEK)
    JOIN Inserted AS i
      ON i.id = c.id
    OPTION(LOOP JOIN);
END;

Common Notes

All of these triggers have some things in common:

• They start by checking to see if any rows actually changed before moving on
• The SET NOCOUNT ON happens after this, because it will interfere with @@ROWCOUNT
• You may need to use the serializable isolation level to fully protect things, which cascading foreign keys use implicitly
• We don’t need to SET XACT ABORT ON because it’s implicitly used by triggers anyway

Thanks for reading!

Going Further

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

Patternistic

I see this mistake quite a bit! Golly do I. And then I cry and drink and drink and cry and why do you people insist on ruining my makeup?

Most of the time, it’s some query that looks like this:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.OwnerUserId LIKE '2265%'

The cruddy thing here is that… SQL Server doesn’t handle this well.

Even with this index on OwnerUserId, bad things happen in the form of a full scan instead of a seek, because of the implicit conversion function.

CREATE INDEX
    p0
ON dbo.Posts
(
    OwnerUserId
);

Because the OwnerUserId column has to be converted to varchar(12) to accommodate the wildcard search, we’re unable to directly seek to data we care about.

Many Other Wrongs

As if LIKE weren’t bad enough, I often see other patterns that attempt to mimic the behavior with more typing involved:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE LEFT(p.OwnerUserId, 4) = '2265'; 

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE SUBSTRING
      (
          CONVERT(varchar(12), 
          p.OwnerUserId), 
          0, 
          5
      ) = '2265'; 

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE PATINDEX
      (
          '2265%', 
          CONVERT(varchar(12), p.OwnerUserId)
      ) > 0;

And even this, which doesn’t even work correctly because it’s more like saying ‘%2265%’.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE CHARINDEX('2265', p.OwnerUserId) > 0;

These will have all the same problems as the original LIKE search pattern shown above, where we get a scan instead of a seek and a conversion of the OwnerUserId column from an integer to a varchar(12).

Is there a way to fix this? You betcha.

Computered

Adding a computed column does the job for us:

ALTER TABLE 
    dbo.Posts 
ADD OwnerUserId_varchar 
AS  CONVERT(varchar(12), OwnerUserId);

CREATE INDEX 
    pv 
ON dbo.Posts
(
    OwnerUserId_varchar
);

And without changing the original LIKE query, the optimizer will match it with our indexed computed column.

Note that none of the other attempts at rewrites using left, substring, or patindex will automatically match with new indexed computed column.

Thanks for reading!

Going Further

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