Blog

This week, Brent, Richie, Doug, and Tara discuss Growing databases, most useful SQL certifications, replication issues, group discounts, backup software 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.

Office Hours Webcast – 2016-06-22

Brent Ozar: All right, we might as well get started here. Let’s see, we’ve got all kinds of questions pouring in today. We’ve got 75 folks in here, let’s go see what they’re asking questions about. Going through… oh, Scott asks an interesting question. Scott says, “How would you handle a new manager that insists his databases should not grow?”

Doug Lane: Insist his business should not grow.

Brent Ozar: I like it.

Richie Rump: Monster.com?

Brent Ozar: Yeah, you’re never allowed to sell anything. You’re never allowed to add new data. What I would do is make sure that he understands that update statements don’t make the database grow. So if he updates your salary, it doesn’t take additional space in the database, it just changes an existing number.

Tara Kizer: I wonder if there’s more to the question though. Is the manager SQL Server savvy and is saying preallocate it big enough that it never has to auto grow? He is probably not a good manager though, doesn’t understand SQL Server technology or databases in general.

Doug Lane: We’re quick to judge in these parts.

Brent Ozar: Scott says, “No, he is not technical.”

Tara Kizer: And he’s saying that SQL databases should not grow. That is just odd.

Richie Rump: If he’s not technical, why is he giving technical advice? That doesn’t make any sense.

Brent Ozar: It’s probably a budgeting thing. He’s like, “Look, I’ve got to keep my budgets the same number. It’s very important.” Scott says, “He is very used to monitoring the database.” What you do is, I had a monitor—I shouldn’t say—okay, we’ll I’ve started down the road so I just might as well. So I had a manager once, not in my department but in another department, who was asking anal things like that. Like, “Oh, I want to make sure CPU never goes above five percent.” So what we did was we hooked up his monitoring tool to point at a server no one used and we just called it SQL whatever and told him that’s where his stuff lived. He was totally happy. He was completely happy. Thought we were amazing. Yep, that’s my job.

Brent Ozar: All right, so Gordon asks a related question. Gordon says, “I’m not really sure what to set autogrowth to on my very large databases.” Usually when people say VLDB they mean like a terabyte or above. He says, “Yes, I should be growing manually during a suitable maintenance window, but I also have an autogrowth value set just as well in case. Is one gig a good number or what should I do for a one terabyte database?”

Tara Kizer: I used one gigabyte on the larger databases, sometimes even maybe a little bit bigger. As long as you have the instant file initialization for the data files so they could be zeroed-out so that the growth isn’t a slow growth. Log files may be a little bit smaller. I did a lot of times [inaudible 00:02:25]. Sometimes I did one gigabyte on larger databases where I knew the patterns and it was going to use a larger file for certain things. But I tried to preallocate those.

Doug Lane: And IFI is something that comes on by default if you want it to in SQL 2016.

Tara Kizer: Oh really? I didn’t know that.

Brent Ozar:          There’s a checkbox in the install.

Doug Lane: There’s a little checkbox that says, “I want IFI to work with this installation.”

Brent Ozar: Microsoft calls these things “delighters.” They’re trying to add delighters into the product. I’m like, “I am delighted! That’s actually wonderful.”

Richie Rump: It’s just faster.

Brent Ozar: It is faster. It’s just faster. And they’re right. I like them.

Doug Lane: It works.

Brent Ozar: I have an interesting question from Wes. Wes asks, “What are the most useful SQL Server certifications?” So we’ll go through and ask these folks for their opinion. Richie, we’ll get started with you because you’re on the left on my monitor. What do you think the most useful SQL Server certifications are?

Richie Rump: The one you have. That’s it. That’s the only useful one.

Brent Ozar: The A+?

Richie Rump: Yeah. Certified Scrum Master. No, the MCM, right? I mean that’s by far the most useful one you have. I mean as soon as you get it, you’re recognized as an expert anywhere.

Brent Ozar: You say that but nobody still believes that I actually passed that test, for rightful reasons. I wrote a check, it was a really large check. Then I brought another bag of $20s and I gave that to the instructor and off we went. Tara, how about you?

Tara Kizer: I’m against SQL Server certifications. A while ago they had all these practice tests online and I am a terrible test taker. I felt like at the time I was really good at what I did, you know, SQL Server, DBA for a long time, and I could not pass the test. So I feel like it’s for people that don’t have experience that are just trying to get their foot in the door. I already had experience, I don’t know that certifications are required at any job when you have as many years of experience as I do but I could not pass the test. I also wasn’t willing to study for these tests. Some of the stuff is just useless information I didn’t need to know. So why add that stuff to my brain?

Brent Ozar: Doug, how about you?

Doug Lane: It depends on how you define useful because is it useful in the sense that it will get you a job or is it useful in the sense that it will make you better at your job? Certifications will tell you what you don’t know as you test for them but apart from their value as actually holding the certification, there’s very little value to it. It’s the kind of thing where you decide if you want it on your resume or not. In most cases, it won’t matter. Again, apart from exposing blind spots in what Microsoft thinks you should know about SQL Server, it’s really not going to help you that much.

Brent Ozar: It does teach you a lot—go ahead.

Richie Rump: As a former hiring manager of both developers and data folks, I never looked at certifications at all. It didn’t help you; it didn’t hurt you. It just never came into play because it’s just a test. It’s not exactly how you work, like Tara said, it’s just a test.

Tara Kizer: I had one job that if you did do the certifications that it was something to put on your review, that this was something that you worked towards. So it was a review cycle thing, a possible extra bonus or promotion, but it was just a bullet point on the review. You had all the other stuff on your review as well.

Brent Ozar: For the record, we don’t bonus our employees that way. If you want to take a test, that’s cool. We’ll pay for it. We also pay for passes or fails, it doesn’t matter because I know from taking them too, I’m like, I walk in, I look at the test, I’m like, “They want you to know what? XML, what?”

Tara Kizer: And there will be more than one right answer. They want the most perfect answer and it’s like, well, there’s three of them here that could be the right answer.

Brent Ozar: Yeah.

Richie Rump: PMP was crazy like that. I mean it was, “Oh look, they’re all right. But what’s righter-er-er?”

Brent Ozar: PMP, Project Management Professional?

Richie Rump: Professional, yep.

