SQL Server Performance Office Hours Episode 7

How do I size SQL Server for new project? Where to start, what to take into account? THX!

When are you going to start adding your referenced links to the details section so i can copy and paste? <3

Should one still care about Index Fragmentation in the days of Azure VM Premium disks, or local SSD’s

When (if ever) will we see a Tyson vs Paul type grudge match between Erik and Brent

your face looks smaller lately. r u sick?

Video Summary

In this video, I address several questions from viewers. First, I tackle the topic of index fragmentation in modern storage solutions like Azure VM premium disks and local SSDs, explaining that while logical fragmentation is less of a concern, physical fragmentation can still impact performance but isn’t typically measured by standard scripts. Additionally, I discuss my recent weight loss journey, attributing it to a combination of reduced gym attendance due to the pandemic and aging, which has made maintaining high strength levels more challenging. The video also includes some light-hearted questions about potential future collaborations with other industry experts and a bit of self-deprecating humor regarding my appearance.

Full Transcript

Erik Darling here with Darling Data and boy howdy we’re gonna do it. We’re gonna drop in office hours here. Where I answer five of your questions at a time that you submit to me through my form. If you want to ask a question here you can do that at this link. This link is available in the video description down below. While you’re looking at the video description down below, if you think, boy, this Erik Darling sure does produce a lot of content that I rather enjoy. Maybe I don’t always learn something. Maybe I do. Maybe I don’t always enjoy myself. Maybe I do. But I would like to support those efforts. You can, you can, while you’re, while you’re floating around down there, there’s, you can, you can become a subscribing member of the channel. And for as little as, as little as $4 a month, you can, I don’t know. What, what is, what is $4 a month? At the end of the day. It’s really what it, what it does in the aggregate, right? About 60 other people have decided to be so kind as to support my efforts here. So, uh, you would, you would join the, that choir of angels.

And I would, I would be eternally grateful to you in the aggregate. Uh, if you, if you ran out of $4 a month, and maybe you died, uh, you can, you can, from the grave, you know, this is America. So, if you can, dead people vote. So, if you, dead people want to like, comment, and subscribe, you can, uh, you can, of course, do that as an alternate means of supporting the channel, uh, from this mortal coil or, uh, from, from, from beyond. If, uh, dead or alive, you would like help with your SQL Server, uh, I am, I am very good at all of these things. Uh, some, some, by some, I mean the, the nice folks at Beargut Magazine might say that I am, I am the best in three out of four hemispheres of the world at it.

Uh, if there weren’t for that damn island nation in the, in the Pacific, I would, I would reign supreme over the entire globe. And if you need a health check, some performance analysis, some hands-on tuning, uh, if you are having a performance emergency, or if you want to get your developers training so that, uh, your SQL Server stops being consistently on fire, well, you can call me up and I’ll do that. And as always, my rates are reasonable.

Uh, if you would like some reasonably priced SQL Server performance tuning training, I have that as well. About 24 hours of it. Uh, available at that URL with that, with that coupon code, which is also fully assembled for you down in the video description.

So, there’s a lot, there’s a lot you can do with the video description that will, that will clarify many things in your life for you. Uh, SQL Saturday, New York City. Come in your, come in your handy dandy way, your happy way, uh, on May the 10th.

That is this May the 10th of 2025, taking place at the Microsoft offices in Times Square. Highly suggest you, uh, find that event and buy a ticket because, uh, space is limited and those tickets are going, what, faster than Sbarro pizza slices. So, that out of the way, uh, let’s, let’s do this office hours thing.

Got, got quite a lineup of questions here. Uh, first and foremost, uh, we have this question. How do I size SQL Server for new project?

Where to start? What to take into account? Fix. Well, uh, there’s a lot of stuff to collect on this, right? Um, new project.

Wow. A lot of stakeholders involved. Um, uh, you know, some, something that might help is if, uh, this is, uh, a new project. That, uh, is using an existing third-party piece of software.

Third parties will often publish some sort of minimum specs for, uh, what, what they expect out of a SQL Server. Uh, this would include hardware, version edition, all that good stuff. Um, and, uh, you, if, if this is a, for a third-party piece of software, you might, you might even ask them, uh, what the typical, what the average installation is.

Um, is for a SQL Server, at least, at least as far as database size goes. They might, they might have some ideas there that would help you out. Um, but if I, uh, you know, other things that you might want to take into account is talking to various stakeholders about the importance of this project.

Um, you know, it might, it might be something where, uh, you know, high availability and disaster recovery are a must off the bat. It might also be a thing where they’re like, let’s just, let’s just build an MVP and see how it goes. Uh, a lot of the times, um, you know, when these projects start off, uh, they are on, uh, you know, the, the database has no data in it.

So, if you’ve got that going for you, it doesn’t, almost doesn’t matter what, what size SQL Server you start with, uh, especially given the flexibility of hardware configurations, both with virtual machines and the cloud these days. Um, uh, I had another thing to say there. Um, if this is, uh, you know, a new project that is perhaps based off an existing project, you might take a look at the, the current set of things there.

