Tag: Query Tuning

YouTube Days: Predicates In SQL Server Query Plans

Video Summary

In this video, I dive into the world of predicates and how they impact query performance in SQL Server execution plans. Starting off with a lighthearted anecdote about adjusting my microphone settings after receiving feedback on audio volume, I transition to a more technical discussion. I explore different types of predicates, such as seeking directly to primary key values for efficient index usage, versus scanning entire tables when indexes are absent or not well-suited. The video delves into the nuances of equality and inequality predicates, demonstrating how SQL Server handles seek and residual operations in various scenarios. By walking through practical examples and execution plans, I aim to provide a clear understanding of why certain queries perform better than others and offer insights on designing effective indexes that align with query patterns.

Full Transcript

Erik Darling here with Erik Darling Data, fresh off reading some Streamlabs OBS tutorials about how to make microphones sound better in Streamlabs. As someone who spends much of his time wondering why no one does basic Google searches before writing a SQL query to figure out what might be a good, better, or best way to write the query, it didn’t occur to me until today to see if there were any nice, helpful blog posts about how to make Streamlabs microphones sound better and all that. Not that it sounded bad, it was just low. I got some complaints yesterday aside from people who were wrong about commas going first in lists of columns in select query, well any query really.

I got a slight neg from probably some spy at Beargut magazine saying that my audio volume was a little low. So I’ve cranked the gain up, I apologize if anyone goes deaf listening to me. I’ve done my best to screen out breathing noises and other uncomfortable mouth sounds that drive people with misophonia up walls, so hopefully that works out too.

So we’ll find out. Anyway, I am here today live in the flesh, in the present, to talk about different sorts of predicates that you might see evaluated in queries like seeks and selects and residuals and other things like that. When you look at your SQL Server execution plans, the majority of the queries that you’re going to see today are all going to have this little one equals select one tacked to the end of them like so.

And I’ve got a, what is hopefully a helpful blog post about why that’s there here, that will be in the show notes and all that good stuff. So anyway, let’s get going. So first, probably the easiest thing to start with is seeking to a value in your primary key slash clustered index.

If you’re one of those people who has a clustered index and a non-clustered primary key, you might see different results. I’m not saying that’s a bad thing. This is not quite commas first in the realm of bad ideas, but it’s certainly not a typical pattern that you see explored in databases, at least in SQL Server databases, perhaps on my learning path with other database platforms.

I’ll find out differently. But anyway, if we run this query and we look at the execution plan, we are going to see what is, at least I hope, this late day and age in our lives, a clustered index seek.

And we have our seek predicate down the bottom there. I’m working on my weather meteorologist pointing skills here. That’s a little funny because everything is backwards.

This is my left hand even though it appears on my right. Magic, how the magic happens, how the sausage gets made. But you can see that we seek directly to where id equals one, which is exactly what our query asked for.

No bugs in SQL Server at all for that. What happens if we have a where clause where we don’t have an index on the column that we’re searching, in this case this reputation column right here, is we are going to scan, in this case, the clustered primary key of the table and evaluate that predicate.

We don’t have an index that helps us find data. SQL Server is asking us, begging us for an index that would help that. But we don’t have one at the moment.

We’ll get around to that in a moment, I promise. But if we look at the, come on back tool tip. If you look at the tool tip for this now, we have a predicate right up there. Point D weather man.

Where we scan the entire table and as we’re reading data pages, we are filtering the reputation column to find where anyone has a reputation of 56106. So we do have to scan the whole table. That doesn’t take very long in the context of this query.

It’s not a very big table and my computer is pretty good. So we get through all those rows in about 258 milliseconds there, give or take some timing there, drawing up query plans and whatnot. But then if we search an indexed column, right, like the ID column again, it’s a clustered primary key, and also search the reputation table, we get back to doing pretty okay as far as performance and, you know, being able to seek to certain values goes.

