This week, Brent, Erik, Tara, and Richie discuss syncing logins and database data via log shipping, monitoring page life expectancy, cumulative update issues, when to change MAXDOP, doing SQL Server install on AWS, connecting to SQL Server instance on a virtual box from the desktop where the virtual box is running, and what real DBAs drink.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2019-09-12

How can I sync logins and Agent jobs?

Brent Ozar: Jason asks, “When it comes to log shipping, are there any solid solutions out there relating to syncing logins and stuff that’s in the system databases?”

Tara Kizer: I mean, we know about Robert Davis’s script for syncing logins, but everything else, there isn’t really one tool to sync everything. I think there have been tools over the years that people have released, but I don’t know that there’s much that people are recommending. So yeah, Robert Davis’s sync login script, jobs, I’ve used doing it manually, making sure that my DR server or whatever server is in sync at all times. Every time I make in the primaries, make it the secondary. There’s scripts out there that you can run. I don’t know if dbatools, PowerShell stuff can help out with this maybe.

Brent Ozar: That’s true too. I was going to say Kehayias has a syncing job script…

Erik Darling: I just stuck that in chat too. That’s the only one I’ve read about. The only thing is that post is from 2013. I don’t know that it’s been updated in a while. Not that I expect him to keep it up to date either because it was a freebie for a blog post, you know. We all know the quality of those freebie blog posts.

Brent Ozar: We’re not saying anything about Jonathan’s; we’re saying this about our own as well. I look at this as one of those things where a third-party vendor should totally step in and do. But you’ve just got to be aware that if you make a change to a job and that job gets synced automatically to every other server – maybe your event paths are different, like the places where you store files. Maybe the schedules are different. Maybe you have some jobs that only are supposed to run on the primary and you didn’t code in any detection to see whether or not it’s the primary. Just be really careful when you sync them around from one place to another.

Erik Darling: And if you’re really changing jobs that frequently then you probably just want to have a separate server that holds all the jobs and points them at the stuff in your AG or log shipping or mirroring environment. That way, at least when you have those jobs run, you have to be really mindful about detecting if it’s the primary or the secondary because you can’t just have things running full sail everywhere. I mean, you can, but you…

Brent Ozar: Job failures everywhere.

Erik Darling: Yeah, good luck dealing with those.

Richie Rump: What would we do about job failures? I have no idea…

Brent Ozar: So I’m surprised Richie doesn’t Tweet more about – I haven’t looked over to see if he Tweeted, but I’m surprised he doesn’t Tweet more about some of our adventures in production.

Richie Rump: Good god, no. It’s one of those things. Maybe, if I have a good idea of why things happen, you know, but a lot of these, like what happened yesterday, we had 11,000 failures in production, which was just a fun thing to have. And it happened to be a problem with one of our load tables which wanted a vacuum. And then I scratched my head and I’m like, what’s the hell a vacuum? So we figured out a really short order. We just had a lot of errors happen really quickly at the busiest time of the day. But oddly enough, everything, with the exception of 12 files, everything reprocessed and everything went back through, so there we go.

Erik Darling: We need to get one of those signs. It’s been like zero days since our last accident.

Brent Ozar: One day since last failure.

Erik Darling: You know, Brent’s little pet project is building like automated animated signs. We could get one of those or something.

Brent Ozar: True, we totally could. So the demo for this – let’s see, so the project that I’m working on right now is a little animated sign to show the next three flights coming into our airport. Because right over there is the San Diego Airport flight-path and I worked with a developer to build a little tool so that it will show just like an airport flip-board the next three flights coming in. And this is a screenshot, like a static list of flights – it’s not the current flights in San Diego – which shows the flight number, the equipment type, the departure airport, arrival airport, and the time it’s coming in at. So the goal is there that we’re going to throw it up on a projector so that it just shows on the wall about what’s coming in next. So that’s my life. There you go.

Erik Darling: San Diego Airport, which we recently learned has the least secure wifi in all major international airports.

Brent Ozar: Yeah, absolutely.

You should make flavored vodka.