Brent Ozar: There we go.

Brent Ozar: Nate Johnson says, “It may be a waste of 15 minutes of company time but I do enjoy these pregame antics.” For those of you who just listen to the podcast, you miss out on when you come in and join live we just shoot the poop—there goes a bunch of old jokes but I’m just going to keep right on, I’m not going down there.

Brent Ozar: Tishal asks, “Is it possible to see the size of the plan cache using T-SQL?” The answer is yes and none of us know how by memory. There is a DMV for it, if you Google for that, there’s a DMV for it. In the show notes, we’ll go find that and track it down for you.

Brent Ozar: David asks, “Replication question.” And then he never types it in. Oh, no, he does later. “We us replication extensively. In this scenario…” What do you mean your scenario? Is this like a game show? Are you trying to test us? “We have a bunch of reference tables that hardly ever change replicated out to dozens of locations. Should we use transactional replication or snapshot replication or an ETL process and just refreshing them once a day would be fine?”

Doug Lane: What are you the most comfortable managing?

Brent Ozar: Oh, look at that Doug. Go on, elaborate.

Richie Rump: Welcome back.

Doug Lane: If you feel really good about setting up some sort of SSIS package to do this, then by all means do and get away from replication. But this is the kind of thing where it really comes down to a comfort level. Replication will never be your best friend. It’s just too finicky and complicated and aggravating to work with. But it can get the job done.

Brent Ozar: When you say finicky and complicated and aggravating to work with, that describes most of my best friends so I’m not sure what you mean by… yeah, Richie is pointing at himself.

Tara Kizer: I had a scenario like this for reference tables. We actually did not replicate them. The only time that these tables changed was during deployment. So if we needed them on the subscriber database, we just deployed to both the publisher and the subscriber for those tables. That way we didn’t have to add them to the publication. There’s not really any overhead as far as transactional or snapshot except when it has changes coming through. But why have them in there if they hardly ever change and it’s part of a deployment process?

Brent Ozar: James asks, “What’s the best practice for setting minimum server memory? There’s a lot of guides out there on how you set max server memory but what should I set min server memory to?”

Tara Kizer: We took the max server memory, best practice, four gigabytes or ten percent whichever is greater, then we divided it by two. That was our min server memory. That was our standard across all servers.
Brent Ozar: I like that. I think in our setup guide it doesn’t even give advice on it because we never—if that’s your biggest problem, you’re in really good shape. I love that you’re asking this question because that’s a really good, detail-oriented question.

Tara Kizer: We had the standard because we were mostly a clustered environment. We had, I don’t even know how many clusters, maybe 100 clusters or so, a lot of them were active active, not a lot of them, but some of them were active active and you want to make sure that when the failover occurs and you’re running on one node that the SQL instances—the one that has to failover can get memory. We would set max down also in the active active environment.

Doug Lane: It also kind of depends on how much you’re piling on that server because if it’s your Swiss Army knife server, you’re probably going to have trouble if you’re trying to run Exchange and other stuff on it, but you know [inaudible 00:09:37]. You’ve got all the BI stack running on it too then you want to make sure that under no circumstances can other stuff steal away from SQL Server to the point where your database engine is actually beginning to starve a little bit. So keep in mind whatever else is on that box. If you really just have a dedicated SQL Server database engine box, then yeah, it’s not going to be as big of deal because it will take whatever it needs and there really won’t be competition for that memory in terms of like it getting stolen away.

Brent Ozar: Mandy asks, “We’ve got SQL Server 2014 and our tempdb files are on local solid-state drives. Recently we’re starting to see higher and higher IO waits on those tempdb files, upwards of 800 milliseconds. I’m new to solid-state, is this normal or is this indication of a problem?” That’s a good question. My guess is, depending on how old the solid-state drives are, their write speed actually degrades over time. It can get worse over time. The other thing that’s tricky is depending on how many files you have, if you spider out tempdb to say one file per core and you’ve got 24 cores, solid-state may not be able to handle that workload as well. So generally speaking, we aim for either four or eight tempdb files when we first configure a server. This is one of those instances where more can actually harm you rather than having fewer but I would just check to see. You can run CrystalDiskMark against those solid-state drives and see if write speed has degraded since they were new. It’s certainly not normal though.

Brent Ozar: Wes asks, “Are any of you speaking at the PASS Summit?” Well, all of us will be speaking, we’re all going to be standing around the hallway talking to all of our friends. Are we going to be presenting? That we don’t know yet. That announcement comes out today. So we’ll find out later today. I keep looking over at Twitter to see whether or not it’s come out and it hasn’t come out. So as soon as it comes out, we’ll say something.

Brent Ozar: Wes says—and I have no idea what this is in reference to—“Use Walmart as a precedent.”

Richie Rump: Enough said. I don’t think we need to say anything more about that.

Doug Lane: For the “adios pantalones” shirt.

Brent Ozar: That’s probably true.

Brent Ozar: Next up, Tim says, “I’m fighting for only using stored procs. I don’t want to use inline SQL even for simple queries. My developers are fighting against this and they want to use things like Entity Framework. Am I wrong for pushing hard for only using stored procs?”

Tara Kizer: I have a lot of experience on this topic. I was very, very pro stored procedures for the longest, longest time. Slowly, as developers changed, they wanted to use prepared statements, parameterized queries from the applications, and we didn’t want to stop them from the rapid development that they were doing so we did allow that. Once we realized that the performance was the same between stored procedures and prepared statements, parameterized queries, it became okay from a performance standpoint. However, from a security standpoint, you’re having to give access to the tables rather than just to the stored procedures. So that was just something that we had to think about. But as far as Entity Framework goes, I know Richie is very pro Entity Framework. Entity Framework, and what’s the other one? NHibernate. There are some bad things that it does that can really, really harm performance. So it’s something that you have to watch out for. They use nvarchar as their datatype for string variables and if your database is using varchar, you’re going to have a table scan on those when you do a comparison, the where clause, and you’ll be able to tell in the execution plan. It will say, “An implicit conversion occurred.” You’ll see that it said nvarchar and you’ll be like, “Whoa, why?” Your table is using varchar. It’s because of the application specifies nvarchar. Something that you can override, but if you’re not overriding it, this is what they’re going to do.

