This week, Brent, Richie, Tara, and Erik discuss their learning styles, Always On Availability Groups, memory leaks, Windows updates, how to transition from developer to DBA, upgrading SQL server from 2008 to 2014, temp tables vs table variables, and how to become an MVP.

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 – 2016-10-05

 

Brent Ozar: We got a couple of early questions, or not really questions, but comments from folks. One person says, “Thank you for the set SPN answer last week. Robert Davis’ blogpost SQL Solider was great.” Totally agree. I love that myself, refer back to it all the time. Another person says, “Last week I asked about parameter sniffing issues and as you guys suggested, I’ve applied the update statistics and it worked for me. Thanks for your help.” Awesome. Glad to hear it. That’s very cool.

Erik Darling: Handy dandy. That will be 50 bucks.

Brent Ozar: Just hold your credit card up to the screen and then…

 

Should I put 3 2TB databases on one server, or 3?

Brent Ozar: Upendra asks, “We have three critical databases and the total size of them is about six terabytes. Would you recommend putting all three databases on the same instance or breaking up each database onto its own instance?”

Tara Kizer: Are you virtualized? Because that might change my answer. If you’re virtualized, I’d probably split them up into three virtual machines.

Erik Darling: I’d probably split them up either way.

Brent Ozar: And why? What would be your guys’ concerns when you’re running databases of that size?

Erik Darling: Resource contention, locking. That would be a concern of mine with a database that size but not necessarily with keeping them on the same server. But yeah, resource contention mostly. One big giant table starts getting write in, buffer pool gets cleaned out from something else, so all of sudden just kind of fighting each other. Unless you have terabytes and terabytes of memory to make sure everything is cached when you need them, could be trouble.

Brent Ozar: I like it in terms of management for backups in CHECKDB too. Man, if you’re going to do a backup or a restore of a two terabyte database, that can be pretty tough, and other people on the same box will feel it at the same time you’re running backups or CHECKDBs. At this tier, it’s likely going to be Enterprise Edition. It’s likely going to be SQL Server Enterprise Edition so it’s going to be expensive when you say, “I want to break it out to multiple servers.” So start with that question around how long can we be down when this box goes down. If that answer is shorter than your time to restore, like if it would take you eight hours to restore all these databases, it’s time to start thinking about things like clustering or Always On High Availability.

 

Is there any good documentation on Always On?

Brent Ozar: Which leads to a next good question. “Is there any good documentation on Always On High Availability?” Before I let anybody else answer, I just want to give you gold star for putting a space between Always and On. Look at you. I still do Always On as all one word myself. Microsoft keeps changing this stuff. So you guys, where do you go to learn about Always On High Availability stuff?

Tara Kizer: When I was learning it back in 2012 and 2013, I actually learned by doing it. Just implementing it. Starting in a test environment, dev environment and promoting it through the different environments. Working with QA and development, making sure that everything worked properly. I’m not even sure that I referred to any documentation back then. Maybe for some quick help but I’m a learn-by-doing type person.

Brent Ozar: Yeah. How about you, Erik?

Erik Darling: Same boat. There is not a lot of great documentation, at least for free, unless you work here. So I would say learn by doing. If there’s anything you can’t figure out, ask a question specifically about that on dba.stackexchange.com or something until you get a really specific answer because a lot of the documentation is pretty high level. That’s probably the road I’d take.

Brent Ozar: I think to some extent that’s probably something we all have in common too. When you have to learn new features as they’re just coming out, there just isn’t good documentation yet. You have to start playing around with betas. When Always On Availability Groups first came out in 2012, those of us who wanted it had to start playing with it as soon as the alphas and betas were hitting. There wasn’t a good Books Online. You had to go and do. Often, I’m the kind of a guy who given the choice between reading about it and actually trying to do it, I’ll go copy/paste me some syntax and start breaking around to see what happens. Richie, I know you’ve been having to learn stuff like crazy as we’ve been developing new apps. When you go to learn, what’s your style of learning?

Richie Rump: So you know, I don’t always go learning things, but it turns out that we have a nice blog called brentozar.com. Is that too upfront? No?

Brent Ozar: No.

