This week, Brent, Richie, and Erik discuss the requirements for getting a Senior DBA or Database Architect job, data migration, encryption, fragmentation, differences between SQL Server 2016 and 2014, update stats strategies for large tables vs small ones, what it takes to work for Brent Ozar Unlimited, and more.

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 – 2017-03-15

 

How can I tell if a table has been accessed recently?

Brent Ozar: Let’s see what’s going on here. Bill says, “Is there a safe, definitive way to know if a table has been accessed in the last so many days?” Of course there’s the high expensive ones, like running a trace or capturing every query that runs. There’s less overhead ones that involve looking at the plan cache. There’s also, if you look at the index usage DMVs, there’s a count of the last user query, or like the number of queries and the last date and time of the user queries. If you use sp_BlitzIndex with mode equals two, I believe it is, you may have to check the documentation. Mode equals two will dump out the contents of all of your indexes. Then for any given table you can check to see when the last—it says quote/unquote user query was. But that’s any query from end users, system queries, jobs, you know, Richie’s application, whatever ran.

 

What’s required to get an architect or senior DBA job?

Brent Ozar: Graham says, “I know you guys are not big on formal education or certificates…” That is true.

Richie Rump: Well, he’s not. Or, wherever I’m situated is not. I dig college, dude. I had so much fun in college. I think there’s a lot of learning that you need to do as an individual in college, not necessarily to get you ready for a technical job.

Brent Ozar: Mm-hmm. Degrees show that you learn, that you are able to learn and pass tests. And finish projects so to speak, with your own education being a project. What’s your degree in?

Richie Rump: I have a bachelor of business administration and computer information systems.

Brent Ozar: Yeah, so Graham says, “Most companies have no idea how to get qualified candidates degrees or certs. So you get those to get your resume to the top of the pile.” He’s going on and on and on. He says, “What is required to get a senior DBA or database architect job?” Richie, what would you say is required for like a database architect job?

Richie Rump: Experience in designing databases. You don’t even really need to understand the tools. It helps, but understanding how to break down databases—well not even break down database—just break down requirements into a logical and then a physical model. That’s what you really need to know. Being able to talk to a user and understand this is what you really mean, not this is what you say. Right? That’s stuff that you’re not going to learn in college. It’s just, you’re not going to learn in any cert course. This is stuff that you have to go into projects and you have to build them yourself. Now, whether that’s in a job or not, that’s up to you. Probably having a job would be helpful but it doesn’t have to be. If you’re doing open source type stuff, that’s a project. You’re doing work. It’s the same deal.

Brent Ozar: Yeah. I’d say that same thing. That magic word that Richie started out with—experience—is the same thing I’d say for senior DBA working as well, both in terms of database administration and architecture. You start doing—not as your full-time job, but just as part of your job and then you gradually make it a full-time part of your job. You can start as a developer. You can start as a systems administrator, what you find really interesting and segue your way into. The other flip I ask people is think about as a business, what kind of risk are you going to run into if you hire a bad architect or a bad database administrator? It’s really expensive. So you want someone who has made some of those mistakes before.

Richie Rump: Yeah, exactly. Have someone else pay those mistakes so that you don’t have to later. I remember I had a three-key primary key once. This was way early in my career. Sure enough, the requirements had changed where you needed to add another key to that concatenated key. At that point I’m like, “Maybe I should have done something differently. Maybe I should have used a composite key.” At that point, I learned, oh, I should have put composite keys on everything.

Brent Ozar: The users swear this will always be unique. They promise on the Bible or whatever it is they like to swear on.

Richie Rump: Yeah.

Brent Ozar: Yeah.

Richie Rump: You just have to understand that, yes, the user said that and that’s what they meant right now. But in a year, they have to do something else. They may or may not understand that. So, being able to talk to a user and then essentially you’re jamming. You’re just going back and forth, “Well what about this and what about this? So what you really mean is this.” You start white boarding and it’s like, “So what I’m understanding is that this is how you want it to work” and you’re jamming back and forth on how their work is or how they see the system to do. Sometimes they have a good idea and sometimes you don’t. You just have to kind of understand that you’re not going to get a full picture from the users. Sometimes you have to kind of glue stuff together. It’s like working for you, Brent.