Richie Rump: So this just in, that is not a bug. That is a problem with the developer’s code. They didn’t specify that the column was a varchar so because .NET uses Unicode as their string type, it automatically assumes everything is nvarchar. So there’s a way that we could go in and say, “Hey, this column is nvarchar.” If you don’t do that, that will cause the implicit conversions. That’s only if you’re using code first. If you’re using the designer, the designer does the right thing and doesn’t put the N in front of it so it doesn’t put it as nvarchar so you get that implicit conversion. So that’s only for code first and if the developers aren’t really doing the right things when they’re doing their mappings in code. And just because I have Julie Lerman’s phone number doesn’t mean that I’m pro Entity Framework.

Tara Kizer: You’re pro because you speak about it. You present on the topic.

Richie Rump: Oh, okay. So if you go to the pre-con, you’ll hear me talk more about it but it’s more of a heavy, it’s a balanced way—we’re not going to be able to tell developers not to use. Microsoft is saying to use it. So if you’re saying that, then you’re saying, “Don’t do what Microsoft says,” and that’s a much bigger uphill battle than you probably want to face as a DBA. So the general rule of thumb is usually for most things, it’s okay. But for complex things, if it’s going to be complex in the SQL, it’s going to be complex in the link, and now there’s two hops it’s got to go through to figure out what the actual plan is. One it’s got to change that link into a SQL statement and it’s got to change that SQL statement into a plan. That’s probably going to be 50 pages long, which nobody ever wants. So at that point, cut your losses, then do a stored procedure and everybody is okay. But there’s a big difference between when we have to write SQL as developers, when we’re typically not very good at it, as opposed to, “Oh here, let me just do context.tablename.get” and then it just does it for us. So there’s a speed issue here to development and there’s usually a lot more developers than there are of you. So unless you want to stay up all night writing SQL statements…

Brent Ozar: Is that a threat?

Richie Rump: Yeah. You guys get paid more than us so I don’t understand what that is either.

Brent Ozar: John says, “I just saw your announcement about pre-con training in Seattle. Do you guys offer group discounts?” We do actually. If you’re going to get five or more seats, shoot us an email at help@brentozar.com or just go to brentozar.com and click on contact up at the top. Then you can contact us, tell us which class you want to go to and we can for five or more seats we give group discounts there.

Brent Ozar: Gordon asks, “If I’ve got an Azure VM that replicates data, I want to send it down to an on-premises database. It’s a VM, it’s not using Azure SQL database, what are my HA and DR options?”

Tara Kizer: That’s unusual to go in that direction. I don’t have an answer but I’ve never heard of anyone doing that.

Brent Ozar: Should be able to use replication, log shipping, AlwaysOn Availability Groups, anything that you can use on-premises you can use up in Azure VMs, I’ve got to be really careful when I say that. The hard part of course is getting the VPN connection between on-premises and your on-site stuff. That’s where things get to be a bit of a pain in the rear.

Brent Ozar: Jennifer asks, “Is the MCM still available?” No. They broke the mold after I got mine, thank goodness.

Tara Kizer: Yeah, 2008 R2 was the last version right? I mean it was the only version really. It’s been a while.

Brent Ozar: Whoo hoo.

Brent Ozar: Kyle Johnson asks—I try not to use last names but Kyle, that’s a generic enough last name and your question is so cool, it doesn’t matter. It’s totally okay. You shouldn’t be ashamed of this question. It’s a good question. I’m not just saying that to get you a gold star on the fridge. He says, “I was on a webinar yesterday where you covered sp_BlitzIndex. Are you aware of any scripts or [inaudible 00:17:06] columnstore indexes? Or is there anything I would look at in order to learn whether or not columnstore is a good fit for me?” Everyone is silent. So there’s your answer. The closest I would go to is Nikoport, N-I-K-O-P-O-R-T.com, Niko Neugebauer he’s from Portugal, he’s got a really hard to pronounce last name—has lots of information about columnstore indexes. He’s like Mr. Columnstore.

Brent Ozar: Tim says, “I’ve just inherited a data warehouse project.” Well, you have really crappy relatives, Tim. “With five weekly updated data marts. The largest table is closest to 300 million rows and it’s approaching a terabyte. My loads are taking longer than usual. What’s the best way to diagnose performance tuning on stuff like this?” So, data warehouse that’s got a bunch of data marts, tables approaching a terabyte, and my loads are taking longer. Where should I look for performance tuning?”

Tara Kizer: What is it waiting on?

Brent Ozar: What is it waiting on? And how do you find that out?

Tara Kizer: You run a query, a saved script. I don’t have the DMVs memorized. I mean, you could run sp_WhoIsActive I guess. I assume that would work in this environment and while it’s running, see what it’s waiting on. You know, is it a disk issue? Something else?

Brent Ozar: My favorite of course because I’m me is sp_BlitzFirst. sp_BlitzFirst, if you run it with the SinceStartup equals one parameter, SinceStartup equals one will tell you what your waits stats have been since startup.

Richie Rump: The script formerly known as sp_BlitzFirst.

Brent Ozar: Yeah, we’re running a contest now to figure out a new name for it because we just open sourced a whole bunch of our scripts and I don’t want to have it called sp_BlitzFirst anymore because the strangers are going to start checking in code and I don’t want that answer to be reflecting on me. “I asked Brent and it said I was a moron.”

Richie Rump: That’s already in there.

Brent Ozar: Yeah, it’s in right at the end.

Brent Ozar: Ankit asks, “How do I troubleshoot SQL recompilations increasing on SQL Server?” So there’s a Perfmon counter recompilations a second and recompilations is increasing. What should he go look at it?

Doug Lane: Did anyone recently add option recompile?

Brent Ozar: Oh, okay. I like that.

Doug Lane: Like someone may have tried to solve a parameter sniffing problem on a frequently run query. Just one idea.

Brent Ozar: Some yo-yo could be running update stats continuously. You can capture a profiler trace—this is horrible—you could capture a profiler trace and capture compilation events and that will tell you which SQL is doing it. How else would I troubleshoot that? Oh, no, you know I’m surprised Tara didn’t ask the question—it’s the question that we usually ask, “What’s the problem that you’re trying to solve? What led you to recompilations a second as being an issue that you want to track down?” That’s an interesting thing.