Uh, like, you know, whatever, whatever hardware is in the current SQL Server and all that. Um, look, there’s, there’s not, there’s not a whole lot here for me to go on. So I could just keep listing off different things that you, you might think about and ask as you’re doing this.

But, uh, really, um, this is the sort of thing where that, you know, DBAs do, do have to get paid for because sizing a SQL Server is not just a one-time set it and forget it thing. You need to, you need to keep an eye on the performance of this SQL Server if this is something that you care enough about to, to ask the question. Uh, I would say that, uh, you know, you want to keep an eye on those weight stats and make sure that your hardware is keeping, whatever hardware you initially assigned to, that is keeping up with the workload, the number of users, all the queries that are in there.

Um, you know, if it’s a, if it’s an in-house project and people keep adding features and stuff, you’re going to have to keep looking at indexes and other aspects of the database to make sure that it stays in touch with development reality. So, uh, I don’t know, like, I think maybe, maybe it depends a little bit on, uh, how much people are willing to spend on it at first. Uh, you know, if it’s, if it’s going into, it’s going into the cloud, uh, whether it’s on a VM or on a platform as a service offering from any cloud vendor, you might want to ask what people are willing to spend on it because that’ll do a pretty good job of dictating, uh, exactly how much hardware you can get out of it.

Uh, if it’s, if it’s going to be an on-prem virtual machine or something, uh, then it has a little bit less effect. But, uh, you know, you might start, you might think about like, um, you know, for standard edition, right? And I’m not, I’m not saying that you would ever use standard edition.

I’m not calling you that much of a cheapskate. But what I am saying is that for a lot of people, when they build a standard edition box, uh, like VM somewhere, uh, they will, uh, choose a set of hardware. Uh, that maximizes the capability of SQL Server standard edition, uh, you know, somewhere between eight and 24 cores, depending on, uh, the workload that hits it.

Uh, 192 gigs of memory because SQL Server standard edition, uh, well, it is capped at 24 CPUs these days. And at least as far as I’m aware, SQL Server 2025 has not changed any of the capacity limits for SQL Server standard edition. So you still have the 128 gig cap on the buffer pool, but what you, but, uh, what, what a very common practice with standard edition is, is to give the SQL Server, uh, about 192 or so gigs of memory.

Set max server memory somewhere in the 180s. And that way you have 128 gigs of data, of memory rather for the buffer pool. And then SQL Server is allowed to use memory between the end of the buffer pool and max server memory for all sorts of other things.

So it might be smart enough to just start all your builds with a maxed out standard edition build, even if you’re using enterprise edition. That’s, um, usually an okay way to go. Of course, uh, CPU count has a much bigger impact with enterprise edition than with standard edition.

Um, it being the, the, the $2,000 of a core versus $7,000 of course. So again, it really does come back to budgetary constraints and what people are willing to spend on this hardware. Doesn’t it?

So make sure you get those numbers, right? Second question here is, when are you going to start adding, oh, that was the wrong button. When are you, when is zoom it going to start listening to me?

Uh, when are you going to start adding your reference links to the detail section so I can copy and paste? Well, uh, I, I, I always endeavor to include all of the, the reference links in my, in my video descriptions. If I ever miss them, please feel free to point it out.

Uh, and I will correct, I will aim to be as eventually correct as MongoDB and, and, and, and get that in there for you. But, um, I am, I am an imperfect soul and all I can do is beg for your forgiveness and, and, and, and try to correct any, any issues in that area. And let’s see here.

Should one still care about index fragmentation in the days of Azure VM premium disks or local SSD, SSDs? What? Apostrophe abuse right off the bat.

Uh, no. So look, this is something that I’ve, I’ve, I’ve talked about a bit. Uh, the type of index fragmentation that most people, uh, cared about at some point in time was logical fragmentation. It’s data pages being out of order, uh, on, on, on disk.

And that is, no, that is not something that I tend to care much about when it comes to, um, when it comes to SSDs or flash or, or, or memory or like, you know, RAM memory, uh, not RAM disks. That’s not for SQL Server. Uh, but, uh, there, there is always a specter of physical fragmentation that is empty space on data pages.

Um, and that can affect scan density. That can affect read ahead, read size. And, you know, it might be something that you want to look at.

The problem is that, um, not, not many, uh, readily available, uh, index maintenance scripts measure physical fragmentation. They all measure logical fragmentation. And there’s not really a good correlation between a logically fragmented index and a physically fragmented index in either direction.

Uh, if you want to go and start measuring, uh, uh, physical fragmentation. And you want to start, uh, rebuilding or reorganizing indexes or in order to, uh, cram your data pages more densely, uh, with data. Then you are, you are welcome to figure out at what threshold that makes sense for you to do and, uh, and pursue that endeavor.

But, um, if you are, if you are just asking me, like, how to configure all the scripts or something or something like that, it’s not in there. So, um, that is something that you have to kind of figure out a bit on your own. And it’s not something that, that I want to get into the business of doing because there are all sorts of situations out there where, uh, even physical fragmentation would have no profound effect on a workload.