Brent Ozar: Let’s see. Next up, Lee says, “Y’all need to meet some Russians and learn how to make flavored vodka. Pinon nut vodka is amazing.”

Richie Rump: Vodka – you got to say it right. It’s wodka.

Brent Ozar: Wodka…

Erik Darling: I mean, it’s still vodka. Every time I’ve seen someone drink flavored vodka, it’s never been good and the person has always been awful. So like, it’s always been like birthday cake flavored vodka or something. Like, here’s cupcake vodka and I’m like, die. Stop. Let the cirrhosis take you.

Brent Ozar: Well if it’s always in combination with, this is great with whatever… You know, this is great with Diet Coke – how about alcohol that tastes great by itself, you know. And you can’t drink birthday cake flavored vodka for any length of time or you will be dead. You will be dead.

Why is PLE different for different NUMA nodes?

Brent Ozar: Gordon asks, “What might be the reason for hugely differing values for page life expectancy on a server with two NUMA nodes? Shouldn’t the PLE be the same across both nodes?”

Tara Kizer: I’m really confused on the question because what does PLE have to do with two NUMA node servers.

Erik Darling: Is it that PLE for one NUMA node is lower than on the other?

Tara Kizer: Oh, got you.

Brent Ozar: Paul has a blog post about this. To me, the takeaway is – it’s a good blog post, but to me, the takeaway is don’t monitor page life expectancy. It’s just a garbage metric that doesn’t tell you anything.

Erik Darling: It goes up, it goes down…

Tara Kizer: Mic drop.

Richie Rump: You would think that a bunch of big popular bloggers would have wildly different opinions on how you go and do performance tuning. I bet, if you put us in a room, especially in separate rooms and you gave all of us written tests – like where do you start with performance tuning – we would all have a lot of different answers, but I bet the big thing would wait stats. Like, we would start with wait statistics to find out what the server’s bottleneck is. You’ll find all kind of blog posts about all kinds of other techniques that we also use after we find the wait stat that’s a problem. But I think everybody starts with wait stats instead of page life expectancy.

Erik Darling: I mean, wait stats drive me as far as do I want to look at queries that are running now, do I want to look at the plan cache? What type of plans do I want to look for in the plan cache? Like, with BlitzCache, we can sort things by different metrics. So if wait stats are showing my high CPU then I’m sorting by CPU, stuff like that. and it also drives me to, like, do I want to go right to the plan cache or do I want to go look at indexes? Because something has just clearly run amok over here. So if I look at wait stats and I see a lot of locking, I’m going right to the indexes. I’m not going to start with the plan cache right after that.

Tara Kizer: And then also, large memory grants can cause the query workspace area [inaudible 0:06:57.8], which can cause the buffer pool to shrink down. So I mean, sometimes, when you see page life expectancy, it’s because of jobs that were running like rebuilding indexes. It could be you don’t have enough RAM for the buffer pool. But what about large memory grants that can cause buffer pool to shrink down.

Erik Darling: Even just CHECKDB that’s, you know, doing something crazy. That might be the one thing that got everyone in different rooms to pick up the sword and shield about; index rebuilds.

Brent Ozar: That’s probably true. And it would just boil down to two rooms. There would be the one room on one side and the other room would be like Scotland in the freedom thing…

Is SSMS 17 slower than older versions?

Brent Ozar: Michael Tilley asks a question that’s about a paragraph long. I’m going to scan it just to see if I can get a short answer on it. Michael says, “I have various SQL Server instances. If a plane leaves Philadelphia at the same time…”

Erik Darling: Someone flushes a cheesecake…

Richie Rump: I think the best part of this question is he’s like, “Okay, SQL tech question now…” Like, calm down, people.

Tara Kizer: He’s asking about the speed of SSMS.

Erik Darling: So it’s slow in the SSMS?

Brent Ozar: He says that, “The standalone version of SSMS is slower. So, like, the 17 branch of SSMS is slower than the one that comes with installation media.”

Erik Darling: I thought it didn’t come with the installation media anymore.

Brent Ozar: Well for his older versions. He’s got 2008.