Tara Kizer: I wonder if it’s due to they say that your recompilations should be like 10 percent or under of your compilations. I wonder if that’s something that they’re monitoring, maybe that’s increased. Or maybe it’s a counter that they’re already tracking and the number has gone up.

Brent Ozar: Yeah, I don’t think any of us—have any of you guys run into a situation where that was the problem, recompilations a second?

Tara Kizer: That’s a Perfmon counter that I always pull up along with the compilations and I just take a quick peek at it and then I delete those two counters from the screen. A very, very quick peek.

Brent Ozar: Yes, yeah.

Tara Kizer: Of course, if recompilations are occurring more frequently, you probably have more CPU hits. So if you see that your CPU has risen, maybe it is something to look into.

Brent Ozar: If you put a gun to my head and said, “Make up a problem where recompilations a second is the big issue.” Like if I had a table that was continuously being truncated and then repopulated, truncated, and repopulated where the stats are changing fast enough that was causing compilations, even then I don’t think it’s going to be too many recompilations a second. So that’s a really good question.

Brent Ozar: Tim asks a great question. This isn’t the other Tim, who also asked a great question as well, it’s a different Tim. “Is performance tuning approached differently from a transactional system versus an analytical system? When you approach an online system versus a reporting system do you do troubleshooting for performance any differently?”

Tara Kizer: I don’t as far as reporting but I’ve never really supported a true OLAP environment.

Doug Lane: Yeah. There are so many options that don’t apply to regular OLTP environment that do apply to OLAP, specifically talking about cubes because they’re all different kinds of models that you can do. You can do hybrid. You can do ROLAP OLAP. All different ways of kind of choosing how much of that data you want to compile ahead of time. So the troubleshooting process would be very different if you’re talking about SSAS cubes for example. If you’re talking about the source data, usually people don’t care about the underlying data because that ends up in some other final format, like a cube, so I mean I guess if I were to look at a database that was just straight—what would that be, it’s been a while—ROLAP I think, where you get it right out of the database. Then I suppose I would use some of the same troubleshooting steps, like looking at wait stats, looking at long-running queries, and so on and so forth. But if you’re talking about troubleshooting a cube, that’s a whole different bag from OLTP.

Brent Ozar: Adam asks—not that your question—I just said “Adam asks.” It’s not that your question is bad. I didn’t say it was a good question. It’s still a good question. I can’t say “good question” every time or else people won’t take me seriously. “How would you approach doing replication in AGs?” So if I have the publisher in an Availability Group, do I have to reconfigure replication again when I failover to my DR replica?”

Tara Kizer: So the distributor isn’t supported as far as AG. So if the DR’s environment has its own distributor, the answer is yes you do. Hopefully you’re scripted and hopefully when you have done a failover to DR it wasn’t an automatic event. Usually because DR is so far apart you can’t have automatic failovers occur. So if it was a manual DR, hopefully you were in downtime window, all the applications were down. You made sure that there was no data that was left behind, you know, that hadn’t been fully sent to the subscriber. If that’s the case, you just need to run your scripts to start up replication again right where you left off. You don’t have to reinitialize. This is a topic that I’ve done quite a bit, failover to DR, using replication AGs, pretty much every technology.

Brent Ozar: And we have a bad question from Nate. I’m not going to lie, this question is bad, Nate. You shouldn’t feel bad, but it’s a bad question. He says, “Is a self-referencing linked server as slow as a real linked server? And is it generally a bad idea or not?” How’s that work guys?

Tara Kizer: What problem are you trying to solve? Why are you self-referencing itself?

Brent Ozar: I’ve seen people do this and it wasn’t a good idea then either but I’m just going to repeat what they did. So they had linked servers inside their code so that they could have the same code whenever they moved it from server to server. Then sometimes they would have reporting servers where they changed the linked server to point somewhere else. They thought that somehow doing linked server queries was going to abstract that away. Like they could move some of the tables at some point to another server. So for those of you who are only listening to the audio version and not seeing our faces, none of our faces are happy at this point. We’re all sad. Sadly, it is as slow as a regular “linked server.” SQL Server doesn’t know that that’s a remote server.

Brent Ozar: Let’s see here, what’s up next? All kinds of questions here. Nate says, the context: he has a replicated view that points at two servers. So what you should do because this is kind of a multi-paragraph thing. He’s got a few things inside there. Post this on dba.stackexchange.com. Post in as much details as you can and talk about what the problem is you’re trying to solve. Generally when we talk about doing reporting servers, we’d rather scale up than scale out to multiple servers. You kind of see why here. Managing multiples is kind of a pain in the rear.

Tara Kizer: I think that the answer should be just don’t use linked servers though. If you need to be able to contact another server, do that through the application, not within SQL Server. Programming languages can handle this, joining two different datasets together.

Brent Ozar: Yeah. Conor Cunningham has a great talk at SQLBits when he talks about the difficulties of distributed queries. It’s pretty bad.

Brent Ozar: Nate also asks—Nate redeems himself by asking another question. Nate says, “Finally, a backup software question. What do you guys like/prefer in terms of backup software? There’s a bunch of different versions out there. Whose tools should I buy? Are they all compatible with Ola scripts?” I think Ola scripts work with everything at this point, like Idera, Redgate, and LiteSpeed. In terms of like who we prefer, we’re kind of vendor agnostic since we don’t have to manage anybody’s backup software. But just in terms of experience, we’ll go through and ask. Richie, have you ever used third party backup software and how was your experience and which ones were they?”

Richie Rump: I’ve never used backup software.

Brent Ozar: All right, Richie doesn’t use backup. He just puts things in GitHub and lets the rest of the world backup his source code.

Richie Rump: I let the DBAs handle that.

Brent Ozar: Tara, how about you?

Tara Kizer: I have a long answer. I’ve been using SQL Server a long time and backup compression didn’t exist in older versions, so yes, we started off with Quest LiteSpeed, worked really, really well. It was fairly expensive. We wanted to get the Redgate’s SQL Toolbelt and they gave us a deal and we were able to get the backup software—we were able to completely replace all of our LiteSpeed licenses, which we had already paid for, it’s not like we got a refund from all these and we put Redgate out there instead. The reason why we did that is because all new incoming servers were going to use the Redgate software instead. So it made sense to have one tool rather than multiples. But both of them we did testing, we did a ton of testing on them, they pretty much produced the same compression ratio, the same file size, the same restore time. I mean, absolutely everything was—the difference was so minor. One was just cheaper than the other.