Richie Rump: Even before I came and worked here, I was constantly on—not only our blogsite but also I actually picked up a couple of our courses. I love our courses. I think we’re some of the best in the business. I tend to do a lot of video courses so the Pluralsights and even some of the YouTube stuff because those kind of get to the meat of the matter. I can kind of passive listen to a lot of those things. As I’m kind of doing something else, email or whatever, I can kind of grok what’s going on. If I need to go back, I can go back. But that kind of gets you—hey, all I want is enough to get going. When I get going, if I run into other problems, then I could do the Stack Overflow or whatever but I just need to get started. The stuff on YouTube, but mainly Pluralsight and our site, brentozar.com, has been really helpful.

Brent Ozar: If you’re a developer out there in the audience, Pluralsight is a great investment, just the rapidity of how of how fast they bring out new courses. It’s also interesting that between the crew of us, I think Richie is actually the only one who likes learning via videos or likes learning passively, which is hilarious for me to say because we run a training company that sells videos. I love doing them and I know that a lot of customers enjoy learning via videos. I don’t like watching learning videos at all. They make my skin crawl.

Erik Darling: Maybe what it is, is you don’t like other people’s videos.

Brent Ozar: Oh. You know what? That’s true.

Erik Darling: You found a formula for videos to make the videos the way you like videos.

Brent Ozar: You’re right, because I loved watching Doug’s. Doug’s videos I had such a good time with. He had such a good theatrical kind of quality to them. I can’t watch training videos where I don’t see the person. If I can’t see the person talking, I’m out. I need to see a human being that I can pay attention to.

Richie Rump: I think one of the things that make your videos so great, Brent, is that whole interaction. Yeah, the whole song and dance. I can now connect to someone as opposed to this voice kind of out there speaking to me, you know? Dropping knowledge. It’s a personal thing. It’s like, oh, you’re talking to me as opposed to wah wah wah.

Brent Ozar: Charlie Brown’s parents teach you SQL Server.

Erik Darling: A disembodied voice with just some quick typing in the background. This like ongoing monologue where you’re like, “I don’t understand what you’re saying. Like, stop.”

Brent Ozar: You can hear the guy flush the toilet. You’re like, wait a minute, did I just hear that?

Erik Darling: He’s blowing his nose.

Richie Rump: If I had a dollar every time I email Pluralsight, “The audio in this course is terrible,” I’d have one month full of free subscriptions.

 

Can SQL Server run on Windows Nano Server?

Brent Ozar: Next question we have is, “What are you guys’ thoughts about running SQL Server on Windows Nano Server?”

Tara Kizer: What is that? I’ve never heard of it.

Brent Ozar: Everybody’s faces tell the story there. Windows Nano Server is a really lightweight stripped down version of Windows. SQL Server does not work on Windows Nano Server. There’s no indication that it ever will. It might, I just have never run into an instance where I’ve said, “Oh, I really wish I had less Windows stuff on this SQL Server. It would have made my pains go away.”

 

How do I find a memory leak in SQL Server?

Brent Ozar: Next up, someone says, “How can I find a memory leak in SQL Server?” Wow.

Erik Darling: Attach a debugger.

Tara Kizer: Why do you suspect that SQL Server has a memory leak?

Brent Ozar: If you’re looking at SQL Server always using more and more memory, that’s what’s SQL Server does. If you google for a sysadmin’s guide to SQL Server memory, there’s a post I wrote a few years back explaining how SQL Server uses RAM.

 

Why aren’t SQL Server patches visible in Control Panel?

Brent Ozar: Next question. “SQL Server patches are not visible under control panel.” I’m assuming that you mean Windows update. “What do you guys do if you don’t see SQL Server updates available under Windows control panel? What’s the reason for that and how do you go fix it?”

Tara Kizer: I just download it from the Microsoft website, the ones that I’m interested in—service packs, cumulative updates. I like that they’re not available in Windows updates. I don’t want that stuff just pushed out to my environments.

Erik Darling: There was a recent charge—was it for 2016 where it started showing up?

Tara Kizer: Yeah, where it automatically pushes.

Erik Darling: Or it wasn’t service packs that started showing up, cumulative updates is what started showing up.