If the queries are, if the majority of the queries are performing index, index seeks, it is index scans that are affected by the page density that is lessened by physical fragmentation. And seeks don’t really have that sort of performance hit. So, uh, this is a weird question.

When, if ever, will we see, no, zoom it, listen to me. When, if ever, will we see a Tyson versus Paul type grudge match between Eric and Brent? It’s a, it’s a very strange question.

Um, I wasn’t aware of a grudge between myself and Brent. Uh, if there is one perhaps that I’m unaware of, uh, you can feel free to, uh, enlighten me about that. Uh, at least the last, last time I spoke to him, uh, fairly recently, there was, there was still no, there was no grudge.

So, uh, the, the, the, the, um, potential for a grudge match does infer the existence of a grudge. But, um, at least I am unaware of one. And finally, your face looks smaller lately.

Are you sick? Well, thanks for noticing. I do appreciate it. Uh, a fella does work hard to, to keep in shape. Uh, no, I, I’m, I’m, I’m not sick.

I am, I am, I am as well as I’ve ever been. Uh, I did, I did lose some weight though. Uh, if you, if you want, if you, if you, if you care about a full, uh, the full story, uh, you can, you can keep listening. If you, if you don’t care for, uh, any sort of explanation, uh, you can, you can stop watching the video now.

But, uh, around about, uh, 2016, I got very much into, uh, strength training and like sort of a, like a powerlifting type thing. And, uh, you know, before that, I, I just kind of horsed around in the gym like many misled, uh, time-wasting young gentlemen. And, uh, I just realized, after a while, I was just like, this isn’t really getting me where I wanted.

So, um, I, I, I discovered the, uh, the starting strength program by, by Mark Ribiteau. And I started doing, uh, the novice linear progression. And, uh, after, after a few years, uh, I got, I got my lifts up pretty good.

But I, I was also, uh, you know, that, that getting stronger does require putting on body weight. Generally, if you want to gain muscle, you’re bound to gain some fat in there. But, uh, you know, I, I, I bulked up to about 225, 220 in there.

And, uh, you know, my lifts were my, but I had like lifts to match that. You know, I was dead lifting around 600. I was squatting over 500.

Um, I was benching over 300. I had a 250 something pound overhead press. And these are all for like singles. This wasn’t like me just banging out reps with that stuff. But I did get my lifts up pretty high.

And, uh, but, you know, I felt kind of okay about it because that’s the goal I was pursuing at the time. And the additional body mass was, uh, was sort of required for me to pursue that goal. But then, you know, um, COVID came around and, uh, in New York, gyms closed down for quite a while.

And then when they reopened, uh, you still had to wear a mask in the gym, which I, which I did try to do a couple of few times, but for the type of lifting that I was doing, it was very uncomfortable.

So, um, you know, I, I did, I did drop my weight back down a little bit and, uh, then I lost about 20, 25 pounds, uh, because I just wasn’t working out and, you know, carrying all that around when you’re not actively lifting heavy weights is kind of, but then I could just never, um, you know, uh, I got older, my, my, my, my mid middish forties.

And, uh, I could just never get, um, the type of training going where, uh, I was, I was getting my lifts back up to that. Right.

After take, after that long of a layoff in the gym, it’s like, you’re starting basically from scratch. And so, uh, yeah, you know, I just, I just kind of realized that, uh, is a, is a, is an aging gentleman and, uh, you know, uh, sort of struggling a bit with training consistency and stuff like that, that I just wasn’t going to get my, my lifts back up to where they were.

So, uh, I just, you know, decided, you know, lifts be damned. Unfortunately, uh, I, I, I did, uh, pursue just losing weight. I was still, still, still lifting weights, but all of my, the, like my, my lifts for what they were just dropped way down.

Uh, now, um, you know, just sort of, uh, slowly trying to add back some, some strength into the mix and, uh, get them at least back to some respectable numbers. But, you know, that’s going to be a slow process because, uh, I am not, uh, I am not in the same, uh, bulking frame of mind that I was the first time around.

So, you know, it’s, you get a bit stronger, but also not, you know, bulk up to some horrible weight. I am at the, I’m at the point of my life where doctors are starting to prescribe interventions for certain things like blood pressure, cholesterol, and all that other stuff they give you because you’re going to die if you don’t take it or something.

So, uh, uh, I’m not sick, but, um, I was, I was feeling not so great there for a bit. Uh, cause, you know, I had, I had all of the, uh, the, the outer symptoms of, uh, of, of a powerlifting career without any of the, the, the strength that went along with it, which is not a, not a terribly good combination.

Uh, so, that’s my story there. If you listened, thank you. If you didn’t, I totally understand. Anyway, uh, that does bring us to the end of this office hours. Uh, again, you can submit your questions, uh, via the, the link down in the video description that says office hours, and I will happily answer them.

So, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you, uh, if you are a young, young person wasting your time in the gym, you will stop doing that.

Get some, some barbells in your life. It’s a much better way to live. Anyway, 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.