This takes absolutely zero time whatsoever because we seek to like one data page pretty quickly. And if we look at the tool tip for this, we have a seek predicate at the bottom that evaluates the filter on ID, and then a residual predicate. It just says predicate up at the top there, but most people will call it a residual predicate because it’s a predicate that you evaluate after the main predicate.

So it’s kind of stuck on the back end. I think that’s Hollywood talk, right? Get some residuals in there. But we seek to where the ID column equals 77, and once we find any IDs, and this, I mean, it’s a primary key, so it’s unique, so there’s only going to be one of those.

But we seek to where we find 77, and then we evaluate to see if the row or ID 77 also has a reputation of 56106. And in this case, because I have very carefully engineered all of these demos, we do find one row of Mr. Darren Kopp. I don’t know if that’s supposed to be a funny name or not.

It might be a really great joke in German or something. And we can do that with multiple different kinds of searches on the ID column, right? Because it’s the leading column of the index.

It’s, again, the clustered primary key. We can do all sorts of searches against that column and still seek to those values, right? How efficient the seek is is going to depend on how many rows get located. Right now, the users table in the Stack Overflow 2013 database has around 2.4 million rows in it.

So searching for anyone with an ID under 78 is pretty quick. If we were to search for anyone with an ID greater than zero, we would seek, but we would seek to everything in the table. We would read all that stuff, and that’s not a very helpful seek, though it is a seek, and most people would fail job interviews for that.

We would say that seek is good. At least if I was giving the job interview. Watch out!

Anyway, we can get this. It’s pretty efficient. Darren Copp is still the only person who shows up. And we have a pretty close result to the last query.

Our seek predicate way down the bottom. We’re just looking for anything less than 78. And again, our residual predicate, beep, beep, beep, tickle, tickle, tickle that little one.

So we’re going to go to the actual equals sign. Searching for where reputation equals 56106. Now, for some reason, when I’m answering questions about indexes, I get a lot of questions about index design, index theory, like what’s the best index for this?

How’s the best index for that? And really, the best index is one that kind of closely matches the queries that you’re running. It’s not like you can just design an index out of nowhere and be like, good index.

Like you can put the most selective column first, always and forever. But if you’re not searching on that column, it just gets in the way. So to kind of demonstrate that, I’m going to create this index. And this index, which I’m going to talk about once I finish hitting the control and E stuff over there to execute it, has account ID first.

Account ID almost matches the ID column. It’s almost a mirror image of it. There are a few discrepancies, but that’s okay. It’s still a unique column, right? There’s still no duplicate values in there.

So we put a very selective column first, and we put reputation, which is mostly not terribly selective, second. The thing is, this index doesn’t help us run our query. I’m going to run a blast from the past, and we’re going to look at the execution plan, where we’re going to use the unhelper index in this case.

And we’re going to try to search for where reputation equals 56106. And we’re going to use that nonclustered index, but we still scan it, right? We look at this.

We still have to scan the entire index and apply that predicate to reputation. We can’t seek to any values in reputation because that account ID column is in the way. So like we could search for account ID equals something and reputation equals something, and that would get us where we’re going.

But just searching on reputation when we have an index that does not lead with reputation doesn’t really help us out all that much. So I’m going to create an index that is going to be helpful for several queries, and we’re going to look at how useful it is.

So if we force SQL Server to use the primary key, the cluster primary key of the table, and then we also force SQL Server to use the nonclustered index, and I just do that to make it very clear which index is going to be used where and what you should be looking for in them.

If we run these two queries, they’re both going to run pretty quickly in all the zeros of seconds. That’s fast. That’s fast. We don’t have any problems here. Great. Fixed it all.

We’ve already looked at that plan before, but let’s look at the plan now for the index with two key columns, and it has both reputation and ID. Now we actually have two seek predicates that get evaluated.

All right, we have a seek to reputation. We have a seek to ID. So SQL Server can evaluate multiple seek predicates within the same query as long as they are the key of the index. Right? Like we have an index on reputation and an index on ID.

We can seek to all the values and reputation and ID really easily. We can find those very quickly with our where clause. Things change a little bit when you get away from equality predicates and start getting into inequality predicates or range predicates.

