Live SQL Server Q&A!

ICYMI

Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.

Video Summary

In this video, I dive into the daily struggles of working in a hot environment that seems to never end. From the moment I step into my office, it feels like I’m stepping into an oven, and by midday, I’m drenched in sweat. Despite the discomfort, I manage to keep things light-hearted as I address various technical questions from the community. We tackle topics ranging from database performance tuning and server hardware requirements to antivirus software issues and the latest SQL Server versions. It’s a mix of practical advice and personal anecdotes that hopefully provide some relief and useful insights for anyone navigating similar challenges in their work or learning journey.

Full Transcript

It’s hot. It’s so hot. Everything is sweat. Shower five, six times a day just to try to stay ahead of me. It’s awful. Awful. It’s like, it’s so hot that I’m just like tired. I just feel exhausted from existing. I’m busy. The worst. I don’t even, I don’t know, I don’t know how to make it better. The beer comes down soon. I guess. I guess. Alright, look at this. healthy, happy crowd. Look at all these nice people. Get better air. I have great ones. I just haven’t put them in yet. That’s half the battle. I haven’t put them in yet. I have one air conditioner. It’s like 14,000 BTUs. I’m pretty sure it’s illegal in Europe to have an air conditioner like this one. It could like, probably like preserve.

meat. I have a pair of meat. If I put meat in front of it. Then I have a couple smaller ones for the smaller ones. But that big one, man. That’s something. The thing is, it’s like, I don’t know. It’s only, like only in the 70s. It’s just so human. It’s like death human. It’s like living in a mouth. And this is sitting in a tongue constantly. It’s like, constant, constant tongue in my entire being. It’s terrible. Oh, man. So, everyone excited for the weekend? Big weekend plans. Fun, interesting, exciting things to do this weekend. Yes. Yes. You do need to turn them on. Plug them in. Mount them in a window. All sorts of weird steps you have to go through before air conditioners actually work. I know. I know, man.

It’s like, I’m not going to go through the weekend. I know, man. I know. I know. I know. I know. The ADR paper came out. Looking forward to reading it. Well, you should paste the link in chat. Don’t keep it to yourself. Forest. Julie says, SoonerCon, science fiction, fantasy, literary con. Man of visitor is guest. All right, Julie. Where are you going with that? What is SoonerCon?

Weekend plans. Weekend plans. There we go. All right. Now it all makes sense. It’s like, Julie, you’re crazy. Someone call an ambulance for Julie. Wow. Darren says, Holy jeez. Darren says, Have you ever seen anyone that just maxed up on a schedule to match changing workloads during the day? No, that’s pretty creative, though. I think if most people, I mean, I want to say most people. I will say most people who would have those needs might just invest in resource governor and have different workloads run under different logins so that you can classify them and set maxed up differently.

But, you know, you can’t do that on every version of SQL Server. You can only do that on enterprise. Well, I’ve never personally seen that, though, but I guess I don’t hear that much. I don’t think that would freak me out too badly. that would have been a lot more. I don’t think that much. I don’t think that’s good.

Let’s see. Nothing interesting there. Twitter is terrible as usual. All right. Good stuff. Lee asks, Lee inquires, why does option recompile not seem to work sometimes? We had an issue the other day where it made zero difference even though we knew it was using a poor plan.

Oh, man. So there’s a lot of missing detail here. And it could just be that even with a recompile and SQL Server thought that it was choosing a good plan.

That would be my guess. You say not work, I mean, that’s a gray area for anything. But if I walk up to me and my hands are like, this doesn’t work. What’s not working?

What exactly didn’t work? I’ll give you an example of the difference. Like, I’m testing a lot of my current demos on SQL Server 2019. And I frequently complain that they don’t work anymore.

It doesn’t mean that they’re like throwing errors. It just means that they are no longer exhibiting the behavior that they used to. Screw you, SQL Server 2019.

You’re making me do work. He says, we passed the values through another set of parameters and then it used a new different plan.

Oh, so you did the optimize for unknown trick. You declared variables, set them equal to the other variables, and then did the thing. I guess that’s it.

I would like to see this dynamic sequence, too. Because I bet. Yeah.