Brent Ozar: Yeah, everything is negotiable. Back like ten years ago, there might have been differences, today, not so much. Doug, how about you? Have you used any third party backup tools?

Doug Lane: I yield my time to the consultant from California.

Brent Ozar: Nice. I’ve used all of them as well. They’re all good. Anything is better than trying to roll your own.

Tara Kizer: And, yes, I mean they definitely are compatible with Ola, especially the two that he’s listed. I know you said that they probably are, these two specifically are.

Brent Ozar: Yeah, absolutely. Well that wraps up our time for today. Thanks everybody for coming and hanging out with us. We will see you guys next week. Adios, everybody.

Tara Kizer: Bye.

Doug Lane: Bye-bye.

This week, Brent, Angie, and Tara talk through your questions about monitoring tools, transactional replication, configuration management, source control software suggestions and much 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.

Office Hours Webcast – 2016-06-15

Why did some of my drives disappear?

Angie Rudduck: Bruno says he has an instance with several DBs and suddenly a couple of them became unavailable and the physical disks where the data log files disappeared. No Windows events. How can he audit what happened at SQL Server level?

Tara Kizer: It’s unlikely a SQL Server problem. I’ve encountered this many, many, many times. You’ve got to talk to your sysadmins or you’ve got to talk to your SAN admins, server admins, they’ve got to take a look. Something happened. It’s almost certainly not a SQL Server issue.

Angie Rudduck: Yeah, if your drives disappeared, it’s probably not SQL Server’s fault.

Brent Ozar: When the drives disappear, I don’t know that you would see a Windows event unless there’s some kind of error message that pops up from the SAN or whatever. I’m assuming it’s a SAN.

Tara Kizer: You would eventually see a SQL Server error once it finally has to write to disk. I mean, it’s going to be a little bit before that happens since SQL Server does everything in memory. So it’s not going to know about it for a while. But the checkpoint, any kind of writing to disk. It’s finally going to start throwing errors and those should be posted in the event log.

Brent Ozar: Backups.

Tara Kizer: Yeah. We’ve encountered weird bugs on like Cisco hardware that caused it and just various weird things. But it has happened numerous times, across many servers, many different hardware platforms, different SANs. It just happens.

Brent Ozar: I think it’s usually it’s just human error. I mean like Robert Davis, a fellow Microsoft Certified Master, just ran a blog post on how he’s like, “Even I screw up.” Drops the wrong one and all these database transaction logs disappear.
Angie Rudduck: Oh yeah, I’ve dropped two databases from prod before. Two separate occasions I have dropped a database from prod. Thankfully both were quick enough recovery. The second one turned out not really used, so that was okay.

Brent Ozar: It’s a matter of time. That’s how you become senior too, you have to have those experiences.

Angie Rudduck: I was just going to say I feel like something that, I’ve met people having only been a DBA for three years, I run into people who have been DBAs for ten years and I know things they don’t only because it’s things I’ve experienced that they never did because maybe they were a smaller shop and I worked in bigger places. Just all about what experience you had.

Brent Ozar: Yeah, everything that involves replication. Tara knows everything.

Angie Rudduck: Somebody already, “Since Tara is here, blah blah replication” question.

Brent Ozar: Here we go.

What’s the best SQL Server monitoring tool to use?

Angie Rudduck: Constantino—I butchered your name, I’m sorry—he has a long-winded, easy question. Basically they’re trying to look for a good monitoring tool for production servers. They’re looking specifically for health monitoring that can alert them when something happens or is going to happen. So don’t get Ignite, it’s not in your list, but don’t get Ignite. He’s looking for a full-rounded solution. They’ve tested a bunch: Spotlight, Foglight, Redgate, SQL Sentry, Idera. Do we have any favorites that we would point them to for health monitoring and for SQL alerting?

Tara Kizer: SQL Sentry provides both with the performance advisor and then the event manager tools I believe. Both of those together can give you everything you need. At previous jobs, we used SQL Sentry at my last job and previous jobs we used Spotlight. I wasn’t a big fan of Spotlight. It was great for looking at certain things. I did set up some availability group alerts but it wasn’t as comprehensive as I wanted. We also had Foglight which I think is now called Performance Analysis. Then we had SCOM, so Microsoft’s System Center Operations Manager with the SQL Server management pack. But SQL Sentry, their two big tools did replace SCOM and the performance analysis tool for us at the time. But it’s pretty expensive. SCOM plus another tool is not as expensive. But SCOM requires almost a fulltime monitoring person that knows how to use it. It’s very complicated.

Angie Rudduck: Yeah.

Brent Ozar: I’ve used all of those too. I’m fine with all of them. It comes down to personal preference.

Tara Kizer: Yeah.

Angie Rudduck: Did he mention Dell’s? That’s Spotlight, right? Dell is Spotlight.

Tara Kizer: Yeah, Spotlight and Foglight. Foglight is the name that we used to call them. I think it’s Performance Analysis, I think. People may still refer to it as Foglight.

Brent Ozar: They renamed it again.

Tara Kizer: Oh they did? They went back to Spotlight?

Brent Ozar: Yes.

Tara Kizer: Oh, I didn’t know that. They were probably sick of people calling it Foglight and they’re like well we should just call it that too.

Brent Ozar: A friend of mine calls them FogSpot. He’s like, “I don’t know which one it is. I’ll just call it FogSpot.”

Tara Kizer: Yeah, one of them.

What should I do about the replication error “undelivered commands”?

Angie Rudduck: All right. So we will move along. Let’s see—not that one—we will go to Nate, with the transactional replication. They have a setup where often they get stalled transactions from the “alert for undelivered commands job.” Any thoughts?

Tara Kizer: Stalled transactions. I’d probably need to see the full error. So undelivered, so it probably means that it’s sitting at the distributor and it hasn’t been sent to the subscriber. I would take a look at the throughput. Take a look at the distributor and the subscriber to see if there’s any kind of CPU issue, possibly it’s just a lot of data got pushed through. Yeah, I don’t know for undelivered commands. Usually it’s a bottleneck on the publisher with reading the transaction log. Maybe there’s just a lot of stuff in there, you’re not backing it up often enough so the amount of data that has to go through is bigger. Mirroring, availability groups, and—well those can add to replication latency because everything gets stored in the transaction log.
Angie Rudduck: All right. So I realized I missed this very small question from Greg, so I will give him some attention. He said he saw some tweets recently that stated you should have four cores per NUMA node. What do we think about that?

