Could You Be A Kangaroo?
Video Summary
In this video, I delve into the nuances of query tuning and parameter sniffing issues in SQL Server, providing practical solutions for handling these challenges. Parameter sniffing can significantly impact performance when a stored procedure or query is executed multiple times with different parameters. To illustrate one approach to mitigating this issue, I demonstrate creating an index that helps in optimizing the execution plan based on the most common parameter values. This method involves writing a stored procedure that accepts a single parameter and uses it to filter data from the `votes` table in the Stack Overflow database. By counting records and formatting the result for readability, we can see how different parameter values influence the query’s performance and execution plan.
Full Transcript
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you, Varis. I’d like to thank you.
Bye-bye. Wild. Wild stuff. All right.
Everything looks like it’s working there. That’s exciting. Everything looks properly in place. Minimize that. And let’s close the browser.
Let’s minimize the browser so that we don’t have that distracting us from the important work. That we need to get into. Make sure that we’re in the right database for all this stuff.
Because how else can we live if we are not in the right database? We’ll have to be stuck. Hello, everyone.
Let me… Oh, what the heck. Let’s see. This should no longer… Slideshow.
This should be… Pretty static and in place. There we go. Now we’re looking good. Cool.
So how is everyone today? What are we all up to? What is today? Wednesday? It is. It is Wednesday.
Fantastic. Fantastic. That means… The week is nearly over. The week is nearly over.
We are almost… To Friday. Which means… There are only… Thursday.
Let’s see. Thursday. Friday. Saturday. Sunday. Only four more days of work until Monday. Sunday. That’s going to be fun.
All right. So… I don’t know. Do some small talk. We’ll wait for… Wait for some people to show up in here. Wait for some more eyeballs…
To jump on in. He says… I have the next days off. Not sure why I’m broadcast. I don’t know.
Like… Like… Like… Like… You have the next two days off. And then the weekend, right? Or… Or… Or do you have the next two days off because you have to work the weekend?
Because I’ve… I’ve definitely run into some… Some people who have had that problem. In their life. It’s like…
Yeah. My boss gave me… Just like… Like… Tuesday and Wednesday off. Like… Oh. Cool. Why? Because I have to work the weekend. I’m like… Oh. That sucks. You didn’t really get days off. You just switched days around.
Four days. You’re just making… Hey. Thanks for calling the chat boring. That’s nice of you. And then…
Also… Thank you for dragging down the boring chat with your boringness. Let everyone just pile on. Do we have…
Do we have any other insults for the chat? While… While… While… While Lee is… While Lee is busy. So I’m going to need a couple other people to step up and volunteer to hurl insults at me.
While Lee is off of work. And probably not watching me do anything. So hopefully…
Someone… In the greater SQL Server community will be able to step up. Fill those big shoes. And call… Call my chat names. Today I’ve got proper seltzer back.
I mean… By proper seltzer I mean it’s not flavored. So I feel much better about myself. Much better about myself. Do do do.
Alright. Since I think everyone understands what we’re going to be doing today. I’ll stick this one up behind me for now. Let’s see.
Oh that looks pretty. That looks pretty. So I’ve got over 70 people slated to show up on Friday. And I’m mildly terrified now.
At first I was just like… You know what? I should pack in as many people as I can. And now I’m like… Man I better not screw anything up. No pressure though right?
No pressure. No pressure. Just recalling about it’s gold. Yes. I wish more things that I owned were gold. So that I could sell them.
I could sell them. I could buy gym equipment. And then I would not have to wait until… Maybe phase four of New York’s reopening.
To go see a gym. That would be all that I did. Now I’m curious like…
Now I’m thinking like you know… It’s going to be a lot of people… Or there’s going to be a lot of gyms that are closing. I wonder if I could like buy some… Some like going out of business gym equipment. But then I’d just be very tempted to just to like…
Like just open my own gym. Get the hell out of this computer thing. Computers are terrible. Have you ever used one?
Computers are absolutely miserable. I hate computers. I want nothing to do with them anymore. They crash on me constantly.
They don’t listen to reason. Let’s see. Lee says… Yesterday I learned that CX packet is not to be ignored. I love SQL Server.
Every day I learn something new. So Lee… So Lee… Like why would you not… Like under what circumstances… Should you not ignore CX packet?
I look forward to hearing about this. Let’s see. If you bought a gym with gold… Would you make it a gold gym? No. And I tell you why. Tell you why.
There was a brief period of time… When we lived in Austin, Texas. And I got a membership… At the Gold’s Gym in Austin.
And I paid for a year up front… Because I had some special on it. But we ended up moving out of Austin… After six months.
So like three months in… We were like… I’m out. We’re leaving. Just can’t do it. It was too much of a weird culture shock. Not like politically or anything like that.
But like… Just having lived in New York forever… Not being able to get like… Like real Chinese food.
And like pizza being served on Wonder Bread. And it being hot. And there being bugs this big. There was just a lot of…
Of reasons why it didn’t work. But I was like… You know, Gold’s… I’m leaving for… We’re out of here. We’re leaving Texas. We’re not going to be there.
We’re not going to be here. You know, I’ve… Paid for a year. Is there any way I can get any of that back? And… They said… Where are you moving? I said…
We’re moving back to Brooklyn. And they said… Ah… Well… We can’t give you a refund. Because there’s a Gold’s Gym… Within 25 miles… Of where you’re moving to.
I said… 25 miles? Where is this Gold’s Gym? And they pointed… And like… There was like… Some Gold’s Gym map. Where there is a Gold’s Gym… Like…
Like… Way out on Long Island. There’s one closer in Brooklyn now. But… It wasn’t open then. And they were like… Ah… There’s one… Out on Long Island. I’m like… No…
Do you know… Do you know anything about New York? Is there like… Like… You have like the slightest clue… How things work here. Because no one… Is traveling… From Brooklyn…
Out to like… Wherever on Long Island… To go to a gym… And then come back. Like… It was just… Like insane. If there was one in the city… Or if there was like another… Like a gym somewhere in Brooklyn…
That would be a different story. But they’re like… Like… It was like… 15 miles away on Long Island. I’m like… Well… That’s not good. Gold’s Gym…
Are they like… No… The Gold’s Gym that I was going to… Was more expensive. I want to say it was… Like… 600 bucks for the year or something. Like… It wasn’t a big deal…
That I didn’t get the 300 bucks back. I was just… Like… I was mostly annoyed… That they were like… There’s a gym within 25 miles. I was like… Yeah… That’s going to happen. That’s going to happen.
Let’s see… Uh… You know… I could buy a 24-7 gym. The problem with a lot of 24-7 gyms… Is that… They don’t have… Uh…
Good barbell equipment. So I would have to… Buy the 24-7 gym… And then probably… Import my own barbell equipment. And he says… I used to think it was a safe weight type…
But I found out… That… Query with uneven thread work a load… All… Get even memory grants… Since your memory just sat there… Doing nothing… Wazier…
Yeah… Um… So… I wrote a post a while back… About… That…
Oh… It was over on Brent’s site though… Hang on a second… But it was… Not… I mean… Not exactly about CX Packet. So…
A while back… Microsoft decided… To… Um… Split… CX weights… Into… Two different categories… CX Packet…
And CX Consumer. And of course… Uh… CX Consumer is… Like the coordinator threads… Waiting on… Things coming on in…
And CX Packet is still… Queries going out there… And running and doing stuff. So… Uh… There are… Times when you will have… A… Query…
With… Uh… With skewed parallelism. And on older versions of SQL Server… You will… Uh… You will see that register as… Uh…
CX… High C… You can see that register as… High CX Packet. Uh… Newer versions of SQL Server… You will see that register as… High CX Consumer. So…
Uh… Well, that can be… A sign that… Uh… You know… Parallel queries are… Skewed… In some way… In their row distribution. Um…
Yeah… I mean… Like… It’s certainly something that you could… Uh… It’s certainly something that you could… Uh… Look at when you’re dealing with a parallel query. Um…
But I think in general… If you see high CX packet weights on a server… It’s not necessarily a sign that… Every single query is terribly skewed… All over the place. There is…
There is a… There’s a chance. There’s a chance. You could also just have high CX packet from queries going very, very parallel. There’s also that.
Uh… Let’s see. Let’s catch up a little bit here in chat before we get started. We’ll get started in a couple minutes here. Um… Let’s see.
Ah, yes. Congrats on getting a call-up from Itzikbengan. Yeah, that was… That was a crazy… That was a crazy thing to happen. I was…
I was… I was just as shocked to have that come out of nowhere. I was like, what the hell? That was crazy. All right.
So, uh… Let’s… Let’s get started and talk a little bit about some query tuning. Oh, you know… Just really quick in case anyone who is joining today who hasn’t joined the last couple days. Um…
You know… Uh… Doing a thing on Friday. All day. Advanced performance tuning. If you feel like joining. Uh… You can get a ticket. You can get a ticket for very cheap.
125 bucks. Includes access to all of my SQL Server video training. Which is… 24 something hours of it. And, you know…
Well worth it. Well worth it. He says… It’s something on my list to look into more, though. Or also… Let’s see…
Uh… No… You’re… You’re getting ahead of yourself a bit, Lee. I think you’re just saying words that you’ve heard now. All right.
Let’s get the heck out of this thing. Let’s go look at demos. Because that’s where all the good stuff happens, right? When we start doing demos. Everyone loves demos.
That’s where you learn everything. So let’s… Let’s clear out… Everything. Oops. I didn’t highlight everything. All right. We’re going to create some indexes. And while we create some indexes…
We’re going to look at… Um… So this… This… This store procedure started off as… A… A thing that I wanted to show is…
Uh… One way of dealing with a parameter sniffing issue. So like… You know… You… Have a… Have a store procedure or something else that accepts a parameter. And…
What happens next is parameter sniffing. Because at some point… Despite all of your best efforts to… Reasonably index for things so that you have… Selective columns and…
You know… You… You cover all your bases. Uh… Even… Even if you… Even like… Not every value in every… Every column is going to be…
Selective. And… And… You know… Part of the whole selectivity thing is that… Uh… You know… Values and columns being selected… Or…
Being selective rather… Does kind of depend on how people are searching them. So… You know… If you have a string column… And every string is… Semi-unique…
That’s fine if people are searching inequalities. But as soon as people start putting wildcards in… Selectivity… Kind of gets… Kind of gets…
Can get hurt. The same thing with dates too. Right? So if you have… You know… Date ranges… And someone searches for like the last week… That can be pretty selective. If someone searches for the past five years… That’s not as selective.
If you have… You know… Uh… Prices… And you want to search a range of prices… Things that cost between… You know… Ninety dollars and a hundred dollars… Might be fairly selective.
But things that cost between zero dollars and a thousand dollars… Might be… Not as selective. So… You know… Selectivity… Not only depends on… You know… Uh…
Like… How unique the data is at face value… But also… Kind of how people search that data. Right? So… There’s stuff to think about there. And… Um… Parameter sniffing can really happen…
From… Either situation. Or sometimes it takes a combination of parameters for it to show its face. Like… You need to have like… Like… Like… Two non-selective parameters or something. But this is a store procedure that…
You know… I… I wrote… And I want… And like… I wanted to show one way of handling… Issues with parameter sniffing. And… You know… When I teach about parameter sniffing… I…
I try to keep it as simple as possible parameter-wise. So… This just takes one single… This store procedure just takes one parameter… Uh… For vote types over in the… Over in the votes table in the Stack Overflow database. And just to kind of give you…
Uh… An idea of what that looks like. Hoop… From… From… That’s a good job, right? Never trust a professional presenter. That’s to vote type ID…
Count… We’ll do a big count. Because we are big counters. And we’re also going to nicely format… Our count.
Right? Because… The only thing… More annoying… Than big numbers… Is big numbers without commas. Because they’re hard to read. Especially if you’re as dumb as I am.
And we’ll call this… Records… To hopefully annoy some people. Let’s see. D-back says… Is parameter sniffing something new…
Or it’s been there for years… Like dating back… It has been… It has been a problem… For as long as there have been… Parameters in databases. It’s… Going back to forever. Basically.
Since like… The dawn of databases. It’s… It’s called some different things… In different databases. In Oracle… I know they call it… Parameter peaking.
Or… Like… Bind… Bind… Parameter… Bind peaking… Binding parameter peaking. Because Oracle just calls it… Something a little bit different. But… In SQL Server… It’s been parameter sniffing…
Or parameter sensitivity. Is probably the nicer way… Of saying it. But it’s been around… Just about forever. The term itself… I…
I don’t know. I don’t know… I don’t know the history… Of it that well. I… I… It’s a good question. I don’t…
I don’t think I know… The etymology of that one. It’s not like Query Bucks… Where… We very much know… That it was Kendra Little… Who came… Who came up with the term…
Query Bucks. Because Kendra… Is one fantastically smart cookie. And she says funny things constantly. Oh!
Ha ha ha ha ha ha ha! I made my first big mistake. Does anyone see what I did wrong? Does anyone see what I did wrong? Someone in SQL Passes…
He invented it. Ha ha ha! You’ll have to tell me who that was. I would like to know who that was. But this… This… This… This result set sorted… Very… Very strangely.
And it sorted very strangely… Because I am converting this count… To a comma delimited string. And so now SQL Server thinks… That this is… A string and not a number. So we have to order by…
The plane count… If we want to get things back… Descending. Yes. Arch our ordering. Bummerino. Not my favorite thing in the world.
Let’s see. Do I have Zoom It turned off over here? I do. Wonderful. All right. Cool. We are limited to Zooming… Over here. That is just what I wanted.
So… When we look at… The Vote Type ID column… Over in the Votes table… We have many, many different… Vote Types.
Well, not many, many. We have about 16 vote types. But this is one of those… Selectivity things where… You know, up at the top… We will have… Vote Type 2 with 37 million rows. And down at the bottom…
We have Vote Type 4 with 733 million rows. In the middle… We will have some values… That are a little bit closer together. Like, you know… We will have some that are… Up over a million. And then we will have some…
That are under a million. But still a fairly good amount. And then some that are just… Kind of like… Less than that. Right? So there are some… There are like… Like three or four different… Like how are the categories like… Like three or four different categories…
Of selectiveness… Within this column. And when we… You know, when you think about… Trying to index for selectivity… This is the kind of stuff… That you sort of have to take into account. Because you really do need to…
You know… Look at how people might be… Searching these things. Especially if someone was going to… Search a range of Vote Type IDs. Like if someone… You know… Did not just do an equality on one…
Like someone… These like 40 million rows… 37 million are going to be Vote Type 2. So… Selectivity isn’t just a guarantee. Right? But we have… We got this store procedure.
And what this store procedure does… It was it will search on Vote Type IDs. And that’s what… Sort of this first part does. Alright? We get some data from the Votes table… Where Vote Type ID…
Is equal to Vote Type ID. And we want to find… Votes… Where… I don’t know… We have…
Posts… That… So… I’m going to tell you a funny thing about this demo query. It does not make… The most sense in the world… Given the…
The stack overflow schema. But… It works really, really effectively as a demo. So I’m not even going to try to explain… What this thing is actually looking for. What I’m going to do is just show you… The monstrosity…
That is the parameter sniffing that goes on here. So… For these queries… All of these vote sniffings… Vote Types 1, 2, 3, 5, and 10…
I’m not going to run these in front of you… Because they run for a pretty long time. If we go look at the saved query plans I have for them… Vote Type 1 runs for around 15 and a half seconds.
Alright? Vote Type 2 runs for around… 35 seconds. Vote Type 3…
Another 15 seconds. 12, 15, 14… So like 13 and a half. So… These are all fairly long running queries. And these are all… Ones that I run with recompile.
So there’s no sniffing here. These are just plain slow. These just don’t do so hot… When SQL Server comes up with an execution plan for them. And that’s sad because…
A lot of the times when I talk to people… About parameter sniffing issues… A lot of them think that recompile… Is just a straight up… You know…
Golden ticket to solve the problem. Sometimes it is. Sometimes you can… You can go pretty far with a recompile hint… Placed in the right… In the right… In the right spot. And you can avoid… Dealing with…
With parameter sniffing… Because SQL Server is just going to come up with a… Good plan each time. But you can also run into just weird stuff… Where SQL Server… Like you know… Goes and just picks a bad plan.
Even with recompile… SQL Server is capable of picking a bad plan. Even if you compile a query… Specifically… For a parameter.
You can have a bad plan for that. For all these vote types… So 4, 6, 7, 8, 9, 11, 12… We have… Fairly good and fast plans.
These ones I do feel… Pretty safe running in front of you. Right? So all of those finish in just about 2 seconds. Right? We are on… Oops.
Didn’t go over quite far enough. There we are. About 2 seconds. So we’re in good shape there. Right? And if we look at the execution plans… These are going to get much different query plans… From the ones that we saw over here.
Right? These are all going to… These are going to get… Like you know… Slightly different plans than we saw… From like the plans that recompile.
Right? There’s a lot of like hashing… And craziness going on over here. And like big parallelism… And scans and all that. And over here we have something… A little bit different.
TZH said… SQL Server picks a bad plan. Certainly not. Yes I know. It’s insane. You know… But that’s the thing about working with databases. Is that… You’re not special. Your query isn’t special.
Your indexes aren’t special. Your schema is not special. I mean… Maybe it is special. But here’s the thing. Is the optimizer is a Swiss army knife. It is a general purpose utility.
And… It needs to be good… Across a wide variety… Of hardware… And indexes…
And schemas… And selectivities… And… And target… Like… It has to be good across all these different things. And that’s tremendously hard. And most of the time… It does pretty okay. But there’s all…
I mean… There’s all sorts of stuff that… You know… Like… Like you and I can do… To mess with the optimizer. To like make it… Like make it pick something bad. But then there’s… There’s just… You know… Blind spots.
There’s… Things that the optimizer… Is strong at. And… You know… There’s… Just sort of… Weird edge cases where… You know…
It… It makes some funny… Inference… Where like something is… Just cost it all wrong. So… You know… There’s that to consider. You know… It’s not like the optimizer is… Designed to come up with a perfect execution plan…
For every possible query. It’s sort of a general purpose thing… To find a good enough… Cheap enough plan pretty quickly. And… I’m… I’m cool with that. I’m cool with that because…
Uh… People still have to call me to tune queries. So… What we’re going to do is focus in on… A couple… Vote types.
We’re going to look at vote types 4 and 1. We’re going to look at 4 and 1 because… These… Two queries… Have… Fairly different run times. But also…
They have very very different distributions. I don’t want to sit around waiting for vote type… ID 2. Because vote type ID 2 runs for about 40 seconds. And I don’t want to sit… I don’t have enough to say… To kill 40 seconds every time I need to run this.
This one runs for about 15 seconds. I can… I feel very comfortable filling 15 seconds. With either… Um… Badums… Whatever’s…
Uh… Humming… Asking how you’re doing… Uh… Telling you what I ate for breakfast… Uh… Complaining about… Not being able to go to the gym. And just various other things. There’s like…
Fun stuff that I think… You know… I think… I like talking about weather. Weather is a big one. Weather is a huge one. So what I’m going to do is… Even though we ran this with recompile… And there’s nothing really sitting around in the plan cache…
To make this thing useful… Or to make this… To be useful to us running this. What I’m going to run… Is the… Store procedure for vote type ID… For…
Just alone by itself. And this is going to finish really quickly. And we’ll get this… This nice kind of… Like… This is like a wonderfully… OLTP-ish… Execution plan. We have…
Nested loops joins… And we have a little… Little tops… And little seeks… And just all sorts of nice little things going on. I also want to point out… Something very, very annoying…
About… Uh… Execution plan warnings. If you notice… This nested loops join… Right here… Has this red X over it. Right?
The sassy red X. And if we look at the… We look at this operator… We look at the tool tip for it… It’s going to tell us that we don’t have a join predicate. And this is so absolutely wrong… Because we have a seek here…
And the seek predicate… Is looking for… Is seeking to a very specific… Owner user ID… In the post table. And…
This is searching for… A very specific user ID over here. So any rows that come out of this seek… Because the seek is going to be coming from… User ID values over here. So we’re going to pass out…
One user ID at a time… To the nested loops join. That nested loops join… Is going to go down here. And because this is… An apply nested loops… Right? We have this outer references… Marker in the tool tip.
Which means it is apply nested loops… Not a regular old nested loops join. What they… What some might call… A naive nested loops join. Because we have this… Outer references thing here… We know that we are doing…
What’s called apply nested loops. Which means we’re taking… Basically one… Like one user ID at a time. We’re taking… We’re putting that through the nested loops. We’re going to seek here… And seek here for that same…
For that same user ID. And then we will… Whatever we join together here… In this nested loops join… Is going to be a match. Because we are searching for one at a time…
From these two things. So this happens very, very quickly for us. Right? And that’s pretty good. And now we’ll actually see… For vote type ID 1… Things be a little bit better than 15 seconds…
Even though we’re using an execution plan… That was not… That did not… That SQL Server did not optimize… Specifically for vote type ID 1. Amazing, right?
When we use a plan… That was created for a different parameter… This actually finishes a little bit faster… Than it does when we come up with an execution plan for it. Specifically for that vote type ID.
Now… This… The operator times here are probably going to look a little confusing. He says, sorry… But why does it say no join predicate then? Because the warning is very naive.
The warning is not aware of… The fact that we are doing apply nested loops… And it’s just kind of dumb.
Sorry. That’s all it is. It’s just not smart. I wish there was a better reason… But it’s just stupidity. Probably something just…
It got implemented by a summer intern… And the summer intern did not… Think about all the marvels and miraculousness… That can occur within an execution plan.
It’s just not terribly well thought out. Most of the time you can ignore it. There is… Like… I think when it first got added… People used to freak out about it.
I think a lot of why it got added at first… Was back when people used to write old style joins… Where they’d write from table, table, table… And put the join condition in a where clause.
Where we’re… Like, you know… It was fairly easy to… Like… Forget a join condition… And sort of end up with like a crazy Cartesian product from things. But, you know…
And I don’t know… Maybe apply nested loops wasn’t around… When this thing got added as a warning. I just don’t know. But it’s pretty poorly done. But, you know… We have this no…
We have the no join predicate warning… Even though… Anything that we seek to here and seek to here… Is going to be a match… Because it’s the same user ID that gets brought out here. It’s just not very smart. So…
Looking at this execution plan… It’s a little confusing… How things go. So… If we look… If we look at the properties of the plan…
We look at the query time stats… We’ll see that… Let’s see… Question… What compat level do you run that DB on?
This is 150. 150. 150. We’re on SQL Server 2019. You don’t mind going to look over here. Oh!
That’s SQL Server 2017. I’m in there… I did not… Get rid of that database. Or I did not get rid of that server connection. Now SSMS is going to make me wait. Stupid SSMS.
If only Azure Data Studio was any good at anything… Other than developer eye candy… I would like to use something other than SSMS someday. I’ve tried other IDEs…
For SQL Server… Like… What’s that? JetBrains 1… Or like… Beaver DB… Or whatever it is…
None of them had the same flavor. So let’s see… There we go… There’s 2019. So let’s go look at… Stack Overflow… Properties…
Options… This is going to be… Compat Level 150… There we go… There we go… Oh, don’t worry… It was my own stupid fault for not… For not disconnecting that server…
When I switched over to use 2019. Yay! Alright? So looking at this execution plan… We can see that… So like… When SQL Server… Runs…
Queries in batch mode… Right? We can see a lot of these… Operators are going to be run in batch mode… Even though this is… This is all rowstore. This isn’t columnstore stuff. This is a new SQL Server 2019… Engine feature.
It’s only for Enterprise Edition… But me being… Me using Developer Edition… Means I get… All the wonders of Enterprise Edition… For free! Ha ha ha! Go take it live to prod!
But because we have so much… Batch mode stuff going on in this plan… A lot of these operators… Are just going to be… Tallying up individual times… That’s a big difference between…
Row mode plans… And batch mode plans… Now… This query plan is going to have a mix… Of row and batch mode in it… We can see that this nested loops join… Is in row mode…
So what we’ll see is… You know… Like all… Pretty much all of like… The accumulated stuff… From here… Here… This sub tree… Kind of rolled up to this nested loops join…
But this hash match aggregate… Which is in batch mode… Does have… Sort of the majority… Of the 8-ish seconds… That this thing ran for in it… Now I know it’s spilled… And that’s not…
That’s not nice… But you can see that… In total this thing ran for… About 8.5 seconds… And if you want to see… Another cool engine feature… With SQL Server 2019…
Batch mode… Oh… Did we still… We still spilled… Let’s give this another run… Wow… Oh… That’s a batch mode spill… Maybe it’s just never going to get better… Are you ever going to figure it out?
No… You’re never going to figure it out… So much for that… So like what I was going to show you is… Under memory grants… What we should get… Is this thing…
Adjusting memory grants… And saying… Ah! I didn’t give you enough… Let me give you more… So… We are just not… We are just… I think just not moving up fast enough…
I wonder how many… Runs… I wonder if I can do this like… A few different times… And actually get… The memory grant… Yay! It only took 4 executions…
To get the memory grant… Right… But now this finishes much faster… Doesn’t it? Now this finishes in a reasonable amount of time… But I want to show you a downside of that… If we go back and we use…
Vote type ID 4… Now we are going to have a different warning over here… That says… We have an excessive grant! No!
And now if we run this again… That will probably go away… And we will use… We will ask for far less memory… And if we go back to this one… We will go back to spilling… So… Nice try!
I guess… But this is going to go back to being slow… Is there a way to tell Dev Edition… No there isn’t… If I wish there was… People have been asking for it… For as long as… There have…
There has been Standard and Enterprise Edition… But Microsoft has not budged… Don’t know why… Don’t understand why… So like the… Like the… I think the… The most compelling theory that I have…
For why… There is no… Button… To make Developer Edition… Act like Standard Edition… Is because they want developers to go out… And get all these secret performance features…
And be able to use… Like fully… All of the programmability features… And they want… Yeah exactly… They want you to taste the good stuff… They want to get you hooked…
On Enterprise Edition… And then by the time… And then if you try to go live on Standard… And everything falls apart… Then…
Well… All of a sudden… Your CPU cores are worth $5,000 more apiece… But anyway… So we just saw… One of SQL Server 2019’s fancy features… So actually this is a 2017 feature…
Batch Mode Memory Grant Feedback… Was a 2017 feature… But with Batch Mode… Being available on rowstore in 2019… It is sort of new to rowstore… You could do all sorts of things…
To fake SQL Server out… To make it think that queries were… Going to get like fully… Like Batch Mode… Batchy Modey on 2017… Even if they weren’t… With like…
You know… Empty temp tables… With columnstore Indexes on them… But… Not here… Not here… We just get it for free… And by for free… I mean with Enterprise Edition… So not really for free at all… Right?
But so… That kind of stinks… Let’s see… The cost jump… Isn’t a small one… Can’t think of how many people… Do that and just think… Okay… I’ll spend… Yeah… I mean… It all depends on the organization… Right?
Some people… Some people might… Find that out… And just switch to Postgres immediately… I wouldn’t blame them… So let’s run… So let’s re… We recompiled vote sniffing… Right?
So we looked at what happened here… When… When vote type ID 4 runs first… And then we run vote type ID 1 next… Right? What happens… Vote type ID 1 uses the plan… For vote type ID 4…
And… What do we get? A plan that runs for about… Seven, eight seconds… And then… Now let’s turn the tables… Let’s run… This query for vote type ID 1…
And we get this plan… Which is a pretty decent plan… On 2019… The plans that I showed you over here… Were actually for 2017…
I should need to… I need to re-screen cap those… Because things are a little bit different… Lee says… We’ve had issues of late… With new performance improvements… For 2019… On our managed instance…
I would love to hear more about those… Please tell me more… And now when vote type ID 4 reuses… So this one… So this actually is a 2019 improvement… So one thing that’s actually very cool…
With SQL Server 2019… Is… We get… These handy dandy… Adaptive joins… Right? I love it…
So let’s look at… What happens… When we post this for… When we run this for a truly… Villainous vote type ID… See vote type ID 5… Is going to get a very very similar plan…
To… What happened… For vote type ID 1… We’re going to have this same sort of… Adaptive join thing going on… Except this one just doesn’t go as well…
So that… When we ran it for vote type ID 1… What happened? Fast. Right? When vote type ID 1 got its very own…
Gym jam plan… Everything was great. Now… I don’t know. We’ll see what happened. Microsoft support turned off…
TempDB recompilations… Which stopped our instances falling over. That is a very bold statement. I would like to hear more about… How they turned off TempDB recompilations.
Do you mean recompilations from temp tables… Or from table variables? Because… Because table variable deferred compilation… Is a new thing in SQL Server 2019.
I would love to hear more about this one. That is a… That is a big sentence, Lee. That is a big sentence…
Some advancements, huh? Okay. Around…
Around table variable deferred compilation… Or something else. Inquiring minds. Still trying to get… If you’re still trying, Lee… You may never… Your efforts may be in vain. I don’t know.
I don’t know. I don’t know. I don’t know. It’s all craziness. Ooh.
Ooh. We have… We have a… We have a blog post. Let’s…
Let’s actually bring this up… In the browser… So everyone can see it. Let’s be nice. Let’s be nice people. Ooh.
Joe Sack. Handsome Joe. Oh. Don’t you just love when Joe Sack blogs? I love when Joe Sack blogs. I get so excited when Joe Sack blogs.
He’s like one of my favorite bloggers. So let’s see. Reduce recompilations for workloads… Using temporary tables across multiple scopes. Ooh.
That is a mouthful. Let’s see here. Create or alter outer proc that creates a table and then executes an inner proc. No, no, no. This is fine. This is the kind of railroading that I live for.
And then inner proc will insert into outer proc and select ID. Okay. Let’s see.
We create a temporary table. Ooh. CU5 included a fix for that feature. Yes. It sure did. Lucky CU5. Thanks.
Thanks for that, CU5. The CU5 stuff for the scale UDF inlining was especially troublesome. Temporary tables across multiple scopes. Who would do that?
If you have to ask that question, you have not lived the nightmare life of a consultant who has seen what many, many independent software vendors do to SQL Server. Where you’re like, have you used a database? Do you know what to do with a database?
So let’s see here. The end result is a reduction in unwarranted recompilations and associated CPU overhead. The number of occurrences with the blue line representing batch requests a second and the green line representing SQL recompilations a second.
So that is quite a bit of recompilations up here and quite a low number of batch requests a second here. It’s funny because they share very similar spikes and peaks and valleys in those. And this one over here, wow, you push that way up.
The feature was enabled after we saw improve throughput. Wow. Thanks, Joe Sack. Everyone say thank you to Joe Sack.
Everyone go on Twitter and say thank you to Joe Sack. Let’s see. There we go.
Let’s thank you, Joe Sack. I love Joe Sack. But yeah, they turn that off.
And suddenly our post-deployment scripts would work and the instance would stop falling over to the other node. Well, that sounds like a win to me. Sounds like a big win to me. I would like it if that stopped happening.
But now I have to ask, Lee, what are you doing with temp tables that made your managed instance fall over during deployments? Now I’m fascinated. Now I need to know what’s going on with these deployments.
All right. So when we run this stored procedure now. So we saw vote type ID 1 get a fast plan. We saw vote type ID 4 get a fast plan.
We even saw vote type ID 1 use vote type ID 4’s plan and be okay. But what struck me, what caught me out is just so crazy and odd. Just so bananas.
Is when I run this query with vote type ID 5. This thing takes 2 minutes and 17 seconds. Now the blog post that I wrote about this, I was using a full-size version of the Stack Overflow database where this thing would run for like 15 minutes. I’m using the 2013 version for this because, you know, I just don’t want to make you wait 15 minutes.
All right. That’s bad news. Lee says, nothing special.
I checked this section of the script. It’s the Wild West with those managed instances. Okay. All right. I mean, I’ll believe you on this one. I’ll believe you on this one. If we look at where a lot of the time is spent in this query plan.
This index seek is not slow. This compute scalar is not slow. This index scan is not slow.
I know everyone freaks out when they see index scans. But this index scan is 129 milliseconds. I’m pretty hip to the speed on that. And I’m pretty cool with this one taking 167 milliseconds.
Now, since this join is adaptive, we’re going to see a second potential join down here. But you can kind of tell by the width of this line that it didn’t do any work. A lot of the time in this query is going to get spent here and here.
All right. You can see that pretty well. If we go look at the total execution time on this, we will get a long time.
Long time. Why are you linking to other people’s Twitch streams in here? I’m going to ban you for spamming Coyote McD.
I’m going to kick you from the chat forever. Dead to me. Dead to me. So what happens up here? Something kind of interesting happens where this hash join is running. And let me just bring up the properties of it.
Because the properties of it is where we get all of the information kind of happening at once. So the execution mode over here is batch. And if we look at what it’s up to, we can see that it is a bitmap creator. And it is true that it has created bitmaps.
All right. So we created a bitmap and we use that bitmap down over here in this part of the plan. This index scan and this index scan.
Now, you can see that there’s a fairly bad estimate from one of these. All right. One of them is pretty good.
The other one is pretty bad. But if we look a little bit closer. Oops. There we go. Look a little bit closer.
We have a predicate that wants to probe with this optimized bitmap filter. What are we even doing here? What are we doing here?
We are trying to apply a bitmap filter to this index scan. It is not going well. And actually, you know what? I take that back.
It might go spectacularly well. The problem is that we get a very, very bad estimate because of that. Now, I’m going to stick a link into chat for a very, very smart post. If I can learn how to type.
By the patron saint of SQL Server Performance, Paul White. It’s about batch mode bitmaps. And you can kind of get a feel for just how naive a lot of the cardinality estimates for batch mode bitmap filters are.
And since this hash mode semi-join over here is a bitmap creator and we use a bitmap, we get a pretty bad bitmap estimate. He says, I still don’t fully understand bitmap’s role. Yes.
They are. They can be rather difficult to grasp. If you might help you understand a little bit. Let me grab another link for you. Do, do.
Where are you? There you are. Ha, ha, ha, ha, ha. Where is? Yes. Yes. I just need to make sure to mute myself from there. So, I have a video, an old style video.
An old style video. Over on YouTube. About useful and useful, useful versus useless bitmaps. That might, might help you get some understanding on that.
Don’t watch it now though. Because it’ll just be distracting. And if you hear, if you hear me talking from two different videos at once, your, your head might explode. It might not be the most fun time in the world for you.
So, this is not a particularly good plan for, for vote type ID 5. And it’s not a particularly good plan because of the bad estimate that we get from that bitmap over here. Where way, way, way, way, way more rows come out of this operation than expected, right?
We expect 6,000 and we get, let’s see, 3, 0, 4, 9, 3, 4, 1. So, we get back 3 million. And worse, when we join the 3 million rows from posts to the 8 million rows from badges, we get just about everything.
All right. So, we have another bad estimate. What’s the CPU time on what?
Coyote McD. Be specific about on that. And I will happily answer you. So, we get this adaptive join. And this adaptive join is a join just like any other.
You want to know what the CPU time on this is? Let’s look. Why am I, why am I not saying where you go? There we go.
Yeesh. I don’t know why that took me so long. There you go. So, 11,000 seconds. 11,000 milliseconds, rather.
So, that’s fun. But the elapsed time on that’s a stinker, right? What happened there? What happened to you? Let’s look at it. Oh, that’s not too bad.
It’s not so bad at all. You could do far worse than that. So, we’ve got this adaptive join. And this adaptive join spills because we have just some bad guesses going on over here.
How much it spills? Not terribly bad. There are hash spills, though.
So, hash spills can get kind of weird. But really, and what struck me, I think, most while I was trying to figure out what was going on here is that if we had a better guess, SQL Server might do things differently. If SQL Server had made a good guess about how many rows might come out of, like, one of these, we might see an aggregate over here somewhere.
So, we see an aggregate over here. SQL Server decides to smush a whole bunch of rows down to one row here. But I think if SQL Server understood that we were going to end up with, let’s see, 217292920 with a nine-digit number of rows here.
Because remember that we have to take all the rows that come out of this index seek. We build a hash table here. And then we have to probe the result of this join.
Which, when you have a guess that’s off by 605,000%, becomes quite a chore. So, all the results of the index seek on the votes table, right? Which is correct, right?
That’s an accurate guess, right? That’s 100% there. SQL Server was spot on with this one. SQL Server was spot on with this one. But when we get here, and we have to all of a sudden take the rows from the votes table, and we have to probe into the results of this adaptive join, probing into the 217 million rows, yeah, the 217 million row result of that adaptive join is unpleasant.
Now, it’s unpleasant because that’s just a ton of rows. Like, all of a sudden, like, this thing running for two minutes and 20 seconds makes a lot of sense. Because that’s just a lot of work to do.
That’s a lot of probing to do. That’s an unhappy amount of probing to do. What I thought was very, very funny, kind of about these circumstances here, though, was if we take the recompile out of the equation, right?
Let’s just take recompile out. Let’s leave recompile out of it for now. Let’s run for vote type ID 4, and let’s get this. Wait, no, we do have to recompile the proc because I used it for something else.
There we go. Let’s get vote type ID 4, and let’s run that. So now we have the vote type ID 4 plan. And let’s get the vote type ID 5 plan.
Let’s parameter sniff on purpose. Let’s have a parameter sniffing party, you and me. Let’s all get together and do that. When we reuse the plan for vote type ID 2, what happens?
It finishes almost immediately, right? That runs in about two seconds if we look at the query time stats on that. Two seconds.
So let me ask you a funny question. If you had a query that started showing up on monitoring is running for five minutes or two minutes and something seconds, and you saw that maybe the compile time parameter and the runtime parameter were different, you were like, oh, we got a parameter sniffing problem.
But it’s not quite that, is it? Because if we run this store procedure for any of these queries except vote type ID 5, we get the exact same execution plan that we get when vote type ID 5 runs.
There’s no real difference between them. And these all finish relatively quickly. It’s the same execution plan over and over and over again.
And we’re recompiling all of these. So we’re coming up with an execution plan specific for 1, 2, 3, and 10. But when 5 runs, whoo! When 5 runs even for itself, the execution plan for 5 sucks.
When 5 uses another, even any one of the other tiny little plans, 5 does fine. We almost have reverse parameter stuffing because the plan that we get for vote type ID 5 sucks for vote type ID 5 very specifically. If any other query uses a plan specific to vote type ID 5, it’ll do just fine.
If we recompile this, and we run for 5 first, oops, you know what? We’re not going to run for 5 first. We’re going to run for 1 first because 1 gets the same plan as 5.
It gets the same adaptive join plan. We get all that stuff. And we rerun this for 4. 4 does just fine with this plan. 4 just shrugs it off.
4 is like, you know what? I got this. It’s when vote type ID 5 gets that really, really bad guess. Everything just goes to crap. So how can we fix vote type ID 5?
How can we remedy vote type ID 5? How can we get in there and start messing with things? Well, one way might be a temp table.
All right. So if we create a temp table called votes, and we put that initial part of the join or the initial part of the query that filters down to the vote type ID. Right.
So because remember, Lisa has a query hint. Sure. What query hint? Tell me which query hint to use. So if we filter down to just the vote type ID we want first, what we can do is take the results of just vote type ID 5, isolate that, isolate the cardinality involved there, and then maybe we can do a better job with this query or with this execution plan. Maybe stabilizing that will be a good idea.
Let’s sacrifice one of these things to be vote type ID 5, and let’s see what happens. Now, my computer over there is humming, so I know we did some work. I know we did some work.
And what happens here? Well, that’s pretty all right. Oops. That’s pretty all right. Do an index seek over here.
Do a couple of stream aggregates over here. We insert the results into our temp table over here. The whole thing takes about 1.5 seconds. And now then we have a pretty quick query over here, too.
Now, notice something. We still end up with the exact same execution plan that we had before the temp table. Right.
So what happens in here is nearly identical. We start off with the votes table up here. Here we compute a scalar. We have a hash join. We have an adaptive join. And then we have posts and badges over here.
So we make just about all the same mistakes, but we just don’t have that same terrible performance. Pretty crazy, right? So this is another place where paying too much attention to costs and estimates and all sorts of other things like that can sort of be detrimental to query tuning.
The estimate isn’t as wrong. No, it is just as wrong.
That is off. Well, I mean, okay. So, yeah, that is slightly less wrong, but it is still wrong. It’s not as wrong, but it is still pretty wrong. So it went from 6,000 to – so it went down by – I don’t know.
I’m not exactly sure what an order of magnitude is. I hear it’s a big number. But, yeah, this went from 605,000 to 6,000. So it’s still wrong by a big chunk.
But I don’t know. Like, what would I fix in here taking 1.9 seconds? I just don’t know what I would do in here. And if we go look at the query time stats over here, what would I fix?
I don’t know. I don’t know what I could fix in here to do better. This thing up here is still up to the same nonsense with the optimized bitmap.
The optimized bitmap is still the reason why we get this not good situation here. I don’t think it occurs down here, though. Oh, it does.
Interesting. So I didn’t notice that before, but the optimized bitmap actually gets applied to the badges table this time, too. In the previous plan, we didn’t get that. We didn’t have that extra bitmap-y goodness. So that’s actually a fun new thing that I just noticed in here is the bitmap gets double applied.
I wonder if we have a bad estimate here. Yes, we do. Now we have a bad estimate here. Now we have an estimate of 490%.
In the old plan, this was spot on. So we actually got two bad estimates from the same bitmap. Isn’t that sweet? It’s so nice when bitmaps just work out like that. It’s so nice.
It’s so nice. So let’s look at something a little bit different. So we looked at a couple of possible different ways, or we looked at one way to rewrite this using a temp table up here. I think my main beef with this, though, is if we had to do that for vote type ID 2, if I run the same proc for vote type ID 2, we have to put kind of a lot of rows into a temp table.
Probably more like if this was a highly concurrent store procedure, if this was something that was running a lot, it was like really just like, you know, like a critical part of the application, we could end up, you know, like kind of having to do a lot of work, right?
Like in some cases, like we would, like we might end up having to put a lot of rows into a temp table. This also takes about 10 seconds total, which I’m like not thrilled with either. No, and it’s just, and it’s all spent up here, right?
It’s all up in this top part. So like, I don’t know that I’m thrilled with this. Like, I don’t know that this is like, like, I don’t know that I’m really psyched on the, like, like that for the temp table. I don’t know that I just want this thing like pounding around on temp TV.
So let’s look at a couple different ways that we might be able to rewrite this. Now, this first one was all my idea. This one was mine, I think, at least as far as I know.
I’m going to show you another one from a smart friend. But this one here, we’re going to, so what we’re going to do is rather than, you know, use the votes table to drive the temp table.
We’re going to use the post table instead. So we’re going to use that first join between posts and badges. And we’re going to have an exists back to the votes table to make sure that we really narrow down the number of rows that we care about, right?
So let’s, oops, that’s F4, not F5. And that ends up pretty quick. Oops, I didn’t turn on execution plans there.
Let’s try that again. I’m going to have to drop this P table, right? Can I get an expert witness on dropping a temp table? We’ll just say drop table P.
You know why? Because we can do that. We are live. We are live in dev. We can do whatever we want. We can do what we please. All right.
So that takes about 2.7 seconds. And that’s a pretty reasonably small number of rows, right? Like I’m not going to cry over a 200,000 row temp table. And then if we run this query, just looking for what’s in that temp table instead.
And I’m going to, actually, no, I have to redrop P, don’t I? So I’m going to run that all at once now.
I wanted to make sure that we had the temp table insert isolated to see what that looked like. So now when we run this, that’s about, boy, that varied a little bit, didn’t it? Yes, select 1, 0.
And then this part runs relatively quickly. All right. So we simplified this part. I don’t know that I’m thrilled with, like, this part, this temp table insert. I don’t know.
Maybe I’d stick with the votes one. So, like, I don’t know. Like, so thinking about, yes, select 1, 0. It doesn’t do anything. It doesn’t exist.
So thinking about, like, what I would do with a situation like this, where if I knew that if, like, at some point, like, I could have a query that runs about a second faster and uses potentially more tempDB or have a query that takes an extra second but uses a reliably small amount of tempDB, I might just opt for losing a second. And depending on my system, depending on, like, sort of, depending on, like, you know, the, like, what I care the most about. If we’re just going purely for time, I don’t know.
Maybe I would pick the slightly more abusive to tempDB query. But since we have a choice, we can figure it. Maybe we don’t need to do that at all.
So this was user submitted by my dear friend Paul. My dear friend Paul from New Zealand. And he came up with this query, which is insane and typical of Paul outdoing me in every possible way.
And this doesn’t need a temp table at all. All right. So we’re going to create this.
We have to create a slightly different index in order for this to really shine. But it runs pretty well. Lee says, I realized I’m guilty of getting it fast rather than thinking about cost. Well, you know, don’t think about query cost.
Query cost is a not good metric to think about. You know, the cost that I was thinking of was more related to, you know, resource usage with tempDB. Which, you know, tempDB is kind of made for that.
TemDB is, like, kind of made to take a beating. But, you know, you can certainly run into strange contention up in tempDB. You know, if I really wanted to spend a long time on this, I might try to run, I might try to use, like, O-Stress or something to run a whole bunch of copies of this all at once. And then, you know, kind of, like, you know, see if, like, I could hit some, like, like, what kind of tempDB contention I could hit.
Sort of, like, you know, just, like, running, like, 20, 30 copies of this. Option max.80. Coyote McD, here’s the thing, man.
You have servers where you could use, well, you can’t use max.80, can’t you? You can use max.64. You could use max.64.
TZ says, are the differences between temp tables and table variables any different in newer versions? They are only slightly different in SQL Server 2019. If you, you know what, if you tune in tomorrow, I’ll talk about that.
I think that’s a good topic for tomorrow. So, tune in tomorrow, and we’ll talk about the difference between temp tables and table variables in SQL Server 2019. That seems like a good, that’s, you know what, that sounds like a winner.
Now I’m mad I didn’t think to do that one today. It’s talking about this stupid blog post query. All right, so, with this, let’s see how this goes.
Let’s turn on execution plans, though. Let’s make sure that we get that. And once again, Paul has absolutely wrecked every single thing. He’s a madman.
So, this finishes in 700 milliseconds and doesn’t require a temp table at all. It is kind of strangely written. It is kind of strangely written.
And we do have to force the index here. I forget exactly what happens if we don’t. Yeah, it goes back to using the other index. So, you know what, let’s get rid of that one.
Come on, Pally. Let’s put index on votes. There we go.
Now let’s see what you do. There we go. So, even without the index, so without the index and with just that index in there, we actually, we do pretty well.
And this is without a temp table at all. We do still have this annoying warning on the nested loops join, which is completely wrong, but that’s okay. Okay. But that’s okay.
What we don’t have, though, or what we, actually, more importantly, what we do have. What we do have, it’s very, very important. And this is, I think, what makes the biggest difference in here.
If we think logically about all the things that we’ve looked at with this query and not liked, the biggest one is that SQL Server was not choosing to aggregate any of the join stuff prior to a join. None of the join columns are getting aggregated before we went into the join.
Like, logically, like, you and me thinking about it out loud, like, there’s, like, if we know that a column is not reasonably selective, a column is not reasonably unique, the column is going to have a fair amount of duplicate values in it, we would probably want to aggregate that down to as few values as possible to go into the join.
And that’s what happens here. We seek into the votes table, but then we also pre-aggregate our join column a little bit. If we hover over this, you will see that we aggregated the user ID column from the votes table down to however many, however few rows we could reasonably aggregate it down to.
So let’s see here. We started with, let’s see, 3, 5, 1, 1, 7, 3, 3. So we started with 3.5 million rows, and we were able to aggregate that down to 267,000 rows, which is a substantial reduction in the amount of work we would have to do at join time.
And then this has to do a lot of work. Let’s see. Coyote McD says, that’s a lot of seeking.
Does RCSI make a big difference in lock overhead on this one too? Yes, but let’s see. What would be a good way to…
Show that. You know what? We can just come back over here to this one. We need 4 and 1 and 1 and 4.
So what Mr. Coyote McD is asking about is sort of a follow-up blog post to this, where if we run this, what we’ll get is… So this is one that I think went out yesterday.
It was about how read queries need to take locks too. Right? Read queries take locks.
Unless there’s a lookup in the query. They don’t accumulate locks the way modification queries do, though. They don’t build up over time. They just kind of get taken and released.
Again, unless there’s a lookup in there. If there’s lookups in there, you’re screwed. Locks can hold on for a while. But if we run this query between 4 and 1, the vote type ID 1… Actually, I’m not sure which…
Did we? Yes. We’re going to do the same thing. So looking at this now, this ends up taking a whole lot longer to get those rows in there. Even though it takes 7.5 seconds to put one row into this temp table versus it taking nothing for this one.
Right? And now we could play with it a little bit. I don’t think we have any batch mode operators in here.
I want to double check, though. Because we might get enough of a memory grant connection to help with that. But I don’t think so. No.
So this two stream aggregates aren’t going to be valid for batch mode. Right? They’re not going to be batch mode eligible. The compute scalar is in row mode. This compute scalar is in row mode.
This index seek is in row mode. And this sort is also… So this is all row mode stuff. So we’re not going to get batch mode memory grant feedback on this. We would just get the same crappy 7.5 second plan over and over again.
At least I think… Pretty sure we need that batch mode stuff. Oh, it was a little faster. Did we? Oh, no.
I guess it did. Let’s go look. Oh, yeah. Yes, adjusting. Good. Oh, that’s nice. I wonder what we’re getting that from. What a nice touch.
Thanks, Joe Sack. Thanks again, Joe Sack. I’ll draw another heart on your head later. But what’s interesting, though, is if we run this for vote type ID 1 first… Well, this is faster.
I don’t want to go parallel there. But vote type ID 1 and 4 run much more quickly. And so… But what Coyote McD is getting at was sometimes you do have…
There can be overhead. Let me grab the blog post that he’s referencing so that everyone has it. You don’t have to go searching for it.
The last thing I want anyone to have to do is go searching for things. It is that under some circumstances, the locking overhead of reading data from a table can be particularly loathsome. So one way to test that is to, let’s say, add a page lock hint here.
And we will get… So long as the demo gods are smiling upon me. Well, that could have gone better.
Highlighting was never my specialty, folks. I apologize. I apologize. Demo gods are not smiling upon me.
This is what happens when you go off script. Thanks, Coyote. But at least this part… At least for this one, the index seek was a lot. It was 1.5 seconds before. It was only half a second this time.
So thank God for that. Thank God for the little things. Oh, yeah. That’s fun.
Triple click for a single line highlight. Yes, I know. But I don’t… I’m an old-fashioned guy. And I just…
I just highlight things. I would have messed up the triple click and just… And done something silly there, too. Anyway, it’s been like an hour. And I need to go hang out with my kids, apparently.
So I’m going to get going and do that. If we have any final questions, anything… Anything you want to ask about, know about, SQL Server stuff, you can…
Now is an okay time to do that. Let me go back over to… This one here. And let’s go look at… This.
All right. I’ll hang out here for a minute. See if anything comes into chat. Let’s see.
Thanks to one of your videos, I found a deadlock in a database, which I solved with an index. Nice. All right. Mr. P, you’re welcome. Mr. Pshaw, you are absolutely welcome.
Lee says, thanks to the stream. No, leave. You can always ask more questions. That’s what I’m here for. If you don’t ask questions, I don’t have answers. Isn’t that sad?
Just all these things locked away in my head that I don’t have answers for. That I have answers for. That I just can’t use.
All right. I will try to get on a little bit earlier tomorrow so you fine folks in Europe don’t have to stay up past drinking time to hang out with me.
But I’ll announce it on the old Twitter and whatnot when I want to go live. So I’ll see you all tomorrow. Thanks for joining me. Take care. And, you know, be excellent to each other.
As a wise man once said. All right. adios. Audrey. So, see you.
I don’t know.
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.
this happens for the second time, it could be from my side but there is no Audio