Yeah. So when you declare local variables like that, it’s basically like saying optimize for unknown. And you can, I mean, sometimes the guesses are like okay. A lot of the times the guesses are very bad.

I probably wouldn’t want to leave that solution in place for very long. Who knows when that ticking time bomb will go off. So you know what?

I think you should post. I think you should post this on DBA.StackExchange.com. I think you should show us the dynamic SQL. I think you should show us the plans.

I think you should get us some more information. All in one place we can see. Because then you could get a great answer. From me here, you’re just going to get a very speculative answer.

The proc will be rewritten. All right, fine. Fine. I didn’t want to help anyway.

I didn’t want to help. You think about that. Unless you’re hiring me. Is that me you’re hiring me to do it? That would be awesome. Woohoo, I have a job.

I’m just kidding. I still do. On the plus side though, I have stopped applying for jobs. So I no longer feel rejected.

It turns out that I get as many jobs when I don’t apply to them as I do when I do apply to them. So I have the same number of jobs just without any threat of rejection.

So that’s nice. I feel fine. I feel a-okay.

It’s a good time. Good time. Good time. Good time. Still hot.

Still too hot. Still too hot to exist. I don’t have any plans this weekend in case you’ll want to.

I got nothing. Low key. Low key. As usual. I’m going to buy some ice cold wine.

And then just drown myself in it. That’s my big plan. There’s a lot of weird-well not weird, but there’s a lot of oddly placed school holidays right now.

I’m not saying the holidays are weird. I’m just saying the timing during the week is weird. Like today, my daughter had Tuesday and Thursday off. And so like there were weird like sleepovers and like play dates.

And I was just like, just go to school. Go to school. It’s too much work when you’re not in school.

It’s exhausting. Can’t keep track of the babies. I have a gigantic fan in here.

Actually, you can’t see it anymore. Never mind. But the gigantic fan is not helping my situation. It’s all downhill.

The opposite of Lee Brown Hill. It’s Eric Downhill. All right.

Do we have questions? Does anyone have a question? Anybody? You can make something up. Even if you know the answer, you can just make up a question. Otherwise, I’m going to go back to sweating off camera.

Which is preferable for me. Because these headphones add about 30 degrees to my head. 30 degrees.

Darren asks if I work with JSON and single server at all. No, and I probably won’t unless all of a sudden query plans get converted to JSON. Boris asks, what’s your naming convention for test machines?

My, I don’t have one. I named them SQL and then the version they are. So my test VMs are back to SQL Server 2008 with the most recent service pack. And we’ll just call it SQL 2008, 2008 R2, 2012, 2014.

Because I have no need for it. I don’t have dev and QA and test and all that. All I have is a few lowly VMs.

I just have to make sure that scripts don’t return written results on. Rex says he’s working on an issue with McAfee post upgrade or McAfee post upgrade causing 98% CPU and the application gets orphan sessions.

It sounds like someone didn’t do the right AV exclusions. And some like McAfee is trying to scan your SQL Server stuff. Like MDS, LDS, bin folder, things like that.

I’ve never really understood the point of having antivirus on a SQL Server. People aren’t generally uploading things to it. Like putting things on there.

I don’t know. That’s just me though. Seems like it causes more trouble than it’s worth. Yeah, the database server hosting the database.

Yeah, I got you. Jane says, any reason not to use SQL Server 2017 in production over SQL Server 2016? Haha, I wouldn’t use either one.

I would just wait for SQL Server 2019. Why put yourself behind the upgrade eight ball when SQL Server 2019 will be out in, I don’t know, three, four months?

I’m going to use the newest, latest, greatest, buggiest version of SQL Server that Microsoft has available in production. That sounds like a much better time than me.

I would wait for that. Let’s see. That’s a good name.

I’m going to go with that part of it. What would you recommend is essential basics to learn for a newly appointed junior DBA with no DBA or SQL experience?

Well, that depends on what you’re administering a little bit. If it’s your job to just deal with backups and check DB and uptime and looking after whatever HADR is in place and making sure that your servers have the right settings, then it’s a different story than if you’re looking at indexes and performance.