Brent Ozar: Somebody was pulling your leg. It’s not configurable. It just comes down to for Intel processors it’s the number of cores per processor. If you turn on hyperthreading, it’s going to suddenly magically double. There are differences under virtualization, unfortunately, it’s such a huge topic you can’t possibly say, “You should always have four cores.” It depends a lot on the host hardware that you’re using and whether or not that hardware is identical across all of the hosts in your cluster. But yeah, anybody who just says four, they’re over simplifying something. Or it might have been for just one particular guy’s setup, like if one guy had just one host design.

Angie Rudduck: Yay for answers where people are pulling your leg.

What’s the best way to create a SQL Server inventory?

Angie Rudduck: Okay. Samuel wants to know, “What is the best way to create a SQL Server CMDB/inventory without buying third party software?”

Tara Kizer: I don’t know what that is.

Brent Ozar: Configuration management. Idera just had a new tool. If you go to Idera.com and click on free tools, I want to say it’s Instance Check, it’s got something along the names of inventory in it or it’s not discovery, but it’s something along the lines of inventory. So go to Idera and click on free tools. The other thing to search for is Dell Discovery Wizard. Dell Discovery Wizard will go through and survey your network and discover SQL Servers and identify them for you. Put them into a database. Another tool that you can use is SQL Power Doc. SQL Power Doc is an open source PowerShell script from Kendal Van Dyke. If I had to pick one that I like, I used Dell Discovery Wizard a bunch of times. Idera’s looks pretty easy as well. SQL Power Doc, not very easy, but super powerful.

Angie Rudduck: Very good.

Should I use multiple Availability Groups or just one?

Angie Rudduck: Eli has a question about availability groups since Bret Ozar II isn’t here. They’re planning on upgrading from 2008 R2 to 2014 to take advantage of availability groups. They would like to know if there is a performance advantage to having databases spread across multiple AGs instead of one single AG. His example is having the primary on one AG but be on a different node than another primary to take advantage of the hardware.

Tara Kizer: Yeah, I mean, definitely. The first part of your question, there is no advantage to spreading them across multiple AGs unless you are putting the primary on a separate replica. But you know, you do have licensing implications in that case.

Angie Rudduck: Curse licensing. Always out to get you.

Brent Ozar: That was a smart question. I’m glad he said move the split around different primaries because I was like, “No, there is no advantage—Oh yeah.”

Tara Kizer: There is an advantage there.

Angie Rudduck: Tricky wording there.

Why am I getting an external component error when installing SQL Server?

Angie Rudduck: Kimberly, welcome back, I haven’t seen you in a bit. She is installing SQL Server 2008 R2 on Windows Server 2012 R2. This is compatible based on MS docs she checked. However, she’s getting the “external component has thrown an exception error.” What is she missing?

Tara Kizer: I wonder if there is a prerequisite that you need to install first. At least on older versions of SQL Server and Windows it was supported on newer versions of Windows but you had to install something first. I don’t remember what it was and I don’t know that that’s why you’re encountering this error. This is the type of thing that I’d probably open up a support case with Microsoft.

Brent Ozar: The other thing, go download that, whatever the ISO file or the [XE 00:09:32] that you got for the download, go download it again and just save it to another place and try it again. Because I’ve gotten so many funky setup errors just from a corrupted ISO file. Then when I go and get another ISO, like bloop, works perfectly. I’d also say anytime you get them, I’m kind of paranoid like this, but anytime that you get an error during setup, I’d rather wipe Windows and start again. I’m just paranoid. I want to build something that’s going to last forever. So if you’re having repeated errors on the same Windows box, hey, go download a new ISO and then try again on a fresh install of Windows.

Tara Kizer: You can go through the setup logs to see if there’s a better error because that’s probably a pretty generic error. The problem with the setup logs is it’s hard to find the errors. Scroll all the way to the bottom and then you might have to start scrolling back up to see the failure. Because even though it failed, it might have done a lot of extra work afterwards and all of that is going to be logged.

Brent Ozar: There’s like 50 million instances of the word error in the log.

Tara Kizer: Yeah, exactly, it’s awful.

Angie Rudduck: I do like the trick that I learned about filtering the Windows log, starting here during triage. I had no idea about that and then one day when I watched you do triage and you right clicked on the left side bar, I was like, “What? I only knew…” Because half of the time during triage I have to ask the client to move my head because it’s always floating right over the filter log on the right panel in Windows events, so that happens a lot. I’ve been trying to work around not asking them to move my head because it sounds weird to me.

How should we do source control with SQL Server?

Angie Rudduck: Since we’re talking about a lot of software, let’s ask another question from Scott. Do we have any suggestions on source control software? When Richie is not here of course.

Tara Kizer: Yeah, I was going to say, Brent and Richie love Git.

Brent Ozar: So there are two ways you can do this. One is you can do source control before you deploy your code, meaning you go make a stored procedure change, you check the code into GitHub or Subversion or TFS, whatever tool you want to use. That’s proactive. Then you go deploy the code after you’ve checked it in. Man, almost nobody does that. Plus too, you end up having DBAs who need to change indexes in production or need to make an emergency change. So the other way you can do it is reactive source code control which means this tool goes out and looks at your SQL Server every x number of hours and then goes grabs any changes and checks them into source control. So this gives you a postmortem log of everything that changed but not who did it and not the exact time that it changed. I am personally a fan of reactive source control as a DBA. I don’t really care as much about who did it but I want what was changed. I want a breadcrumb list of everything that changed on objects. So Redgate’s source control for SQL Server has that ability that they’ll just go through and patrol your SQL Server periodically and check in any changes. It’s just not the source control that your developers are used to. That proactive change control is really, really hard.