Tara Kizer: And doesn’t SSMS automatically get patched now?

Brent Ozar: Yes. At least it tells you you can go download one. I love that.

Erik Darling: An update is available. But then there’s no update dialog internally, it just brings you to a website. It’s like, “Okay, I guess I’m clicking on this.”

Richie Rump: “Update is available. More stuff is broken.”

Brent Ozar: That’s the really bad part is that you end up with like the 16.4, the most recent update, came out and broke up alter scripts. So they took it back down and two days later bring out another build. I’m so happy that they’re updating Management Studio. That’s wonderful.

Erik Darling: Yeah.

Brent Ozar: I don’t want to sound like I’m looking a gift horse in the mouth, I am just absolutely in love with how much resources SQL Server is getting these days. Speaking of which, Erik, you should announce that you got extra resources in SQL Server.

Erik Darling: I did.

Brent Ozar: What happened recently?

Erik Darling: I got my very own developer and he fixed a connect item of mine. Well, he addressed a connect item of mine. I complained that DBCC SHOW_STATISTICS is an old and clunky and kind of a horrible way to get information about your statistics histograms and whatnot. It looks like that got fixed somehow. I don’t know with what or anything. I don’t know if it’s a function or if it’s a DMV or any of that stuff but it’s out there on some developer’s desktop waiting to be released. Thank you whoever did that, or whoevers, maybe it took more than one of you. You know what? I bet I’m so special that Conor did it himself.

Brent Ozar: You know, I bet your name is in the source code. I bet it says, “This is done in order to make Erik Darling happy.”

Erik Darling: “Love, Conor. XOXO.”

Richie Rump: “Bleep you, Erik.”

Erik Darling: I spent my weekend…

Brent Ozar: So if any of you ever have things that you want to see changed in SQL Server, go to connect.microsoft.com. That’s where you can log bugs or requests for enhancements in SQL Server. They’ve been better at addressing these. I always used to joke that then they’ll ignore you the same way they ignore me. They’re actually fixing things. It’s fantastic. And things that aren’t even necessarily bugs, like Erik’s is not even a bug. It’s just something that would be cool if we add it.

Erik Darling: Yeah. One of the first things I ever heard to describe Connect was just like an idea graveyard.

Brent Ozar: That’s so true.

Erik Darling: [Inaudible 00:11:13] for ideas. They just go there to die.

Brent Ozar: And drive real slow.

Richie Rump: We should change ours to the Connect.

Brent Ozar: Yes.

Richie Rump: We should change it to Connect.

Brent Ozar: We’ll have to put in some kind of buffer so that it runs really slowly and the search sucks.

 

How can I move from SQL Server developer to DBA?

Brent Ozar: Next question. “I am a SQL Server programmer. How can I move from a SQL Server developer to become a DBA? I’ve been working in development for eight years and I want to become a DBA.” Richie, what would you do?

Richie Rump: Don’t do it. That’s why. I don’t want to be on call. What’s wrong with you people?

Brent Ozar: On call is a really good point. A really good point. I know when we go out and hire people, that’s one of the biggest things that I go tout. “How would you guys like to never be on call again?” Just all of a sudden you can hear the choir singing and the angels…

Erik Darling: My question would be why do you want to move from development to DBA. If you think it’s like a money thing or a career thing?

Tara Kizer: Usually money, but in order to get the more money, you have to be on call. So it’s a double-edged sword. If you can find a DBA job that doesn’t have an on-call rotation, then that’s like the best.

Richie Rump: Then you’re working for us. That’s how that works.

Brent Ozar: If you think that more money sounds attractive, look at what the more money is and think about what could you do if you worked part time after hours on a schedule that you controlled, especially since you’re a developer. If that means building projects, if there’s some kind of application that you want to go build, what could you do to monetize that in time windows that you could predict and control? Instead of being on call and never seeing your family again.

Richie Rump: If you’re serious about becoming a DBA, then you need to immerse yourself in DBA topics. Being here is a good way to do that. You need to learn about the hardware, probably take a huge Windows 800-page SQL book, probably the SQL Server Internals. I think that’s probably a good place to start. Start learning how everything works internally.