So scope your job for me. Tell me what you do day to day. I will give you a better answer. Farah says, what are common issues to watch out for on 100 core plus servers?

Oh, man. I don’t know. Not thread cool. Licensing. Licensing would be an issue that I would watch out for pretty hard.

That’s expensive. That sounds like an interesting one. I would be really careful how I set maxed out.

Really careful. Martin says he’d be worried about having enough IO to serve those cores. I would have so much RAM in that server that IO would not be an issue.

I would have just like if you can afford a 100 core server, which indicates that you have Enterprise Edition. a 100 core server that’s not a problem.

If you have a 100 core server, then there is absolutely no reason that you shouldn’t have that thing packed to the gills with memory. I don’t care if you have a 4 gigabyte database on that server. I would have like 8 terabytes of memory.

That’s an interesting point though because you’ve got to figure with, you know, per core you can do about 200 megs a second if they’re like decently fast cores. If you have a 100 core server though, I’d be a little bit curious what the clock speed is on those. If it’s over 2 I might be surprised.

Like 1.6, 1.8, somewhere in there. Those wide processors have grandpa speeds on them. They’re not fun.

I see a lot of people have to buy those too. They get a VM host and they have to get enough cores to serve a whole bunch of guests and it’s just 28, 56, 48 cores of garbage. I saw a VM server recently with AMD chips in it.

It was fast. Why did you do that to us? Why? Why?

Just cut one of your lights off. AMD in the server.

That’ll be the day. AMD’s going to start writing checks on for you. Yes, keep saying that. Monitoring.

You know what’s funny is every time I post one of these things I’m like go ahead ask your questions via email or you know what do you call it? Twitter or wherever. Like literally ask a question in any way that you want.

My DMs are open and all I get are porn bots saying hi to me. Then following me and then getting their account gets deleted shortly afterwards. I end up even lonelier than the more.

The story of my life. Porn bots and loneliness. Title of my memoirs. All right.

Waiting. Watching. Staring. All right. I’m going to give this one more shot.

No. All right. Thanks for showing up. It is too hot to stand here. Oh man.

There we go. James had to ask. James had to catfish me. James says what do you recommend the minimum hardware requirements for a production system that has a thousand gigabyte database. My current company has a setup of two cores and four gigs of RAM for all their new systems.

Are you messing with me? Like physically trying to mess with me? That’s not going to fly.

Unless there is like. Do you have users? I guess would be my question. Because if you if you don’t have if you don’t have users that will probably work really really well. Yeah.

So the the funny thing about you know where I would start with that is you know the sort of funny thing about SQL Server is that it doesn’t work with pages on disk. Anything that you want to read like presumably people want to read stuff out of that hundred gig database. And I’m willing to bet that your your tables or indexes are over the four gig mark.

So anything that SQL wants to read or do anything with it doesn’t matter if it’s insert update delete select it has to be in memory first. So.

Oh. It’s funny. It’s funny too because even if you’re on standard edition. 2012.

You can put like 96 gigs of memory in there and you can cache 64 gigs of memory right in the buffer pool. And then you can have like 16 gigs for the OS and you can have like I don’t know 30 or something 20 something in there. Just for SQL Server to use for caches and memory grants and all that other fun stuff.

And if you have SQL Server 2014 or newer 17 you can have a hundred and oh excuse you. You can have 128 gigs of RAM just for the buffer pool. You can cache that entire database in memory.

So I would go out on a limb and say that that is probably inadequate hardware for a 100 gig database. Matter of fact I would probably say that that is mostly only adequate hardware for a four gigabyte database. Maybe eight if you’re very lucky.

Maybe even 12 if you’re if you have incredibly good indexing and incredibly well written queries. But otherwise this is not going to pan out as well. So you know well I’m sure that there are you know query and index tuning opportunities available in that database.

I just don’t think that with four gigs of RAM I would I would just walk away from that server. There is absolutely no point in trying to do anything that that’s always going to suck. Yeah it’s basically like a bare minimum when that was installed.

Yeah you can hire me to do anything you want. I will do anything for money. Keep the women in my life happy.

