SQL Server Performance Office Hours Episode 62
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:30 – Community Interaction
- 00:02:45 – Free SQL Server Monitoring Tools
- 00:03:37 – Upcoming Events
- 00:05:05 – Question 1: Indexed View Updates
- 00:08:19 – Query Tuning Debate
- 00:10:04 – Weight Stats Discussion
Full Transcript
Oh, hello, Erik Darling here with Darling Data, your friendly host for this week’s episode of Office Hours, in which I answer five questions that you, the greater SQL Server community, and what community is greater than the SQL Server community, have asked me since the last time, so we’ve got that going for us, don’t we? Down in the video description, you will find all sorts of helpful things, you will find ways to interact with me emotionally, intellectually, but most importantly, financially. You can hire me for consulting, you can buy my training, you can become a supporting member of this very channel, and of course, you can also find links to continue to ask me Office Hours questions, and if you like this content, if you feel like this content would make a meaningful addition to someone’s life, you can like, subscribe, tell a friend, help the channel grow in other profoundly meaningful ways that are not an emotional, intellectual, or financial connection with me.
I mean, you could perhaps imagine your own set of those connections through watching these videos, but one generally will not exist. I will not acknowledge it publicly. If you need SQL Server monitoring that doesn’t suck, for free, open source, no email sign-up, no phoning home, no weird telemetry data, just all the T-SQL collectors that I would care to run on a server to monitor its performance, cool stuff, weight stats, blocking, deadlocks, top queries, and all of this goes into a very pleasantly colored dashboard for your consumption.
And if you are a fan of our new robot friends that have sprung up from vast data centers around the world, there are optional built-in MCP server tools so that you can just ask questions of your performance data and not have to worry about the robots running ramshackle or repshow, whatever that word is. I forget that word. Sometimes they suck.
Sometimes they just jump out of my brain while I’m talking. It happens. The robots trampling all over your SQL Server, you can do that. It’s pretty okay. This will probably be your last chance to buy tickets for my pre-con down in Jacksonville.
That will be on May 1st. When this gets published, it should be April 28th, so you should hurry up and buy your tickets if you care to see me talk about advanced T-SQL live and in person. All attendees of the pre-con will get access to the Learn T-SQL with Eric material.
That’s a whole lot of hours of recorded material, and it’s way more than gets covered just in the one-day class. Other places in the world will be coming up very soon. Wow, it’s just creeping right up on you.
May 7th and 8th, I will be in Chicago for Pass on Tour Summit East in the Midwest. Not the actual East. The actual East would be like New York.
Or Boston or something like that. We’re in Chicago, though, so it’s the East of the Midwest or something like that. I’ll also be at T-SQL Day in Poland, May 11th and 13th. I have advanced T-SQL pre-cons at both of those events, so you should get your butts in seats for those.
Because who knows how much longer we’ll have to talk about advanced T-SQL for. You never can tell. After that, I will be at Data Saturday Croatia, June 12th and 13th.
As well as Pass Data Community Summit taking place in Seattle, Washington, November 9th through 11th. I don’t think that full tickets are on sale for that one yet. But as soon as they are, you should line right up and go to Seattle.
Just go live there. But with that out of the way, it is still April, and we are still baseballing. The Mets and the Red Sox are not off to a very good start.
They are having some trouble. But that’s okay. Because we still get to watch and sort of enjoy baseball. And, you know, win, lose, or draw, you still drink the same amount.
So that’s the kind of wonderful thing about sportsaholism. You can be a sportsaholic and everything is just all the same. Anyway, I believe we need to go over to this Excel file.
And we need to answer some questions here. Hey, Eric. Hey, you.
How are you doing? I send this blurry-eyed after a painful evening in the SQL mines. Well, that sounds terrible. Are you a dwarf? How did you end up in the SQL mines? Without index tuning.
Okay. Do you know any ways to encourage an update to a table that participates in an indexed view to only seek the members of the view? Batching works.
But if I get the batching even slightly wrong. Then SQL will scan 70 million rows rather than seek for like 500. I would probably try doing a little. So I’ve covered this technique in a few different videos.
I can’t precisely recall the titles of them. But it might be like around like query transformations and stuff like that. But what I would probably do is.
You hit the index view with a sub query and a no expand hint. So that when you are updating the table. It is aware of the indexed view.
And it will hit only members of that. That would probably be the way that I would encourage it the most directly. But, you know, this is one of those things where there are many ways that you could have asked this question.
That would have involved sharing a little bit more information. So that I could give you a more intelligent answer. Because perhaps there are many questions that I have as a follow up for this.
But, you know, you could have pasted the plan. You could have gone to plans.erikdarling.com. And you could have shared the plan via that.
There are many ways with which you could have presented me with sufficient information to give you a better answer. But I would probably like combine. So like as far as getting the batching right.
Like I would probably want to do like where the, you know, the key. So like whatever ends up being the unique clustered index in the index view. I would say like, you know, update table select top.
You know, however many batch rows you want to do. From the index view with a no expand hint. So that SQL Server only.
The SQL Server is better able to understand what your goal is. That would be the way that I would first attempt it. But it’s a little hard to give you much more there without seeing some plans or some T-SQL.
Do you and Brent Ozar argue about logical reads being important, not important for query tuning? Nope. I’ve never had an argument with him about that.
I just disagree completely. There’s nothing to argue about. I’m familiar with cross apply and select top one. Good for you.
And I’m familiar with row number filtered with RN equals one. Well, that all depends on what you alias row number is, isn’t it? Alias it is. All sorts of things, right?
In your optimizer rules, I wish SQL Server had video. You show off cross apply into row number that you later filter to row number equals one. How do you decide when to prefer cross apply into row number?
Well, I look at the query plan. If I am unhappy with the performance of top one and I am unhappy with the performance of row number, I may sometimes combine them. And then you often get the best of both worlds where you get a top above the row number or whatever is generating the row number.
It could be a segment in the sequence projected row mode. It could be a window aggregate in batch mode. It all depends on the query plan and which one runs the best.
That’s why we have these alternatives to explore. It should be fairly obvious right now that I care most about what makes the query run the fastest. So if one of those seems a little too slow for me, then I’ll try the different variations there.
There’s not really a rule that I have beyond that. Which weight stats do people freak out about? That usually don’t matter much.
You know, honestly, it’s kind of like you have two camps. You have people who have been like mentally conditioned to freak out over like, you know, like CX weights. And then you have people who have no idea at all what any weight stat really means or when to freak out about it.
So, I mean, probably not. I mean, probably this, I think the CX weights are probably the most obvious answer there. People don’t seem to freak out too much about any other weights.
You know, I can’t really think of anything that, you know, like don’t matter much. I mean, any weight can matter in sufficient quantity. But usually it’s the parallelism related weights that people are like, there’s too much of them.
I’m like, okay, well, compared to what? I don’t know. It’s always fun to sort of get into their heads and start asking like, well, why do you think there’s so many of them?
Could it be because, you know, your queries suck and you have no good indexes? Things like that. Like perhaps you have not changed your parallelism settings at all.
Who knows? There’s all sorts of funny things that can happen on your way to, in a friendly, reasonable rated consultant fashion, get to the bottom of these mysteries with people.
Why do you feel the weight stats are too much? Are the weight stats in the room with us right now? There are many ways you can go with that. But usually the CX weights are the ones that people lean on the hardest as being a problem.
You know, there’s a lot of dumb blog content out there about them. You know, a lot of the scripts that people use to measure weight stats kind of only give you like, what percentage of weights are the various weights?
They don’t really give you context. Like how long has the server been up? How many hours of those weights have accrued compared to how long the server has been up? Things like that.
So there are many things that, you know, just, you know, matter a lot contextually that people don’t take into account. Anyway, I think that’s probably good enough here. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video. I have not quite yet decided what I’m going to talk about. But when I do, boy, are we ever going to talk about it. 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.