Erik Darling: Well yeah, they added more crap to it. Every time you add more crap, you slow something down. All those new versions where Microsoft gets to add things like quarterly to it and they just add new things. And when you add new things, you slow things down. I keep adding the fat and that keeps slowing me down.

Brent Ozar: Or the pineapple flavored vodka. And I think too, they change the version of Visual Studio that it was built on top of and it added all kinds of new bloat when they did that. if you want something that’s lean and mean and kind of fast, you could start with SQL Operations Studio, which is their new electron-based one. The problem is, it’s also kind of faster because they took out a whole lot of features. It does way less stuff. I’m not saying it’s bad. It’s fine. If you want to use SSMS on a Mac or on Linux and you don’t want to install a VM, that’s one way to do it. I run a Mac as my home main machine. I’m getting ready to watch the Apple Keynote in about 30 minutes and I’m going to have my credit card ready going, shut up and take my money. I want a new iPad so bad and I’ve been holding back for so long. I don’t want another phone with 2TB of RAM. That doesn’t make any sense to me. But yeah, and still, I don’t use SQL Operations Studio. I think it’s fine. There’s nothing wrong with it; it’s just SSMS is amazing.

Erik Darling: Someday, SQL Operations Studio will be just as big and bloated as Management Studio, all the features, and we’ll complain about that then.

Brent Ozar: Cortana…

Erik Darling: Yeah, why not? Go crazy.

Richie Rump: Brent is so buying another phone.

Brent Ozar: I’m not. I’m not. I’m totally not.

Richie Rump: It’s totally going to happen. People, trust me, it’s going to happen. (Update: the Apple keynote happened, and no, Brent did not buy another phone.)

Brent Ozar: Anything that they could bring out, I’ll be like, no, no, thanks, I’m good, because I am holding back my credit card for the iPad. Because the instant that they throw – and I don’t want the 12 inch – I don’t want something that’s the size of a Trapper Keeper; that’s ridiculous. I just want an iPad Mini that’s like nine inches, 10 inches across. That’s all I need.

Richie Rump: But what if there’s a Lamborghini on the iPad Trapper Keeper? I mean, come on, Brent.

Brent Ozar: Ooh yeah, okay, yeah. Okay, so now I have to show this. so one of my favorite websites, Autotrader Oversteer, this is where a lot of their commentary goes for Autotrader. Tyler is this guy who has an incredible car collection problem and he continuously buys the cheapest expensive car in the United States. So his latest purchase after his Ferrari F355 caught fire was he bought the cheapest Ferrari Testarossa in the United States. So then he goes and buys the cheapest version and blogs about all the problems that he runs into with it and how expensive or inexpensive it is. Yeah, so there’s that.

Richie Rump: What?

Why didn’t Microsoft catch this bug in 2016 SP2 CU2?

Brent Ozar: Eric, different Eric, asks, “I applied…” it’s not really an ask; he’s just kind of making a comment. “I applied Service Pack 2 Cumulative Update 2 for 2016 a couple of weeks ago and my Database Mail stopped working. To resolve it, I had to reconfigure .NET35 on the server. How did Microsoft miss this? I might not apply Cumulative Updates anymore.”

Erik Darling: It’s amazing when there are bugs in something that gets released monthly.

Brent Ozar: And Database Mail, like the further you go off campus, like the further you go into features that are kind of not mainstream, SQL Server’s a great relational database. But the more that you start to use a feature that automatically lowers and raises your car’s antenna, the less that that stuff is really reliable and tested. There’s just only so many tests they run.

Erik Darling: Well even the last round of CUs for 2017, they left a bunch of trace flags on in one of their releases and they had to roll that back. Not that I blame them. I get oversights, especially with a product that big and the co-release that big and all the stuff you have to do. I get it. I’m not trying to harsh the Microsoft vibe there, but man.

Brent Ozar: yeah, it was 2016, CU2, there was a GDR security patch. They unreleased it because it had trace flags turned on, then rereleased it again like a week later. And if you’re pushing the updates out as fast as they come out, man, you’re going to have a bad time.

Erik Darling: I think if I had to deal with the CU release cycle now for prod, I would probably be a month or two behind.