Brent Ozar: Things never get better though. No, yeah, it’s a discussion back and forth. It really helps everybody flesh things out and go, “Oh, yeah.”

Richie Rump: I remember having those discussions all the way down from the secretaries and the admins and then going all the way up to the CEO. Those conversations are wildly different. But being able to talk at every level of the organization I think makes a good architect. I mean because you have to be able to say, “Hey, this system has got to work for your admin level as well as what the CEO plans for it to get done.”

 

Is an upgrade really a migration?

Brent Ozar: Let’s see here. Terminology question from John. John says, “We have a 2008 R2 instance on a VM. We’re considering upgrading to SQL 2016 or vnext depending on when it comes out. I’ve heard that it’s generally a bad idea to do an in-place upgrade and what I should instead do is spin up a new Windows server and install SQL Server from scratch and then copy the data over. Is this a migration scenario then?” Really, for me, every time I drop a new version of SQL Server in, it’s a migration. I want to go copy all the data over first while I’m still in production on the old version and let users run acceptance testing against the new one. And go, “Is everyone comfortable with this? Do you like the way it performs?” Am I comfortable in failing it over back and forth? How my scripts work, all of that, before I then go live on it. I don’t mean let them test for 15 minutes. I mean give them that server and let them bang on it for weeks.

 

Do DBAs know who Scott Guthrie is?

Richie Rump: I just saw a SQL Intersections thing coming up, right?

Brent Ozar: Yes.

Richie Rump: It had Paul Randal, Kim Tripp, and then Scott Guthrie. But my question is, does your normal DBA know who Scott Guthrie is?

Brent Ozar: I think Intersections tends to be more of a jack-of-all-trades type stuff.

Richie Rump: Okay. So they wouldn’t know of the red polo.

Brent Ozar: Yeah. I’m always surprised by—like when I do sessions. It’s usually one half to two thirds developers or sysadmins, depending on the angle of it.

Richie Rump: Oh, okay. So this is most—like pure DBAs wouldn’t attend, don’t usually attend something like that? Or is this, hey, this conference brings a mix of great, a lot of good people and they kind of mix and match their experience to whatever they want?

Brent Ozar: Yeah, I’ve seen a lot of companies bring a team of three or four people, like a DBA and two developers and an architect. Then they all go fan out during the day and they hit different sessions. Then they regroup at lunch and after hours and go, “All right. Based on what we’ve learned, here’s some things that we need to do.” I’ll be accosted in hallways where I’ll get a team of like four different people with four different job descriptions that have a design question. We talk through the whole thing and sketch it out together and get everybody on the same page.

Richie Rump: Well I hope it’s not about database architecture because they’re asking the wrong dude, man.

Brent Ozar: Thankfully, no. And I never get the design questions either. I’m always like, “Just go by Louis Davidson’s book.”

Richie Rump: Yeah, exactly. You know what’s funny is I recall the one year I went to TechEd, I went with a colleague of mine. From the time that we got on the plane until the time that I got to the next plane, which was like a five-day, TechEd used to be really long, I saw him once and that was it.

Brent Ozar: Yeah.

Richie Rump: It was like, got off the plane, I’m like, “See ya, guy.” Boom. I hit every party. I hit everything. I was totally networked out that one, where he just went to session, to session, to session. I’m like, session?

Brent Ozar: I think that’s to some—I don’t know how much of an extent—but like with us when we go to PASS Summit we all end up fanning out and going to different sessions all day and then we are sometimes overlap at stuff like Speaker Idol.