Erik Darling: Yeah, the Kalen Delaney books on internals are a great place to start if you want to become the all-knowing DBA swami and sit in the lotus position fixing corruption.

Brent Ozar: Because I have control of the PowerPoint, I’m actually going to dump in a PowerPoint from this week’s training classes. I’m teaching this week about performance tuning. One of the modules that we start out with—I’m going to fast forward to the slide that’s involved. When people say DBA, there’s a few different job roles that DBA means. Down the left-hand side, those of you who are listening to the podcast, I just want you to close your eyes. Ideally you’re not driving, but if you are driving, you know, it’s been a good life.

Erik Darling: YOLO.

Brent Ozar: Down the left-hand side you have the lifecycle of a database from designing tables, designing indexes, writing queries. From the bottom up, you have the systems administration part of database work: designing high availability and disaster recovery, installing SQL Server, troubleshooting outages. If you have eight years’ worth of development, start from the top of this chart and work down. Go from right—designing tables, writing queries—then work your way through monitoring performance. I don’t expect you to become a great production DBA on the far right-hand column. That’s kind of hard. But the job role that might interest you a lot is development DBA. All right. Let me throw the slide deck back on there.

Brent Ozar: Let’s see what our next question is.

Erik Darling: A lot of salaries flying around in that window.

Brent Ozar: Money, money, money. Which I think is generally true. I think a good database administrator can make pretty good money. But it’s the on call and it’s that there’s no school for it, that’s pretty tricky.

 

Should I do my backups on my primary or my secondary replica?

Brent Ozar: Shan says, “I’m struggling with the decision on whether or not I should do my backups on my primary replica or my secondary replica. If I’ve got Always On Availability Groups, where should I run my backups?” We could probably break this up into full backups and log backups. For this, I’m going to turn directly to Tara, our Always On Availability Groups expert.

Tara Kizer: I don’t know about expert. I prefer running them on the primary replica because I know that the primary is up-to-date with data. Even on a synchronous replica, it may not be up to date. So if you’re running backups there, your backups may not have up-to-date data. I don’t like that. When your RPN and RTO are really really low, I need to run them on the primary replica. I don’t necessarily see any reason to move them to another server. Backups should not be causing resource issues unless you have severe bottlenecks.

Erik Darling: If your bottlenecks are that bad you probably should not have an availability group on there.

Tara Kizer: Yeah.

Brent Ozar: You’re clearly using Enterprise Edition, which is an expensive edition in terms of SQL Server. If you were going to offload the backups to a secondary, that means you have to license it. So at $7000 US per core, generally the smallest Enterprise Edition box I see is eight cores, that’s $56,000 US of licensing that I would go put on the secondary. Maybe I’d want to go put a few thousand dollars’ worth of hardware on the primary first, rather than spending the money to license that secondary.

Erik Darling: RAM is cheap.

Brent Ozar: RAM is cheap.

 

Is there a checklist for upgrading from SQL Server 2008 to 2014?

Brent Ozar: Kahn says, “I think someone asked this question before.” All right, you lose. Next question. “I’d like to ask it again. We decided to upgrade from SQL Server 2008 to SQL Server 2014. Is there a checklist or a to-do list before the upgrade?” I would break this into two parts. Do you guys want to upgrade a SQL Server in-place and then is there any kind of checklist that you would look for as you go do these processes?

Tara Kizer: I’d like to start with the best practices. The BPA, Best Practices Analyzer, just so I can identify if there are any code issues. If your data access is through stored procedures, it can identify if there are any issues. The BPA is a great tool to help you identify problems for the things that it checks. It doesn’t check for everything. We found an issue where the raise error command, that this application had been using, was using old syntax and the BPA in 2014 did not alert us to that issue. So there are things that it’s not going to find but make sure you test things. I don’t know, is the BPA available for 2016 yet? I don’t know if I’ve seen it. I know it wasn’t available when it RTM’d.

Brent Ozar: I think 2014 is the most recent. But still really valuable. There’s also a technical upgrade guide if you search for SQL Server 2012 or 2014 technical upgrade guide. But warning: this is 200, 300 pages’ worth of stuff. It’s a monster. I like the BPA a whole lot better.

 

