Strange Query Plans With Inequality Predicates In SQL Server
Video Summary
In this video, I delve into a fascinating aspect of SQL Server query plans—inequality predicates and their impact on performance. Specifically, we explore how parameterized queries can lead to strange query plan patterns when dealing with nullable columns. By examining real-world examples and discussing the nuances of index usage, I demonstrate practical solutions such as adding redundant predicates to improve query performance without altering indexes. If you’re facing similar issues or just want to enhance your understanding of SQL Server query optimization, this video is packed with valuable insights that can help streamline your database operations.
Full Transcript
Erik Darling here with Darling Data, and in this video we are going to examine, like, I guess it’s all on the next slide, isn’t it? Strange query plans with inequality predicates. And by inequality predicates I mean not equal to, whether it’s the exclamation point equal sign or the two opposing type characters there. because I’ve run into this, you know, I’ve reached my cost threshold for talking about it publicly, working with clients on a few things. And there are a few things about this that I think are interesting and that I hope can help you with your query tuning life because you can’t always change the indexes, right? So we’re going to talk about this. There are some things that go along with it, like, nullable columns, and parameters and parameters and stuff, but there’s only so much you can put in the title before it just becomes, like, an article unto itself. But before we do that, let’s talk about you and me and my friend Moolah.
So, if you would like to support my endeavors to bring you the very highest quality, most interesting, incisive, probably the most important SQL Server information available anywhere in the world, you can visit the link in the video description below and sign up for a membership. And for as few as $4 a month, you can keep my beard nicely aligned because, you know, the razors are expensive these days. I don’t know if you’ve bought razors lately. Man, more expensive than eggs. It’s insane. If you have spent all your money on eggs and razors or if you’re over there shaving eggs or whatever it is you do with your free time and you have run out of money, you can do other things to help this channel move along in the world.
You can like, you can comment, you can subscribe. We are up over, let me actually get a current tally of things here. I’m going to look at my YouTube app here. We have about 6,200 subscribers and about 60 paying members. So, we are reaching nearly like a 1% status there. Pretty good. Pretty good. Pretty good.
If you would like to ask a question that I will, privately, that I will answer publicly, there is also a link down in the video description to do so. There is a little Google form. You type in your question and then I answer it during office hours. I do them five at a time, which is, I don’t know, just seem like a nice number at the time.
Maybe I will change that if people are, for some reason, five-a-phobic or something. If you need help with SQL Server in a way that asking office hours questions or poking around the internet isn’t doing you much good for, I am a consultant with reasonable rates and I do all of this work with SQL Server quite effectively and quite efficiently.
And, you know, you’d have a hard time finding a better deal on performance tuning SQL Server. At least, if you’re trying to avoid just some numbskull who’s going to look at missing index requests and tell you to add them. Because that ain’t my game.
If you would like some equally reasonably priced training content, you can get all 24, 25 hours of mine at the beginner, intermediate, and expert. Not just advanced, but expert level. For about $150 USD and that is good for the rest of your life.
It just keeps going as long as you keep going. So, stay healthy out there. SQL Saturday, New York City, 2025, May the 10th, Times Square, Microsoft offices. It’s going to be a hoot.
It’s going to be a real hoot. With that out of the way, let’s talk about the subject of today’s video. Now, in order to sort of show you where this gets interesting, I have created two indexes on the post table.
One on a column called parent ID and one on the column called owner user ID. And you’ll notice a couple little red squiggles here, which means I was kind enough to create these indexes ahead of time. And if I run this query with a couple literal values, we get a very sane and rational query plan using both of those indexes.
We have an index seek into P1 and we have an index seek into P0. And when we seek into these indexes, we very efficiently evaluate. Oh, come on, tooltip, stick with me here.
This predicate, the equality predicate on owner user ID. And we seek into this index and we find where the parent ID is greater than 0 and less than 0. Or some combination there.
Both greater than and less than 0. So, not equal to 0. And that all looks pretty good. Now, there is a missing index request here. And if you are able to create composite indexes or change indexes on your server and do all that stuff, great.
We’ll talk about that in a moment. But what gets interesting is when you take a query like that and you parameterize it. So, now I have the exact same query set up.
And this would be the same with the store procedure. This is no different than using the store procedure here. But when I run the query like this, where both of these parameters have the same value, 0 and 22656. And they are the same definition in here.
And they are used the same in here. The query plan takes on a rather strange shape. Look what happens. Now, we have all this additional stuff in our query plan.
We have some constant scans. We have some concatenation. We have some top-end sorting. We have some merge intervaling.
And then we have a nested loops join to the P0 index on the table. And, of course, the P0 index is where we are looking to do our seek on parent ID, that inequality predicate. What’s particularly, let’s say, a bit icky about this one is that this whole thing is in a serial zone.
You’ll notice that SQL Server steps out of the serial zone immediately after doing that and distributes the streams parallelly to the rest of the parallel zone in the query plan. But this is where we spend the majority of the time. This thing runs for about two seconds total.
And we spend 1.7 seconds in this section right here, between the 1.4 seconds there and the 1.8 seconds there, isn’t it? Pretty close. Now, this is because SQL Server has to do some additional protections in case you ever pass in what might be a null here.
It doesn’t have to do that when you have a literal value. Part of why this has to happen is if we hover over, and I’m going to show you what happens when you flip these in a second. But both the owner user ID column, you can see that is nullable there, and the parent ID column, you can see that is nullable there.
And if we were to switch these around, and I’m not saying that this is the correct query, but if we best show you what I mean. I’m going to hit the insert key there. We don’t want that.
If I switch these around so owner user ID is an inequality predicate and parent ID is an equality predicate, the exact opposite will happen. All right. This will run for roundabout the same amount of time.
Well, actually, a little bit longer there, 3.8 seconds. Hoo-wee. But now the index seek have switched places, right? Now the index seek up here for the equality predicate is on parent ID, and the index seek down here for owner user ID is this is where things get all weird, right?
So this is the strange part of the query plan now. But let’s focus on the original form of the query, right? So this is limited to the inequality predicate with a parameterized query with a nullable column.
Now, what you can do if you want to fix this without changing any indexes is add a sort of redundant predicate here and say, and P, that’s supposed to be a dot, and the dot didn’t come through. Parent ID is not null. And if we add this in alongside our inequality predicate, all of a sudden SQL Server has a whole lot less to worry about.
And we get just about the same query performance that we were getting before, right? So this plan looks just about the same. We have an index seek.
We have an index seek. And this all takes just about 550 milliseconds, which ain’t bad at all. Now, I’m going to quote this out for a second. And I’m going to create the composite index that SQL Server was requesting on the post table.
So that’s leading on owner user ID with parent ID as a secondary key column. That’ll create in a second there. And what I’m going to do is just show you that even, like, this does help the performance generally.
But you still get the weird query plan when you don’t have the not null check on the parent ID column for the inequality predicate. So if we run this, this query will run very reasonably fast. But we still have all this weird stuff in there, right?
We still have the constant scan, the concatenation, the top end sort, the merge interval, and the index seek down here, which we can, of course, get rid of if we keep the semi-redundant predicate on parent ID not being null. And we can get a much nicer, neater execution plan when we tell SQL Server to discard any nulls that might exist in that column.
Now, the kind of funny thing here is that in the owner user ID column and the parent ID column, actually, neither one of these actually has any nulls in it. But SQL Server does still have to protect itself because it has to create a query plan.
Because what if some nulls show up? Sure, there are no modifications right now. But what if, like, three seconds later, I insert a null value in there, and all of a sudden, SQL Server has to figure out some way to cope with that?
So if you have inequality predicates in your query plans, even if they are rather quick query plans, but you have all of that, you start seeing weird stuff with the query plan pattern that I showed you before, where you have the constant scan, concatenation, top end sort, merge interval thing going on there.
All it takes is the redundant predicate to weed all this stuff out. Sometimes that is just a useful thing to do to cut down on query plan weirdness, because you never know who’s going to be looking at these query plans and getting very confused by things.
They might see all that stuff happen and say, wow, I have no idea what all that is. I don’t know. I have no clue.
So it’s just a nice formal thing to do to get rid of it with a redundant predicate and say, let’s reject those nulls out of hand, and let’s just have a nice simple index seek.
So if you are having performance problems with this type of query, that’s one way to fix it. Of course, the composite index is another way to fix it. So, you know, you might want to mine that a little bit.
And of course, if you need help with this sort of thing in real life, and you just can’t figure any of this stuff out on your own, well, my rates are reasonable.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that we will meet again soon in the next video. All right.
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.
Things I Wish Inline Table Valued Functions Helped With In SQL Server
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
All About SQL Server Stored Procedures: Correct Dynamic SQL Usage
Video Summary
In this video, I delve into the correct usage and safe implementation of dynamic SQL in SQL Server, covering everything from parameterization to object name protection. I discuss why `sp_executesql` is essential for safely executing dynamic SQL, highlighting its importance over `EXEC` when dealing with linked servers. Additionally, I explain how using `quote_name` can significantly enhance the security and reliability of your dynamic SQL by protecting against SQL injection via object names. The video also explores practical examples and best practices, such as handling Unicode characters, managing string lengths with `quote_name`, and ensuring proper concatenation to avoid truncations. By the end, you’ll have a clearer understanding of how to write safer, more efficient dynamic SQL that can withstand unexpected challenges.
Full Transcript
Erik Darling here with Darling Data, and we’ve got a wonderful video for you today. And this is going to be all about the correct usage of Dynamic SQL. Slides messed up, zoom is messed up, you click once, and da-da-da. Anyway, so this isn’t going to be the performance part of it. This is going to be how you write it correctly, safely, and some neat tricks you can do with it, and some gotchas around Dynamic SQL that you need to keep in mind when you’re writing it. So the performance one’s going to be next, I guess, because I don’t think I have a choice. I think that is just the forward arrow of time dictating to us when things will happen. But, man, there’s like a five second delay between when I say slide and this thing. slides. If you like this content, if you’re enjoying, I don’t know, maybe this series on store procedures, or maybe if you just like things around here generally, you have all sorts of ways available to you to keep this show going. You can sign up for a membership for as few as $4 a month. And you can say, good job, Erik. I appreciate you. You can like, you can comment, you can subscribe. Other ways to get some numbers increasing for the channel, always good to see upward trajectory with things. And if you want to ask me a question about SQL Server performance, or I don’t know, if anything else strikes your fancy, I guess you can ask. I can’t promise I’ll have a good answer, though. At this point, the entirety of my brain is wrapped around SQL Server performance stuff. So things outside of that, unless it’s about like, something in the gym.
I don’t know if I would have a terribly good answer for you. I could probably answer some questions about like, wine, scotch. Maybe some food stuff. But yeah, I don’t know. I’m pretty, I’m pretty dumb otherwise. That’s why they call me an E-core. Not a P-core. If you need help with your SQL Server. If you have reached the limits of your patience with some SQL Server performance issue, and you would like me to help you out, you can do that. And I can do all of this stuff. And more. And as always, my rates are reasonable. If you would like to get some training from me, wow, boy, wouldn’t that be nice. You can get all of mine for about 150 USD with that discount code.
Fully assembled down yonder in the video description as well. A SQL Saturday, New York City, May the 10th, 2025 with a performance pre-con by Andreas Walter on May the 9th. So that’s Friday. Saturday is the 10th. I’ll be there, of course. As an organizer, I won’t be speaking there, but I will be taking care of all sorts of things, wandering around the halls.
If I get a break, maybe I’ll sit at a table and just answer questions. I don’t know. Just don’t talk to me while I’m eating. I might bite you. Anyway, let’s talk about safe dynamic SQL use. Now, I spent a lot of time in my… Actually, let’s make sure I ran these things first. I don’t want to get caught flat-footed late and be like, oh, look at the dumb thing I did.
It happens enough in my life. I spent a lot of time talking about dynamic SQL and different reasons you would want to use it. In this video, we’re going to talk about some of the finer points of using it that make using it safer, easier, better, all that other stuff. I wish Microsoft would give us some sort of dynamic SQL data type or template thing where it would not require as much fuss to get dynamic SQL correct.
It would be just a far easier, far safer, far more approachable method of dynamically generating a string for execution. But, you know, instead we get fabric and ledger tables and big data clusters and such junk, such absolute junk, terrible products that who cares? All right, let’s not waste any more time on them. Anyway, first things first.
When you’re executing dynamic SQL, unless you need to do exec at a linked server, which I’m not going to be talking about here because, God, linked servers, you know… It’s funny to see how many questions people ask about linked servers because you’re just like… Would you give it up already? Would you just stop with the linked servers?
So unless you need to do exec at a linked server, you want to be using sp-execute-sql because that’s the only way that you can parameterize dynamic SQL safely. There are… In the performance section, we’ll talk about this stuff, but there are a few limited use cases where somewhat unsafe dynamic SQL would be the name of the game. In some cases, for use with very specific things that disagree with parameters, like filtered indexes.
But we’ll talk about that later. When you’re using sp-execute-sql, all of the arguments that you pass to… Well, two of the arguments that you pass to sp-execute-sql do need to be nvarkar.
So in this case, I have one incorrect, that one, and one correct, that one. And if I try to run this, we will get an error. And SQL Server will say, This procedure expects parameter statement of type n-text, n-char, n-var, n-car, n-var, n-char, n-var-char.
n-car, care, whatever. They’re not characters. They’re characters.
n-var-car, not char. They’re not characters either. It’s like vegetarians, right? If they were like vegans, they’d be vegetarians.
But they’re not. If we try to do this the wrong way here, we will get a different error, right? So here we have this one correct with n-var, n-var-car.
And this one we have incorrect with varkar. And if we try to run this one, of course, SQL Server will throw a different error and say, The procedure expects parameter params of type n-text, n-car, n-var-car.
So of course, neither of these work. But these are the rigorous demands of a very strongly typed language like SQL. I’m kidding.
So we get errors from that. But if we do everything correct and we have our strings set up correctly with these, then we will execute flawlessly. And this is how you want to do it when you’re writing it.
Besides, you never know when something Unicode-y or something outside of the standard character set might sneak in to one of the strings that you’re building. And when it does, I’ve seen all sorts of cases where database names, table names, something else ended up with a Unicode character in them.
And you will get just question marks. You will get one or more question marks depending on how many bytes your special character consumes. All right.
So that’s not good. You don’t want question marks showing up in there when you should have a valid character in there. The other thing is that when you are dealing with dynamic SQL, you always want to use quote name to protect your object names.
We’re going to talk about limitations of it, but quote name protects you from SQL injection via object name when just square brackets won’t. Now, square brackets do do something, right?
But they don’t do the full thing. Because quote name will actually double bracket stuff when it should, where just using square brackets won’t. So if I get rid of this table and then I run these two sections of code, the first one is…
Oh, boy. I did that all wonky, didn’t I? Look at that. Oh, Eric. Oh, you’re slipping. Slipping and tripping. Plus signs go at the end of the line, not at the beginning of the line. That’s just as bad as the leading comma.
So what I’m going to do is run this where I’m using the square brackets to enclose the string here. And then in the second one, I’m going to use quote name to enclose the string. And I think what’s interesting for both of these is that neither one of them actually does something, right?
Like they both throw an error, right? To not find store procedure print one. They both throw that error.
But in the case of the first one, the table actually does get dropped, right? Because when we try to… What do you call it?
When we try to select from it, we get invalid object name T down here, where that’s something we don’t get down here. So up in the results, we can see that we get an ID 2. And that’s from this second one, right?
That’s from… Jeez, I did not hit format on the script before I ran it. From the second batch where we inserted the value of 2 into it, right? We inserted a 1 up here.
For the first one, we didn’t get anything back. We just got an error saying invalid object name. And then we did this next one. And in this one, like quote name protected us from dropping the table in the dynamic SQL. So like quote name really does make your dynamic SQL safer all around.
So if you’re planning on accepting some combination, one kind of note up here. Sysname is the best data type to use generally for objects in SQL Server. That’ll keep you from messing a lot of stuff up.
But if you are planning on accepting fully qualified things like schema.object, database.schema.object, or database.schema.object, you do need slightly longer in VARCHAR strings because quote name has a limit. And we’ll talk about that in a minute.
Anyway, one reason why you need the bracket things in general are when SQL Server hits things that it can’t identify easily. So if I try to run this and create this clown table, SQL Server will say incorrect syntax near that. All right.
But if I say create table clown in brackets, I can do that. Where this gets interesting for dynamic SQL is that even if we use all the correct data types and we do everything the way we ought to, if we end up with this as a string, SQL Server will not be able to use that.
And we will get the same sort of error here with the incorrect syntax near this thing. Right. And that’s not a good time.
If we contrast that with this, where we use quote name on the table name, all of a sudden SQL Server will be able to figure that out just fine and select stuff from it. Right.
So using quote name there gives us that. In this case, you know, you could square bracket. You could just square bracket that, but our goal is to write the safest possible dynamic SQL, not to write just whatever the laziest, clumsiest dynamic SQL we can rethink of on the spot is. Now, one thing that is worth noting, though, is like, and this happens to me a lot in my store procedures, where I’m like, okay, I’ve got this identifier, right?
I’ve got a database server database schema table, whatever. And I stuck it in quote name. But now I want to go figure out if that object actually exists or not before I go try to do anything with it.
Is like none of the system views have object names quoted in them. Right. So like if you do something like this and you say, well, you know, I’m going to be safe and set my parameter or variable to the quoted version of something.
If you need to go look that thing up, you need to either like unquote it or like do like replace or just use parse name. Parse name is a handy built in function that is meant to do that. So like running these two things, what we get is for the one where we didn’t unquote stuff with parse name, we get nothing back.
For the one where we did unquote stuff with parse name, we get a valid result back. So if you’re going to like look at objects in here, you’re going to have, you’re going to want to do that anyway. Quote name, though, is, or we’ll talk about that in a second.
But you can, of course, use, you can, of course, like tell quote name anything to put in for what to use as the identifier. Right. You can like by default, it’s square brackets.
You can use quotes. You can use parentheses. You can use anything you want here as long as it’s like valid, I guess. But this is really only useful for dynamic SQL. Now, like if you have to embed one of these in a string and like you want to say like where like, you know, some object name equals something, you might want to use quote name to make, make your life easier with like the, like the single ticks and stuff.
Right. Because like if you just use that in straight, like, like a normal query, that’ll put, this will put double ticks around this. And there’s no object name called double tick, called tick clown tick.
Right. It’s just, it’s not there. Quote name does have a limitation, though. And that is 258 bytes. And keep in mind that the string, like the lengths that you see here are not characters, they are bytes.
So if we do this, we will get two strings back yelling, ah, at us. This one, you can see, does have the quote on it. I forget if this will get us to the very end.
It does. So we actually get both ends of the quote name. But if we say 129 here, then this will actually just return a null. Right.
So you can’t use quote name on very long strings. You can’t use quote name on anything longer than a single in VARCAR 128. You’ll notice that I have one, I have this defined as 129. It’s all about what you put in the string, though, right?
So if this were like in VARCAR max or something, like it would, it would return null if it were longer than 128 bytes and wouldn’t quote it. So quote name is kind of only useful for single parts of object identifiers. So like server name, database name, schema name, object name, whatever that object is, whether it’s a procedure, table, view, function, whatever.
So just, you know, be careful with quote name because you can have some unexpected disappearing strings in there. Another problem that I run into a lot with SQL Server that is difficult to reproduce reliably is when you’re concatenating dynamic strings together, a lot of the time what you’ll have, like what might happen is, and this is like seemingly completely random. Like there’s just some weird like implicit conversion that happens and your beautiful dynamic string gets truncated.
And this happens a lot when you’re just like putting some smaller portion of a, like tacking some smaller portion of a string onto your longer string. So what I end up having to do in a lot of my procedures is whenever I need to like, like add on something in here, I need to explicitly convert whatever I’m adding on to be another in VARCAR max. So I don’t like, I don’t get the string doesn’t concatenate surprise.
And I’m sitting there like, like trying, like printing it out and like, wait a minute, there’s part of the string missing. What, where did it go? Cause like, like I know print has limits on it and you know, you can run into, you can run into problems there. So, but if you’re like printing sub strings and you’re like, wait a minute, I’m printing this sub string and my string is still disappearing.
You most likely have to convert some like tacked on addition to the string with, with convert. I know that the concat function exists. And some of my procedures do run in versions of SQL Server where the concat function is available.
Like they’re only running that like quickie store. I just don’t use it because, you know, I distribute my scripts as like, as a whole, pretty much like you can get that. We can get like the install all file.
And I don’t want someone on like an older version of SQL Server that doesn’t, maybe doesn’t have concat where some of the store procedures are still valid and will still return results and give you information back. I don’t want those to like error out because of something in a different file. So another thing that’s important with dynamic SQL is formatting.
Most of the time I will, I will write as much of the query out as I can and format it and then paste that into my dynamic SQL string and do whatever other stuff I need to. So all of my dynamic SQL is formatted in exactly the way that I would format a normal query. And I am pretty happy with that because then I have a nice legible dynamic string that I can copy and paste out of here and make my life is a lot easier.
You know, side note on style stuff, whatever you’re writing dynamic SQL, when you can put a comment in the string that tells people what store procedure it comes from. Output is another very, very useful thing that you can do or rather output parameters or output values is a very useful thing that you can do with dynamic SQL. And in this case, you can actually use these things as input and output values.
So this is kind of a neat trick where I’m going to set I equal to zero. I’m going to set E equal to the max database ID. I got my string here, right?
So I got all this stuff lined up and then I’m going to pass. I have the same parameter in here. I and I. So what I’m doing is selecting the top one database ID where the database ID is greater than I. And then down here in a loop, I’m just going to say while I is less than E, well, like I is less than the max database ID, just keep outputting that and running stuff.
And what we can do there is actually just pass that in and out where we’re looking at database one, two, three, four, five, six, seven, eight, nine of nine. So you can use output to drive loops with dynamic SQL, which is pretty neat because then you don’t have to either keep rerunning syntax to find some value. And you don’t have to, in your loop, you don’t have to increment.
You don’t have to remember to increment anything here. It’s almost as cool as a cursor where you don’t have to remember to be like, oh, set I plus equals one or something to go find the next value. It’s especially helpful when you might not have contiguous values.
So like, let’s say I had database IDs one, three, five, seven, nine. I wouldn’t waste time looking for database IDs two, four, six, eight. So you can do neat stuff with output there.
But what I use output for a lot in the context of dynamic SQL, and here’s actually a good example of using quote name with the single ticks to make string quoting within the dynamic string easier. What I end up doing for it a lot is using it to validate the existence of other things and other databases. So what I’m doing here is I’m using the output stuff to figure out if the schema DBO exists in the master database.
And so if I run this, SQL Server will be like, does the output schema exist? Go run this query in the master database context where name equals, oops, where name equals DBO. And then it’ll say, hey, it looks like that schema does exist.
All right. But if I change this to like something stupid, like typing, if I change this to something stupid that obviously doesn’t exist and I go look for it, SQL Server will be, oh, I forgot to highlight the rest of that string.
That’s the important part, isn’t it? If I highlight this, we’ll get an error that says it looks like the schema barf doesn’t exist in that database. Now, like granted, like schema checking is one of those things where you could probably skip over checking to see if DBO exists. Right.
Like, like obviously it’s going to be there. Whether you have permission and access to it might be a different matter, but it’s there. Now, the last thing I want to talk about in this video is when you are accepting any object name, like even using quote name, you know, sometimes you want to be extra safe. Right.
And the way to be extra safe is to never concatenate or even using quote name, put those into whatever dynamic string you’re going to execute. So what I’ll do sometimes if I need to be extra, extra safe is I’ll accept the parameters for whatever in here and then I’ll declare safe local variables for those things here. Right.
And what I’ll do is I will only use the parameter values to look up safe values. Right. So like I’ll go to sys.databases and based on the database ID, like if this is if there’s if this is invalid, like you can’t execute anything in here. It’s not dynamic.
See, well, this isn’t going to execute like some malicious string, but this will just set the safe database name to the name that aligns with the database ID. So we ditto this in here with like the safe schema and table. We just don’t join sys.tables assist.schemas.
And sometimes it’s useful to put like a backup thing in here just in case like but, you know, you don’t obviously don’t have to do that. And then for columns, what I’ll do and I know some of these functions aren’t available in all the every version of SQL Server and every compat level. But this is just sort of a brevity here.
I’m going to use string ag and string split to do this. And what and what this will get you is like a safe list of columns based on whatever someone supplied in the column column names parameter. Right.
So the safe columns get assigned to a comma separated list there. And each one of those each one of those column names gets put gets put in quote name. Right. So that that’s all worked out.
Now, you want to like do some checking, be like, hey, if any of these come back. It’s null. Just say, you know, something was invalid and whatever. Now, one thing with the safe columns and I’ll show you this when we execute stuff.
But it’s kind of neat about the safe columns thing is if someone gets one column wrong, the store procedure won’t error out. It’ll just skip that column. So then, of course, this will be our dynamic SQL where we’re still using quote name on all this stuff just in case there’s anything weird in them.
But like I know we could have said that. We could have said we could have set some of this stuff earlier. We could have said like quote name, whatever, but it doesn’t matter so much.
Then we’ll set that stuff in there. But let’s just make sure this is created and everything is good with this. And then let’s walk through a couple executions of this.
So this with like doing this will return results. Right. This will be fine. Doing this, we get all the results back. If we put in invalid database name and we just put a Z on there, this will say, hey, invalid database.
And if we do the same thing to the schema name, it’ll say invalid table or schema. And if we can see the debas there, right, that’s the incorrect one. And then if we do that for album, let’s say that make that albums.
This will throw the invalid table, like invalid schema or table for albums. But here’s what I was talking about with the column list. And let’s say we just put a Z at the end here.
Now, actually, I’m going to run this once. Let’s make another copy of this. And let me just show you how this is different. If you run these two, notice the track ID column isn’t this one, but the track ID column just gets emitted from this one.
And that’s because when we look for stuff up here, like that track ID just doesn’t make the in clause for this. If we were to change this drastically and we were to do something like maybe only select one column and have it have a Z at the end, then we would get the column list thing here.
Now, if you were like really interested in making this extra like verbose and whatever, you could, of course, like, you know, compare the list of columns passed in to the list of safe columns or the list of columns that you find and be like, hey, this column was invalid.
But maybe look at that. I just didn’t do that here. Anyway, this is about as much about like safe and sound and good dynamic SQL uses I can fit into a video a reasonable length. It’s a lot to remember and a lot to think about.
But hopefully the more you do it, the easier and more intuitive that becomes. So anyway, I hope you enjoyed yourselves. I hope you learned something.
And I will see you in the next video where we will talk about dynamic SQL, like I said before, in the context of performance, which is generally what we care about. But at some point, we also care about tables not getting dropped and data not getting exfiltrated or vandalized and all that good stuff.
So anyway, we’ll do that. And I forget what’s after dynamic SQL, but that’s OK. It’s OK.
Once you know dynamic SQL, what more do you need, really? Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
All About SQL Server Stored Procedures: Wrapper Stored Procedures
Video Summary
In this video, I delve into the world of wrapper stored procedures and how they can help improve performance in SQL Server. Specifically, we focus on transforming local variables into more performant parameters, preventing code from compiling when it’s not used in an if branch, and dealing with parameter sniffing issues by generating different query plans. While wrapper stored procedures offer some benefits, I also discuss their limitations, particularly the maintenance overhead they can introduce and how dynamic SQL might be a better solution for complex queries with many optional parameters. By walking through practical examples, I demonstrate how to use wrapper stored procedures effectively to address common performance problems in stored procedures that rely heavily on local variables.
Full Transcript
Erik Darling here with Darling Data, and in today’s video we are going to continue our stored procedure soliloquy, and we are going to talk about how to use wrapper stored procedures to improve performance. Now, there are a couple topics in here that I am not going to cover because I’m going to cover them when we start talking about temporary objects in stored procedures. particularly around unique naming for temp tables, pound sign temp tables in stored procedures, and also the sort of like creating a temp table in one procedure and then referencing it in another procedure. So we’re going to do that stuff. And in a different video, in this one, we are going to stay focused on how wrapper stored procedures can help you with certain performance issues, particularly ones around the old local variables in stored procedure code. So, if this thing will kindly progress to the next slide. Thank you. Slide, please. If you would like to support this grand content that I produce for you, you are free to click the little link in the video description that says join, and become a full-fledged paying member. I’m going to have a little surprise for paying members coming up there. A fun little thing to test out. And for as few as $4 a month, you can help me stay motivated to do these things.
I don’t mean to threaten you too much here. If you are just shy of $4, nothing good going for you. I don’t know. I don’t know what’s wrong with your life. You can do other things to support this channel like like and comment and subscribe. I’ve noticed that a few videos lately, which is rare for me, have gotten a thumbs down. So if you are planning on leaving a thumbs down for some reason, please do leave a little note as to what did not meet expectations. It is difficult for me to improve if all you do is say, boo, if there’s a good reason for it. Please do let me know what it is.
Even if it’s just me. Even if it’s just, I hate you. I hate you and I want you to die. You can say that. It’s fine. I have very thick skin. If you would like to ask a question that I will answer on my Office Hours episodes, that link is also available in the video description. If you click on that, you will be taken to a page with some information and a link to a form where you can plop your question in there.
I have a few episodes of those recorded and scheduled out since I answer five at a time. You’ve been kind enough to grace me with many questions in there for me to answer. If you need help with your SQL Server beyond what you think an anonymous question answered on YouTube can provide you with, I am available as a consultant with reasonable rates to do all of the above tasks and more.
Don’t feel limited by this list. There are many other things we can do here. If you would like some also reasonably priced SQL Server performance tuning training, I’ve got a hedge over 24 hours of that. And you can get all of that for about $150 US dollars.
And that is good for the rest of your life. It is not a subscription product. This link is also fully assembled for you down in the video description.
SQL Saturday, New York City. March 10th. May 10th. I don’t know why that always happens to me.
May the 10th of 2025. New York City. Microsoft offices. Times Square. Performance pre-con by Andreas Walter on May 9th. Just be there or be forever square and you’re holding something.
Peace. Anyway, let’s talk about wrapper store procedures. And I’m not talking about…
Actually, that would be a stupid joke. I’m not going to… I’m absolutely not going to make that joke. I’m actually somewhat appalled with myself for even considering making that joke. Anyway, wrapper store procedures are good for all sorts of things.
Like what I’m going to show you today. Transforming declared local variables into much more performant parameters. Preventing code from compiling when it is not used in an if branch.
Which would be a very handy thing in some cases. And also generating different query plans to deal with parameter sniffing. Which is a perfectly good and valid use case.
But it really only works if you are worried about a couple different… Maybe like one or two faulty parameters. Beyond that, the number of store procedures that you need to maintain to deal with that very quickly becomes unwieldy.
And you are much, much better off in the majority of these scenarios… Just using some well-formed dynamic SQL. There is some upside to this, of course, over dynamic SQL.
You know, all the typical stuff around security and permissions. And, you know, if you’re into that sort of thing… You might care very much about this.
I do not. I do not delve into security. I do not delve into permissions. I stay away from that stuff just as much as possible. Because it is incredibly dull.
And frustrating. And annoying. I have… I have… Just… I have enough grievances and annoyances with having to use authenticators for things. Where like…
Not like… Oh, I am never going to use an authenticator. Send me an SSMS. Like I use authenticators for a lot of stuff. It is aggravating because I have like five of them now. And some of them have really long lists of stuff.
And I am like… Well, I can never remember like which thing is in where. And then like… Like scrolling through this long list of crap. And then like… They all do like the yes… The confirmation screen differently.
Where it is like… Like the yes and no will be on different sides for different authenticators. And then like… Some of them just have really confusing logic. Like… Like yes, it is not me.
Or no, it is me. And like… Huh? Should… Which one lets me in? Just give me a green button and a red button. I do not need…
I do not need all this confusing wording. And my authenticator apps life is hard enough. Anyway. Anyway. The sort of downside of store procedure… Using store procedures or…
You know, for… Like… If you have like store procedures that are going to have to maintain duplicative logic. That’s where it kind of sucks just for that thing.
Because now it’s like if you change one, you have to change the other one. And if you have a bunch of them, you have to change a bunch of them. But there is a shared downside of… Well, I mean, not a downside.
Just a little bit of a caveat to either wrapper store procedures or dynamic SQL. Where the resource usage of the underlings, right? Like the inner store procedure or the inner execution of dynamic SQL.
Will all be attributed to the outer store procedure. So like… You might be looking at the plan cache or most likely query store. And you might see a store procedure pop up in there.
And you’re like… Wow. This thing… This really does all that? And then you look at the store procedure. And it calls like other store procedures. Or creates a bunch of dynamic SQL. And that… Like all of that…
Bubbles up to the parent that calls it. And so… You… Like… Like it just becomes like a little bit more… Strenuous to figure out… Like either which of the sub-store procedures.
Or which of the dynamic SQL executions… You know… Caused a problem. Granted, it’s a little bit easier to… Find other store procedure names. In either the plan cache or query store.
As long as their plan cache is sort of reliable. But… I think… You know… With dynamic SQL…
The additional sort of… Additional sort of pain with that is that… There is no parent object associated with it. It is completely headless and detached. Much like…
Microsoft’s implementation of the parameter sensitive plan optimization. Where like… Like there’s like… Like you don’t get like the… The calling procedure name with the plan variant. Which is pretty annoying.
Um… But you know… If performance is… Generally acceptable. This is somewhat less of a concern overall. Uh…
Oh… Hey… Zoom it. First… First wink of the day. Uh… But if performance is okay… Then you generally spend less time on this. Um… Of course the… You know… The classic…
Uh… Solution for dynamic SQL… Is to put a comment… In… The dynamic SQL block… With the store procedure name that calls it. So you can still search the text of stuff for a store procedure name. That’s just a little…
Uh… More CPU intensive than just looking for an object name. But the goal for us is of course better performance. It is not necessarily… Any of… Any of this stuff. So we’re gonna…
We’re gonna not talk about much more of that stuff. But this is kind of my point with… Wrapper store procedures. Right? Like… Like let’s say you… You know…
You do some stuff. And then based on that stuff… You go do some other stuff. Right? Now… Uh… Let’s just say… Let’s just pretend that these are store procedures. And let’s just pretend for a second…
That… Uh… You know… Uh… We… We maintain very similar logic… In these. And all of a sudden… If we need to add some exclusion… Or exception…
Or some other columns… Or some additional join logic… Or filtering logic… Or something… Uh… That… You know… We have to maintain that now across to it. It’s obviously a little bit more… Or work for you. And some more stuff to have to remember.
But… Again… Minor point. Uh… If you have a lot of if-else branches… Uh… You’ll have a lot more store procedures to dig around. Um…
Let’s see… Uh… Did it… Uh… Let’s see… Store procedures aren’t a very good use case for kitchen sink queries… That have a lot of optional parameters. Because again… The number of permutations and different… Combinations of stuff is not going to be fun…
For you to create all of those objects for. Dynamic SQL is the best… Uh… Best deal there. But… For this one… I’m just going to show you real quick…
Uh… How… Uh… Wrapper store procedures can be useful for… Uh… Fixing performance problems with… Uh… Store procedures that use local variables in them. Since that is sort of what led us to this point.
Uh… So we’ve got two indexes on the post table. Uh… One called P0. That is just on the owner user ID column. And I already created these because I didn’t want to make you wait when I did all that.
And one called P1. That is on parent ID, creation date, and last activity date. And includes post type ID. And uh…
What we’re going to do is pretend in here that either… Someone did something like this. Right? And said when parent ID is less than zero… Then set parent ID fix back to zero. Uh…
Or they were just like… They’re just one of those… Ha ha. No parameter sniffing. I’m going to… People. All right? That’s like… Not… Not the brightest bunch typically.
And then… Uh… We’ve got another store procedure down here. Where… Uh… And like we take the… The query that would have used this. Right?
Which is this thing. Uh… And we put that into an inner store procedure. And we have an outer store procedure that still does our little fixer upper here. But then executes the inner store procedure here with the parent ID fix stuff in it.
So… Uh… When you run this… Uh… You are going to of course…
Uh… Use a local variable. Uh… Parent ID is going to get replaced by the local variable in the where clause. And if we… Uh… If we run this…
We are going to be unhappy with the performance results. Uh… Not only are we going to use a… Well… Two things are going to happen. One… We’re going to get a real bad cardinality estimate on parent ID. And two…
Because we get a real bad cardinality estimate on parent ID. We are going to choose a less efficient index. And we are going to choose a less efficient query plan. Um…
See here… Uh… We choose the index P1. Remember this is the one that led with parent ID. So… Because we created an index that leads with parent ID. We have a full scan histogram on parent ID. But because we use a local variable.
SQL Server makes a real real bad guess on how many rows are going to come out of that. And because of that real bad guess. SQL Server cost this plan very very low. Right?
Estimated subtree cost of 0.0192738 query bucks. And we get a really bad serial plan out of this. If this plan went parallel. It would probably be a bit faster.
Because we would have more CPU doing more work here. But that’s not… That’s not really the point. SQL Server just didn’t even come close to a parallel plan on this one. There’s not even like a little like edging you could do to bring that one up.
With this one. This is the store… This is the version of the store procedure that is going to… Call the wrapper store procedure inside of it.
So even with the local variable thing that we do in the outer… Store procedure. Since that gets transmogrified into a parameter when we pass it to this inner store procedure. Right?
That is in the parameter list there. And that is a parameter there. SQL Server is going to do its… It’s like normal cardinality estimation process. It is not going to use that density vector guess that we get from local variables. And of course this will run much faster.
We got a little bit of a funny execution plan there. But not the end of the world. This one does go parallel. This one does seek into some stuff.
And we do… Do a pretty good job of getting a fast enough execution plan there. So…
Local variables… When it comes to performance… Tend to cause more problems than they solve. There is some room for testing in that. I’m happy for you to test things and figure out on your own if there is an appreciable difference with things.
Don’t just test one execution of the store procedure though. Test a bunch of them because you might find things get a little weird. If you’re having a parameter sniffing problem…
What you really want to do to… Like dig into a parameter sniffing problem… Is have two sets of parameters to test. One that creates the plan…
That you want shared by future executions. And then a set of parameters that has a far different distribution than the initial set. So you want the plan to get reused… So you can figure out if the query plan that you’re generating with that initial set is good and shareable amongst others.
If you’re going to test local variables… Don’t just test that. Test everything.
Test the first set. Test the second set. Don’t just test one set. Make sure that whatever parameters you’re testing the local variables with… You have a variety of values that you can put in there…
To make sure that across a large number of executions… You see a significant improvement. That is not just a one and done thing.
That is if you are facing a parameter sniffing issue… When your big idea to fix parameter sniffing… Is to remove parameter sniffing from the equation by using local variables… Then you need to seriously consider…
Finding a number of permutations that cause the parameter sniffing issue in the first place… And making sure that it is significantly better in both cases. You don’t just want to be one of those people who said…
Oh, it seemed to help. A number of phone calls I get on where I see dumb stuff in code… And someone says, it seemed to help. And we’re sitting there staring at some query that runs for like 30 seconds, a minute, more. Like, did it seem to help?
Did it… What did it help exactly? Did it finish a second faster? Like, tell me what it seemed to help. Because we’re on the phone now and it didn’t seem to help anything. We have reached an impasse with it seeming to help.
So, just be careful out there when you’re using them. Make sure that you are testing things thoroughly. And make sure that you see an actual improvement. Look at those actual execution plans.
Because that will tell you… That will give you more information than you just running the query and being like, Yeah, it seemed to help. Because someone like me will sit there and stare at you.
Stare into your soul until you admit you were wrong. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you in the next video where we will talk about some other store procedure stuff. Undoubtedly. Or maybe we’ll take a break from store procedures and talk about something else.
And then come back to store procedures. I haven’t… I cannot see into the future, my friends. I am anything but psychic. Anyway.
Thank you for watching. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Hi Erik! I recently read a post stating that not de-fragmenting your indexes lead to more expensive plans, therefore ignoring index fragmentation Is a bad idea.” What do you think about this? This Is the post I’m referring to: https://sqlperformance.com/2017/12/sql-indexes/impact-fragmentation-plans
How can I get more out of SolarWinds DPA? People like it so much that I really don’t know what I’m missing. What is it great for?
When tables get large and the default sample rate inflates estimates do you generally recommend increasing the sample rate to lower the estimates or doing a full scan and disabling stats updates, or something else?
When viewing a long-running query in sp_whoisactive, how can I retrieve the parameter values of the query? I thought I could get them with @get_plans = 1 and then examining the ParameterCompiledValue in the execution plan, but that is unfortunately the param value for the cached plan that it is using, not the current param value that is causing it to run slow. I’m on SQL Server 2019 and have query store enabled. This would be a huge help, thanks!
If you could only have one watch from your current collection, what would it be? Also, what’s a grail watch that you think about a lot but can’t afford/justify/get ahold of? PS, if you can only answer one, please answer my serious SQL related question. Cheers
In this video, I dive into some interesting questions from the Darling Data community during our Office Hours session. We start by discussing a post from 2017 about index defragmentation and its impact on query plans. I share my thoughts on John Cahias’ perspective, emphasizing the importance of understanding different types of fragmentation and the specific context in which his advice was given. Moving on, we explore how to get more out of SolarWinds DPA, leading to a strong recommendation to switch to SQL Sentry instead due to its superior performance and reliability. The session then delves into statistical sampling rates for large tables, where I highlight the complexities involved and commend those who have successfully resolved such intricate issues. Finally, we tackle retrieving parameter values from long-running queries in SP:who is active, discussing limitations with SQL Server 2019 and suggesting alternative methods like extended events. Throughout the session, I share practical insights and tools that can help address these challenges effectively.
Full Transcript
Erik Darling here with Darling Data. And if you can’t tell by the big smile on my face that it is time for Office Hours, well, you haven’t watched enough Office Hours, or I haven’t recorded enough Office Hours, Hourses yet. But anyway, before we do Office Hours, let’s talk a little bit about my channel. If you would like to support it, you can do that. If not, totally fine. You can do other stuff, like like and comment and subscribe and ask questions on Office Hours. It’s a great deal. If you would like to ask me questions in exchange for actual money, and have me fix stuff in exchange for actual money, my rates are reasonable and we can make arrangements to do all of these things. It’s a wonderful setup being a consultant. If you would like to get some training from me in the long form, in the streaming video variety, not on YouTube, you want to feel real close and intimate with me, you can get all 24 hours of my performance tuning training content for about 150 US dollars. Good for the rest of your life. That link, that discount code again down there in the old video description. You can also catch me handing out lunches and making sure everyone’s happy at SQL Saturday New York City coming up May 10th 2025 at the Microsoft offices in Times Square. If you’ve never been there, I can’t recommend Times Square.
They got a Sbarro, they got a Sbarro, they got a Ruby Tuesday, they got, what’s the other one, I think they still have a Bubba Gump shrimp, shrimp, shrimp boat, house, something. You could also walk a few blocks away and get real food that’s good too. Whatever you’re into. Some people don’t have any real sense of food. So, delete anything. Probably explains a lot. But anyway, let’s answer some questions. Alright. So, the first one up here that we’re going to deal with is…
Hi, Eric. Hi. How’s it going? I recently read a post. You recently read a post from 2017. Good, good, good. Stating that not defragmenting your indexes lead to more expensive plans, therefore ignoring fragmentation is a bad idea. What do you think about this? Well, going by memory, because I don’t want to click on any links here.
Going by memory, that post was written by a very smart fella named John Cahias. And, you know, we’ve got nothing bad to say about John. He’s been a valuable contributor to SQL Server stuff for a very long time. Very smart guy. I hope one can’t say a bad thing about him. I hope he has all the great weekends money can buy.
And I remember that post. And I think you should probably read the comments on that post before you get too married to it. And I have no doubt in my mind that that post stemmed from an actual problem that Mr. Cahias ran into. Where I recall feeling, at the time I read it, that the post fell a little short.
Is in maybe framing the problem a little bit better. You know, I think from remembering like the tables, like the one was highly fragmented because of something with GUIDs and there had like the, the problem wasn’t logical fragmentation.
Like, like the type of like when you’re saying fragmentation and like the post didn’t make a good distinction with the type of fragmentation that was the problem. So like every index script that you would go and run to find fragmentation would still be looking for logical fragmentation that goes for all this stuff that goes for that stupid thing in the tiger tool box, toolkit, whatever. Unless you write a custom script that goes and looks for physical fragmentation, which requires a higher, like, or rather a higher level of detail when you are gathering fragmentation metrics on your indexes than finding logical fragmentation does.
Like when you’re finding logical fragmentation, you can use that, whatever that table valued function is with like limited. And you can find pages that are out of order. That’s not the kind of fragmentation that caused a problem here. The problem that got caused in this one was physical fragmentation.
That was there being a lot of empty space on data pages because of the fragmentation. There was also a thing where, um, I think that the, the two demo queries, uh, neither one of them had a where clause on them. They were just like two group, like it was just a group by of two like string columns from the different tables.
So, um, I don’t, I don’t know how much I’d like, like go with that is like, oh, you must defragment the indexes. Uh, like I’m sure that like that was an actual problem that, you know, Jonathan or someone at SQL, SQL skills ran into that. Jonathan blogged about, but, uh, there there’s, there’s sort of a lot of stuff in there that is very specific to the setup of that.
And not necessarily, I think you could use to take as general advice, uh, about how to like either decide if your queries were, um, having problems, like, or your query plans were changing because of this or not. The other thing that I remember about that post is that, um, the, the query that hit the table with fragmentation got a parallel execution plan and ran like three or four times as fast as the query that hit the, the, the non fragmented, uh, table. And, uh, because the one that hit the non fragmented query was considered cheaper by the optimizer and got a serial execution plan.
In my performance tuning life, the majority of the time where I have had a gripe about, uh, parallel plan choice has mostly been in the other direction. It’s mostly been, man, I really wish SQL Server would choose a parallel plan here. Why the beep is it chewing, choosing a, a serial plan here?
Uh, and me trying to figure out a, um, a supported way to, uh, get SQL Server to choose a parallel execution plan rather than a serial execution plan. Um, that, that there’s probably about a hundred to one ratio. There are times where I’m like, damn it, SQL Server.
Why, like, like, why aren’t you choosing a parallel plan? They’re very much, much smaller number of times have I been like, damn it, SQL Server. Why did you choose a parallel plan instead of a serial plan?
I want to, I want a serial plan for this. Um, of course, when, if you want a serial plan, it’s a whole lot easier to apply a max.1 hint than it is to apply a go parallel hint. Because setting, if you set max.8 to 8, that doesn’t force the query to go to max.8.
That just says you can go up to max.8 if you choose. Right? It is not min.Dop.
It is max.Dop. Uh, and the two ways that you can, uh, for, try to force a parallel plan with trace flag. 86.49 or the enable parallel plan preference, uh, option use hint. They’re both very specifically not supported by SQL Server.
And the fact they say, don’t use these in production. They’re not min. Because who knows? So, um, I’d be a little careful with that one. Uh, read, read, read, read the full post and the comments.
I don’t, not often you’ll hear someone on the internet say, oh, read the comments. But no, go ahead and read the comments. All right. That one is done.
Let’s go on to the next question. Question two of five. How can I get more out of SolarWinds DPA? People like it so much that I really don’t know what I’m missing. What is it great for?
Well, the best way to get the most out of SolarWinds DPA is to uninstall it and then call up SolarWinds and say, hey, I want to get SQL Sentry licenses instead of SolarWinds DPA licenses. Please, for the love of God, give me SQL Sentry licenses. I don’t want DPA anymore.
Uh, DPA is a trash heap of a, of a, of a monitoring tool. I can’t say enough bad things about it. Like every time I’ve tried to use it, it has been endless frustration and annoyance. Uh, avoid it at all costs.
So, there we go. And here we say, do, do, do, do. When tables get large and the default sample rate inflates estimates, do you generally recommend increasing the sample rate to lower the estimates or doing a full scan and disabling stats updates or something else? Well, boy, oh boy.
There’s, there’s, there’s a lot, there’s a lot to think about here, isn’t there? Um, if, if, I think if, if you’re able to, uh, sufficiently isolate the process, you’ve got a problem to, to, to this and you, you’ve come up with a solution.
I mean, what, what, what more do you want me to give you on this? You’ve already, you’ve figured it out already. So, but you’ve, you’ve, you’ve come to a, you’ve come, actually come to a very good point in your career. Congratulations.
Where you were able to look at a, a, a performance problem, uh, identify that the default, uh, auto, like stats update, whether it’s auto stats or like a manual stats update with the default sampling rate was causing a problem. Right. It was inflating estimates for some portion of queries that were hitting the table and it was giving you a bad execution plan.
So, you know what? Good job. Like, great. That’s fantastic.
I don’t like, I don’t have a general recommendation here because things like situations like this are so unique and, um, have so many moving parts that there’s not like a good general piece of advice here. When you’ve gotten to the point where this is, these are the types of issues that are hitting your workload because you’ve cleared up like many of the other, like simpler, more easy to identify things. Then you’ve got to, this is, this is the kind of stuff that you kind of have to figure out based on all the local factors that apply to you.
Um, I will say that I’ve been in situations where certainly I’ve, I’ve had to do full scan stats updates to, uh, prevent, um, cardinality estimation issues of the variety you’re talking about. I’ve been in situations where I’ve disabled auto stats updates because the auto stats updates that happened were not good. Um, on newer versions of SQL Server, you can do something a little bit cooler and you can actually, uh, preserve the, the stats update, uh, percentage.
So you can create statistics or update statistics and you can tell SQL Server every time you update these statistics, you have to use X percentage sampling rate up to a hundred. So you can like control that a little bit better now, but, uh, really congratulations. You’ve solved a weird, hard problem.
Um, but I don’t have general advice on this. I have very specific advice that I would figure out and give based on what’s happening with the server, but you’ve done that. There’s, there’s, I’m not, I’m not going to have anything better because you, my friend have figured out the problem.
Congratulations to you. All right, let’s answer this one. Uh, let’s see.
Ah, there we go. When viewing a long running query in SP who is active, how can I retrieve the parameter values of this query? Uh, so I’m going to assume you mean the runtime parameter values of this, because you’re saying, I thought I could get them with get plans equals one and then examining the parameter compile value in the execution plan. But unfortunately that is the pram value for the cash plan that it is using, not the current param value that is causing it to run slow.
I’m on SQL Server 2019 and have query store enabled. Uh, so you can’t do it with SP who is active unless you’re on SQL Server 2022 or one of the cloudy builds like SQL DB or managed instance. There’s a database scope configuration called force runtime parameter collection.
There’s a big note in the, in the docs for it that says, this is not for extended use. You use this for a limited time for troubleshooting. Uh, so like, don’t leave this on forever.
Cause it, that it’ll be a mess. So, uh, you could do it if you were on SQL Server 2022, but you’re on 2019. So there’s no joy for you. You would have to use extended events, uh, to capture that.
And you would have to figure out like, you know, um, like it’s, it’s a, I guess it’s sort of unclear to me if this is like a store procedure or like a, like an ORM query using SP executes equal or something. But, uh, if you use my store procedure, SP underscore human events, there is, uh, uh, a class of event you can use called where it’ll collect runtime query information. This isn’t going to tell you like for currently executing queries, like the query does have to finish for it to get logged to the extended event.
Uh, but, uh, that like extended events can capture the parameter runtime values. Uh, one of the things that my tool captures is my tool captures, sorry, uh, is the, um, the post execution show plan. So that would show you, uh, in the, in the plan XML, uh, what the parameter compile and runtime values were.
If you don’t, you can skip collecting that. And one of the, uh, and like some of the other extended events will show you the actual call with, um, uh, with the parameter values. Uh, it occurs to me while I’m answering this, that one other, one other thing you could try with SP who is active is that, that sometimes work.
It won’t always work depending on how parameterized things are. But if you’re looking at like a store procedure call where it’s like, you know, it’s calling the store procedure and you can, and like, like the application is passing, passing in literal values and not just passing in like another set of parameters. That like, like, like your, like, like the, the store procedure parameters equal.
So if it’s like, like store, like store procedure parameter equals literal value, you could do it. But if it’s like store procedure parameter equals some other parameter value from the ORM, you couldn’t do it. Uh, there’s, uh, another SP who is active parameter called get outer command.
And if you set that equal to one, you’ll get like the full thing that called the query you’re looking at. And you might be able to see the parameter values there. Uh, if it’s not there, then you have to go to extended events.
All right. Final question for this week’s episode of office hours is, wow, it’s not, not SQL Server related at all. It’s a very personal question.
Uh, if you could only have one watch from your current collection, what would it be? Well, this one, uh, this is the, this is, this was, this was my, my business is going well watch. So this was the one that I would, this is the one that I would keep.
I would, I do want to be like upfront. My watch collection is two watches. It is not an extensive collection. I do not have a wall of watches spinning on winders. Then I, and I have to like painstakingly choose which one is going to go best with my Adidas shirt for the day.
I have two. I have the, like a stainless steel watch that I wear to like the gym and the pool and the beach and other stuff where I don’t want to like mess up a gold watch. And then I have my gold watch, which I wear for everything else.
So I don’t have a lot. Uh, it’s not a huge collection. Uh, but the question here is that, I mean, is probably worth answering is also what’s a grail watch that you think a lot about, but can’t afford justify get ahold of. Uh, and then PS, if you can only answer one, please answer my serious SQL, SQL related question.
I don’t know what your SQL related question was. It doesn’t tie like users to questions in any way. So, uh, I hope I answered it, but, um, maybe it’s, maybe it’s one of the ones before, maybe it’s one of the ones after, but, uh, the watch that I would love to get if, if so.
So, it’s kind of a sad story because the person who I worked with to get my watches with was, uh, the, the watch world is weird, right? Like you have to like suck up to people and like make friends and do all sorts of like jump through all sorts of hoops to buy like, like nicer watches. Uh, unless you just go to like a gray market dealer and, um, like, like you just like, you know, spend whatever money because they don’t, they don’t care.
Right. Uh, but like the, like the, like no watch store really is, is very few watch stores are owned by the watch company. They’re all sort of like franchises. The like, so like, like a lot of places, it’ll be like a jewelry store or something that opens up boutiques for watches because, you know, that watches are part of that.
But when you want to sell the nicer watches, not secondhand or something, then you have to like, have like a branded boutique to sell them out of. So, um, the person who I buy my watches from currently, this, the, the jewelry store they work for was opening up a Patek boutique. And I was very excited because there was exactly one Patek that I wanted.
It was a Nautilus 5980, uh, full gold, the black face, it’s a gorgeous watch. Uh, and I was like, man, as soon, as soon as, as soon as that opens up, like I’m putting my name in for that. And then like three months before that, that boutique opened up, Patek discontinued making that watch.
So now you can only get it on the gray market. And now like, like the low end on the gray market, it’s like 180, 250,000 for the thing. And there’s ain’t no way that’s happened.
Like, I would have to win Powerball before I start thinking about that. You know, retail was like, like less than, like way less than half of that. Like, like, like if I, you’ve got it from the store, but you know, that, that opportunity is, has passed me by. So, um, yeah, anyway, that’s, that’s it for there.
Uh, I’m not going to talk anymore about that because it gets obnoxious pretty quickly. But anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something about, well, maybe about watches, if not about SQL Server. And, uh, I will see you for the next round of Office Hours questions. Uh, the next, the next, next five. Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Query Store Query Plan Confusion (Paramaters and Variables)
Video Summary
In this video, I dive into a peculiar issue involving query store and local variables in SQL Server. I explore how using local variables can lead to confusion when examining query plans and query store data, as the optimizer might make it look like parameters were used even though they weren’t. This behavior can be particularly frustrating when trying to diagnose performance issues or understand execution plans. Throughout the video, I demonstrate this with examples of both local variable usage and parameterized dynamic SQL, highlighting how the absence of a parameter list in query store for local variables can make it challenging to identify the actual cause of certain plan behaviors.
Full Transcript
Erik Darling here with Darling Data. You may be wondering what we care about today. I am too. I am deeply curious about what things we will find to care about in this video. It’s going to be interesting. Anyway, if you’re ready for the big reveal, I’m going to talk a little bit about query store, query plan confusion. Now, the issue that I’m going to show you here is one that has been irking me as of late, and it is a situation where when you use local variables in a query, query plan kind of makes it look like they were supplied as parameters. And you might get very, very confused as to why your query plan and query store looks like it used a parameter but has no compile time parameter value. So we’re, tyle wanted.p so, it was24t also t free my efforts to continue bringing you this hard-hitting, cutting-edge SQL content, you can click that link in the video description right there, and you can sign up for his membership for as few as $4 a month.
If you have spent all your money on other hard-hitting, cutting-edge SQL Server content, well, I guess I’m just going to have to do better next time. You can do other things to support the endeavors of this channel, like liking, like commenting, and like subscribing, just like that.
If you want to ask questions privately that I will answer publicly, you can go to the Office Hours link, which is also in the video description, and dump the question in there, and I will answer it at some point in the future.
Usually I do those videos once I have five answerable questions, and those usually get done, I don’t know, I try to do them once a week. Depends on you, though.
I can’t answer questions if you don’t submit them, so you should submit them. If you need help with SQL Server beyond what you think just a common YouTube video or blog post or even a well-thought-out Office Hours question might provide, well, I am the best consultant in three out of four hemispheres of the world, so you can hire me at a reasonable rate, mind you, to do all sorts of good things for your SQL Server.
If you would like some training content at an also incredibly reasonable rate, you can get all 24 hours of mine for about $150. again, the fully assembled URL to attain that level of enlightenment, that nirvana, is also in the video description.
Click away, my friends, click away. We have SQL Saturday, New York City, May the 10th of 2025. For those of you in the American area, well, come on by.
It’ll be a hoot. Isn’t everyone looking for a reason to come to New York City? Anyway, with that out of the way, let’s look at this junk.
All right, so I’ve got two queries here, obviously. I’ve got this query up at the very tippity top, which uses a local variable, that is, in which we declare a variable.
We set it equal to a value, and then we use it in our where clause. This is a local variable thing. I should have done that a little bit differently.
Let’s do another take on that. Let’s say that underline didn’t go so well. Let’s see if we can get a steady hand on this one. It’s not very easy doing this with a mouse, having a steady hand.
So we have declared a variable up here. We have used it in our where clause down here, and I have put a helpful little comment right here that says local variable demo. Down below, I have switched, I have changed the game entirely.
In this one, we use dynamic SQL, parameterized dynamic SQL, where we still have a declared variable, but this time we make some dynamic SQL, and we feed that dynamic SQL a parameter.
And this is, you can see, a very helpful comment here, parameter demo, because the vote type ID in this one, we’re going to go for the steady hand on this.
Oh, boy, that’s not going well. Jeez. For a guy who’s already had four drinks today, you’d think this would be going better, but anyway. Within, in this, in this instance, we have, I mean, we have still declared several local variables, three local variables, but in this case, we have passed our local variable as a parameter to the dynamic SQL block, and we have used the lovely talented SP execute SQL to execute our parameterized dynamic SQL with the value for that vote type ID.
So let’s run these, and we’re going to look at the results, and the results are not going to be all that interesting or spectacular. I promise you, it’s not going to be anything all that great worth looking at.
We are going to have two queries, one with a quite, I mean, both with a quite obvious missing index request on the votes table, because we don’t have an index on vote type ID, so we have to scan a clustered index.
This is not the problem. I mean, you know, you could consider the cardinality estimate for the local variable demo using the density vector estimate is problematic, right?
Because it guesses 3, 7, 8, 0, 6, 2, 0, 3.78 million rows. Wait, that’s a, yeah, seven digit number, right? Because I have three fingers left over. Okay.
Seven, three left over out of 10 means these are seven. So, that, I mean, that’s probably not a great guess, right? But that’s what we get from local variables. The parameterized one, of course, gets a good guess on the first compilation.
If we, if we did something real, real fun, and we say, change this to three, and we did this again, we would, we would get parameter sniffed. And, you know, that 700 and something row guess would no longer be very valid, right?
And all of a sudden, we, we, we have like a reversi problem, but that’s not really what I’m here to talk about. What I’m here to talk about is, of course, when we look at query store, what we get from both of these in the query text.
And I, the reason why I put those comments in the query text is so that we could, we could differentiate very easily between one query and the other. And if you look here, both of these look identical up until this point, right?
Both of these look like parameterized SQL got passed to SQL Server and we, and we executed them that way. But that’s, but, but the, that didn’t, that isn’t what happened at all, is it?
That local variable did not, did not get, was not a parameter. Only in the dynamic SQL branch was it, was it a parameter. And where this can get really annoying, right?
And let’s, let’s look at the parameter version first. If you look at this query plan, that is indeed the query plan we got. And if we go to the properties, what we’re going to see is a parameter list. Let’s, let’s squeeze this in a little bit.
Let’s have everything nice and compact for you beautiful viewers at home. We have, we have a parameter list with a compile time value for that, right? And the first time we executed this, we did indeed compile it with four.
Great, right? Good job, parameter demo. Where things get annoying, of course, is if you were to look at the plan for the, the, the local variable demo, well, go, go to the properties just like we did before, but we, we don’t have a parameter list here, do we?
There is no parameter list. There, there is optimizer stats usage, right? No, like this isn’t going to help us much. All right? This doesn’t, doesn’t do much for us.
This doesn’t do much at all. It doesn’t, doesn’t help us ascertain anything. The only thing that you could maybe do is, you know, if, if you, if you see a query like this in query store and you, you look at the query text and you think to yourself, oh, wait, that’s the wrong, that’s the wrong one.
We need this one. Oops. Gave up, gave up a little something on that. Sorry about that. Don’t tell anyone. The only thing that you could maybe do with this is, you know, just maybe run this.
Like if you don’t see the parameter list in there, just, you could probably figure out that it’s a local variable by declaring this as a variable and rerunning this with any value in there or, you know, like any, many like couple values over like, you know, a few execute to two or three executions.
And if you keep getting the same card, like bad cardinality estimate, then that might be a sign that it was indeed a local variable. Now, if we did something a little bit different, right?
And let’s say in the, well, I mean, in either one of these, if we did like option recompile, and let’s, let’s steal this.
And let’s, let’s do this here as well. Just so we have, we have adequate things to compare here. And I realized that the, the boat type IDs are different in there, but if we, but that’s actually, you know, sort of a helpful thing as well.
If we look at these, right, we’re going to have two more instances of local variable and parameter text in here. And if we look at the query plan for either of these, oops, let’s keep doing that.
If we look at the query plan for either of these, the parameter value rather than like be like, rather than having to go over here and look, there’s not going to be a parameter value in either of these, right?
I’m just going to park that here. So that stays up. We don’t get parameter values for either one of these now, but the literal value is going to be embedded in the, the predicate here, right?
So you can see predicate where vote type ID equals four, for the, the local variable demo. And we’re going to see the embedded vote type ID equals, two for the parameter demo.
So the, the recompile hint will change things up a little bit. But, you know, we still have no, like no very good sign for either one that a, that a local variable versus a parameter was used.
Do I wish that there were an easier way to identify this? Yes. It’s not, not very easy or straightforward to do that. Do I wish that I had remembered my end prefix here?
Of course I do. I didn’t, I now I look quite foolish in front of all of you lovely people out there, but I do hope that you can forgive me. Anyway, uh, I, I didn’t enjoy this.
I don’t enjoy this one bit. Um, and I don’t expect you to either. So, um, when I, my usual spiel, when I, when I say thank you for watching, I hope you enjoyed yourselves.
I have a, I have a, I have a strong, strong feeling that no one has enjoyed themselves on this, on this occasion. So we’re going to skip that part. Uh, but I, I do hope that maybe you learned something.
Like, this is strange behavior. And, I do hope to see you in the next video where we’ll talk about some stored procedure stuff. Probably.
Maybe something about data types over here. Perhaps. We’ll see what happens. I don’t know. Just got to live that long. Anyway, uh, thank you for watching.
I’m going to go do that now. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
All About SQL Server Stored Procedures: Local Variables
Video Summary
In this video, I delve into the often-overlooked topic of local variables in stored procedures within SQL Server. While many developers use them for convenience or believe they are a panacea for parameter sniffing issues, I explore both their proper and improper uses, as well as how they can impact cardinality estimation and overall performance. I share practical examples from recent experiences where local variables caused unexpected slowdowns, emphasizing the importance of testing before implementing such fixes. By understanding these nuances, you’ll be better equipped to write efficient T-SQL code that avoids common pitfalls while leveraging the benefits of local variables when appropriate.
Full Transcript
Erik Darling here with Darling Data. And we are going to continue our magical demystifying tour of SQL Server stored procedures. And in this one we are going to talk about another thing that comes up quite a bit, and that is the use of local variables in stored procedures and how they can have strange and profound effects on many things. Well, I realize that this is a topic that has been well-trod on this channel, but again in the interest of completeness for a series about stored procedures, I’m doing a video about it here. And also this is a topic that I cannot get away from. Just this past week I was on a call with four senior developers for a fairly large company, and they were all puzzling over why they were and they were all puzzling over why this stored procedure was suddenly acting up. And, you know, when one of them decided to share their SQL Server Management Studio window with me, well, what happened was not a stored procedure. Of course, it was, creator-alter procedure was quoted out. The parentheses around the, you know, two or three parameters for the stored procedure were quoted out. And the little, uh, little parameters that they had, little parameters that they had, a few parameters that they had, had a little declare, uh, inserted above them and they had some values assigned to them. And they would hit F5 and have no idea why this thing was suddenly a lot slower than when they were testing it for some QA thing. And of course, we had to have the talk about the birds and the bees and local variables. And, um, as soon as, as soon as it was executed as a stored procedure, everything was, back to normal again. Um, it’s a rather a long store procedure. So I didn’t want to have them go through and put option recompile hints on every statement because that would have, that would have taken a long time to do and then undo. And then, uh, you know, they, they, they, they probably would have had, uh, perhaps a new bad habit formed for them, but, uh, it was, it was, it was, it was demonstrative enough to just execute as normal.
Now, uh, what we’re going to talk about in this one is, um, some of the okay uses of local variables, uh, some of the not okay uses of local variables, how to tell, uh, when local variables and other similar constructs, namely the optimize for unknown hint are causing problems. And of course, how to test code that is parameterized appropriately. So we’ve got, uh, we’ve got quite a rundown here and hopefully we can do it all before the death of the universe or something. Uh, if you enjoy this content, if you are gung ho on what the me and bats cook up in the kitchen and bring to you here on YouTube, uh, you can, uh, sign up for a paid membership.
We don’t get anything extra. You just say, thanks for doing a good job. It’s a little tip jar for me to keep doing these things. Uh, link in the video description, four bucks a month entry is the entry fee on that. Uh, you can of course go higher if you are feeling generous at all. Uh, if you, uh, are short the four bucks a month, perhaps because, um, I don’t know, maybe you spent it all on collective, collectible PEZ dispensers or, uh, the actual PEZ that goes in them.
Uh, that’s fine. I understand financial hardship, especially if you’re a government employee, uh, might be having a, having a tough time recently financially. Uh, you can do other things to support the channel. You can like, you can subscribe, uh, and, uh, you can comment. Just be nice. Some of you leave crappy comments.
And, uh, if you want to ask questions privately that I will answer publicly in my office hours videos, uh, in which I answer five user questions at a go, uh, you can go to that link, which is also down in the video description. Make this tremendously easy for you. If you need help with your SQL Server, even, even if you are a government employee, I will help, will help anyone who, who, who writes a check.
Uh, we can, uh, we can do any one of these things. Health checks, performance analysis, performance tuning, like actually fixing problems. Not just someone standing there telling you everything you’ve done wrong, but actually, actually helping you fix things.
Uh, if you are having a performance emergency or if you need training so that you do not cause any future performance emergencies, the easiest fire to fight is one that never starts, right? Uh, I am available for all these things. And, of course, as always, my rates are reasonable.
If you would like some training to get better at SQL Server performance tuning, uh, golly and gosh, do I have a whole bucket of it? Uh, and you can get it for about 150 bucks for the rest of your life. Just, uh, I would maybe recommend signing up with a personal email address because if it’s tied to your work email, you know, you know how that goes.
Uh, if you use the link up there and use that discount code, that will bring the price down to something affordable even for the, uh, recently departed. Phone keeps making weird, weird sounds. Uh, if you would like to, uh, in person see me do various, uh, administrative tasks, I will be at SQL Server, SQL Saturday, New York City, 2025, taking place on May the 10th in beautiful New York City.
Times Square, really the height of New York City, uh, where every subway stops, I think. Uh, and, uh, there’s a, uh, full day performance tuning pre-con on May the 9th given by Andreas Volter. And, uh, he’s got some performance stuff he’s going to teach, teach the world about.
Uh, so that, those, those, that’s going on. I’m still waiting to hear about shoring up some dates for other stuff. So hopefully I’ll be able to expand on some of this soon.
But with that out of the way, let’s talk about these local yokel variables. Now, uh, let’s, let’s see if Zoomit works. I’m going to hit control and one here.
We’re going to see what happens. Wow. First try. So, uh, most people use local variables out of convenience. Just purely, hey, I can declare a thing and do something with it.
Okay. Some people get it in their heads that local variables fix all their parameter sniffing problems. You will never have another performance problem so long as you avoid parameters.
Do you honestly think that if parameters were that bad that they would just stay in the product as a way to fool you, to ruin things for you? Well, it seems a bit goofy to me.
But just like you can’t fix a broken bone with some good vibes, you can’t fix parameter sniffing with bad advice. Uh, we’re going to talk more about that later on in the series, but I do want you to be prepared for this eventuality.
Uh, like many things that we’ve talked about so far, uh, local variables have their uses and they are a convenience for you to do various things with. But, uh, when you use them in specific ways, they can, they can really muck up, uh, cardinality, estimation, plan choice, and ultimately performance.
If you are a frequent watcher or viewer of this channel, you, you’re probably not terribly surprised by any of that. You’ve, you’ve seen me talk about it before. You may have even read some of my extended thought pieces on the, on the matter.
Uh, so, you know, it, it, I, I, well, I do try to, uh, balance up the, uh, the, the, the more advanced weird stuff on here. I, I do have to, uh, do some of the evergreen content that hopefully people find and solve some, some easier, earlier problems for them.
Uh, but local variables do present, uh, a rather attractive proposition for a lot of people who are programming in T-SQL. You can set a constant value for something like SQL Server doesn’t give, have like domain variables for stuff.
Uh, and you can assign values from rather complex subqueries to a single variable so that you don’t have to recalculate that rather complex subquery over and over and over again. Uh, you can also do other stuff for them.
Um, they are really handy for loops, uh, when you want to, uh, print or, uh, raise error some feedback about where, where, how your loop is progressing, things like that. Um, so they are useful for stuff.
Uh, one of those things where, you know, that I am okay with is, you know, either, you know, using it to like have a consistent batch modification time. Uh, this is, this is an okay thing to do for what I’m going to show you because this sets the batch modification time to one single value.
So if you have to update multiple tables to show a batch modification time, then it will be consistent for each step in the batch. Otherwise, the sys date time, the sys date time call will be, uh, recalculated every time it’s invoked.
So if one batch modification happens like five minutes after another one, well, that they’re going to, they, they might look like different batches because they’re spread apart and, you know, they don’t have the same batch time.
Uh, and of course, if you need to calculate some rather complex sub-expression and assign that to a variable, it becomes even more important to, uh, to stabilize that somehow so that you, you don’t get caught re-executing the same thing over and over and over again.
That can be, can be not fun, especially if, uh, this thing ends up interwoven as a subquery into a bunch of other queries. So where that’s okay with me is if you’re going to do something like this, where you just need to use that as a, as a scalar value and insert or an update or something like we’re just setting a value to something or inserting a value with something that’s totally okay.
There are good and convenient and sensible uses for local variables where things get, um, a little thrown off is when people start using them down in where clauses, I guess probably, uh, far less common would be in a, in a join, but, uh, where clauses, you do see them in there quite a bit.
Though, uh, I, I do see some people really go out of their way to, to screwball things by, uh, setting a local variable to something and then like joining on a, like case expression or an, or an or clause where if the local variable is one thing, you, you join to this other thing.
And if it’s one thing, the other thing, you join to this other thing. And you’re like, stop, you stop. This is not, not going well.
Uh, but many developers are under this strange impression that, uh, parameter sniffing is the worst thing in the world, right? They hear the words parameter sniffing and all of a sudden they, every, everyone has an opinion.
Everyone has a feeling, uh, deep in the gut. And all of a sudden they, they, they are the world’s foremost expert on something. It’s like when you say optimistic isolation level and someone’s like, well, I read a blog post that says it’s dirty read.
Like, I’m like, well, good for you. You’re wrong, but stop reading wrong blog posts, I guess. But, uh, the real problem that you aren’t into is parameter sensitivity. But, uh, local variables are often not a very satisfying fix for parameter sensitivity issues because while, while they do remove, uh, parameter sensitivity as a performance issue, you do, uh, end up with what is a sort of generalized, uh, cardinality estimate, which may not fit well for many of your data distributions, depending on, uh, if they skew high or if they skew low or something like that.
Uh, local variables use an estimate derived from the total, uh, rows in the table multiplied by the assumed uniqueness of a column’s data. And more often than not, that’s either a very small number or a very large number. So if you have the type of skewed data that led you to having these parameter sensitivity issues, that, that sort of fuzzy guess will either skew high or skew low, and you probably won’t get a plan that’s very good for one or the other to use anyway. You do remove parameter sensitivity as a performance issue because you are no longer using a parameter, but you are introducing another, uh, potential performance issue into the mix by doing so. Um, every, so every once in a while, this, this does fix something.
And if you want to test it to see if it, if it fixes something across a variety of executions and parameter, uh, uh, uh, parameters, parameter values with different distributions assigned to them, you can, you can absolutely do that. I don’t want to tell you never to do it. I just want you to tell you to test to make sure it’s a valid thing to do because more often than not, it’s the wrong thing to do.
So this is sort of the effect of local variables. Uh, what I’m going to do is, uh, clear up the plan cache because I want you to understand that there is no plan reuse happening here and the plan, plan reuse is not why we are seeing the same estimate over and over and over again. If I run this loop when I do have query plans enabled, so we don’t have to worry too much there, we are going to get our eight counts back.
And I want you to see that all eight of these counts return wildly different numbers, but when we go look at the query plans, we get the same estimate across all of the query plans. This will be the same number no matter what. This is not a lack of statistics. We have statistics on the table, uh, or rather on for this column, you can see SQL Server using them. If we go to optimizer stats usage, we will have a variety of statistics loaded in where a SQL Server was cardinality estimating for us, right? So SQL Server was, was using statistics. We are not in the statistics free zone here, but because we use a local variable, SQL Server performs the same calculation for every single one of these plans in order to determine, uh, the cardinality of, uh, this operation. And of course this is wrong for every single one of them.
We’re off by 380% there. Uh, we got 167 of 2857030. So 167 of 2.8 million. Uh, this, this particular, uh, density estimate density vector estimate skews high or somewhat high. But if you look through all of these, we just get a sort of poor guess for every single one of them. Even on the high side, we’re either wrong by 210 or 388%. So this isn’t, this isn’t a very good, uh, thing either.
Uh, this will be, uh, this would be the same thing if we were to use optimize for unknown, right? This would, this is like the same basic effect here. Now, if we throw option, like, like what I was talking about earlier, if we throw option recompile on this, we will get the same counts back. And you will see that SQL Server all of a sudden is capable of, of getting, of garner, garnering us, uh, precise cardinality estimates. Here we hit, well, we’re close enough to right there. We’re close, we’re, we’re dead on there. Uh, we’re good here, right? Well, I mean, we’re off by like what, 12 or something, 20, 20, 20, 20, something like that, some math. Uh, but like SQL Server is able to get very close to the number that we care about for all of these, right? So SQL Server does have good statistics to calculate that, but it doesn’t do that calculation. It doesn’t look at the histogram when you use, um, local variables or when you use optimize for unknown, it does not do that. You do not get, uh, a view of the histogram, uh, that the SQL Server has for a column. We just get the like two, uh, components of the histogram, the number of rows in the table and the, the assumed selectivity and those get multiplied together. Um, the percentage that SQL Server uses will be different across the quality predicates. So direct, you know, column equals something like we’re using here, uh, versus inequality predicates, like greater than, equal to, less than, equal to, or not equal to. You get all different stuff for those. Um, but, uh, sometimes, what happens is people use, um, people use local variables in a way where because of some other predicate, uh, it could be a parameter. It could be a literal value like, like we’re using here, but because there are other predicates in the, in the query, the effect of the local variable gets tamped down quite a bit. So if we look at these two queries and I’m, I’m forcing SQL Server to use the clustered index here. But what’s important to note is that the index that you use to fetch data from is not always the index or the statistics that you use to perform cardinality estimation for the query.
SQL Server may choose a completely different statistics object with a better sample, like a or something like that to, um, or a more, uh, more recent, uh, update, fewer modifications, something like that in order to, uh, do the cardinality estimation. So for these two queries, where in one of them, I’m using a local variable, the other one I’m using just a literal value, you’ll see that the cardinality estimates for these are not terribly far off, right? We get the same count result back, but when we look at the query plans, and if SSMS will listen to me, pretty please, pretty please, there we go. The cardinality estimates for these two queries, one using the local variable and one using the literal value, they’re not terribly far off, right? 15,282 versus 15,606. Was either one of them particularly close? Not very, but this is kind of what happens to a lot of people. Uh, they’ll, they’ll use a local variable in a spot where they have other parameters that, you know, sort of make the, uh, cardinality estimate look closer than it is. And maybe they don’t realize that there is a, there is a difference in how SQL Server estimates cardinality for these. So while there is a small difference here, it’s not so profound that it would like change much about the query plan in a way that like you would notice.
So, uh, when you want to test whether something you’re doing is okay, the hardest part really is, uh, understanding what options you have available to you that might, may produce different results. Uh, there are many ways to pet, see, pet, pet, uh, a dog. I don’t want to just say the thing with cats.
Many ways to pet a dog up here, behind the ears, near the, near the butt, not like on, like above the tail, above it. Stay, stay above the tail. Don’t go below the tail. Uh, if you go below the tail, I don’t know what’s wrong with you. Uh, but there are many ways to, um, to, uh, work with SQL Server to, uh, figure out if what you’re doing is the smartest thing that you could possibly be doing. Uh, there, but like I said, the hardest part is really knowing that what you’re doing, what you’re doing might not be a good idea and understanding what all of your options are in these cases. Um, a lot of this is why, uh, when you look at a SQL Server code base and you find a problem, you see that problem repeated everywhere. That’s how various things that just are like pervasive, uh, it was like issues in, with, with T-SQL code that I look at just ended up all across it, right? Code is culture. The second something yucky ends up in the culture and no one knows any better, or people are afraid to like do anything with it or do anything different or touch it or, you know, get away from it or say, hey, that’s not good. Uh, that, that one instance ends up everywhere, right? And that goes for a lot of things. That goes for like stuff like no lock hints, table variables, uh, CTE, scalar UDFs, multi-statement UDFs, and people wrapping like over protectively every single column and every join and where clause with like, is no coalesce, stuff like that. And like, there’s like, like, like undoing all this stuff is a long process, right? Cause like, it’s just absolutely everywhere. And like, especially if you’re like, especially if it’s, you know, the, in the UDF territory, uh, you have to like not only rewrite the UDFs in a lot of cases, but you also have to change the way that the queries call those UDFs because invoking a table based UDF is a lot different from the way you call a scalar UDF. And of course, local variables are on this list. So, uh, what I’m, what I want to show you here is, uh, sort of more explicitly how, uh, some of the stuff that I showed you can end up causing a performance issue. I’ve got a couple indexes created on the post table, one on the parent ID column and one on the owner user ID column. And we are going to create or alter this store procedure here. And then we are going to run it. And while it runs, I’m going to, I’m going to show you, uh, the different ways that things are going in here. Oh, you know what? I do, do, do, do have an extra index that I forgot to drop. So we are going to really quickly pretend that didn’t happen. Do, do, do, P2 on posts, because one thing that I was going to show you later was how compound indexes can help stuff a little bit, but for now we’re going to return to, uh, to Narnia here. So, uh, while this runs, we are going to have, uh, one, uh, expression of the query where we’re using two literal values. We have one expression of the query where we are using two parameter values, right? These are the parameters that got passed in up here. Uh, and then we have another one where, where we are using local variables that got assigned the parameter values that got passed in. That’s what we’re doing right here. All right. This is, and this is what I catch a lot of people doing when, with a little comment that’s like, fix the parameter sniffing boss. And then down here, we are using the optimize for unknown variant, which gets you the same, uh, cardinality estimation effect of using the local variables. And if we look at the execution plans for these, uh, this one turned out okay, all right, fine with this 817 milliseconds, fine with me. 815, 851 milliseconds, again, fine with me.
Uh, these are, this is for the literal values. This one is for the directly used parameter values. But down here, we have two very different query plans, right? Rather than using each of the non-clustered indexes that we, uh, we originally created individually, we have the p1 and the p0, uh, being used. Uh, this one, these two just do a more classic, uh, index seek plus key lookup here and here, right? You can see the little key lookup operator, and these take significantly longer.
These are about 11 seconds a pop. And this is specifically because SQL Server did a sort of a bad job of estimating what would be the best, uh, what would be the best set of stuff to use here. Um, so this is one of the, like, just an example of how, when you, uh, when you write with, like, when you use local variables, when you use optimize for unknown, you do not always end up with the best execution plans. So across all that, uh, it’s important for people to know how they can test code effectively. Uh, now not all code comes from a store procedure. You might be dealing with an environment where, uh, ORMs are in use and they are hopefully, uh, you, they’re hopefully passing in parameterized queries, right? One, one would hope anyway. Uh, so what I’m going to do is show you how you can test that code effectively. So if you’re troubleshooting a performance issue, you want to make sure you’re troubleshooting the correct performance issue. So I’ve got, uh, two queries.
Here, I’m going to show you one of them and, uh, I’m using my store procedure, SP quickie store. You can get this at my GitHub repo. Uh, and I’m searching to, for a couple of different query IDs. Uh, no particular reason for these, just that they are fairly good examples of what, uh, what, uh, an ORM query might look like in query store. And I’m just going to click on this top one. So when you click on this, you get the full query. And if we go and highlight this whole thing, except for that little question mark doodad down there, we can plop this right into our query editor window. Now, of course we can’t execute this as is all sorts of, we would get all sorts of errors. But what you might notice here is that we have what looks like the beginning of the store procedure. Now, one thing that, uh, SQL Server does allow you to do is create temporary store procedures so that you can have, uh, what is, I mean, just like effectively, uh, what you would get from like parameterized SP execute SQL, which is the way most, uh, ORMs work. And if we just say create or alter up here, and I’m just going to call this pound sign P just to keep it short. And all we have to do is come down here and stick an as right there. Now, uh, just to make life a little bit easier, let’s put these on new lines.
So when we assign values to them, it’ll be a bit simpler. And, uh, now we’re going to need another thing from the query plan, right? What’s that other thing that we’re going to need? Oops, that didn’t go well. There we go. The other thing we’re going to need from the query plan is the parameter values.
So if we come back to, uh, query, the query store results and we open up the query plan, there are two ways for you to go about this. You can either right click, oops, tool tip getting in the way. We can either right click on the select operator and we can go to the parameter list and we can see all the values that we care of all the compile values that we care about in here.
All right. But if there’s a bunch of them, it gets a little tedious copying and pasting back and forth. So what I usually do is show execution plan XML, bomb all the way to the very bottom and copy out this block. And once you’ve got this block copied out, you can get rid of the query plan.
All right. You can just, we can paste the, paste this up here. And now all we have to do is line up the, uh, parameter names with what we have down here. So offset equals zero and fetch equals 50. And end date equals 2014, blah, blah, blah, blah, blah. So we’re going to put that here and, uh, start date equals 2013, blah, blah, blah, blah, blah. And so we’re going to put this here and now we can create or alter this store procedure with, uh, those as the default values. And if we just say execute pound sign P, we will get a working store procedure that effectively mimics the way a parameterized ORM query would, uh, would run with, uh, with, uh, like with SP executes equal SP executes equal, and this are a completely the commensurate, uh, equal way of doing things. One thing that I do want to bring up though, is depending on the ORM you are using and, uh, how that ORM operates, you may need an additional piece of information.
So one thing that, uh, Quickie store returns way over here in the results are the context settings for your query. So any context setting that you see here, uh, would be on, O-N, in the, in the query. So if anything is not in here, you would have to, uh, you would have to, um, look at the ANSI settings and you would have to set that to off. These are generally the ones that you would care about. Uh, so this usually, and usually the one that ORMs have off that they may want to have on is a Rith abort.
So just be very careful with, uh, usually if you’re testing against ORM stuff, you want to turn a Rith abort off, uh, so that you get, uh, the same, uh, either cache hit or something for them. It’s not going to fix your parameter sniffing problem, but it will at least, you know, bring you to the query, like get, usually get you the query plan of the thing you are testing. So that is, uh, how you can effectively test code without falling into the, uh, local variable problem that we’ve been discussing for this last, wow, 30 minutes. Jeez Louise, got ahead of myself a bit there. Ah, anyway, uh, I should probably wrap this one up then before, before the camera starts to overheat.
Lord knows most people start to overheat if they look at me long enough. Uh, so thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. We, where we will talk about how you can use wrapper procedures. You can see that over there, uh, to alleviate some problems with, uh, things that we talked about. Like when you have a bevy of local variable assignments happening and you need to use those local variable assignments in other queries where cardinality estimation would matter. So, uh, I’m going to go prepare those demos, hopefully not, not, not forget any, not forget that I created any indexes and, uh, we’ll, we’ll go from there.
All right. 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.
All About SQL Server Stored Procedures: Conditional Logic
Video Summary
In this video, I delve into the challenges of conditional logic in SQL Server stored procedures, specifically focusing on two key areas: branching within store procedures based on different conditions and complex where clause or join predicate logic. These issues can lead to poor query performance due to bad cardinality estimations and suboptimal execution plans. While discussing these problems, I also touch upon the broader context of how SQL Server compiles and executes stored procedures, emphasizing that even unexecuted branches are considered during compilation. To make this concept more tangible, I walk through a practical example using a simple store procedure with conditional logic to illustrate the issues at hand. This video is part of an ongoing series aimed at providing comprehensive guidance on optimizing SQL Server stored procedures, and it sets the stage for future videos where we will explore solutions that address these challenges while also covering related topics like parameter sniffing and query sargability.
Full Transcript
Erik Darling here with Darling Data, and we are in this bang around video going to be talking about how conditional logic can be kind of lousy in SQL Server store procedures. Of course, this is the topic that I’ve been on about a few videos in the past, but again, since we’re putting together a playlist of all the stuff that pertains to store procedures, in one convenient place, rather than in some disparate videos that maybe you’ll get lucky and find on your hunt for relevant material, we are going to have this thematically integrated, synergistically, across all store procedure verticals. So, I hope that you enjoy this one. This is going to cover two aspects of conditional logic. One is, if I’m going to branching in store procedures, and the other is sort of conditional where clause logic. Just keep in mind that the conditional where clause logic is just as applicable to conditional join clause logic. We’re just not going to explicitly talk about it. The, as far as like solutions and fixes for this, those will be covered in a later video because the solution and fix to this is also useful in other scenarios that I would like to cover before that. So, I want to cover before that so that you see a more sort of complete version of things when we are done. But before we do that, oh boy, I think you know what time it is. It’s time to have a weird rib mark on my face. It’s great. It’s awesome. Hair and makeup is fired on this one. Let’s talk a little bit about you and I and our, the dance we do together. If you want to support this channel and the work that I do to bring you this amazing, long form video content, you can sign up for a membership down in the video description for as few as $4 a month. That is the cost threshold for an espresso here in New York City. If you have already spent all your money on various performance enhancing substances, I understand. I know what it’s like. I’ve been thinking, after I watch some of these videos, I’ve been thinking about getting some performance enhancing materials for my hair, which if you find this is upsetting as I do, maybe that $4 a month could go towards some sort of growth product in there. But we haven’t gotten quite there yet. When my wife tells me to shave my head, then maybe we’ll reassess that. You can like, you can comment, you can subscribe. Those are all free. And so is asking questions for my office hours videos, in which I take time out of my busy consulting schedule to answer five questions.
of yours for free. If you need the type of help with SQL Server that just can’t be can’t possibly be covered by the YouTubery that I that I that I put out here, you can of course, you can call me, you can say, Hey, Eric, I need some help with my SQL Server. And I’ll say no problem. My rates are reasonable. And we can do all of this stuff together. And we can we can we can we can check out your healths, we can check out your performances, we can fix your health healths and performances. If you’re having an emergency, we can fix that too. And we can also train your developers. So that you have fewer emergencies. And of course, by we I mean me, that is my responsibility. That’s what I am. That’s what you are paying me for. There is no we in that really. It’s just, just, just me and the chickens. If you want some training, perhaps you feel like it is time for you to just get better at SQL Server performance tuning. On your own time, that’s cool too. You can get all of my training content for about 150 US dollars for life.
Woo. And of course, you can get all that via the link in the also in the video description because I like to make things easy on you. I care about you. I realize life is hard enough. Without you having to figure more things out. So there we have that. SQL Saturday, New York City coming up in May seems very far away here in the the doldrums of February, but March, you know, it’s gonna be baseball weather. It’s gonna be nice to probably catch a Mets game. Pretty, pretty nice out there. Probably the I think city field is the nicest part of flushing. So, or do they call that something else now? Is that like Queens Meadows or something? Really, really try to obfuscate some of the some of the geography here in New York. It’s like, where do you live? East Williamsburg? No, you live in Flatbush or Bushwick or something. You do not live in there’s no such thing. Anyway, regional, regional humor doesn’t always cut over well to these international audiences. With that out of the way, let’s talk about conditional logic and store procedures. Now, there are two forms of conditional logic that I personally end up having to clean up quite a bit. And that is if branching to run different queries at different times depending on different circumstantial parameter, local variable gathering, setting, contextual things happening. And complicated join and where clause logic, which is most often like, if this parameter equals zero, then do this. Or if this parameter equals one, then do this.
And all that is a mess for SQL Server to untangle. The problems with both are fairly similar in terms of performance, both with bad cardinality estimation, and just general query query plan problems, usually stemming from cardinality estimation problems, but sometimes from other places. Now, keep in mind, all of this pertains specifically to queries executed, let’s just say natively, or naturally, or naturally within the course of a store procedure running. If you have a separate, if you have code separated out into anything else that requires its own execute, meaning its own execution context, whether it’s executing another store procedure, or executing dynamic SQL, that does not fall into this purview. This is a completely different thing. And doing stuff like that is often a solution for the problems I’m going to discuss here. But since those solutions work for a bunch of other things that cause problems in store procedures, I’m going to save the fully fleshed out solution for the video specifically about that. I want to cover the other stuff that that stuff fixes first along the way.
Now, of course, SQL is a declarative language, you declare what you want. And SQL Server goes off and tries to get that for you. But there are procedural elements for it. There are all sorts of control for logic things, there are loops, there are while’s, there are ifs, there are then’s, there are else’s, there are begins, there are ends. There’s all sorts of things. There’s all sorts of things. There’s all sorts of stuff you can do in there that makes SQL Server act sort of like a real programming language when it is a structured English query language. Strange, isn’t it? But when you’re developing store procedures, the thing that you really need to understand is how SQL Server builds query plans, which is, like I said, everything in the store procedure is like the batch, and that all gets compiled and planned out on execution, except for stuff that is tucked away under separate execution contexts. So by that I mean, you’re chugging along in your store procedure, and then you execute another store procedure. Executing that other store procedure is not part of the initial compilation.
That only happens if you actually execute that store procedure along the way. So, we already talked about that. Good, good, good. If any of this sounds familiar to you, which it just might, because you watch my videos, and you pay attention, and you care about the things that I say, you’ve probably heard me talk about similar problems around parameter sniffing, local variables, and even query sargability, which is SQL Server’s ability to take the search arguments you provide to your queries, and come up with a good way of using indexes based on those.
But, let’s see. It did a separate execution statement. We talked about that. All right. So, let’s talk about a good way to demonstrate this initial starting problem, right? So, I got this store procedure here called I live. I don’t know why I call it. I think that’s a Team Fortress 2 reference.
I believe that heavy weapons guy says, I live, when he does something in the game. But, it’s been a long time since I’ve had any time to play video games, so I forget most of these things. But, just keep in mind here that we have a parameter that is a bit.
By default, it is null. If the bit is true, we are going to run this query, where we select everything from a table called dinner plans. If that is false, then we will just select the top one.
Now, I’ve got this hidden away in there, because I want to show you an easy way to sort of understand just exactly how this stuff works. And, of course, if we pass in a null decider, our store procedure asks us nicely to just make a damn decision.
So, what I want to do first is unquote this, and I want to create or alter this procedure. And this happens just fine, mind you. But, if I try to run this branch for true, SQL Server says, well, we don’t have an index with ID2 on the table dinner plans.
It’s specified in the from clause, and it doesn’t exist. So, that means that even though we try to execute the store procedure in a way that would run this query, SQL Server attempted to create an execution plan for this query.
All right, so even though we only explored this branch, we didn’t explore this branch when we executed the store procedure. SQL Server was still like, well, hey there. We need to compile a plan for this.
So, just the if block is not enough to get us around various compilation things or compilation processes. So, let’s recreate this with that coded out so we don’t have to deal with it anymore. And what I want to show you is that right now we’ve got nothing in the plan cache for this store procedure.
And if I ask for an estimated plan for this, the estimated plan that we are going to see is going to include a query plan with a conditional for our first select with no top, our second select with a top, and our third select, which is a conditional. This is a select without a query.
All right, select without query. So, that’s what you see when you just say select some expression. There is no from that has a query.
So, the query operator we get is just that fine dandy little thing there. Now, keep in mind with just with that estimated plan, we still don’t have anything actually in the plan cache for this. But if I go and run this, and I say, hey, decider, are you true?
And decider says yes, then we get six rows back there. And now we have, of course, one execution of the store procedure. And if we run it for null, we’re going to get please just make a decision.
And now we have a second execution of the store procedure. And if we run it for false, we now see our top query plan. And now we have three executions of the store procedure.
But note the plans within this, right? This shows us all of the stuff that happened in there. And if SSMS would be nice and just cooperate with me resizing these things, I don’t understand why there is so much white space in these to begin with.
But hey, maybe there’s a good reason for it. But we’ll see all of the queries that we executed in the final query plan in the plan cache for this. All right.
So perhaps not the most intuitive thing in the world, perhaps not the best time. So if you want to understand a lot more about this, I don’t know why that’s out there being all giant and long and annoying.
There will be a link to a playlist that I actually created the playlist for this because there was just three videos hanging out before. And I thought, hey, Eric, you know, you often forget to add links.
Maybe if you only had one link to add, maybe if ZoomIt would work. Maybe if you only had one link to add, we’ll do a better job of remembering that. So I’ll do my best there for you, for you nice folks at home who just want to click on things and don’t want to do any work.
So that’s the first problem is the if branch, right? That’s one set of conditional logic that you’ll see in your, well, that I see in a lot of store procedures I have to fix.
What you might see in other places, and I, of course, see in quite a few store procedures that I end up having to fix, are conditional logic and predicates. And that goes for join and where clause predicates both.
This is not isolated to one or the other. And I know that it is a disappointing thing to hear because it is often quite fun to dunk on SQL Server for doing a bad job of this or that, or the optimizer doesn’t do this too well, doesn’t do that too well, blah, blah, blah, blah, blah.
Of course, there’s all sorts of stuff, but query optimization is a truly difficult and demanding task. It is hard to figure out a sane and rational way to handle all of these different things without being quite a burden or a nuisance to query compilation time.
You know, there are a lot of people out there who just seem to think, hey, this is a big, expensive piece of enterprise software. I should be able to do whatever I want, and it should be able to figure it out immediately.
The problem is you do a lot of real, just bad stuff. I can’t imagine having to design software to account for all the bad things that people do out there.
There’s just too many of them. At some point, some rules have to be established and followed for this piece of software to work up to its capacity. So there’s just certain things you shouldn’t be asking it to do.
There’s certain things that even though they exist in the product and you are capable of doing them, like they are valid syntax, doesn’t mean you should go do all of them.
The query optimizer is quite good at applying its craft to a wide variety of queries written in a wide variety of ways. But at the base of things, the query optimizer is still just a computer program written by people like you and me.
Like you and me, except much smarter. Much smarter than you or I. I’ve heard them talk. They’re really, really, really smart. I sometimes feel bad for them that they’re forced to deal with us.
We are their consumers of their work. But when you think carefully about the goal of a generalized query optimizer, not a specific, overly specific query optimizer, remember specialization, as a wise man once said, is for insects.
These things need to be generalized so that they can be applied to a wide swath of things and problems that need to be solved and queries that need to be answered.
But you need to, the optimizer needs to come up very quickly because no one wants to sit around forever waiting with a good enough execution plan regardless of the surrounding hardware while still respecting the logical semantics of the query and within the confines of the available indexes, constraints, hints, and settings that you have applied to your database and database server.
As you add more and more complexity, as you change the scope of things that the optimizer has to do, the harder that job gets. If you think of it sort of like planning a car trip, like logistics problems, finding the shortest set of routes between like 15 different destinations, the more destinations you add in, more things you add on top of it, right?
More difficult that becomes. Then you have to throw in all the unexpected stuff. There may be things that you’re unaware of. There may be traffic, there may be construction, there may be weather, there may be a bunch of knuckleheads randomly gluing their hands to the road, just whatever.
It’s all chaos. It is all chaos out there in the world. And, you know, there are, so, and this isn’t just like limited to people who sit there and write T-SQL.
You know, ORMs specifically don’t provide you any good facilities for breaking queries up into multiple pieces that make query optimization easier.
You know, like you as a SQL developer, like writing a query, hand crafting a query, might at some point look at the vast length and girth and breadth of a query and say, hey, I don’t think this is going anywhere good.
I might want to like use a temp table here. I might want to maybe not like have to, at some point in my life, try to debug the logic and the correctness of an 18 page query.
It’s not a good time. So like you might, you might have that option. You might have that control and that sort of power in your hands, but ORMs do not really give you that.
So let’s get over into the Stack Overflow database. Now I’ve already got a couple indexes created that I don’t want to spend time waiting to recreate. And I want to show you a few different anti-patterns.
Now, this first one is dealing with parameter nullness or not nullness in if branching. Like we talked about above, this has very, very few upsides.
This does have one upside compared to the next thing I’m going to show you. And that is that at least for when we do something with a query, SQL Server will be able to use indexes effectively for these branches, which is not so true with other ways that this query could be written.
But we do run into a problem with cardinality estimation because like I said before, when you compile, when you like actually compile a plan for these store procedures, cardinality estimation and all that other stuff is done based on the initial set of compile parameters.
If you throw a recompile hint here, either at the store procedure or statement level, like you can solve some of that, but then, you know, and that might be fine.
I’m not anti-recompile. I use it all the time. It’s great. It solves a lot of problems real quick. So, but if you look at the estimated plan for this, you will see all three of the query plans that we have available to us in this.
One of them hits P0. One of them hits P1. The other one hits both. I don’t know. Is that good? Is that bad? We’ll find out. So if we run this one, first compilation, we seek, everything’s good.
We get a good cardinality estimate. Everything is fine here. But if we run this one, we will start to see the cardinality estimation thing maybe be not so great because now SQL Server is guessing that we were getting one row back from this.
Why? Well, because if we look at the parameter list, we can see the compile and runtime values are two different things. It was initially compiled with a null.
That null came from this compilation right here. And this is not something that you can fix by inside of the store procedure changing the parameter value to something else because that is not what SQL Server will use.
So for example, if I say something like if at owner user ID is null, set, let’s do this, begin, end, we could spell end right.
That’d be a nice start, right? And we could say set owner user ID equals 22656. Let’s put this down a little bit lower though.
Let’s put that right here. Right? Because we might say, hey, we don’t necessarily want to do anything else.
But let’s just take this one out so we don’t end up doing anything too, too goofy. But if we rerun like this and we say run this one, right, we still get that one execution plan back, right?
And this one is going to be fine, right? If we look at the compile and run time values here, oops, let’s get that open, we get the same thing for this, right? But let’s say we go over here and we run for this one again.
The execution plan is still going to show the bad cardinality estimate and we are still going to show the compile time value of null here because we didn’t actually, like, like, doing that here didn’t actually affect anything, right?
And, like, I had to do a little bit of surgery here just to not, like, have weird duplicate stuff happen, but I just want you to be aware that, like, modifying a parameter or even, like, giving a default value for a parameter up here isn’t going to change anything for if someone, when someone passes in an actual value, right?
So it’s what you pass, like, what you compile the stored procedure with as a passed in value here that matters. Now, the other anti-pattern that comes along with this is, of course, you know, rather than making, like, like, an if branch for every single possible, like, thing that could happen in the stored procedure, this gets very long and unwieldy if you have lots of different parameters and lots of different combinations, but if you do something like this where you’re, like, oh, I’m just going to, you know, figure it out when I run the query, things get a lot worse here for different reasons.
And it doesn’t matter how you express this logic, it doesn’t matter if you use is null or coalesce instead of doing this, it doesn’t matter if you use a case expression to figure out what’s null or not, and it doesn’t matter if you use null or not null checks on the parameters.
That has, none of those are going to help you or change anything here. But when you do something like this, I think the situation gets a lot worse. Now, let’s run this query, and what we get is, well, I mean, this may be not great, right?
Clustered index scan that takes 1.5, or sorry, nonclustered index scan that takes 5, like, almost 1.6 seconds. Not so great, but, you know, we get the, we get what we sort of expected.
Our parameter list for these two things, we have a compile time value for owner user ID and a runtime value of null, right? That’s there. But then for parent ID, we got 184618, 184618.
All right. So, one other important thing here is, like I said, with the if branch, there is an important thing that makes us better, or make that better, is that we could at least seek into the index that we care about.
This one, notice our predicate looks like this, and we scan that index, right? So, we scan all 17 point something million rows of it here. That’s not, this is not a great setup.
Note also that we use the index on P0, and the index P0 coming back up here is the one that’s on parent ID, right?
Now, if we go and execute this for a null parent ID, an owner user ID 22656, we’re going to be sitting here and waiting for a little bit.
Why? Well, I mean, you can, so, coming, like talking about indexing a little bit while we wait for this to finish, you can partially alleviate performance problems with this, with compound indexes.
I would normally never sign up for indexes that look like this with just a single key column. If we had composite indexes on like parent ID, owner user ID, owner user ID, parent ID, maybe something like that, we could, we could partially alleviate the performance problem that we’re seeing now, but not any of the cardinality estimation problems.
So, if you have bad indexes, you’re going to make bad query, you’re going to make query anti-patterns even worse performance problems, right? You’re going to just magnify your issues.
So, if we come down here now, this thing all finished, if we look at what happened, SQL Server, well, we spent three seconds here now, rather than 1.6 seconds, we spent almost 30 seconds in total in this query plan.
That’s 27 seconds. Why? Well, we fully scanned this. We didn’t actually have a predicate to apply here because parent ID is null, right? We didn’t actually filter any rows out here, but we still use this index.
And then we do a key lookup back to the clustered index to evaluate this predicate. And this is where we finally filter out rows, right? You look down here, this is the 27,901 rows that we normally get back when we look at 22656 values in the post table.
So, that’s just a real, real bad time. So, that’s not good. The smarter pattern here is, of course, to use dynamic SQL, which we’re going to talk about in a future video.
But like I said before, it’s also dealing, it’s also very useful for dealing with some other problems that you might run into, like performance or just architecturally in-store procedures.
So, I want to make sure that we cover those and then I show you, like, what dynamic SQL is actually good for across a wide variety of problems. And again, since this playlist is a learning pathway, you will have to follow the pathway to get, to get, to get full knowledge.
So, to get your full learning done. Watch out for that. Anyway, this has gone on long enough. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I hope I remember to put the links, the link to the, the other playlist in the, in the video description. We’ll see, we’ll see how I do there. And of course, I will see you in the next video where we will talk about more problems in SQL Server Store procedures.
All right. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
All About SQL Server Stored Procedures: Data Types
Video Summary
In this video, I delve into the intricacies of data types in SQL Server stored procedures, a topic that often causes headaches for many database administrators and developers. I share my experiences from real-world scenarios where improper data type handling has led to performance issues and other complications. By discussing common pitfalls such as mismatched date and datetime types, and exploring more nuanced problems like max string types and ORM data typing, I aim to help you avoid these traps in your own projects. Additionally, I cover how certain data types can lead to unexpected results and the importance of using temp tables for better query performance when dealing with dynamic inputs. Whether you’re a seasoned DBA or just starting out, this video offers valuable insights into optimizing stored procedures and maintaining clean, efficient code.
Full Transcript
Erik Darling here with Darling Data. In this video we’re going to continue our rhapsody on store procedures. And in this one we’re going to talk a bit about how the data, just data types, okay? Data types. That’s what we’re going to talk about in this one. Because I see a lot of people screw a lot of stuff up with these and as the person who comes in well after these screw ups have been in place and occurring for them, for a very long time, who has to clean them up. It’s a terribly unpleasant task. Sometimes it is a little bit easier because sometimes there will just be a problem with whatever parameter was being passed to the stored procedure. Other times there’s a problem with the underlying table data types. And at this point the tables have grown quite large and, you know, sure you have options like creating computed columns that do data type conversions for you, but you still really do need to index those computed columns. So even if you like, you know, get the sort of lock free added non persisted computed column thing to your table that, you know, does your, you know, try cast, try convert, try parse, whatever to your column to a different data type to a more correct data type, you still incur that sort of, you know, locking, well, potentially locking if you’re on, if you’re on standard edition, you’re definitely locking, but in either case size of data index creation operation to create an index that now helps your query use that computed column in a efficient way. So we’re going to talk about some of this stuff today.
Today. And just some of the some of the ways that you can get around the more more basic common problems with incorrectly typed stuff in your stored procedures. Before we do that. Mm hmm. Mm hmm. Mm hmm. We should talk about how cool I am, how you should give me four bucks a month. How you can click a link in the video description to do that. If you think I’m cooler than four bucks a month, you can give me more than four bucks a month. But I think four bucks a month is like my entry level cool purchase. And that all goes towards supporting the channel in general, making sure that Bats here stays full of delicious Pez and that I can pop a Pez and have the energy to carry on creating this content.
If you disagree with Pez or if you just hate Bats, you can do other stuff. You can like, you can comment, you can subscribe. And if you want to ask reasonable questions that I will answer on an Office Hours episode, the link to do that is down in the video description as well. And if you are out there struggling with a ill-performing SQL Server and you need help at a reasonable rate, well, I’ve got nothing but reasonable rates. And you can hire me to do any of this stuff, which is a pretty reasonable set of stuff, again, at a reasonable rate. So we’ve got all this going for us.
Now, if you would like some training content about SQL Server performance tuning, I’ve got beginner, intermediate and expert level stuff. Really some noodle baking activities here. And you can get all of mine for about 150 USD for the rest of your life. Long live you. Fully assembled link for that is, of course, in the video description as well.
If you would like to see, well, I mean, I will be there in person. I’m not presenting anything unless someone gets sick or dies or doesn’t show up. Then I’ll fill in a session. But if you would like to come hang out and have me give you a bag of lunch, you can come to SQL Saturday, New York City 2025, taking place on May the 10th. With a performance tuning pre-con by Andreas Vorta on November. I’m sorry, not November. May the 9th.
May the 9th. I don’t know where November came from. November is not involved with this. So we have that going for us. But with that out of the way, let’s talk a little bit about data types and store procedures. Now, I see a lot of people screw up a lot of stuff.
Mismatching date, date time, date time to stuff is a big one. That’s going to be the first thing that we talk about because there are actually some like weird bugs. And some of them depend on, well, this, the one that I’m going to say, does depend on compatibility level, but whatever.
Of course, mismatching string data types. I’m going to say, well, I guess, bear care and bear care. I just have a hard time with the bear, even though I’m pretty, pretty harsh on the car there.
Character and bear, variable character, whatever. I don’t want to sound Southern. No offense to the Southern folks.
I just don’t want to appropriate your accent in an unfortunate manner. But then, like, you know, you just see absolute boneheaded stuff like this, where, you know, people have two tables. And they’re like, oh, eventually I’m going to join these two tables together on this column and this column.
But for some reason, even though both columns will have commensurate data in them, one of them is like a varchar 255. The other is an integer. And you’re like, how’d that happen?
And lots of shrugs. And they’re like, is that a big deal? And you’re like, huh? Yeah, suppose it is.
Since we’re talking, suppose it is a big deal. Anyway, let’s move on. So this demo comes from a question that got asked a little while back on dba.stackexchange.com. And it’s something about why date time value return that matches the predicate something.
There’s a lot of words in there. Kind of a noodle bash in that one. And then that ended up with the fellow who answered it, Martin Smith, brilliant fellow, creating a feedback item.
If I remember, the links will be in the video description. If I don’t, sorry. But if you run through this demo, it’s a very strange one.
And you might want to read the question and the bug report because they do add some more detail in there. But the general gist of this is that we declare a variable called dt, which is a date time, and we set that equal to something. And then we declare a variable called dt2, which is a date time 2.
And we set that equal to the date time parameter. Then we insert the date time local variable into a table variable with a primary key on it. And when we ask if dt equals dt2, right, coming from the table, or if we say where cast dt2 as date time, which it already is a date time, like here and here equals dt2, then we get some strange results back, right?
And if you go and you read through, right, like that doesn’t make any sense. But what’s really weird is if you go and you read through the feedback item, there is a comment in there by Sir Pablo Blanco where he says if you take the index out entirely, right? So let’s just delete where it says primary key.
Look what happens now. Now neither one returns any rows, right? With the primary key thing in there, we did return a row from one of them. But without the primary key, we lose that.
So I think Microsoft does potentially have some work to do here because that is a strange result. So be really careful with the date and date time stuff. But there are generally a few data types that will always make me nervous when I see them, either as a parameter or a variable or a column data type.
That will be max strings. That goes for any variety of string that is a max data type. Except for when you’re using it for dynamic SQL, then I am a-okay with it.
XML, always nerve-wracking. JSON apparently is a data type in Azure SQL DB, which is really just a synonym for VARCAR max anyway. So, but let’s not say too much.
But like those, like XML has been making me nervous for years. Also annoying me because now I’m like, great, I’m going to have to write more X query. Now I’m going to have to go look at all the X query I’ve ever written to make sure I’m doing it right.
And of course, SQL variant is another scary one to see because you just don’t know what you’re going to get. I just expect something bad to happen whenever I see any of this stuff show up in the words I see in SQL Server Management Studio. Another thing that is very common that gets screwed up is ORM data typing.
I wish I knew what this pattern looked like in Entity Framework. But when people don’t strongly type whatever arguments they’re feeding to their Entity Framework queries, they can end up as like all like VARCAR 8000 or NVARCAR 4000 or maxes.
Or sometimes what will happen is Entity Framework will at runtime infer the nature of the string. Usually it will like default to Unicode because Unicode is like more safe, like you don’t like just like in the in the sense that you’re not going to lose fidelity if you use Unicode.
Like if someone actually has a Unicode character in there and you don’t and you don’t use a Unicode string type or parameter type, you will end up with like question marks or like blank spaces or empty boxes or something. So they all usually use like infer that some string is going to be a Unicode thing.
But then it will infer the length of the parameter based on the length of whatever you pass in. So if you were to if you had the same query running without strongly type parameters and you passed in like a variety of different arguments, you would end up with a variety of different like entries in the plan cache and query store with all different parameter lengths in them.
So like if you said like where some name equals Eric, you’d end up with an EnVARCAR 4, Kendra would be EnVARCAR 5, Tom would be EnVARCAR 3, Al would be EnVARCAR 2. And if you were trying to find Q Lazarus, you would end up with an EnVARCAR 1.
And that like you would just end up with different like I mean, even if it was the same plan, there would be different plan cache and query store entries for the same query with different plans across all of them. So that’s not very fun at all.
Now, another thing that I will see people do quite a bit is when they are trying to have, you know, they’re like you can search anything with this store procedure and it ends up, you know, being something like this where they never know what this is going to be. And in some cases, you know, like just like, you know, like within the context of Stack Overflow, like the body column is EnVARCAR max.
Like even though I think internally like Stack Overflow sets a character limit on this, like 30,000 or 50,000 or something like that, like this is what you get. Like it’s still like declared or defined in the table as EnVARCAR max.
So what I see a lot of people do is just have like, you know, their universal search string and they’ll stick some parentheses around it. And then they’ll use that to search across a variety of columns with a variety of data types.
Some of them, like these three, might be strings. Then some of them might be dates, like the ones that say date in the name. And some of them might be numbers, like the ones that say ID in the name.
And this is just a whole host of problems that you don’t want to deal with. You are, of course, in these cases, much, much better off defining a, you know, having actual search parameters for each one, like not doing this, like just having a parameter for each one of these.
What you want to avoid unless you are okay with like a recompile hint is doing something like this with it, right? Like this has lots of problems in SQL Server.
SQL Server 2025 is sort of maybe supposed to give you some help with this. We’ll see how it goes. I haven’t seen how that all works yet, so I can’t say anything. But if you want to write like, you know, this kind of search procedure in a way that doesn’t suck, I have a whole video presentation.
It’s a talk that I do on defeating parameter sniffing with dynamic SQL, which gives you all sorts of ways to write these queries in a way that will perform well across a variety of execution.
So that’s nice. Another big anti-pattern that I see is people passing in lists of things, right? Usually comma-separated lists.
Now, table-valued parameters can be a good option to get around this, but table-valued parameters, unfortunately, are backed by table variables and have their own sort of weird set of issues that you might see there.
So you might need a temp table even if you’re already using a stored procedure, even if you’re already passing a table-valued parameter to it so that you can get statistics generated on your temp table and get like better performance when you join that thing off to other things.
If you’re not using your table-valued parameter in a way where performance is critical or where, you know, you get okay performance anyway, don’t worry about it. But if you’re ever using table-valued parameters and you’re finding the query performance behavior a little weird and hard to predict, you might want to start dumping the contents of your table-valued parameter into a temp table.
So a big anti-pattern is to do something like this, though, right? Like let’s say you have your search string, and let’s say this is a CSV list of like IDs, right?
And you do something like this, and you say, ah, well, I’m just going to say where p.id is in when I split this out. And this goes for like any kind of like string dissection method.
The most evil person in the world who contributes to the entity framework, Microsoft stuff, just tried to like fix a problem with in-clauses like this with JSON and JSON string splitting.
And I’ve just had like a bunch of my clients have like, you know, been like, oh, EF Core, it’s so great. But then they get to this JSON thing, and it’s like, why do these queries all take three minutes now?
Why do these queries suddenly take 15 minutes to run? Well, it’s because of the JSON parsing. Like SQL Server doesn’t have statistics on what the contents of this string are. Even if there’s one thing in this string, even if there’s one thing, it doesn’t matter.
Right? SQL Server has no idea what’s in it, right? It does not take every value out and do cardinality for each value. It’s a complete black box for it.
So like, don’t do this. This is a bad idea. That’s why this is anti-pattern up here, right? This is not good. A much better idea, like I was saying, is a good alternative. If you’re having problems with table-valued parameters, would be to do something like this, where you create a temp table with the correct data type for whatever data you’re putting into it, right?
Like you can even, you know, index that temp table in a useful way. And you can do things to protect your query from having, like the contents of your temp table from having problems, right?
So like what I end up doing a lot is something like this, where, you know, like, and I’m not like for, like, like obviously like select distinct on like, like big results with lots of columns in them would be like a, like nasty performance hit in some cases.
But on something like this, where I’m just pulling, I’m pulling a list of values out of a string. I’m not worried about distinct for this. This is fine.
But what I want to do is make sure that like, like, you know, I’m respecting the uniqueness of the primary key with distinct and that I am not doing anything that might result in an error. So like, let’s say like there’s something malformed in the string.
And something gets messed up. And like, you end up with like the letter A as a, as one of the CSV values.
This would obviously mess up if you try to insert the letter A into an integer column. So I always do tricast. And I use tricast instead of triconvert here, because triconvert is compatibility level sensitive, where tricast you can use anywhere.
And then I also stick a where clause on there. And now, granted, if I saw, this is another one of those things where like if I saw this in like a real query, like with tricast wrapped around like a column and a table, I’d, you know, obviously have some, we have to talk about this a little bit.
But in this case, just like the distinct, because I’m just pulling a list of that, like a list of values out of a CSV string with the built-in string split function, this isn’t going to be a big performance hit.
Even if you had a lot of stuff in there, this wouldn’t be that big a deal. But once all that data is in that temp table with the correct data type, and with a good useful index on it, stuff like this starts performing way, way better.
So we do have like, this is a much better pattern for the CSV thing. Another really unfortunate anti-pattern is when, so like it’s important.
What’s important here is that like, if the base table column data type is in VARCHAR max, you’re going to see this behavior no matter what.
Because SQL Server runs into the same problem, whether a parameter or variable data type is max, or whether the base column data type is max.
And that problem is that you can’t push those predicates down far enough into the query plan all the time. Even if you have a good index, you can only partially seek to some of the values.
You’re still going to see a late filter operator in the query plan. But if I run both of these, even with a recompile hint on them, where a SQL Server should be able to say, hey, you know what?
I think something’s weird here. I think this in VARCHAR max isn’t necessary because we don’t actually have a max data, like a max being passed into this. The query plans for both of these are just about identical, right, across the board.
They both have these filter operators in them, right? So we pull all the things out of the clustered index, like all the rows out of the clustered index, all 17 million of them. And then we filter them down over here, right?
And like even with the recompile hint, where we see the literal value, SQL Server has to apply that after the fact. And that’s no good at all. So like without the recompile hint, you would see the same thing.
The recompile hint doesn’t help here. What you want to do instead is use the right, like make sure that like, you know, you’re not using a max.
If you could use in VARCHAR 4000 here, it would be fine because it’s not a max. But like what you want to make sure is that you are as much as and often as you can using the exact right data type for both of these.
Now, this isn’t going to have a profound performance difference because we don’t have a good supporting index on the post table to search by title. But notice for this one, we can like at least we don’t have that filter happening afterwards, right?
We’re going to like for like, if we look at this one where it’s parameterized, like we can at least apply the predicate while we’re reading through data pages. We don’t have to pull out all the data pages and then filter them later.
If we had an index on the title column, you know, we could do some other stuff. And, you know, kind of coming back to like some of the stupidity of the Stack Overflow database, like we could also do some stuff with like post type ID since like both questions and answers are in the post table.
There’s not like a like post questions and post answers tables like that has them separate. Like there’s a post type ID column and only post type IDs of one will have a non-null title. So like you could even do some stuff with like, you know, indexing and like instead of like, like maybe, I mean, probably be a good idea if you’re searching on title that have title available in the index, but like you might want to do something, maybe like a filtered index with post type ID equals one or like, you know, like have that in your query so that you’re only looking for questions because answers can’t have titles.
But, you know, beyond the scope of what we’re talking about a little bit, but you might have, you might have a similar situation with some somewhat denormalized results in there. So when it comes to data types and store procedures, these are definitely things you want to keep an eye out for, whether they are completely mismatched data types or whether they are just, you know, string like max data types in there.
Fixing small things can have a lot of, a lot of big effects, right? Like SQL Server can suddenly do a whole bunch of stuff better. You know, I would, I would be especially careful about accidental max data types if you are, if you are hoping for good performance out of columnstore or batch mode query plans, because, you know, that, that sort of deep data problem can really screw up which operators can like, even like, like get, get or use batch mode effectively.
I’ve seen a lot of problems with that in queries that I’ve been tuning lately where, you know, some column that didn’t need to be a max data type is a max data type and like, like batch mode on rowstore, you still get like a row mode, like hash something for that data, for like joins to that table and it messes everything up.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned a little something about data types and I will see you in the next video where we will talk about whatever topic number 12 is, because this is, this is clearly topic number 11.
So we, we still have some, some things, some things on our minds, don’t we? All right. Cool. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
A Short PSA On Transaction Count, ROLLBACK, and COMMIT In SQL Server
Video Summary
In this video, I, Erik Darling from Darling Data, wanted to share a quick public service announcement about a common misconception in SQL Server coding practices. I noticed an instance where a developer had used a `WHILE` loop for transaction management, which led to confusion and unnecessary complexity. In reality, the code could have been simplified using an `IF` statement. The video demonstrates how, when you start 1000 transactions but only need one rollback, it’s more efficient and clearer to use a simple `IF` condition instead of a nested `WHILE`. By showing both scenarios—using a `ROLLBACK` within the loop and moving the logic outside—the video highlights the importance of understanding SQL Server’s transaction handling. Whether you’re a seasoned developer or just starting out, this example serves as a reminder to keep your code simple and effective.
Full Transcript
Erik Darling here with Darling Data. And in today’s video, we are going to do a very short PSA on an important difference. That’s a public service announcement. We are not going to be testing our prostate hormone chemical levels. That’s a different channel. Because I saw this the other day, working with some client code. And I looked at it and I said, huh, what happened to you? Where you would feel the need to do this? Now, it was a bit of a developer misconception. This while would have been perfectly fine as an if. Because what happens is SQL Server doesn’t really support nested transactions in this way. Let’s just run the demo. So if I run this, we’re going to begin a transaction 1000 times. And then here we’re going to say, while the transaction count is greater than zero, we’re going to print the current transaction count. And then we’re going to roll back a transaction. When we do this, note that we only print the number 1000. You only need one rollback.
If we’re going to print the current transaction count. If we were to change this to say, commit. Then we would end up with results that look like this. Now notice for commit, we do de increment, decrement. I’m not sure what the right word is there. The transaction count by one, every time we issue a commit. And if I had the print under the commit transaction, you would see this zero, of course. But when you begin a transaction, you do need to pair that with a commit transaction. If you begin a transaction, even if you begin 1000 transactions, you only need to roll back once. So if you’re ever curious out there, maybe you’re not sure what to do with rollbacks and commits. Maybe you stumble upon some code like that, that really should have just been if transcontent. If transcount is greater than zero, we will roll back our transaction. Maybe you’re working with the same people I did. And they just haven’t fixed all that code yet. Tough to say. Anyway.
Thank you for watching. I hope you enjoyed yourselves. Technically, I hope you didn’t learn anything. Because you would be a dangerous human being if you learned something here. But anyway. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.