Brent Ozar: I wouldn’t even put it to development within the first week.

Erik Darling: No, no I mean, development would probably be a month behind. Prod would probably be three months behind. I don’t know, just staggered somehow so I can let some other poor fool jump on that bleeding edge.

Richie Rump: That’s usually you, Erik, by the way.

Brent Ozar: Jim says he had the same issue. You can also resolve it by recreating your DatabaseMail.exe.config file. And he said that the mail issue will be resolved in Cumulative Update 3. So see you next month.

Richie Rump: Which will break something else…

Brent Ozar: And we’re not saying not to patch either. Like, oh Brent doesn’t like patching – no, we love – I am a thorough believer that you should be on a relatively current version. The RTM version sucks but…

When should you change MAXDOP?

Brent Ozar: Let’s see. Gus says, “When should you change MAXDOP? If you have a 16 core SQL Server, should you change MAXDOP from the default of zero?”

Erik Darling: Immediately.

Brent Ozar: Would you take a coffee break first or would you change it immediately.

Erik Darling: I would do it immediately. I wouldn’t even hesitate. I would be like, done it, eight.

Brent Ozar: Eight, okay, I was going to ask you what would you change it to, yeah. And at the same time you change it, are there any other changes that you would also make?

Erik Darling: Oh dear god, do we need to stretch our time that badly? Yes, I’d pour flavored vodka on it, strawberry jam, throw some frozen English muffins in there, cook a pancake, I don’t know. Yeah, there’s lots of changes I make to a SQL Server. I try to write about them so people don’t have to continue to be befuddled by setting up SQL Server. This is one of those things I blogged about sort of recently, whether people still need help, basic help, with the setup. And there are settings that, when Microsoft stuck tempdb in front of everyone and said, hey, multiple files, size them the same, so some good stuff, like nudging people in the right direction. And by making a bunch of trace flags the default behavior so that you don’t have to be a DBA with that checklist of things that you do, they started putting some good things in the setup of the product and the way the product is presented to people. They just make their lives easier. MAXDOP and cost threshold are totally some of those things that just people need in front of them when they do setup. And if Microsoft is going to have pages on the internet in which they give you best practices for things, those best practices should show up during setup on those screens.

Even if they’re not filled in for you, just say, we recommend this, you should change it to this. Fine, I would take that over, meh just click next a few more times and out you go. Just put it in front of people so they know. If people still need this much help with it 20 something years after the defaults are added to the product, maybe it’s time to put them up front.

Brent Ozar: And you think too, the versions that go out today – so whenever SQL Server vNext comes out – Microsoft Ignite is coming up in about a week. Ignite is where they set fire to all your plans to go build your next SQL Server because they’re about to release the new version. So they drop the new version out. Whatever version next comes out next week – and it obviously won’t come out for production use next week, it will just be a preview next week probably, and I’m just guessing, I have no inside knowledge, but they did the same thing last year at Ignite. So whatever version they bring out, say that the final comes out in January of 2019, people are going to be installing that for the next seven years, eight years. The setup needs to go, hey here’s a place on the web where you can go for information that wasn’t written by Dwight Eisenhower, you know, things that are vaguely up to date.

Erik Darling: Like written by someone who has since hit retirement age and left. Like, I’m done. Like, I don’t even work with the product anymore. I took a job at a bigger company than Microsoft somehow. Just give us something.

Brent Ozar: Now I’m going to work in a monitoring company and immediately try to fix all of the things that I left behind in setup.

Should you use Amazon’s SQL Server images?

Brent Ozar: Kevin says, “Morning…” morning. Afternoon – afternoon web services. He says, “AWS, do you use your own SQL Server install from the images in EC2 or do you use theirs?”

Erik Darling: I didn’t know we had a choice.