Do you prefer temp tables or table variables, and why?

Brent Ozar: How about an interesting question. Do you guys have any preferences between temp tables versus table variables, and why?

Erik Darling: Boy, do I. There’s a little bit more to that question about if you can create nonclustered indexes on table variables. The answer is yes, in 2014 plus. But, you still don’t get any statistics on those table variables. You still get crappy estimates. So in 2014 and up, it’s 100 rows. Prior to that it is one row when you select data out of them. When you modify a table variable, it’s all serialized. So when it comes to performance, I generally tend to go with temp tables because you can index them, they get statistics a little bit easier for SQL to make good decisions based on that. But if I’m just doing some kind of routine DBA chore and I don’t really care, I will use a table variable.

Tara Kizer: I had a system that had, this was on SQL Server 2005, and the stored procedure was using the table variable. There was only ever one row in that table. Always. A lot of times we’ll say table variables are okay since statistics are assuming one row anyway. We had severe performance issues with it, we were having to recompile all the time. A Microsoft engineer had this test whether or not a temp table would resolve it and it was night and day, the performance improvement that occurred here. So I don’t use table variables ever. Except for ad hoc in Management Studio when I’m just testing something out. Or like Eric said, it deviates [inaudible 00:19:32].

Richie Rump: I never use table variables. Ever.

Brent Ozar: I’ve never even seen an instance where table variables solved a problem for me. Every time I’ve ever run into them, they’ve caused problems for me.

Tara Kizer: I think there was an SSIS package issue that I encountered that I had to switch to a table variable. Yeah, I remember it now. It was a stored procedure that had a temp table and I don’t remember what component in SSIS was causing the issue but I researched it and found using the table variable worked around the issue and it drove me crazy. My rule is to not use table variables.

Erik Darling: That’s horrible. Ouch.

 

Do you have plans to come to India?

Brent Ozar: Another question. “Do you guys have any plans to do SQL Server seminars or training in India?” No, it’s kind of interesting how this works financially. There are big tax implications for doing work in India. I looked into having doing this at one point because we were going to have a client in India. The Indian government was going to make me open up a bank account and pre-pay taxes on things and I would only get them back a year later. I’m like, “Nah, you know what, between the travel and all of that, I’m good, thanks.”

Erik Darling: That’s wild.

Brent Ozar: South Africa was a similar kind of deal. It was really tricky in terms of taxes.

 

Will RCSI help alleviate my blocking problems?

Brent Ozar: Next question, “Is read committed snapshot isolation helpful if I want to get less locking? Can RCSI help eliminate my blocking issues?”

Tara Kizer: Then it says, “Readers do not block writers.” Is that what he’s seeing on his system, or…?

Brent Ozar: I think he’s kind of asking, “Is that how this works?”

Tara Kizer: Yes. You can get a big performance boost. I went on maternity leave—on the system I was just talking about, on SQL Server 2005 and my company ended up signing a very large customer and performance went into the [inaudible 00:21:18]. The database had tons and tons of customers in it. My manager who was also a SQL Server DBA not just a [inaudible 00:21:24] manager. He implemented RCSI and it significantly improved performance. Now it didn’t exactly resolve the issue but it allowed more concurrency, less blocking, and it allowed us to have more time to figure out what was happening which it was bad execution plans, parameter sniffing type stuff. But I came back and I was like, “What is this RCSI?” I had to learn about it because I was the primary DBA on the system and he’s an Oracle DBA and a SQL Server DBA. He was saying SQL Server could finally compete with Oracle once Microsoft added RCSI to SQL Server. Oracle has had it for a long time.

Erik Darling: It’s the default in Oracle. Not exactly, but same principle.

Tara Kizer: Exactly.

Richie Rump: When I do new databases it’s always RCSI. That’s the default for me.

Tara Kizer: I started putting it on the model database that way if anybody created a database it was already automatically RSCI.

Erik Darling: There are potential gotchas though. You can run into raised conditions with your code. If you haven’t tested things, if you do sort of queuing transactions. For that reason, some people choose snapshot isolation instead, which does the same thing as RSCI but you have to request it at the query level. And snapshot can also be used for modifications. So if you’re problem is writers blocking writers then you can do it there too if you’re crazy and have lots of time to…