Richie Rump: Yeah. But typically I go—because I could always catch a session on freaking YouTube or buy the sessions or whatever. But the value is the people that are there. They’re not going to be there when I get back home to my office. So interacting with as many people as possible is usually what I go for in a conference. Even at Code Camp, I went to one other session besides myself and the rest I was sitting there jamming out. I met a Microsoft guy. He happens to run the MVP program here in the south region. I’m like, “Are you presenting?” He’s like, “No. I’m just here supporting my MVPs.” I’m like, “MVPs need support?” What the heck is that about?

Brent Ozar: They give support.

Richie Rump: What kind of whiny crybaby MVPs are you supporting, man?

But no, it was one of those encounters that I wouldn’t have had if I was in a session.

Brent Ozar: Yeah.

Richie Rump: Just wouldn’t have happened.

 

What are your thoughts on security?

Brent Ozar: J.H. says, “What are your thoughts of computer and data security in the future on hearing that the CIA, NSA, everybody has access to every computer? Are data security patches and encryption passwords just a false sense of…?” He says reality but he means security. I think we are kind of weird in that we grew up at the point where we really did have privacy. Growing up, we did dumb things. There was no Snapchat. There was no Facebook. No Instagram. You could make mistakes as a kid and kind of, “I probably shouldn’t go back and do that again.” Today, I think that’s less of a case. I think we’re just the one generation of people that go, “Oh my god, this is terrifying.” But when you talk to kids they’re like, “Yeah, I just Facebook everything and I Instagram everything and I Snapchat everything.” They’re just not really as concerned about it.

Richie Rump: Yeah. Something else that had come up with genetic testing and having your employer being allowed to genetic test and stuff like that, that horrifies me.

Brent Ozar: Yes.

Richie Rump: Because if—because now my genetic makeup is given to my employers and who knows what is in there that they could use against me. Of course, once that data is out, you don’t know where else it’s going. Right? I mean, it’s behind someone. So, yeah, a lot of this stuff does scare me but what’s the alternative? Do nothing?

Brent Ozar: Tinfoil.

Richie Rump: Right. So why don’t you just remove all your firewalls to your networks and keep it open? We still have to do our due diligence and encrypt where we can. If that other person or entity can unencrypt it, then they can. But we still have to do our due diligence and do what we can.

Brent Ozar: Especially for today.

Richie Rump: Yeah, and hopefully newer tools come out that will prevent the decrypting of your data. But if they can, they can. So remove the door off your house because anyone could go ahead and break it down.

Brent Ozar: And you know it’s a matter of time. Like what people can encrypt with today, computers will be able to crack that in ten years, fifteen years, twenty years. So if you have any encrypted backups lying around, so to speak, if you’re dropping them on Dropbox, Amazon S3, whatever, you have to know that it is a matter of time until that gets out. It’s just a matter of time. It’s a time bomb. I think if you come into a different perspective that everything I leave out and around sooner or later somebody is going to get it, I think that changes a little bit of the security discussion.

Richie Rump: Yeah, what was the one that was just verified that it was… it was SHA-1, right? Yeah, got to move something else.

Brent Ozar: It’s a matter of time.

Richie Rump: Yep.

 

Why aren’t you guys concerned about fragmentation?

Brent Ozar: Joshua asks a polarizing question. We’ve got whole bunch of questions in here. “Webinar yesterday with Tim Radney talking in part about fragmentation being a big deal. You guys are not fans to say the least of defragmenting. Can you recap why the differences in expert opinions and what’s your stance again?” I have this thing where I like to grab people by the shoulders and go, “What are you doing?” Just shake them and, yes, sometimes as Richie says, “By the throat.” I want you to have evidence of what your pain point is and then…

[Brent’s audio cuts out] [Brent’s mic turns back on]

Richie Rump: Oh, there you go. Thank you, Brent, for saving me. Thank you. The audience thanks you too because I was going to start talking about Node.js and everything was going to go away.

