Learn T-SQL With Erik: Variables and Date Math
Chapters
- 00:00:00 – Introduction
- 00:02:15 – Understanding Parameter Sniffing
- 00:03:13 – Query Plan Analysis
- 00:08:48 – Performance Comparison
Full Transcript
Alright, it’s the last one of the week, you can all breathe a sigh of relief, till next week, till hell begins again, unremitted, anyway, don’t let me rub off on you, in this video we’re going to learn some more about T-SQL, stuff around variables and date math, so that’ll be fun right, everyone likes fun, alright, down in the video description, you will find potentially one of the more important links that you will ever click on in your life, and that is the link to purchase this training for $100 off, there are also other links in there, which I think are equally valuable, depending on your goals and needs in life, where you can hire me for consulting and become a supporting member of this very YouTube channel, you can also ask me office hours questions, which I will answer every Tuesday, faithfully.
I used to answer them faithfully every Monday, but then I cheated on Monday with Tuesday, and now, I don’t know, I’m stuck with Tuesday, Monday dumped me, I don’t know, the whole sordid thing, might have to, I don’t know, I don’t know what to do here, it’s a sordid love triangle, anyway, and if you perhaps want to do, just do me a solid in life, you can of course like, subscribe, and tell a friend, in the video description there’s also links if you want to…
Have free SQL Server performance monitoring, you can do that, from me, it’s my gift to you, just for existing, and using SQL Server, that’s all, that’s it, the only bar for entry, totally free, open source, no weird sign up, phone home stuff, I don’t want to know more about you, or anything like that, just a bunch of T-SQL collectors, running on a schedule, collecting all the important things that you would ever want to know about your SQL Servers, wait stats, blocking, deadlocks…
Bad queries, CPU, memory, disk, you name it, it’s all in there, doesn’t get better than that, especially for that price, alright, anyway, let’s talk about this stuff here, let’s do the damn thing, so, one thing that I want to talk about, and this is a good start to things, is a pattern that I see in a lot of stored procedures, that I wish that I didn’t, and that is…
We have, for simplicity’s sake, we have one parameter in here, and it is nullable, right, by default, it is nullable, so you don’t have to pass anything in here, you’re not going to get an error, it’s like, SQL Server expects a value here, right, so, what a lot of people will end up doing, is, if the date comes in as null, they have a safeguard on it, and the safeguard, I mean, it couldn’t be anything, but, we’re going to use 2013-1201 for our safeguard, and we’re going to look at the side effects and repercussions of such a bit of code.
So we’ve got query plans turned on, and if we run this, and let’s say that a null gets passed in the first time around, and this is going to run, and run, and run, run so far away, I don’t know, something like that, and we look at the execution plan, we didn’t do too well, right?
SQL Server… SQL Server guessed that we were going to get one row, we got 1, 5, 2, 6, 9, 9, 7, we got 1.5 million rows back, we did 1.5 million key lookups, and we didn’t get a very, I mean, this isn’t the worst of it, because, like, we only go about 600 milliseconds in here, so that’s not, like, terrible, but, you know, our sort didn’t get enough memory, ba-ba-ba-ba-ba, we’re all sad, we’re all having a bad time.
And what’s funny… is that if we go into this portion of the query plan, this is in the properties tab, because we got an actual execution plan, we can see the compile and the runtime value here, and notice that this query was compiled with a cardinality estimate for null, however, it was run with a cardinality, well, not with a cardinality, it was run with the requirement to return everything.
Everything greater than 2013-1201, which is quite a discrepancy in rows, isn’t it? Sure is. Sure is.
So just replacing or overwriting a null with a value in the context of, well, in this context, it is a formal parameter, does not really get you what you want, I don’t think, because you still compiled with a cardinality estimate for null.
That was what your plan was compiled with, despite what it was run with. And if we look at the histogram for the, what do you call it, that we created, the index, we have this one thing here, and we’re basically getting this estimate for the, ah, PowerShell, go away.
I don’t know why. There’s too many button combinations these days. There’s too many hotkeys. It’s getting too damn hot. We basically got this estimate, all right? So that’s not a very good time for us, all right? We’re not enjoying ourselves.
We are not having a good time. Another problem that I see quite often is a little bit more like this, where someone will have, this is an example of someone passing in an integer value that gets added to a time.
So what that usually ends up looking like is two declines. The first one is, you know, we start with the integer value, which is the date of the time when the date of the end date is this one, right?
And the second one is, the second one is the start date, which is the time when the end date is this one. And the third one is the time when the end date is this one, right? And, gee, I hope I didn’t hit a weird button there. That jumped in a strange way that frightened me. I was like, oh, what did you do?
But if we run this store procedure with the local variables in place, and we run these three representative executions of the stored procedure, note the 1, 10, and 5 here, and we will get the same bad cardinality estimate for all of them, right? And this feels like a parameter sniffing thing, and like normally it would be a parameter sniffing thing if there were a parameter, but there’s no parameter within the perimeter, there is just a local variable, so we’re getting the density vector guess, we are not getting a compiled parameter, a sniffed parameter value guess, and that becomes especially incorrect, well I mean they’re all incorrect, right? We got like SQL Server is guessing 8, 6, 9, 7, 0, 8, 0 for all of these, even though we get back far less, so the estimated and actual rows for this are way off because we used these declared variables and we added some time to them.
So, let’s skip over, that doesn’t actually run. What you’re much better off doing, for these cases, is just using the expression itself in the WHERE clause, this is identical to what we had those local, the part that we had those local variables playing in the earlier bit of code, but now when we use the parameters here, SQL Server gets not only a stable guess, but a guess that is, ah, wait a minute, I did that wrong.
What I should have noted, before running those, was this being the old version, and this being the new version, right? So this is the one where we have the local variables, this is the one where we have the expressions embedded in the WHERE clause, and if we come back and look, this one gets the same bad treatment, with the bad cardinality estimate, but this one gets a much more appropriate cardinality estimate because we did not use local variables.
variables we put the expression directly in our where clause. So that is what we want to do and that is what you want to do when you are writing your store procedures. All right it’s all for me. It’s Thursday. It’s the last video of the week which means it’s a long weekend for everyone and I will see you next Tuesday with Office Hours. 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.