SQL Server Performance Office Hours Episode 82 – In the Azores
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:00 – Lock Pages in Memory
- 00:03:24 – Memory and Performance
- 00:04:38 – Row Count Estimation
- 00:05:30 – Blocking Chains
Full Transcript
Erik Darling here with Darling Data and we are currently in the Azores. You can see that maybe in the background over behind me there. It’s pretty nice. So this is going to be a short one because I’d rather go enjoy some nice stuff. Still going to do five questions but maybe I just won’t ramble on as long as I usually do. So the first question we have is will lock pages in memory? Reduce my latch EX weights. It’s my top weight. The server is a busy one but I’ve query index tuned hard enough to get the CPU down to consistently around 25%. Max.8, cost threshold for parallelism 50, 12 logical cores. Well, no, lock pages in memory won’t help there. But the one thing that you conveniently left out is how much memory your server does have. Lock pages in memory tends to be a better setting for a lot of memory. for servers with a lot of memory where SQL Server sort of negotiating the virtual memory space before acquiring physical memory and doing that whole song and dance is sort of like not necessarily a bottleneck but just adds like a certain to your queries. So I don’t think block pages in memory is going to help with that one. Your princess is in memory.
I’m going to have to go to another castle and then another castle and as usual my rates are reasonable. Though with a server that size I’m not sure. Maybe. Maybe you don’t care that much. I don’t know. Alright. Next question is why does increasing memory sometimes make performance worse instead of better? You are I think full of it my friend. Why does increasing memory makes it worse? Jeez Louise. Yeah. I just so I don’t think that I’ve ever run into a situation where so let’s just say let’s just pretend that I don’t know what you’re deriving performance better and worse from. In my experience there are certainly times when increasing memory will move your bottleneck from maybe queries are no longer IO bound and now they are purely CPU bound. So if by performance being worse you mean CPU got hotter or something then that I can see but you know.
In general my experience has been rather the opposite. So tell me what do you think maybe performance being worse means? I don’t know. How do you think performance being worse is? I don’t know. How do you explain to management that one slow query can take down the entire system? Usually proof because usually that has happened. You don’t really need to explain that to too many people. If they’re talking to someone like me usually something like that has already happened.
So you know usually the bigger a system is the sort of less likely that is to happen. Usually it takes some amount of workload parallelization before that that starts getting wonky but you know. I don’t know what you’re dealing with. Alright. Let’s see. That was one, two, three. Okay. Number four here. SQL Server sometimes massively underestimates row counts. What patterns usually cause that? Well I mean the usual spade of villains. Your Dick Tracy lineup card. You know you’ll have things that you do to your system in your queries.
Right. You’ll have local variables, table variables, non-sargable predicates, things that are far outside the optimizer’s ability to estimate things effectively. You might have out of date statistics, stuff like that. And of course the cardinality estimation model that you’re in will have an effect on that as well. So do check all those things. All right. How do you debug blocking chains that constantly change shape?
I turn on read committed snapshot isolation and usually most of those blocking chains go away. But you know for, I mean, debug them? I mean, geez. It’s usually the same stuff that you would do anyway, right? If the shape keeps changing then you just keep tuning, right? You have your block process report.
You get to see all the stuff that happens and the entire blocking chains. You know, it’s, I guess it’s, if your workload is that different day to day then you’ve got sort of a weird workload. You know, for me, it’s a lot of it, I don’t run into that particular thing a lot.
Usually what I run into is a very common set of blocking and deadlocking things, where at least, at least from the blocking side, the lead blocker is, lead blockers are usually a pretty common set of core things that happen consistently. If your workload pattern is changing shapes that often then, man, you’ve got some interesting stuff going on.
All right. I promised a short one, so here it is. Now I’m going to go enjoy that instead of this. But I do like you and I’ll do another short one tomorrow, I think, probably.
All right. 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.