This week Brent, and Richie discuss validating backups using the backup set table, how to figure out if you’re a Mid-Level or Senior DBA, rollbacks, compatibility modes while upgrading SQL Server versions, MAXDOP settings, and Brent’s car purchasing tip.
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 – 1-10-18
How do you validate backups in an AG?
Brent Ozar: G.G. Asks, “How do you validate backups using the backup set table? If I’ve got a failover cluster and I failover to another node, does backup set get moved around?” Backupset is in MSDB; so as long as you’re in a failover cluster, all the databases failover together. Where you get caught is in an Availability Group, because in an Availability Group only the user databases failover. So there’s a different copy on every single server. The way that you would check that – one way you could do it is with sp_AllNightLog – no, sp_BlitzBackups. I’m going to open up on the webcast over in the browser here. If you search for sp_BlitzBackups – this is a stored procedure that we’ve written that will actually go out and centralize your backup data from multiple servers in an Always On Availability Group. SO then you can push all the data into one central MSDB repository. That’s sp_BlitzBackups.
It will also do things like tell you whether or not you are missing backups, what your worst case recovery point objective is, your worst case recover time objective is; you name it.
Why aren’t there more mid-level DBA job ads?
Brent Ozar: Thomas says, “So if I remember right, sometimes companies hire you guys to help them find new DBAs. Do you guys see many people hiring mid-level DBAs? I see almost nothing but senior DBA positions out there.” Richie, I’m going to ask you. Have you ever seen a position for a mid-level developer?
Richie Rump: Actually yes, I’ve actually hired mid-level developers. But here’s the thing – a lot of times when companies say senior folks, they’re actually looking for mid-level folks. A lot of times, mid-level folks don’t know they’re senior because they’ve been mid-levels for such a long time. They never really thought, “What’s a senior and what’s not?” A lot of times it’s just a title. They’re looking just for a DBA and, you know, it’s a pain. We just have senior and junior and that’s all we have. If you think that – if you take a look at the qualifications and you like the company, then take a look at the responsibilities and it’s somewhere along the lines of what you do, go ahead and submit your application.
What’s the worst they could say? No? You know, what’s the worst thing that could happen? You go into an interview and they say you’re not what we’re looking for? Oh, cry one single tear, you know… At that point – but you’ve made connections and then you can start asking what types of things are you looking for from a senior DBA? And then they say we’re looking for this, this and this; and then you start going off and you start learning. “Well, maybe I need to learn more about clusters. Maybe I need to learn more about Linux or start doing Linux…” Or whatever that is – so then you can start becoming more that senior dude.
Brent Ozar: Yeah, the problem with mid-level jobs usually is that it implies there are three levels. The company has to be large enough to have senior people and junior people and mid-level. So that’s fairly large teams. It’s so rare that you see a DBA team that large; that they have three different tiers of DBAs. I’m with Richie that if you think you’re mid-level, you’re probably actually a senior. Think about the number of years you’ve spent working on SQL Server; even if your job title wasn’t senior DBA, or even just junior DBA. I was a developer for like eight years working with SQL Server before I had DBA in my – I know, it’s hard for Richie to believe. I was the world’s worst developer. I sucked so bad. Just picture, you know, that T-SQL code you’re working through – my Visual Basic code was way worse than that.
True story, there’s a company right now that’s still using the intranet that I built for them back in ‘99 and 2000. I built a whole intranet for them. The reason why they haven’t been able to get off of it is I built the whole thing in Macromedia Dreamweaver Ultradev, which built source code into DLLs and they can’t find the source code. SO they’re like, “We don’t know what your logic was when you were building all these rules and we don’t have a copy of the laptop; we deleted everything.”
Richie Rump: I was actually – I talked to Cecil, my co-host at Away from The Keyboard yesterday, and we were actually talking about that. What’s the difference between a mid-level developer and a senior developer? And a lot of times it’s not the actual code. The code is very similar form a senior to a mid-level, but the difference is what you do outside of it. Most of the work that I do is documentation; it’s putting things through the source control. It’s doing things around the code that so when the next person comes in, hey, everything is good and I know exactly what we were supposed to be doing, and things like that. It’s those things that make us senior for a developer and not necessarily, “Oh, I know how to do this algorithm so much faster.”
Brent Ozar: It leans more towards architect. You’re setting policies, designing here’s why we want to do something, determining where it makes sense to cut corners, where it doesn’t make sense to cut corners, you know, being confident enough in your own skills that you can make those decisions.
Richie Rump: I will get there someday, Brent. I swear, I will get there someday.
Brent Ozar: Yeah, but then I’m worried you’re going to get a better job. I need to keep you in a $25,000 per year job as long as I can.
Richie Rump: Especially here in Miami, you know. I got a can of beans this month, so it was good.
Brent Ozar: Wahoo, next month I’m going to get you the bread I promise.
Richie Rump: Rice – we can’t just have black beans without the rice.
Brent Ozar: Thomas follows up with, “Yeah, Pat Phelan and Ed Leighton-Dick both tell me that I’m not mid-level, but I still feel that way. Probably because I compare myself to guys like them and you.” I always think of it as like this pyramid type scheme where you’re always looking up. You know, you’re always looking up at people you admire and there comes a point where you’re like, wait a minute, those two people you admire are bloggers and user group leaders. If you think about most senior DBAs out there, they’re not leading a user group; they’re not writing blogs. They’re not giving back; they’re just putting in their 9-5 at different companies. And I’m not saying that it’s bad to put in 9-5; it’s not. You should have border lines around what you do on your family time, your weekend time, et cetera. But when you’re idolizing those people like that, those are incredible overachievers. They’re just working their tails off in terms of also giving back to the community or whatever. Everybody looks up to people like that. So that’s very different.
Richie rump: yeah, what’s funny is when I was consulting and kind of going from place to place, I would be surprised if a senior DBA knew what they were doing, right. When you start seeing that, now you start understanding, senior is just a title that is kind of given. It’s kind of like Chief Executive Officer is supposed to have this big strategy and stuff like that. Sometimes they don’t, sometimes they just know people and they kind of wiggle their way up to the top. It’s the same thing with DBAs. You kind of know people and do the right things the right way once, and then they kind of just promote you because they don’t want to lose said person who knew that one thing that nobody else wants to get their hands dirty with.
So my guess is, if those guys are saying that you’re senior, you’re senior. Just deal with it, breathe in, breathe out and see what your next step is.
Brent Ozar: Buck Woody has that great saying, “Have you get ten years of experience or have you got the same one year of experience that you’ve done ten times in a row?” There are so many people who say that they’re senior when they’re not really. All they’ve really done is just spend ten years in a row at the same job. I’m going to go off on a tangent here – there’s a couple of other questions in the queue but I’m going to spend a little bit more time digging into this one.
If you have other technical questions, feel free to go ahead and follow up on those in the Q&A. In the meantime, I’m going to hold on and focus on Thomas’s thing for a second. Being a senior means that you focus really deeply on a couple of things inside the SQL Server engine. The SQL Server engine is so big and encompasses so many technologies and they change every year. No one can be an expert on all of them. Those of you who are listening to the podcast can’t see the list up on the screen, but on the screen here I’ve got Always Encrypted, Distributed Availability Groups, R Services; all these things that no one person can know. Even those of us with fake jobs like consultants who get to sit around and learn all the time, we can’t learn all this stuff. I don’t even know a third of the stuff that’s in there on that screen.
So when I think about what a person does, I come up with these eight bulleted items in this list here. I go all the way from designing tables down to installing and configuring SQL Server. And since I had a hard time understanding whether or not I was a senior on various things, what I did was, I put together a little quiz. So take down and write from one to eight – take a piece of paper, a notepad; whatever. Write down one through eight and I’m going to give you a quick quiz to figure out whether or not you’re a senior. Start with number – how often do I design new tables? Give yourself one point if you never design new tables; you deal with tables that other people made. To points if you do it a couple of times per quarter, three points if you do it once per month and four points if you do it every week and you’re familiar with data modeling tools and books like Lewis Davidson’s Relational Data Modeling book.
Not everyone can score four points across the board. I’m a perfect example of that. I only score one point on this question because I never deal with – Richie says four points – and you do, you’ve got Lewis’s book, probably.
Richie Rump: Not only Lewis’s book, but I also have the Data Model Patterns book, volumes one through three.
Brent Ozar: Because you do it. I mean, you’re in there building tables. And as DBAs, I always thought, “Well as a DBA I must know how to do these things…” Just because you know how doesn’t mean you’re doing it. I know how to replace a tire, but if you gauged my skills on how good I am at replacing tires, I would suck because I’d be out there reading a manual trying to figure out how to get the jack to work again.
Richie Rump: Where’s the notch under the car again? I don’t understand…
Brent Ozar: Question two – when I write new queries, give yourself one point if you use the same syntax you’ve been using for years, two points if you try new features every time they come out, like the new string splitting feature inside later versions of SQL Server. Three points if you AB test different features or different query plans often, and then four points if you know how to trick the optimizer into giving you the best plan. And I don’t mean with a hint; I mean rewriting your T-SQL in a way that will make it consider to do different joins first.
Question three – when I deploy new changes, give yourself one point if you do it live, two points if you script it out first and then test it in a development environment, three points if you script it out, test it and document the change that you are expecting – like changing MAXDOP from eight to four will cut CXPACKET waits. Then monitor afterwards to make sure you’ve got the change you expected, and yes, I mean server level and database changes as well, like sp_configure settings. Then give yourself four points if you check the changes into source control, someone else tests them and they’re deployed in an automated fashion.
Question four – when I tune queries, give yourself one point if you kind of bumble around, point and click and you’re not really sure how to read those execution plans. Two points if you know how to apply a missing index recommendation and when you shouldn’t. Three points if you know how to hand-craft an index recommendation without seeing one in the query plan; like you can just read the query and you know exactly what kind of index you need to create and what fields need to go first. And then four points if you can recognize when you’re getting the wrong kind of join given your types of indexes, and how you can influence SQL Server to pick a different one.
Question five – when they say that SQL Server is slow, give yourself one point if you’re completely surprised, two points if you know what metrics to look at, three points if you know what your server’s baselines are for that metrics, and four points – you knew about it because your monitoring tool told you and you don’t have any email rules set up to push those alerts into a different folder..
Question six – when SQL Server goes down, give yourself one point if you didn’t know the end users had to call you. Two points if you remote desktop in and start clicking around. Three points if you know what logs you need to hit in which order and you know how long it will take in order to failover to your secondary. And then four points if you grab your customized checklist and you start working through the steps in order; documenting as you go because you have a standardized triage process.
Richie Rump: So how many points do I get if I just call Erik?
Brent Ozar: You’re a developer. I think that gives you ten points. I think that’s perfect. Question seven – when I install SQL Server, give yourself one point if you just run setup.exe. Two points if you Google for a best practices checklist. Three points if you grab your own customized checklist that includes your specific settings. And then four points if you do an automated installation and you’ve already got those files saved out somewhere.
Last question – when I design high availability and disaster recovery – Richie’s face is a lot like my face these days – give yourself one point if all your instances are standalones. Two points if you use the same techniques you’ve been using for years; like you only know clustering or you only know log shipping. Three points if you know what the options are and then you work with the business to pick a solution; like you know several, clustering, replication, Always On Availability Groups, database mirroring, all those things. And then four points if you get the business’s RPO and RTO requirements in writing and then you give them budget estimates in order to meet those requirements.
Now, when you look at your answers, being a senior doesn’t mean that you’re a three or a four across the whole entire list. Being a senior means you’re a three or a four in your areas of expertise. So if you’re a database developer, I expect you to have three or a four around designing tables or writing queries. And you might be pushing two or three maybe, if I’m lucky, down under tuning queries or deploying changes. But I don’t expect you to be monitoring performance.
Richie Rump: That’s accurate because the last three questions I was garbage.
Brent Ozar: Right, of course. And I’m the same way in reverse. Like when it comes to – I’m more of a development DBA or production DBA over here. When it comes to designing tables, I’m garbage. Can I do it? Sure, I write create table statements all the time for demos, but you don’t want me modeling your data warehouse. I have no business doing that.
So Michael says, “Are these questions available on the Brent Ozar website somewhere? I’d really like to go through and grade myself.” Dang, we just went through them, chief. These are from our senior DBA class. They’ll actually be in the webcast recording though. So you’ll be able to watch it in our Youtube channel or watch it on the blog. It will come out on Monday.
These questions are often usually surprising to people because people think that they’re really – I hear over and over again, people go, “I do everything. I do that entire stack.” Yeah, but you suck. When we look at your points, you’re a one or a two across the board. You’re not a senior, you’re much more what I would call a junior. You have lower scores, even in an area of your specialization.
Now you start to understand what it would be like to be a senior in one of those columns, and you can decide exactly which areas you want to specialize in; which areas you want to get better in. So hopefully, Thomas, that gives you a rough idea about where you’re at. Now we’ll go back to our regularly scheduled slide deck; Richie, any thoughts that you had out of that one before we keep going?
Richie Rump: No, I really love how you could just like jump into a slide deck and just go at it. If I had to pick something up that I hadn’t picked up in a while, it’s like, no I need three days and I need to go through it and practice – Brent just goes right into it; boom…
Brent Ozar: Here we are, live; do it. For a while there, two years ago, we had several setups where I was doing two weeks of back to back training. You know, I would do two weeks of classes. I would fly to Newark, Atlanta, do two weeks of back to back training. Literally, I would walk out of the classroom at the end of a day, I would go straight to the hotel room, I would order room service and I’d start reviewing the next day’s slide decks, because you don’t have a choice; you have to move so fast.
Richie Rump: You lost me at Newark. You lost me there. I can’t figure out why you went to Newark, but…
Brent Ozar: You lost me at Newark as well. Newark is not a – the people in Newark were nice; the city… Not so nice.
I have a backup stuck in rollback…
Brent Ozar: Doug asks a question. He says, “I use Ola’s Hallengren’s backup solution. I had a database backup that got hung. After 48 hours I canceled it. Rollback says it’s going to take four days and is increasing. Should I ride it out or restart the services at night during a maintenance window?” I went through this exact same thing with a client. The best thing I can recommend is call Microsoft support immediately. I had two cases, where one I restarted the instance and we were fine. The second, MSDB was in recovery for an extended period of time. So I just worry about you restarting the server and running into the in recover thing. Calling Microsoft for support just gets you that layer of insurance with your company; that everybody understands the risk involved and it’s not your butt on the line. Opening a Microsoft support case is $500; that’s totally worth it.
Richie Rump: What’s the worst rollback you’ve ever had, Brent?
Brent Ozar: Four days. I had a multi-terabyte data warehouse – go ahead…
Richie Rump: Wow, two months. I had a rollback that went two months.
Brent Ozar: Why on earth would you keep it going? I would restore from a backup at that point.
Richie Rump: Well one, it wasn’t my call. So what I did is that I was able to take a development database and utilize processing on that. And so the operation was continuing to run. So the DBA thought he could have as much time as he wanted to get production back up and running. Meanwhile, we’re down at the development database. But it was a lot of work that I needed to do to get that up and running. But yeah, two months, and then they gave the guy a raise at the end of the year, and I’m like, “But his rollback took two months.” How many Microsoft calls did he actually have to make sure that… I mean, two months, and it’s because he had his partitions incorrect and they were just… Yeah, there was a lot of problems with that one.
Brent Ozar: I can almost imagine him coming into work every day and not even going straight to the computer. Going to the coffee machine first, getting the coffee, “Alright, then I’ll go check on my rollback.”
Richie Rump: “Still going on. Alright, well let me go read this book I’ve got here…”
What compatibility level should I use?
Brent Ozar: Paul says, “We were an early adopter of SQL Server 2014. We’re now moving to 2016. We’re worried about what compatibility mode we should be in; 2012 or 2016? What am I going to hit when I get surprised with moving to the 2016 cardinality estimator?” Here’s my advice on that: when you go to 2016, stay in 2012 compat mode. If you were in 2012 compat mode today, stay there. It’s working for you just fine. Leave it there for the first, say, two weeks. After the smoke has settled – when people go, “Oh okay, this 2016 thing is not so bad,” consider flipping on query store. Query store is like a black box recorder for your query plans. Make sure that it doesn’t kill you in terms of server performance, because it does have known gotchas. As long as it doesn’t kill you, it’s accumulating the query plans that you’re getting. Then a week or two later, on a Saturday or Sunday, flip that thing into 2016 compat mode and you’re immediately going to start recognizing which queries are problems for you and which problems are not.
The queries that are problems, if you wanted, you could pin the old query plan back using query store; it’s relatively easy. But I’ll be honest with you, what most shops are doing is they’re just staying on 2012 compat level – every CU that comes out, Microsoft improves the handling of query plans. So the more time that you take, maybe the query problems that you’re having won’t be such a big deal. After all, the robots are coming to take our jobs, right. The robots are coming in to make perfect query plans; performance tuning is going to be irrelevant and the robots will take care of you.
Richie Rump: Actually, I’ve got a robot interviewing here in just a few minutes. So as soon as I’m done with this…
Brent Ozar: Nice – is he going to work on replacing your [floor]?
Richie Rump: Well he better, or else I’m not going to hire him.
Brent Ozar: That’s the first job.
Will lowering MAXDOP fix blocking?
Brent Ozar: J.H. asks, “Sorry if I might have asked this a while ago…” Dang, J.H. start taking notes. “I currently have MAXDOP set to use all of my cores. But if I set it to lower to avoid CXPACKET blocking, CPU usage, could it slow down other queries?” Think about this logically, and I’m going to use eight cores just because I have eight fingers to hold up… I mean, I have ten fingers; I guess I could use all ten, but eight just seems like a more logical number here to use. I don’t know why; who cares? Pretend I was in a shop class accident and I only have eight fingers.
So say that you have eight cores in your SQL Server and right now you’re using all of them and you want to suddenly start to use four instead. Could your queries take longer? Sure, if you start using half the CPU. Now, what scares me here is you said, “I’m trying to do this in order to lower blocking.” Dude, guess what happens if your queries start running longer – your blocking gets worse, not better. So just make really sure that you know what setting you’re changing. There’s a KB article – there’s the only KB article I know by heart – 2806535. So if you Google for Microsoft KB 2806535, that’s the exact Knowledge Base article on setting your MAXDOP. You can read that, start from there. It talks about the number of cores per processor.
Richie Rump: Yeah, I worked on a large machine once. It was 32 cores. And on my slower queries, what I did is I tested how much MAXDOP it would be. Hey, what if I just ran eight cores and 12 cores and 16 – and I went all the way up to 32. And it turns out that if I ran with like 20, it was faster than it was if it was 32. So I just set that one query to run 20 cores because that was the fastest we tested. And I didn’t have to change it for the whole server and have everyone else with crazy queries – who knows what would have happened if I had lowered it just for that one. [crosstalk] problem, as opposed to it fixing across the entire server and introducing more potential problems.
Brent Ozar: Because every query, you’d theoretically need different MAXDOPs. That’s why you get different MAXDOP settings for CHECKDB now as well. Update statistics in 2017 CU 3 also has a MAXDOP command; just all kinds of cool hints there.
Well, thanks everybody for hanging out with us this week at Office Hours and we will see y’all next week. Adios everybody.