Let’s see here. This is so Nandalusian says it’s not for me it’s for someone I’ve recently appointed so I don’t know what’s best to prioritize for learning. They got thrown in and had to learn as fast as things fell over.

I don’t want them to have the same experience. I want them to have a good rounded education being a DBA. So they’re less surprised by the challenges of the role. Yeah that makes sense.

But again you haven’t told me what they’re going to be responsible for. So if you have them being responsible for like backups and check DB then that’s the first kind of round of stuff they have to learn about. If you have them responsible for a 12 node AG they better they better get their button gear.

Wanting something about AG. You see what I mean about how the scope of the job can be different. Can we get lead to different advice.

Like if they’re just managing like a few agent jobs and maybe if they’re I don’t know just making sure that the server is up and running. That’s a that’s a totally different DBA than you know someone who’s got to go in and check out indexes and see if they make sense. All that other stuff monitor performance not just like up down red green monitoring or whatever color blind colors people have up in the world.

You know what I mean? It’s like different skill sets different things you have to do. Every city truck sounds like that.

Every single one. The city has not had a quiet truck in 25 years. Every truck sounds like an earthquake when it goes by.

Every truck. Fire the city. I don’t know other things I’m thinking about with the 100 gig database on.

And so here’s another funny thing. I’m going to go ahead and assume that that VM or that server that it’s sitting on is a virtual machine. And I’m going to tell you something very funny about virtual machines.

When you buy licensing for them they have a minimum of four cores. So you’re actually you’ve actually paid for two cores that you’re not using. And if that’s standard edition that’s four grand and if it’s enterprise edition that’s 14 grand.

So that’s actually a very stupid setup from a licensing perspective because you’re actually paying for things that you’re not using. And imagine if I was if I was like yeah come to my restaurant. It costs two grand per plate or per seat.

And you have to buy a minimum of four seats to come in. And then you only show up with two people. I get to keep four grand and not do any work.

You’re giving Microsoft money that they haven’t earned. All that all that licensing money that you are not using. It’s silly. It’s money out the door.

No such thing as a two core VM with SQL Server. Start with four. You’re paying for them. Why would you want to give Microsoft free money? Do you have any idea how much free money they have from not fixing anything?

Decades. You keep paying them. You get new versions with features that are broken, half implemented. Don’t really work.

You just keep paying and paying and paying. And then you give them free money with unused cores. It’s ridiculous. Absolutely ridiculous. Absolutely positively ridiculous.

Whoever’s rule that is, I would just, I would pull them in an alley and beat them. The only solution for that level of stupid is probably beatings. I can’t think of anything else that would cure that level of inanity.

So I was like, isn’t it like, that’s how everything starts out. And then like, if there’s a problem, they can magically find more hardware. Or is it like, if there’s a problem, it’s SQL Server’s fault.

And it couldn’t possibly be the hardware because it’s a DM or something. I hate it. Yeah.

It’s always SQL Server’s fault. Oh, Raphael don’t do that. Don’t add hot CPU. Don’t have the hot add CPU. No. It’s not good.

Yeah. Sorry to hear all that. So the first thing I would do is disable hot add CPU.

The second thing I would do is make sure that, you know, the CPU setup of the VM is not a gate. I would do like two by two by what’s that, 16, 20. Wait, you said 24 cores.

And 8, 8, 4, 16 plus 4 is 20. 8 plus 8 plus 4 is 20. I’m being told, whispering in my earpiece, it’s 20.

Cal Bay’s light. Yeah! Wow!