Brent Ozar: Well before I got caught by the fragmentation police. I want to make sure you know what your pain point is and how you’re tying the relief to that. I have never, count them, never seen a server where people are going, “Oh my god. Performance is so horrible,” and then defragmenting your indexes took you across the finish line in the end user’s eyes. I’ve seen cases where it has cut I/O by some percent or cut some query execution time by some percent. But when you look at alternatives like tuning your indexes or tuning your queries or putting the right amount of RAM in the server, defragmentation is just usually shuffling deck chairs on the Titanic. There are edge cases where it can make a difference. But what I would just say is, have you ever seen a demo that really conclusively proved fragmentation took a query from 30 seconds to 3 seconds? If not, if it took it from 30 seconds to 23 seconds, do you think end users are happy with that? Tim is a genius. I really like him. Great guy. But these are the kinds of things that just make a little bit of difference. Fragmentation, sure, it makes sense as a best practice preventative type thing, but when people are coming to us screaming for performance problems, just never seen fragmentation fix the problem.

 

Does IntelliSense cause deadlocks?

Brent Ozar: Kelly says, “Have you ever run into a deadlock with something called F check and cleanup cache temp table? I had SSMS IntelliSense recently get killed by it. I know IntelliSense is no big deal but management was like, ‘Why did this happen?’” I would step back further and go, “Why was a deadlock a big deal?” What was the query on the other end of the deadlock? If it’s IntelliSense, don’t get me wrong, it’s not great, but what is it that was locked on the other side? That’s what I would want to hear more about.

 

What’s the SYSNAME data type for?

Brent Ozar: Guillermo asks, “Have you guys ever used the sysname data type when writing T-SQL and what did you use it for?” Have you ever used it for anything?

Richie Rump: Sysname? No, no, no.

Brent Ozar: The only thing I’ve ever used it for is in like sp_Blitz, BlitzIndex, BlitzCache just because it cleanly, supposedly, converts system name objects, like if they change the system name type or something. That’s my extent of it.

 

Can I ask a replication question?

Brent Ozar: Justin says, “Can I ask a replication question?” The answer is no.

 

Will SQL 2016 help data marts?

Brent Ozar: M.M. says, “For a large reporting data mart application, what are the differences between SQL Server 2016 and 2014?” Large data mart. Have there been any features that got you excited in SQL Server 2016, Richie?

Richie Rump: Nope.

Brent Ozar: No? Oh.

[Erik Darling joins Office Hours] Brent Ozar: Oh my god. It’s Erik Darling.

Richie Rump: It’s another Brent Ozar.

Brent Ozar: So for the large reporting data mart, the one thing that’s kind of nifty is columnstore index improvements. In 2012, you could have non-clustered columnstore but they made the whole table read only. In 2014, they added clustered columnstore. In 2016, they made all kinds of improvements to columnstore. So that would be the one that I’d be most interested in. Columnstore has two big benefits. One is it can make your SELECT queries go faster but the other one is that it compresses the tables too. If you want to learn more about columnstore, go to columnstore.net. This is Niko Neugebauer’s site that just focuses exclusively on his columnstore blogs. Pretty slick stuff.

 

Does shrinking the log file fragment it?

Brent Ozar: Next up. Let’s see here. M.M. says, “A follow up to the defrag. Shrinking a log file does not fragment the file. Correct?” That is correct but what are you doing that for? I want to know more about why you’re doing that. So with that, if you do shrink the log file, just know that whenever it grows back out again, instant file initialization doesn’t help you here. SQL Server pauses while the underlying storage is being written out with zeros so to speak to grow out the file. So make sure that you’re solving a problem there.

 

We stopped defragmenting. You’ll never believe what happened next.

Brent Ozar: Bill says, “We’ve cut way back on index defragging and reorging based on your article and have had no issues.” If you read the comments on my blog post about fragmentation, that’s some of the coolest stuff. Jeff Moden is an outspoken SQL Server guy in the community, has all kinds of polarizing opinions like me. He said, “When I read this blog post it sounded like you were drinking bong water so I did it just to prove you wrong. I turned off all my defragmentation jobs and my database kept getting faster the longer I left it without defragging. So now I like bong water.” There you go.

 