Tara Kizer: We did both proactive and reactive at my last job. We used Visual Studios Team Foundation Server. Anytime we did deployment of the application, that was always proactive. And of course, DBAs, you know, are having to make changes. The DBAs were supposed to go in and do a schema compare and then update TFS. That didn’t always happen. Other tasks were more important. So whoever that next person was that touched that database, when they did the schema compare to create the deployment scripts, they would see that ther are these other things that shouldn’t be in my deployment that they’ve already been deployed to production but weren’t in source control. Besides that though, because you could have databases you never touch again. So besides that, twice a year they went through and did a schema compare against all databases and got them up to date.

Brent Ozar: Scott asks, “I didn’t know about reactive source control. Who makes it?” It’s a technique, not a product. It’s just part of Redgate’s source control as well. I think I even still have a blog post on our blog about how you do it with a batch file. Like I wrote a batch file in VBScript to do it with Visual SourceSafe. I need to burn that in a fire.

Angie Rudduck: That sounds complicated to somebody who’s going to try and totally mess up. That was cool. I was about to ask Tara if you could do them together. So that’s cool that you have seen them both together because I was like I feel like one place we didn’t consider indexes, we didn’t let developers change indexes. So if a DBA throws them in and then doesn’t check it in, that would be great to have the reactive right there.

Tara Kizer: Yeah, as long as you have a schema compare option in the tool that you use. Or you can get another schema compare. Then you can see what the changes are between source control and your database.

Angie Rudduck: Very cool.

What’s the fastest way to modify a big table?

Angie Rudduck: J.H. wants to know, “What is the fastest and/or safest way of exporting a large table and then reimporting it and maintaining its primary key auto identity seed ID … SELECT * into temp table from large table or bulk copy out or something else?”

Brent Ozar: Okay, so I’m going to tell you the terms to google for: modifying a table online Michael J. Swart S-W-A-R-T. So because you said fastest, Michael has an interesting set of blog posts, it’s like a five-part blog post on how you go set up the new table, how you build something to keep the old and new table in sync and then you move data across in batches. So this way end users notice very minimal downtime and yet you’re able to keep the two in sync as you get larger. The only time I would go that route is if, “You cannot take any down time. We’re willing to let you put a whole lot of development work into it” and it’s more than like say 50 gigs in one table. If it’s less than 50 gigs in one table, I would probably just do a select with a tablock and move the data across that way.

Tara Kizer: Then you can use the identity insert option to handle the identities. That way you keep the values the same between the two tables. So SET IDENTITY_INSERT ON. You can only have one table at a time do this so make sure you set it off when you’re done.

How should I manage identity fields with replication?

Angie Rudduck: That’s a perfect follow into Paul’s question. He has existing replication where he wants to change the identity management of primary keys have identity 1 1. He wants to change the primary keys to identify 1 2 on the publisher and identity 0 2 on the subscriber. Is there a way to do this without recreating the tables?

Tara Kizer: You do have the DBCC command where you can change the seed but I don’t think that you can change the increment. Usually, in a scenario like this though what people do is they have the publisher, it’s inserting positive numbers and then on the subscriber inserting negative numbers. So you would have, you know, if it’s an integer, you could have two billion rows for the subscriber and two billion rows in the publisher. That usually satisfies most tables. Otherwise, go to bigint.

Brent Ozar: So there’s a DBCC command to reseed the identity. I cannot remember for the life of me what the syntax is but if you search for that.

Tara Kizer: Yeah, I think it’s [Inaudible 00:16:26] IDENT is the command.

Brent Ozar: Yeah, you just run that on the one where you want to change them.

Angie Rudduck: Good info.

Should I use checksum when taking backups?

Angie Rudduck: Samuel wants to know, “Is it best practice to always add checksum when taking backups?”

Brent Ozar: Did you do that when you were a DBA?

Angie Rudduck: I didn’t.

Brent Ozar: You too, both of you, yeah, yeah.

Angie Rudduck: I didn’t know it existed.

Brent Ozar: I don’t think most people do.

Tara Kizer: I knew it existed. Did we do it? Probably not. It does add overhead to the backups and we were—at least a lot of the critical systems we would always, not always, but we would have a backup restore system. So we were testing our backups regardless. So do you need checksum if you are going to be testing your backups?

Brent Ozar: Yeah, I learned about it after I got started consulting. I’m like, oh, that’s an interesting idea. I went on a little quest of “I’m going to get everybody to do checksum on their backups.” I put it in sp_Blitz as a warning, “Hey, you’re not doing checksums on your backups.” Universally, people were like, “What is that? Why would I want my backups to go slower?” So I took it out as a recommendation just because people don’t like their backups going slower.

Tara Kizer: Does Ola’s solution, does it do the checksum by default?

Brent Ozar: Not by default, yeah.

Angie Rudduck: I think it does.

Brent Ozar: Oh, does it?

Angie Rudduck: Because I’ve been playing around. Yesterday I was playing around, let me double check my savings here but I ran the scripts default and then took a look. So would have to double check, but it’s included as an option at the very least.

Brent Ozar: And doesn’t his do verify by default too out of the box?

Angie Rudduck: Yeah, maybe it does verify by default and not checksum by default. But the verify, I mean the one thing I don’t think people think of is like how it can impact because you might be, “Oh, my one gig backup is taking 20 minutes.” I don’t know. But it’s because it’s just doing the restore verify only command against the backup it just took. So it’s just saying, “Oh, is this still a valid backup?” And at the basic level, right? Correct me if I’m wrong, but it’s only saying, “Oh, yes, I can open this as a file. I don’t know its validity inside.” Just that it could reopen it as needed. So that’s just something to be considerate of, it’s not the saving grace. “Oh, I did verify only.”

Brent Ozar: Yeah, it could be all corrupt data in there. It could be 100 percent corrupt. The way you’re going to have to find out is to run CHECKDB.

Why don’t our SELECT queries show current data?

Angie Rudduck: All right.

Brent Ozar: We’ve got all kinds of questions coming in. It’s crazy.

Angie Rudduck: I know, they’re definitely falling in now. Okay, so Andrea says they have been having issues with data not showing up in reports for sometimes up to 15 minutes. They are an OLTP shop running 2012 Web. Is this possibly a thing with SQL or is it due to something else?

Tara Kizer: I think we would need more information as to how is the data getting into this database? Is it queueing? Is there a backlog in say a Tibco queue or something like that? Or, you talk about reporting, do you have a replicated system? Or in availability groups, readable secondary, maybe there’s a delay in getting the data to those. I don’t think we have enough information to answer it.