You have so much to do. I’m so glad that you’re here to tell me this story. so you hey you have you have much work to do my friend if you want to keep those 24 cores you’re gonna have to skew upgrade if you want to not have SQL server act like a piece of crap you should disable the hot ad CPU and then snow will probably work itself out once you work that out but yeah that’s that’s something that’s something you’re I would I would also try to take your boss’s job I don’t think he’s very good at it you can tell him I said I’m not afraid unless he’s from Yonkers I would recommend I would recommend you for your boss’s position change maxed up to 8 already I don’t even know if I would put it at 8 I might put it at 4 because that that one poor split up CPU just just might get real real in a real weird place with it at 8 I might I might even lower it for probably be my number at least until that other thing gets straight the the other thing gets straightened out I might change it to 8 man at least if it’s 4 you can like keep all your queries within a thing your boss is incompetent is the problem and I don’t think your boss is gonna go check on SQL server parallelism settings pretty sure that you could make that change do your thing and if he or she he or she I don’t want to assume anyone’s role in this this great wide world has anything to say about you can say Erik Darling told them maxed up 4 eric darling tell your tell your boss that he’s wrong you shouldn’t have that job he’s micromanaging bosses shouldn’t be setting those kind of limits on their employees they should not but bosses tech bosses who manage technical employees and who outsource the management of technology to other people should not ever micromanage little things like that ever it’s a big mistake you end up with things being technically dumb especially if you don’t listen to your technical employees well the things that they know best I am very technical with SQL Server but if you expected me to do something technical or you wanted me to make a technical decision about like an availability group happen would say what do you know what availability groups you know you tell me what you want to do with the availability you tell me why it makes sense I’ll ask you hard questions about why you want to do that and see if you thought this whole thing through but I’m not gonna say you can only set maxed up can’t set it to for only eight the only change you can make this maxed up idiot why would a boss make that kind of you about boss who presumably knows nothing about SQL that’s why they hired you I don’t get fire your boss throw him on a window anyway season my physical hosts are two newman nodes with six cores each 12 cores would be the maximum recommended maximum recommended for what like to give the VM you have physical VM hosts with six cores for 12 cores what we gotta talk we have to have a long talk yeah so here’s the problem with that you have a VM host with two sockets and six cores per socket so you have 12 cores total for a VM host and you want to give 12 cores to SQL Server the issue becomes if you have anything else on that box and you have SQL Server trying to co-schedule something where it’s already co-scheduling SQL Server is already co-scheduling quite a bit even if it’s just even if it’s by itself on a VM host SQL Server is being scheduled by vSphere zero server is being scheduled by Windows and then SQL Server is being internally scheduled by itself so you have three layers of scheduling going on already and now you want to know if you throw other VMs on that host they’re also being co-scheduled on the same process is it there is co co co like recursive infinite recursion of of of co-processing going on and that’s just a really terrible idea I’m going to give you a quick example that’s a little bit more extreme than what you’re telling me about and it was I was working with a client when I was still still with Brent and these people had a four core VM I think I’ve told this at a four core VM and they were they had performance problems I said yeah I agree you do you have performance problems because you have a four core VM and you have like a 200 and something gig database you have 16 gigs of RAM and it was really really not pretty and then like the further the further we dug in it turned out that not only did the VM have four cores but it was another consultant who had set up this VM host and the the VM host itself had four cores and the VM host itself was hosting 12 other SQL Server work so there were times we will I was like something’s weird here I didn’t know that stuff until like a little bit later but when I was first trying to poke around itself you’d like run a query do it finish in simply run the same query again take four seconds on the same query again finish it’s run the same query again 11 seconds on the same query instant run the same query 25 it was like every time we ran this query it was like all over the place with how long it would take and wouldn’t you know eventually it came to light that there was no no there was no remote no one it was one no my node with four cores there were 12 other SQL servers sharing the same four cores that was it every every every of the 12 servers on that VM host were using the same four cores and everything was attempting to be scheduled at once and I think the entire box had like maybe 64 gigs of RAM it was like shittier than my laptop I’m sorry crappier than my laptop oh yeah it was a bad time with VM with VM hosts like that if you have a one-to-one mapping of your of your your your your your your guest to your host you have done yourself a tremendous disservice there’s not something you should be doing anyway whoo we have we’ve gone to almost 40 minutes which is which is almost 10 minutes longer than I usually go for anyway I’m gonna get going and try and go take my fourth shower of the day and do that because people are starting to leave now I’ve talked too long anyway goodbye have a great weekend I will see you next time hopefully hopefully hopefully everyone can make it my weekend is not installing air conditioning my weekend will be installing air conditioning and then lying in front of it making sacrifices to the god of gods of Freon that’s mine goodbye