How are Angie, Doug, and Jessica doing?

Brent Ozar: Let’s see here. John says, “Have you guys heard how Angie, Doug, and Jessica are doing? I miss them.” Yes. Angie, I wish Angie had a blog. She does post on Instagram. If you search for her on Instagram, she’s got an account over there. She’s been posting travels of her and her husband, Leif, have been all over the place, really cool places. They have a really cool dog too. Doug is posting under—he has a website called SQL Theater where you can watch some of his newer tutorial videos. Super high production quality. Always makes me laugh, really fun to watch.

Erik Darling: I think this is working now.

Brent Ozar: I hear you.

Richie Rump: He’s not just a pretty face anymore.

Erik Darling: Second time is a charm. Not just an eyebrow floating in the screen anymore.

 

How should I update statistics on large tables?

Brent Ozar: Funny. I have a perfect timing for a question for you too as well. Guillermo says, “Do you have different update stats strategies for large tables?” He says a large table is a million rows. I’d switch a “b” in for that. A million rows as opposed to smaller tables.

Erik Darling: You know, it really does depend on the frequency of data churn for them and how sensitive the data skew is. So like I’ve had giant tables where I turned off automatic updates and I’ve put on the no recompute thing. Because when I take a full scan of those statistics, I want those statistics left alone because I have some data sensitivity issues. So there are times when I do that but most of the time I just let the update stats thing run. I may run it more frequently if it’s a large table but I’m not going to—there’s not too many dials and knobs that I’m going to mess with in there.

Brent Ozar: One person says that the only way our conferences would be more comically is if children ran in in the background. That could totally happen. Both Richie and Erik, both have kids at their houses that could come in at any moment. We talked about that in the company chatroom. I said, “Look, whatever happens, you just put them on your lap and you just keep right on going.”

Richie Rump: Put them on your lap, break out the switch, and let it go.

[Laughter]

Brent Ozar: Same thing you would do if the webcam wasn’t on. You beat them senseless.

 

How should I handle two versions of data?

Brent Ozar: Adam says, “I’m tasked with loading data from one system with two versions.” His metadata is different between the versions. Should he store the versions separately with views to join them or force them into one schema? Before we even answer it. We should probably just say where should he go to learn more? Or what resources should he talk to? Because it’s not us. It might be Richie because he likes this kind of thing, but, yeah.

Richie Rump: Maybe. But I don’t write about it.

Brent Ozar: Louis Davidson would be the guy I would probably ask. If you search for “Louis Davidson SQL Server database modeling,” he’s got a blog out there on SQLblog and takes questions via email. Is there anybody else you would ask?

Erik Darling: As far as a specific person goes, I don’t know. This is, again, the slide is up so DBA.stackexchange might—as long as it’s not too broad of a question. If you can get a decent amount of specifics in there without giving up the ghost on sensitive company information, DBA.stackexchange is a great resource. Lots of smart people read that who can give you—there’s always questions and advice about data modeling on there, normalization, and stuff.

Richie Rump: Typically what I would do is I would just model it out and then throw some data, some real live data, maybe not production data but something that would be representative and then just start writing queries up against it. Then talking about the data with the developers and/or other DBAs. Just run some modeling tests yourself. Sometimes flexibility is first and foremost, which will then drive your model to however what level of normalization you’re trying to get. Sometimes speed is more important which is—that’s when we start denormalizing. I would say go ahead and model it out and start running tests because that’s when you’ll really know when it hits the fan. When I try to be super clever with my model and I don’t do the testing, that’s when I come into trouble. It’s never trouble right now, it’s always trouble like six months to a year where things start really blowing up. So I would definitely say test it out, model it out.

Erik Darling: When actual data starts getting involved.

Richie Rump: Yes.

Erik Darling: Things go boom.