Brent Ozar: I think there’s only two super fast buttons in SQL Server that if you push them in the right circumstances it just makes an unbelievable difference in the SQL Server. RSCI is one of those, my other big one there is forced parameterization. In both cases, neither of these solve everything. But in the exact situation where you’re facing those problems, you just push this button and the angels start singing.

 

Do you recommend using RAID with solid state drives?

Brent Ozar: Next question. “Would you guys recommend storing SQL Server data files on solid state drives that are configured in RAID?” Yes, absolutely. If you’re going to use solid state drives, really any drives, same thing with magnetic, you want some redundancy there because drives fail, all of them. Solid state drives, magnetic, you name it.

Erik Darling: RAID 0 was not a good choice with anything with SQL Server. There’s some best practice stuff around RAID 10 for the right workload stuff like tempdb and log files in RAID 5 for the read workload stuff like the data files but generally you need some sort of RAID on there. If you’re buying a modern SAN, a lot of them have their own under-the-covers RAID configuration anyway. I know Nimble and some other ones have their own black magic RAID thing going on so you might not even have to worry about it.

 

How can I become an MVP?

Brent Ozar: And the last question we’ll take is for Tara. “Tara, how can I be an MVP like you?”

Tara Kizer: Well a few years ago it wasn’t exactly hard, it just took a lot of your time answering questions on the internet and getting your post count high to be noticeable to Microsoft or someone recommending you to Microsoft to be an MVP. These days though it’s a lot harder, a lot of people are losing their MVP. They’re not writing blogs. Maybe they’re still presenting every now and then at SQL Saturday but it’s not enough anymore. I don’t know if answering questions on forums is enough anymore. Microsoft is changing the program and they want more out of you than just answering questions.

Erik Darling: And it all has to be on Azure.

Tara Kizer: They would prefer people to start talking about Azure, be an evangelist for it.

Richie Rump: Those are fighting words. I’ve had to deal with Azure all week.

Brent Ozar: Yes. I would also throw in there that Microsoft runs open MVP days. They’re regional in large cities so Chicago, Seattle. If you can try to put words in with Microsoft people you know, if you know any Microsoft employees, then just ask. Is there an MVP Days event coming up. At those events you get to meet the Microsoft employees, meet other MVPs and ask them questions directly and it puts you on their radar. That may help your edge in becoming an MVP. I would also ask just what you want to be an MVP for. What is it that’s important to you. Almost all the MVPs that I know, the MVP is kind of a side effect from things that they were doing anyway. That they love helping the community which is very fulfilling in and of itself. So if you love doing that kind of thing, you’ll find becoming an MVP much easier.

Richie Rump: Yes, I would absolutely say don’t try to be an MVP. Try to help the community, try to get involved in a community, try to run a user group, try to start a podcast, start sharing your information, start a blog. Once you start doing that, the MVP things will start coming along as well as some other good things that come out of sharing your knowledge and information.

Brent Ozar: It’s amazing to watch every time, so October 1st was the last renewal date for MVPs or the last round of dates. It was so funny to see so many people on Twitter go, “I can’t believe I got an MVP. I’m so not worthy. This is such an honor,” and whatnot. Think about that mindset, maybe there’s a reason why these people are like, “I just do this for fun and it turns out I’m recognized for it.” That’s amazing.

Richie Rump: Yes, I’ve already made the decision that if I was ever made an MVP that nobody’s going to know about it. I’m not going to tweet it, I’m not going to do anything, it’s just going to be out there.

Brent Ozar: So you could be an MVP right now and we wouldn’t know.

Richie Rump: You wouldn’t know.

Brent Ozar: I’m going to go right now and look up in the directory.

Erik Darling: You’re an MVP to me, Richie.

Richie Rump: No, you the real MVP, Erik.

Erik Darling: No, man.

Brent Ozar: You’re an MVP to me. Minimal viable product. All right so thanks everybody for hanging out with us this week and we will see you next week on Office Hours.

Erik Darling: Adios everybody.