So like greater than, less than, greater than, equal to, less than, equal to, not equal to, is not null, is also one of them. And if we look at this query, we get a few more rows back. We don’t just get Darren Kopp back.

We also get the lovely and talented Bob and the hopefully also lovely and talented Rex M. Maybe also funny jokes in German. I’m not entirely sure. But if we look at our index seek now, we have a seek to a reputation is greater than or equal to 56.

And then we don’t have the double seek. Then we’re back to having a residual predicate up there to find where anything is less than or equal to 77. The reason for that is because when you create an index and it starts putting values in order, even if you have duplicate values in that index, if you’re searching for greater than or equal to 56106, what happens is Segal Server might cross multiple boundaries once you find anything after 56106.

So we need a residual predicate on less than or equal to 77 to evaluate everything in that range. We don’t have that result in that perfect equality order. So we have to evaluate a range of values in order to find 77.

Things get a little bit weirder if we throw some more inequality predicates at it. So at this time we are looking for where ID is less than or greater than or equal to 78, 77 and less than 78.

And then where reputation is greater than or equal to 56106 and less than 56107, which by weatherman my way over here we can see that’s 7. All right.

So let’s look at the query plan for this. And this is going to throw you off a little bit maybe if you’re not used to seeing these sort of things. We’re going to go back to seeing two seek predicates down here.

We can see where SQL Server is evaluating the predicate on reputation with the greater than or equal to and then with the ID column looking for anything that is over 77. But then up at the top we have a residual predicate that almost searches for the same thing, but that’s where we throw in the less than 78.

So again, crossing those ranges, crossing those boundaries of values means we have to evaluate residual predicates sort of by scanning data after we seek to data. Things get more challenging when you throw or at SQL Server, especially in join clauses.

Or is very difficult for the optimizer to come up with a good query plan with. Right now the optimizer doesn’t have a way to turn that into like a union or union all type thing where you say, you know, where, you know, this predicate matches or this predicate matches with a union all between them to unify the results.

So we end up with a query plan that looks roundabout like this, which is very strange looking. We have all these filters and constant scans that are producing rows. And SQL Server has to join all those results to a scan of our nonclustered index.

And actually has to scan through it twice because we have to join to this and join to this. Well, I technically, you know, but if we look at this, we have just a predicate now. We don’t seek to anything.

Right? No predicates in there. That’s less than or equal to 77. Greater than or equal to 56106. And if we look at the join, we have outer references listed here, which means it was apply nested loops.

And we took all of the rows that came out of this and we joined it to these conditions down here. Each one of these constant scans is going to represent one side of the OR clause. So you can’t really see it in the query plan, but one of them is going to represent the predicate on ID and one of them is going to represent the predicate on reputation.

So that’s fun. We can apply multiple OR predicates combined with AND predicates as well. And if we do that, we get a pretty similar execution plan.

Right? And it’s going to be kind of the same story here. SQL Server is going to evaluate the range of greater than 77, greater than or equal to 77, less than 78. And then over here, it’s going to evaluate reputation greater than 56106 and less than 56107.

Where we start to run into problems with SQL Server queries is when we start applying functions to columns. Now, if we didn’t have these functions here, we would be able to seek pretty normally to all the values that we want. But because we do have functions wrapped around those columns, we have an index scan again.

And if we look at the details of the index scan, we can see these predicates here on ID and reputation. This comes back to the concept of sargability or being able to search cleanly into an index defined rows. The way that I like to conceptualize this, and this is not like, you know, God’s honest down to the scientific detail truth about how databases work.

But when you apply predicates to an index that can be matched cleanly, SQL Server storage engine can read data pages and apply those predicates pretty easily as it reads them. As soon as you wrap a column in a function like this, you sort of change the layer at which that can happen. The storage engine is way down here and running functions through like the expression service happens way up here.

So SQL Server has to apply the function to the column, which happens up here. It can’t happen down here with the storage engine. It would be cool if it could, but at this point in time, SQL Server doesn’t offer that.