Richie Rump: Yeah, and that takes time. A lot of times when we’re architecting we don’t have that kind of time to do that stuff. But that’s when you’ve got to start begging your boss and saying, “Hey, look, if we figure this out now, it’s going to be a lot cheaper rather than us figuring out six months in having to rewrite all this code.”

Erik Darling: The other trouble with architecting is that you tend to only put perfect data in. You don’t tend to put in weird edge cases or try to account for all the anomalies and bizarre stuff that users can enter in. You can try to, but it’s impossible. You kind of spend more time spinning wheels than trying to figure out what could be wrong and putting in that wrong data.

Richie Rump: Yep.

 

Why don’t I have missing index requests?

Brent Ozar: Mike says, “I’m running sp_BlitzIndex and I’ve got aggressive indexes but there’s no missing indexes.” This is where you want to start playing around with sp_BlitzCache, which will show long running queries. You can sort by duration for example and then start digging in to see which indexes you would personally add. I say that because it’s near and dear to my heart because I’m teaching a tuning class in two weeks where we do that.

 

How should I get started with distributed replay?

Brent Ozar: Mandy says, “Do you recommend any advice or resources for getting started with distributed replay?” Jonathan Kehayias. Jonathan Kehayias has—if I remember right—a course on it on Pluralsight. But if not, he’s written about it in the past as well.

 

What’s it take to work for Brent Ozar Unlimited?

Brent Ozar: The last one that we’ll go hit, Ronnie asks, “Many questions are asked about how to get a DBA job, but what is required to work for Brent Ozar? What skill set do you look for?” So what I’m going to do is I’m going to turn around and ask these guys. So first, Richie, if you had to hire for your own position, what skills would you look for and why? I’m going to tape this because I’m going to replace you soon.

Richie Rump: Good lord. A certain bit of insanity has to be involved working here. Being able to work on your own and especially with my position and being able to take minimal requirements and turn that into code, so that means a lot of imagination. So being able to take on concepts very quickly and then being able to write requirements and then write code up against that. As well as an ability to learn very quickly with new things. We tend to gravitate towards a lot of new things right now. The stuff that I’m writing now is stuff that I haven’t touched like ever. Like nine months ago I had never touched Node, I had never touched serverless, I’d never touched cloud, I’d never touched Postgres. I’d never touch all these different technologies and I’m using it on a daily basis now. So that would be it. It’s not even about the technology stuff because if you’re quick in learning and you understand that stuff, that stuff will come. So a lot of soft.

Brent Ozar: I point over in a general direction, I go, “Richie, I want a serverless. Make it blue.”

Richie Rump: I’m like, “What’s serverless?”

Brent Ozar: Erik, how about you?

Erik Darling: I guess once you get past sort of like the SQL knowledge necessary, you know, be on the phone with someone and get to their pain points and how to solve them, I would say being comfortable talking to people. Being able to talk and listen and Google.

Brent Ozar: Simultaneously.

Erik Darling: Yeah, so like being comfortable with people. Not being uncomfortable and turning into super robot-y on the phone with people where like you just sort of like recite stuff. You have to be able to be kind of loose and talk and sort of like flexible. You learn as you go as well, stuff like that. Another important thing is how would you spend your free time. If you get off the phone and the first thing you do is get up and walk away, I don’t know if that’s a good fit. There’s a lot of stuff you have to do with like the—the stuff I like to do is blogging and writing training materials. I don’t know. Writing in general.

Brent Ozar: Scripting.

Erik Darling: Stuff that I enjoy doing. Working on scripts. Someone who is able and willing to do that would be who I would look for in replacement Erik.

Richie Rump: For the record, Erik and I do completely different things. We’re completely different.

Erik Darling: Different set of expectations.

Brent Ozar: I wouldn’t, like in terms of a skill set, I wouldn’t aim for a specific skill set because with small businesses it changes so fast what you need. Like the next person that we would hire would probably be a customer success person who helps customers get up and running successfully. It just changes every year on what you look for and what you move into next. Well thanks, everybody, for hanging out with us at this Office Hours. We will see you guys next week. Adios.