SQL Server Performance Office Hours Episode 88
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:03:00 – Lob Data Cleanup
- 00:06:00 – Page Density and Compression
- 00:09:00 – Concurrency and Workload Performance
- 00:12:00 – Memory Pressure Metrics
- 00:15:00 – Identifying Memory Pressure Sources
- 00:17:00 – Conclusion
Full Transcript
Erik Darling here with Darling Data. Boy, am I happy to see you. Another, I mean, you know, it’s so hard. After doing all those Office Hours episodes where I was in fun places, and then one where I got to read a bunch of questions from people who really don’t like me, I feel like, I don’t know, it’s a little bit of a letdown to just be sitting here doing this here. Maybe I should start, I don’t know, going in my backyard. I don’t want to annoy my neighbors. It’s the problem. I have a real aversion to annoying people publicly. Anyway, this is the time of the week and the time of the day where I answer five very important questions from between one and five very important people. I am unsure of who asks these questions and what their question asking cadence is. It could be any number of things. But anyway, down in the video description, all sorts of great helpful links for you to hire me for consulting, buy my training, become a supporting member of the channel, continue to ask me these Office Hours questions despite my distinct lack of exotic locales. And of course, you can always like, subscribe, tell a friend. I do like seeing the numbers go up in some facets of this channel.
Maybe. When numbers go down, that’s when I start getting worried. I don’t know. Anyway, I finally got around to fixing this slide. If you want free SQL Server and Postgres, including Postgres Aurora performance monitoring, I’ve got some of that for free. If you go to code.erikdarling.com, it’s pretty clearly labeled the performance monitor repo. So, you can download it and you can start using it. It’s MIT licensed. You don’t have to do anything with it. I’ll show some stuff when I’m out of the slide deck and before I go into the other thing. But yeah, all the important stuff that you want to collect to know about performance issues. And that’s about it there. It’s free. I make the same amount of money whether you use it or not.
But now it is time. We are officially Septembering. And because we are Septembering, I should probably unhide this slide for next time. So, I can say, hey, in like two months, past Summit, well, sorry, past Summit West. Not West. West is taking place in Seattle, Washington from November 9th through 11th. I have a pre-con there. And I also have a regular session there. And so, you should come to both. Otherwise, because that’s numbers going up for me.
And when numbers go up for me, look how happy I am. Isn’t making me happy important to you? It should be. Anyway, free monitoring for SQL Server and Postgres. You can see very clearly a mix of SQL Server. We have availability groups. We have various Postgreses. I shrank that window by accident.
And then, of course, SQL Server 2016 through 2025. Also supports Azure, VMs, SQL Database, Hyperscale, Managed Instance. You name it. I support it. I monitor it. I just don’t keep the Azure stuff in there because it costs money. Also, Amazon RDS, of course. All the major stuff. Gosh, you’re lucky to have someone like me in your life, aren’t you?
I think I want to repay me with thumbs up someday. Anyway, let’s answer some of these questions here. Let’s see. First one. Ah, my favorite person. It’s been awfully quiet in Paul Whiteland lately. As someone who seems to know him pretty well, is he happily enjoying retirement, secretly building the world’s greatest query plan, or should we expect him to pop up again someday?
Okay. So, I made a deal with Paul White that I would not ask him any dreary questions. We do talk frequently. And part of that is we get along about stuff outside of SQL. We just happen to meet via SQL Server, right?
We just get along pretty well in many topics and regards outside of databases. And so, when Paul decided to do his life thing, and I’m not comfortable telling everyone exactly what he’s up to. But when Paul decided to go out and do some life adventuring, I said, let’s keep talking, but let’s not talk about databases.
And we both enthusiastically agreed to these terms. I did have to insist on them. I don’t know if he’s going to blog again.
I think after many, many, many years of doing it, the man deserves a break. And, you know, maybe if something catches his interest in a new version of SQL Server or a new feature, you might see something, but I couldn’t tell you. All I know, and what I am happy to let you know, is that Paul is happily enjoying cruising around the wilds of New Zealand and doing very scenic things at the moment.
So, I don’t know. I don’t know. We’ll see.
Maybe, you know, at some point, you got to think, maybe he’s done enough, you know? Maybe the man has earned some respite in his life. I think he has.
All right. Have you found a way to have Nutanix? Jeez.
Nutanix. Are you kidding? Hyperconverged storage provide low disk response times. We keep getting 100 millisecond spike. Yes, by moving off Nutanix. Now, this is one of those things where, you know, I’m sure that you have done your due diligence in working with your Nutanix representatives and tech salespeople in order to align your system with best practices and all that stuff.
And, you know, I’m sure that there are people out there who use SQL Server on Nutanix and are happy with it. In my position, I just don’t talk to people who are in a very happy place most of the time. Most of the time, I talk to people who are sad, angry, depressed, confused, just don’t know what to do with anything that’s going on around them.
And so I’ve met a lot of people having some really, really bad times with Nutanix, you know, hyperconverged or not. And, you know, I think just past a certain point, that’s not the platform for you. You know, if you’ve already gone through the correct steps with your sales and tech people and you are unable to get satisfactory performance, it’s time to go.
So it’s sort of like when someone points me to a view and they’re like, it’s 4,000 lines long and it’s slow as hell. And I’m like, yeah, no kidding. Look at it.
It’s a junk drawer for all your worst sins and ideas. It’s every table in the database joined and, you know, it’s just a mess, right? You have picked the incorrect vehicle for this thing, right?
Like if you had a stored procedure instead of a view, you could express this logic in perhaps more sane, rational and easily, more easily optimized ways than you have chosen to express it. And it’s the same thing over and over again. If you have done everything within your realm of responsibility and you are not getting adequate performance, you are in the wrong realm.
All right. Is it ever worth chasing fragmentation numbers for performance reasons anymore? So I do think that it is worth checking in on page density.
I don’t think it is something that is worth automating. You know, you would like, you know, like the logical fragmentation pages out of order thing. You can’t, you just can’t talk me into caring about that.
But the page density thing, which can be affected by, which is affected by deletes and can be affected by updates, would certainly be something that I would check in on from time to time. And, you know, if you have very big tables with, you know, like weird page density issues, then sure, I would absolutely, I would absolutely say, you know what, throw a rebuild in that. And while you’re throwing a rebuild at that thing, you should apply page compression.
Just like do something good along with it. And the reason for that, and this is something that actually happened with the client thing that I was doing. I was helping a client of mine move indexes from the primary file group to other file groups.
And I didn’t look at, like, page density stuff beforehand because, like, it was immaterial to what I was doing. But there were some very, very large tables that shrunk appreciably in size when things were rebuilt, especially with page compression on the new file group. But I think most notably, there was a table that was around 1.3 terabytes that ended up being, I think, something ridiculous, something like 30 gigs or something.
There were a lot of, there was a lot of lob data in there that got cleaned up and stuff. But, you know, like, there were some other things going on with it. But I think every once in a while, sure, it’s worth checking in on the page density thing and, you know, perhaps rebuilding on the larger objects.
If only because, you know, especially when you double that up with applying page compression, you can end up with much, much smaller objects. And that’s a good thing for most servers because most servers don’t have enough memory to deal with the amount of data they’re carrying around. And so you take up less space on disk, you take up less space in the buffer pool, things are generally in a happier place.
So there are reasons to do it. I wouldn’t say that it would be like, you know, some, like, I wouldn’t call it like a general query performance thing. You know, if your queries are scanning large amounts of data, of course that improves.
But if your queries are seeking to data in a B-tree, then either one is not going to make such a profound, like, you know, it’s not going to make, like, fixing logical or page density fragmentation isn’t going to make a profound difference to those queries. But I think, you know, every once in a while it is worth checking in on that and maybe doing something about it. So why do some workloads completely fall apart under concurrency even though single user tests look fine?
Well, I don’t know. Why do you get, why do you drive somewhere so fast when no one else is on the road? Traffic’s not fun, right?
When you have competition for finite resources, and, you know, I don’t mean to get all Adam Smithy on you here, but when you have competition for finite resources, someone is going to have to win in that. Like, you’re sharing resources amongst many, many queries, some serial, some parallel, others taking up memory, others going to disk and trafficking things back and forth. That’s, of course, not, you know, going to contribute to queries that in isolation run perfectly fine, not running so perfectly fine anymore.
You know, that competition can show itself either worker thread starvation, query memory grant starvation, taxing the paths from servers to the disks. And, of course, you know, unless you are a real smart cookie and you listen to my advice and you use an optimistic or row versioning isolation level, well, you are going to have some pretty annoying blocking and deadlocking issues in SQL Server because read committed is a garbage isolation level.
So, the shortest answer I can give you is competition, resource competition, lock competition, all those things. How do you tell when memory pressure is coming from SQL Server or the OS or VM host? Well, if you’re goofy enough to have other stuff going on right next to your SQL Server, you should probably rethink that.
You know, the thing that I always try, the thing that I always look at, rather, the first order metric that I always look at is wait stats. And the reason why I look at those is because I can at least start to get a sense of a couple of things from a memory perspective. Memory pressure shows up in SQL Server, either at the query level is resource semaphore, that’s query memory grant stuff, or is any of the various page IOLATCH underscore letter letter weights.
And, you know, if you have resource semaphore waits going on, then what you’ll find is very large portions of your buffer pool are likely getting cleared out and swapped to disk, and then queries have to repopulate the buffer pool, and that’s when the page IOLATCH weights kick in. But even without resource semaphore waits, you can see rather high page IOLATCH weights because you might just have way more data than memory, and you might not be using page compression, and you might not have indexes well-tuned to your queries so that you can seek to smaller regions of them rather than having to scan the whole thing.
You might have very, very beefy indexes because of the data types that you’ve chosen, like large strings. You might be holding on to far more data than you are legally required to hold on to, or even professionally hired to hold on to. I don’t know, there’s lots of stuff that can happen out there, right?
And so for me, you know, it’s been quite a while, I think, since I’ve seen memory pressure coming from outside. You know, like every once in a while, I’ll see, you know, like, especially CrowdStrike. CrowdStrike does a lot of awful things to SQL Server, and, you know, so I’ll see, like, you know, some, like, antivirus or some other filter driver ballooning memory and sort of, like, messing with SQL Server, like, from within the box.
So if you’ve got that stuff going on, of course, look into that. You know, a lot of security people do not follow anything close to a best practice when it comes to excluding SQL Server there. But, you know, for me, mostly, you know, it’s just been so long since I’ve seen anyone doing anything too atrociously bad with either a virtualized SQL Server or with installing nefarious, well, installing things on the SQL Server that are obvious memory hogs, like, you know, like some Java application or IIS or something like that.
People still put CrowdStrike on there. In fact, it has dealt with CrowdStrike causing an AV exception on a SQL Server a couple weeks ago. So, anyway, mostly my investigations are things happening within the SQL Server.
And if you just truly can’t explain why SQL Server is experiencing memory pressure by looking at the activity on the SQL Server, then I guess at that point you could look outside of it. But at least in most of my recent experience, I would say that the memory pressure is coming from inside the SQL Server.
Anyway, it’s gone on long enough here. Thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you in another video this week where we will finish talking about time zone stuff in T-SQL. At least to the extent that I care to talk about it.
There might be other things to say about it, but I don’t care to say them. 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.