Good stuff, right? This is also sort of true of string columns. One thing I should mention up here is that sometimes if you write predicates that are not sargable enough, like SQL Server won’t even be able to apply them when it scans an index.

You’ll have a separate filter operator that goes and filters out rows after you’ve done a bunch of work on an index. So if we create this index, which this one here leads with, well only has display name as a key column. If we create that and we run these three queries, only one of them is going to be pretty reasonably fast.

The other two are going to be slow because we have to match across a whole bunch of bytes in a row rather than just matching the leading bytes in a row. If we look at this execution plan, or these three execution plans rather, we have an index seek up here, an index scan down here, and then just a little bit cut off another index scan down here.

This index seek has a seek predicate where we look for greater than or equal to Jeff at Wood, little d, and then less than Jeff at Wooie with an uppercase E. The SQL Server forms a range where you can find like the little d and then the big E, right? So like if it ended with a, if Jeff at Wood ended with a big D, and no people out there are going to be laughing at me talking about little d’s and big d’s.

But if we ended with a big D, we would still find Jeff at Wood, and Jeff at Wood, but we don’t find Jeff at Wooie. For the other two queries, we don’t have that seek ability because the other two queries have leading wild cards in the searches. So this one here and this one here, our scans look a little bit different.

We just have regular predicates to look for all the Jeff at Wood, and then we have one down here where we just look for trailing Jeff at Wood. And both of these result in an index scan that takes about two seconds. So when you’re designing queries for indexes, there are a lot of important things to think about.

But mainly, you want to tune indexes for queries. And then you also want to tune queries for indexes. So indexes, great things to, you know, key are, you know, where clause columns, join columns, things like that.

Not if you’re doing, you know, these kind of searches, they’re kind of useless for all that stuff. And then you want to design queries that can use indexes, and you want to avoid things like this because it’s not that you can’t use indexes when you do this. It’s just that you can’t use them as efficiently because you can’t hit them where you store them.

You have to run this function before you can apply those predicates. Anyway, this ended up being a little bit longer than I planned on. I don’t know if I talked slow or something.

And also I got some weird green screen creep happening down here under my arm for some reason. I’m not sure why. Gremlins, ghosts, goblins, ghouls, all sorts of things in there. Anyway, thanks for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope you don’t put commas first in your select list. And I will see you in the very next video. Have a good one. 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.

Join Simplification In SQL Server

Video Summary

In this video, I delve into the fascinating world of joint simplification within SQL Server execution plans. I explore scenarios where joins cannot be simplified due to potential null values and unique key constraints, as well as cases where they can be pruned out entirely when no additional rows are projected from an outer-joined table. The discussion is complemented by detailed walkthroughs of various query examples, demonstrating how the SQL Server optimizer identifies opportunities for simplification based on the query’s structure and requirements. I also share some personal insights about setting up a green screen for video recording, navigating through technical challenges like allergies that can disrupt even the best-laid plans, and the importance of maintaining clear communication with viewers despite any minor distractions.

Full Transcript

Hello! Welcome back! Erik Darling here with Darling Data. I know that you probably can’t tell by looking behind me, but I have a freshly steamed green screen with no weird artifacts, at least at the moment. We’ll see what happens. We’ll see if the lighting in here cooperates. Right now I have basically the equivalent of a full tanning bed blasting light at this thing. So hopefully, aside from some weird artifacts, where my tattoo colors match the color of the green screen chroma key stuff, we can avoid any unpleasantness. Sorry if that’s distracting. Anyway, I wanted to talk a little bit today about joint simplification in SQL Server. I was going to record this yesterday, but I got terribly, horribly sidelined by allergies, which you can probably still hear some remnants of from this absurdly large nose of mine. So, there are of course rules to when things can be simplified. There are a number of other simplification rules and steps outside of joins, but I think joins are probably a pretty interesting one to start with, at least for now. I’m not going to fix that in post. You can just deal with it.