Angie Rudduck: Yeah, I agree.

Brent Ozar: It’s never normal to do an insert in SQL Server, commit your transaction, and then not have it be available for a select immediately.

Why am I getting tempdb-full errors when my tempdb is 4GB?

Angie Rudduck: Let’s see what David has to say. He’s getting this on a server with four by one gig temp data, eight gig temp log, insufficient space in tempdb to hold row versions. Need to shrink the version store to free up some space in tempdb.

Tara Kizer: That’s a pretty small tempdb. I’ve supported tempdbs that were half a terabyte in size just because we had business users running ridiculous queries. So, first of all, why is your tempdb so small? Are you shrinking it down? You probably need some more space. Version store, are you running read commited snapshot isolation level? So you need more space for tempdb.

Brent Ozar: And then how much space do you need? Generally, if somebody puts a gun to my head and just says go pick a number, I’m going to go with 25 percent of the size of all the databases combined. So if you have say 100 gigs on the database server, you probably need at least 25 gigs for tempdb.

Tara Kizer: A few jobs ago, we set up hundreds and hundreds of servers. So we just made a policy and tempdb we set at 70GBs. These were shared servers with lots of databases and we didn’t know what was going to occur on them. We would have alerts to warn us if data files or the log file was creeping up on, if they were going to fill up, so we could react to those. But 70 GBs for all of the tempdb data files and I believe 30GBs for the tempdb log file. That was just our default.

Brent Ozar: I don’t get out of bed for less than 70 gigs.

Angie Rudduck: Silly, silly.

If I don’t know monitoring tools, will that hold me back in job interviews?

Angie Rudduck: Ronny supports about 25 prod and dev databases as a DBA. He’s not in the corp Windows DBA group and does not have access to all the tools monitoring performance, etc. “All monitoring I have in place is based on scripts that run and report issues. Will the lack of experience working with the tools like that hurt my chances with pursuing a new DBA job? I know it really depends on what the hiring manager is looking for but is knowing tools like that an important skill to have?”

Tara Kizer: I don’t think it’s an important skill necessarily, I think it’s obviously going to depend company to company but if you don’t have any experience with monitoring tools, I think that that’s fine as long as your other experience, your actual SQL Server experience, is what they’re looking for. You can get up to speed on these tools, I wouldn’t say fairly quickly, but you can at least click around and figure things out and with some guidance get some pretty in-depth knowledge of these tools. For the most part, I don’t think that companies are paying for tools like this. So I think that it’s pretty rare that companies have these tools in place.

Angie Rudduck: Yeah, unless you’re going to a large DBA shop, I don’t feel like you’re probably going to have very many of these tools.

Brent Ozar: And you’d have to know all of them. I mean, you know, if you only knew one and then somebody doesn’t use that one, you’re screwed.

Angie Rudduck: It’s not the same thing as not knowing SQL Server versus MySQL versus Oracle. They all run pretty similarly and nobody expects you to know all of them or they’re only going to hire you if you know this one. Like if you only know Redgate, great, because they’re a Redgate shop. That’s usually not the case.

Brent Ozar: Yeah, when we do interviewing for consultants for example, so when we go and hire people, we will often give them a virtual machine and say, “Now you’re on, take remote control of this thing. Show me why it’s slow.” Or, “Troubleshoot why this query isn’t working.” If someone comes to me and says, “Well, I’m sorry, all I can do is use a tool,” like I only ever troubleshoot this with Dell or Idera or Redgate and I’m lost without a third party tool, you’re not going to do well as a consultant because we can’t rely on those tools either. When we parachute in, man, I have no idea what’s going to be happening on the other end. So it pays better to know the native ways to doing things.

Idle chit-chat about smoking weed and your friend sp_WhoIsActive

Angie Rudduck: I think we have probably time for one more question. Did you guys see anyone while I scroll back and look?

Brent Ozar: Greg says he remembers that the tweets about tempdb stuff was four tempdb files per NUMA node. They were smoking weed too. I don’t know who that was.

Angie Rudduck: They must be in Portland.

Brent Ozar: Yeah, Denver, something like that.

Angie Rudduck: Someone chimes in, Eli says, “The sp_WhoIsActive is your friend about the monitoring” to you Ronny. That is a good point, we love…

Tara Kizer: WhoIsActive and the Blitz stuff.

Brent Ozar: Yeah, free tools. Pro tip: If you’re going to apply for work at our company, you may want to try using our tools. Know that they’re out there. If you come in and use someone else’s tools, it will not be a good time for you.

Angie Rudduck: Yeah, I agree.

Brent Ozar: Not that you ever need to know how to use our tools to work here. We teach you those too after you get here. But, yeah.

Is PostgreSQL better than SQL Server?

Angie Rudduck: Yeah. I feel like there was one that I… There’s a couple that are like…

Tara Kizer: Wes asks the same question, I think he wants—they’re 20 minutes apart.

Angie Rudduck: He really wants me to read his question. Wes, I’m going to tell you my answer is SQL Server pays our bills. Wes wants to know what our thoughts are on Postgres versus Microsoft SQL Server. SQL Server.

Tara Kizer: We’re SQL Server professionals so our answer is going to be SQL Server. If you want me to support your system, I don’t do Postgres SQL so I can’t support it. I mean, I could probably learn it but I don’t really have any interest in learning it.

Brent Ozar: See, I don’t support it either. But I always try to learn about other stuff. There’s stuff that’s really cool about Postgres. Unlogged tables is a classic example. If you search for Postgres on our site, we’ve written a couple blogposts about different features in Postgres that we would want in SQL Server. But boy, at the same time, I kind of like parallelism. Man, Microsoft SQL Server has had parallelism for a long, long time. That’s kind of nice in today’s huge, multicore environments where 16 cores isn’t a big deal. 32 cores isn’t a big deal anymore. Parallelism is pretty freaking awesome. And they’re iterating super fast. So, yeah, I kind of like Microsoft SQL Server. If I was going to start a career from scratch, so like Microsoft is where it’s at in the enterprise environment and Postgres is where it’s at in the startup environment. Well, thanks everybody for hanging out with us today and we will see you guys next week.