Brent Ozar: Well the licensing is the big driver. So if you use AWS’s instances that have licensing included, so like Enterprise, Standard, whatever, then they’re going to make you do one of two things. Either you have to pay by the hour for that or bring your own licensing. If you want to use it for any kind of high availability or disaster recovery, it usually makes sense to build your own image and install your own SQL Server from there. Not because you’re going to do a better job, just because you’re not going to have to hassle with their licensing police. And then you can manage the number of replicas and all that without having to hassle it. Even if you do use their installs, it’s very next, next, next, finish. It’s just like what you would do if you were just throwing the DVD in and you were drunk working for a cloud company and you had to ship it out as quickly as possible.

Microsoft is the same way. Google’s the same way. The stuff that they ship out as defaults isn’t necessarily best practices – cost threshold for parallelism. So you would still want to go through your own setup checklist to get it the way that you would want it as opposed to somebody at a vendor.

Erik Darling: Do you remember what stuff was set to in managed instances?

Brent Ozar: Oh, I might have blogged about that.

Erik Darling: I remember looking at a bunch of stuff. I don’t remember what they had the defaults for there, but I think that would be a pretty good measuring stick…

Brent Ozar: No, I sure didn’t. I didn’t blog about what they did in managed instances. And I bet you…

Erik Darling: I don’t remember looking, now that I think about it.

Brent Ozar: No, and I bet you one crispy burrito that it is exactly the same as the box product. I bet they’re not changing that either.

Erik Darling: Yeah, which would be weird.

Brent Ozar: But remember, they’re charging you for CPU consumed, so on one hand, they want it to perform fast, but on the other hand, they really want to fleece you for all you’ve got.

What’s the perfect vodka martini recipe?

Brent Ozar: And then the last question – well it’s more of an observation from Michael Tilley. Michael says, “Erik, the perfect vodka martini recipe. He wants a vodka with more than 6x distillation, like Tito’s or Dripping Springs Vodka from here in Texas. The number of distillations has to do with the sipability or the smoothness of vodka and the ensuing quality of the martini being made.”

Erik Darling: Can I give you the perfect recipe for a glass of Lagavulin?

Brent Ozar: Yes, by all means.

Erik Darling: Glass, Lagavulin, enjoyment. Too much work.

Brent Ozar: There’s two kinds of people in the world; the people who love to cook and make things, like craft things from multiple ingredients, and the people who don’t. I’m one of those people where if I could reach into the fridge and get out a burrito that wouldn’t kill me and just put it in the microwave and I could magically stay healthy, I would be all over that. that would be perfect to me.

Erik Darling: It’s called a Hot Pocket, Brent.

Brent Ozar: Yeah, but that would kill you. If I only ate Hot Pockets, I would die. And I like Hot Pockets, but – same thing with the idea of a drink. I like wine. You open the bottle, you drink the bottle. Tequila, you open the bottle, you can drink the bottle. But the instant that there’s multiple ingredients involved – I’m all for fancy complex drinks, but someone else needs to be making them; not me.

Erik Darling: Yeah, it’s like the same way that I’m all for fancy complex stored procedures. Someone else should be writing them.

Richie Rump: Brent’s the same way with the software. He’s all for fancy complex software, as long as someone else writes it.

Brent Ozar: Absolutely, yes.

Can I connect to SQL Server running in Virtual Box?

Brent Ozar: Alright, so we’ll take one more question because Mike added one more. Mike says, “Is there any way to connect to a SQL Server instance running on a virtual box, Linux, from the desktop where the virtual box is running?” Yes, it will likely involve – oh yeah, you did that for a while.

Erik Darling: I still have virtual box on here. So to connect to the virtual – I’ll go look at my settings right now because I can pull them up quickly for you. I mean, it’s Windows, so it’s a little bit different, but from my settings – and I can connect to SQL Server here – over in networking, I have enabled network adapter, attached to NAT, and then in the promiscuous mode, I have to allow all. So that will make things magically work. And I can connect to all my VMs from the desktop and all my VMs can connect out to the internet. So if you want something different then you’ll have to Google for that yourself. But those settings work for me with virtual box to connect in and to have those boxes be able to go to websites so I can look at things in a VM on a website. Let’s talk about something else.

Brent Ozar: And that’s a perfect way for us to end Office Hours. We’ll all get back to surfing the webernets. Alright, see y’all later; adios.