So, one time when joins can’t be simplified, and I’m going to explain simplified as we sort of look at execution plans, is when we, I mean we can just get an estimated plan for this one. Right? So, what happens here is because this is a right outer join, and this could happen in a situation where the optimizer rewrites a query to use a right join rather than a left join. It can reorder and reorganize all the things that it wants. But because we do this, and the results that we get here add nulls to what comes out of the users table, right? This u.id column that we’re selecting.

We get this query plan over here, where we have to touch both the votes table and the users table. SQL Server couldn’t simplify that away because we were going to get additional results back from the users table. So, it added nulls to the output where nulls didn’t exist before. The column that we’re selecting here and the column that we’re joining to here, that’s the primary key. It’s a clustered index of the users table. So, no nulls are allowed to exist in there. Another place where SQL Server is allowed to simplify joins is when we don’t select, we’re not projecting any columns from a table that is outer join to, and we wouldn’t get any duplicated rows.

Now, this is a fake join because this isn’t actually the relationship between posts and users. I’m joining on two unique columns, the id column in both tables, again, primary key clustered index. So, SQL Server knows that there’s not going to be a many-to-many relationship here. If we get the estimated plan for this query, you’ll notice that unlike the query plan for the last query that we looked at, sorry, that jumped around a little bit. And this one we only touch the users table, right? So, that join to the post table is completely taken out of the query optimization steps.

It’s pruned out, as smart people might say. Now, if we look at this query, which is an inner join from the users table to the users table, which I know looks a little weird, but what we’re going to do is run this and get the estimated plan. And SQL Server is actually not free to simplify this one. We touch both tables. The reason why is because we used an inner join, and an inner join might actually eliminate rows. This one’s a little funny because, you know, we’re joining the table to itself on its own primary key.

So, maybe simplification could be a little smarter. I don’t know. I don’t think I like this one very much. But in this example, where we wouldn’t actually eliminate any rows because we have an additional condition here, or rather we’re doing a left outer join here, what we’re going to see is SQL Server only hit the users table once on that one. SQL Server can also apply that to much larger queries.

So, I’m going to run this whole thing. And the only part of this that really, really matters is way down at the bottom. And I do apologize to the greater SQL Server community for having that column up there has a leading comma, but I only do that to make life easier when I need to quote it out. So, you can deal with it just for this one query. And if we get the estimated plan for this one, we get this whole gigantic query plan back.

If we say zoom to fit, you can sort of start to grasp the absolute magnitude of this thing. Every single one of those derived left joins that we do throughout the entire query is part of the query plan. SQL Server hits all of these things. If we change this query just a touch, and again, apologize for the leading comma.

Hopefully no one beats me up at the next conference. We quote that out and we rerun this whole entire thing just like before. And we get the, well, we’re not going to run it, but we are going to get the estimated plan for it. The estimated plan this time around is much simplificationed, simplified, because we only touch the users table this time.

We don’t do all the other joints. We don’t do all the other work. So, SQL Server’s query optimizer, when it’s looking at the query that you send into it, one thing that it’s going to do is try to find things that it doesn’t have to do. The optimizer is lazy, just like me. And if it doesn’t have to do some work because it just doesn’t need to, it’s going to find a way to not do that work.

Query simplification can apply in a lot of places. Part of it would be like contradiction detection, right? So if you have a query that’s like where ID equals one and ID equals two, SQL Server is going to say, well, ID can’t be one and two at the same time. Even if you’re one of those nudge nicks that does like a comma separated list in an ID count, it can’t be one and two simultaneously.

So it would just give you a constant scan and say, guess what? Your query didn’t give me anything. So that’s fun. And I don’t know. I think that’s it. This is sort of another little test run video with the new setup. Make sure audio comes through well.

Make sure video comes through well. Make sure that the green screen is still mostly functioning, except where it looks like I have holes in my arm over here, which is interesting anyway. Well, I don’t know. That’s it. I’m going to go blow my nose. These allergies are awful. Anyway, thank you for watching. I hope you learned something.

I hope you enjoyed yourselves and I will see you soon, soonly in another video, hopefully with my simplified recording setup. 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.

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.