This week, Brent, Tara, and Richie discuss VLF issues, SSAS tabular performance support, certification tests, query documentation tools, what to do when the executive team doesn’t value your contribution as a DBA, and more.
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 – 3/14/18
How do I keep VLFs under 300 for a VLDB?
Brent Ozar: [Tishal] says, “Hi guys, I have a 2TB database and I have auto-growth set to 4GB. The number of VLFs is always about 1000 VLFs. I think I’m supposed to keep it under 300. Is there some way – should I keep it under 300 and how do I keep it under 300 VLFs?”
Tara Kizer: I mean, where did that 300 number come from because – so 2TB data – what’s the size of the log file and what size is it growing to? So I’ve had some log files that were, say, 300GB or higher and I can get the VLFs down to, say, 200 to 300, but that’s just, you know, picking the number. I mean, so shrinking it down all the way and growing it back out in like 4GB or even 10,000MB chunks. But it’s obviously auto-growing, since you’re going from 300, fixing it and then going back to 1000. So why are you – what is going on there? So someone must be shrinking it for it to be [crosstalk] this battle.
Brent Ozar: Yes, it says, it always grows back to 1TB after shrinking – oh, it always goes back to 1000 VLFs, I guess is what he’s saying.
Tara Kizer: After you fix the VLF issue, you’re supposed to auto-grow it back out to the original size. I think maybe that step is being missed. So shrink it all the way down to 5MB or 1MB. Do this in a maintenance window because if you have a 2TB database, you’re probably going to need some transaction log space. So make sure you get all the way down, as far as you can; literally like 5MB. Then you grow it back out to the original size. Don’t just let it auto-grow from there. You have to auto-grow it back out and pick some number, such as 5000MB, to do the manual – you’re going to be doing manual growth back out and keep going until you reach your original file size. So I think that you’re missing a step here. I don’t think you’re manually growing it back out.
Brent Ozar: And you could grow in giant increments if you want…
Tara Kizer: Aren’t there performance issues with that? Because you then have some large VLFs and in order to clear those out – it takes longer to clear those out. You have to clear out a VLF at a time when the truncation process happens – it’s a VLF at a time…
Brent Ozar: And the growing’s going to take a hell of a long time too at 20GB a pop. I’m like Tara too, like where did the 300 number come from? Our general number is – I think we alert you at 1000 VLFs in sp_Blitz and that’s only just to warn you that before you get to 5000 or 10,000 that you change tacks. But I’m kind of okay with 1000 VLFs; I don’t have a problem with that.
Tara Kizer: Did you see the Stack Exchange question from last week? They were on their third restart. The crash recover was taking over 36 hours. So the first two times it happened, it took over 36 hours. They must not have investigated it because here they are on their third time. I assume Microsoft patching or something has caused them to do a restart of the box. Why, after the first time, didn’t they start troubleshooting this? Because they weren’t even sure what the VLFs were on this third time while they’re waiting 36 hours for this database to come online. And they follow up to say even copying files, they were getting 1MB per second or something crazy like that. They were like, something’s going on with this box. I checked on it like a couple of days later and I didn’t see an update. I was hoping to see what happened with it.
Brent Ozar: Yeah, like how many hours had passed before they finally got the thing up to speed. Brutal – I remember there was one data warehouse when I was in Miami – a data warehouse in Miami that after 72 hours they came to me. I wasn’t the employee, I was just there as a consultant at the time. After 72 hours they came to me and they’re like, “Do you know a way to see how far along the restart is?” And I’m like, “No, why?” 72 hours.
Tara Kizer: Oh man, I mean you can kind of gage it in the error log because it goes through three phases and it does give you percentages in those phases. But yeah, at least you know what phase you’re in, unless you’re still in phase one after 72 hours.
Brent Ozar: I’m like, what are you doing? Well it’s just a QA box. We’re not really – just delete it. Just delete the databases and start over.
Where can I learn to tune DAX and VertiPaq?
Brent Ozar: Francisco asks, “Where do you recommend for a support community like you guys for an SSAS tabular performance support? I want to learn how to decipher query plans related to DAX and the VertiPaq engine.”
Tara Kizer: Lots of words; I don’t even know what they mean.
Richie Rump: Compaq makes VertiPaq? I don’t know…
Brent Ozar: Yeah, it had that HP sounding name when they came out. Anything ending with a Q. Our poor transcriptionist. VertiPaq is V-e-r-t-i-P-a-q. So the problem is, like, if you take a percentage base, how many percentage of people are using the SQL Server engine and then what percentage of those are using analysis services and then what percentage of those are using, say tabular or DAX or whatever? It’s such a small percentage. It’s not that it’s a bad product. It’s totally fine. There’s nothing wrong with it. It’s just that not only are there so few people using it, there’s like three guys tuning it and then they don’t end up doing any community work because they’re just overwhelmed with tuning it.
So your choices are either go to work for Microsoft, talk to Chris Webb – and I say talk to, but he does training classes over in Europe. I don’t think he does online ones yet, but he does that kind of performance tuning work. The other one is Bob Duffy out of Ireland. It’s prodata.ie. But all of these are just – they blog once or twice a year, but the rest of the time, they’re doing their private training classes. You’d have to go to a class.
Tara Kizer: Just like Richie and Me… Once a year.
Brent Ozar: Got better things to do – come on now.
Richie Rump: Do you know how much bugs Brent puts into my product? Really, that’s a fulltime job.
Brent Ozar: It’s embarrassing how bad my code – like I feel bad – I was talking to Erik about this yesterday. So I check in fixes to our internal stuff in the Constant Care, now that you all can see Constant Care. And I give Richie code and I’m like, yeah this is ready to go. I’m pointing to fields that don’t exist. Fields that don’t even exist… I’m like, yeah, no ship it, Richie, it’s cool. It’s embarrassing. And then at least Richie has the incredible foresight and sense to build automated builds; so as my code gets checked in, you can smell the smoke and how bad everything is broken.
Richie Rump: See, I break stuff way more than Brent does, but nobody’s looking. So it’s a little different when Brent checks stuff in and I’m looking at it like, dude, come on, that field doesn’t exist, man.
Brent Ozar: It’s like, yeah, are you looking for IsDisabled? I’m like, yeah, that’s exactly what I… Oh…
Is it time for me to leave my job?
Brent Ozar: Daryl says – oh, Daryl asks a good question. He says, “I’ve continuously improved the shop I entered four years ago. I’m the only DBA supporting 44 servers and 185 databases. I’m heads-down all day supporting my developers, projects, day-to-day database support. I’ve been in IT for about three decades; two decades Oracle, four years SQL Server. No one knows what I do but the C-level is still convinced that I don’t know what I’m doing. Is it time for me to leave? Should I do in-house presentations on what I’ve done and how it’s impacted the environment?” What would you guys do in that – Tara’s expression…
Tara Kizer: I just don’t know. I mean, if the C-level people don’t know what you’re doing or that you’re doing a good job, I couldn’t stay there. That means I’m being passed up for raises and bonuses and I’m just suck. I’m basically stuck in my career there and it just – unless there’s something else keeping you there like benefits or maybe your starting salary was fine and you’re okay with that, but I could not work in that environment.
Richie Rump: Yeah, it would be extremely hard to communicate to the executive team about what you do and why you’re important. I mean, because they already think you’re not doing a good enough job at the moment for whatever you do. And then explaining it to them something that’s pretty technical and they don’t sound like technical people, that’s an uphill battle as it is. It’s a lot easier to just, hey, what else is out there? And I could probably get paid more while I’m doing it. It sounds like something that you need to look into.
Tara Kizer: And one way to know if you really are doing a good job is looking for another job and seeing what kind of jobs you can get, because maybe you aren’t as good as you think. It’s possible, you know, when you’ve been in a job for a long time, you’re not exposed to newer stuff and it’s hard to keep up when you’re stuck in a certain job for a lot of years.
Brent Ozar: A great example of that – a friend of mine went through the MCM program and they’d been working at the same shop for ten years but just with the same server; just the same server continuously and over and over again for the same ten years. Had built that server themselves, knew exactly how it was configured, never got into really strange scenarios and was totally blown over by the MCM exam and how hard it was. There are all these different features – I’m like, yeah, you know, that’s what we work with. That’s how this thing goes. People use crazy stuff.
I would say too, if I’m in your shoes, you ask, “Am I happy here enough to the point where I can keep cashing this check and I’m ready to retire in so many years?” I don’t know what your retirement situation looks like, if you’ve been doing, you said, IT for 30 years. And who knows, maybe you found what you love and this is the exact perfect niche and you’re like, okay people never know what I do – you just want to make sure you don’t get laid off.
Tara Kizer: That would be my concern.
Brent Ozar: Sell them a little better so that they don’t replace you with someone with two years of experience who wants $50,000 per year. But always – Richie gets heartburn – I would totally put your resume together, have it out on LinkedIn and send it to a couple of recruiters in town – assuming in town if you didn’t want to move – because remote work is almost impossible, despite how easy we make it look. Just so that that way, you’ll be entertained. The time to look is when you have a job. The time to look is when you’re already comfortable and you do, well, you know, I have a parachute here and you can pick and choose and be comfortable and flexible.
Richie Rump: What was that site again?
Brent Ozar: SQLSkills.com…
Tara Kizer: I just got a job posting from a recruiter yesterday for a local healthcare system. Somehow the bullet points in there is like must know Linux… Maybe you sent this to the wrong person.
Brent Ozar: I liked how somebody was Tweeting yesterday, they said, “Java is to JavaScript as ham is to hamster because they’re not related.”
How should I approach getting a Microsoft certification?
Brent Ozar: Kyle asks, “Any suggestions for sites which provide practice exams for certified 2016 database administration tests?” Boy, are we the wrong guys. So it’s been a while since we talked about certification, so we’ll go across the screen. Tara, if I say, you know, should you get a certification test or not, what’s your answer?
Tara Kizer: I have exactly zero certifications and this is because I’ve taken practice tests many years ago, many, many years ago, and I did so poorly on them, I was like why bother with these? I’m already doing a good job. At that job, I was considered a rock star, you know, doing really well. I was like, I just don’t need to be certified. Why spend – that company would have paid for – I figured how many failures, so if I had failed the test, they would have paid for some of them. And I could have taken time off to go prepare for them and they would have paid for that. I had no interest in it, really. That one practice exam I was like, I remember, I’m not a good test-taker. And the questions are so dumb anyway – the test answer might be different than what we do in the real world. So I’m answering real-world answers and that’s not what the test is looking for.
Brent Ozar: Richie, how about you?
Richie Rump: Yeah, for some reason, when I took the SQL development exam – I think it was 2008 – like, the answer was always XML data type. And in reality, the answer is never the XML data type. It’s never it. Way back in the day, because I think I took my first exam way back in 97 and I’d just graduated college and I just jumped right into one of the exams and passed it. I was using the Transcender practice tests. But I haven’t seen them in a billion years, so I don’t even know if they’re still around yet or whatnot. But what I would do is I would – they would come with three practice tests, I would take one, study the stuff I missed, take another one, study the stuff I missed and I’d take the last one and then take the exam – I never passed a Transcender exam, but I never failed a Microsoft exam.
So they were good tests. They were good, but I haven’t felt the need or desire to take a certification test in about a decade. It’s just one of those things where it doesn’t matter as much as it did back 20 years ago. 20 years ago, it would get you in the door. Nowadays it’s just a blurb that people kind of skim over. It doesn’t really buy you too much.
What tool will document my queries?
Brent Ozar: Ronnie asked a question. I’m going to have you flesh out your question more while I talk about Kyle’s follow up. Ronnie said, “Can you recommend a free tool for query documentation, or do you just create your own templates?” Talk more about the kind of output that you’re looking for. Are you talking about comments or are you talking about visual diagrams? Or what do you mean by query documentation? Sounds like a classic DBA answer – what do you mean, documentation?
Tara Kizer: Documentation and Tableau, I’m like, I’m out.
Our vendors are requiring certification.
Brent Ozar: Kyle asked a follow-up. He said, “It’s now a requirement for us to get the certifications in order to stay in good maintenance with one of our vendors.”
Tara Kizer: Yikes…
Brent Ozar: I’m going to give the most politically incorrect answer that will surely get me in trouble when this transcript goes live, but here we go. If you don’t care about the certification and if it’s just to make someone else happy and you already have a job and you’re not worried about whether or not you prove you know something, Google for the test number and Brain Dumps and hit torrent. You can go get torrents of the exact questions and answers that are used on exams. Because what happens is, in less scrupulous areas, people will go take the exam and immediately go out and write down or type out everything that was on the exam. So there are lots of countries where it’s an accepted practice to exchange that stuff for free. Now, that’s dirty in the sense that Microsoft’s going to get angry at me for saying that.
Tara Kizer: It’s so anti-community…
Brent Ozar: Bad man… But I have so little faith in the Microsoft exams, they’re not worth the paper that they’re printed on to begin with. They don’t show what people actually do for a living. That’s the same reason we don’t ask for certs when we go and hire people. They’re totally irrelevant. So if you just have to check an irrelevant box then cheat – which is a horrible thing to say – and there it is.
Tara Kizer: And I’m so passive aggressive that even if it’s a requirement of a company, I would still not do it until they forced my hand, you know, you either leave or you get certified. And I may choose to leave because it’s not something I’m interested in.
Brent Ozar: It’s horrible. We had – at one point, we were a Microsoft Gold Certified Partner because we wanted to go down the road and see what it was like. And they had requirements for you had to have a certain number of certified staff. So a bunch of us went and took the test and we were all pissed because they were BS, and I want to say both Doug and Jess were struggling with the BI tests – and they know more about BI than most people I know. And so, we were like, alright. And when it came time to renew, we just canceled the Gold Partner thing because we were like, it’s just meaningless. Now, I’m not saying the Gold Partner thing is meaningless. It’s not if you’re into that kind of thing, but it just didn’t make any difference for us.
Tara Kizer: It’s pretty weird that our clients ask if we’re Gold Partners or not. I think I’ve seen one email the two years that I’ve been here.
Brent Ozar: No, and I think some of it is probably word of mouth. Like some Gold Partners only want to work with other Gold Partners. So it can help you get a consulting job at certain kinds of consulting companies, if you want it, but I’m so biased against doing that just to get to a specific company. I’d rather you go to someone in your network that you actually want to work with. And then usually, when there’s someone who knows you, knows the work that you do and wants to hire you, they’ll bend the rules to get you in. they’ll make that happen. Richie looks like he wants to say something there.
Richie Rump: No, no I’m fine. Cheat away…
Tara Kizer: Having said all that, I mean, the MCM program was a great certification, you know that really did prove who were experts, for the most part. I’m sure some of them slipped through.
Brent Ozar: It was weird how it worked – and I’ll talk about this now too because it’s been so long. But the first test was – the final test – there were three written tests that were all hard as hell. The three written tests – you got one a week for the three, then the final lab exam was six hours in a lab and you didn’t get a list of questions, you got a couple of sheets of paper, like double-spaced, with big long brain-dumps from the client. Like, here’s everything that’s been going on with the client. And you had to figure out what you were supposed to do. There was no question three-mark C or something like that. It was pretty bizarre.
Tara Kizer: I don’t think I could have done that. So you weren’t on a computer to test things out, you were just on paper with the scenario?
Brent Ozar: No, you had the real – you had like three VMs, but you only had those three. There was no dev environment; it was just go. And there were maybe 20 things that you had to accomplish once you wrote down, here’s everything I think they’re asking, and they would all break each other. So it was a very do it live fun scenario. You’d have done fine. I really think that it was the best experience I’ve ever seen for judging someone who’s worked around databases for ten years. If you’ve worked around databases for ten years, you can smell what’s kind of broken and click around. I’d never really worked with replication before, but there was a replication problem and I solved it because I was like, “Alright, well let me figure this out. How hard can it be?”
Tara Kizer: When I first looked into it, when it was still $10,000 to get through the whole thing. I know that the price dropped at one point…
Brent Ozar: $20,000…
Tara Kizer: $20,000? Okay. So that just wasn’t happening.
Richie Rump: Yeah, I was following Brent’s blog posts on that like crazy because I couldn’t figure out why somebody would spend 20 grand and three weeks of their time – what is going on here, Brent?
Brent Ozar: Yes, I did that back when I worked for Quest. And I had that same reaction when they came to me. They were like, “We have this MCM program. Do you want to be in it?” Like, “I don’t know, what’s involved?” “It costs about $20,000 and three weeks.” No, no, no.
Tara Kizer: Did they cover it?
Brent Ozar: Yeah, they picked up the whole thing. But if I left within – I can’t remember – a year or two years, I had to pay the whole thing back. And I did, so that was a check where I was like, “God I really liked this MCM…” Daryl said, “Was Googling disallowed during the exam?” No, it was open internet. You had whole open internet. You could bring your scripts in and still it had like a 70%-80% fail rate; so it tells you.
Followup on query documentation tools
Brent Ozar: Ronnie follows up with, “For the query documentation, I’m at least just looking to create notes within the query; at best, something that can output database information into some kind of document.”
Tara Kizer: I still don’t understand the question really.
Brent Ozar: There is no self-documenting code. There is no self-documenting app. Richie, what app are you using to generate the 400-500 pages of documentation that you cranked out here?
Richie Rump: These right here; these tools.
Brent Ozar: On the audio podcast, you can’t see Richie’s fingers. They’re all painted in different colors. He does this on company time too.
Richie Rump: You can’t tell but I’ve broken every one of my fingers at least once.
Brent Ozar: How have you broken every one of your fingers?
Richie Rump: I used to play basketball, man. Either sprained or broken, and what you used to have is you get a finger that’s popped, you just take some tape and you run back out there. You tape two fingers together, you know, whatever that is, but you just don’t stop playing. That’s not an option. You keep going.
Brent Ozar: You like basketball way more than Tara and I like basketball.
Richie Rump: Yeah.
I need certification so the men in my office will take me seriously.
Brent Ozar: Katie says, “We’re trying for certificates so that the guys we work with will have faith in our answers.”
Tara Kizer: Eurgh, no.
Brent Ozar: Katie, I apologize. I am sorry that you have to go through that. whoever you’re working with is a pig. That’s ridiculous. It should be the other way around, that nobody should respect anybody – I’m going to get myself in so much trouble. The people who have to get certificates to be treated fairly, it’s just not fair. That’s just not right. It’s bogus. She has a smiley face in her answer, but that’s just crappy.
Richie Rump: I mean, is this one of those things where maybe integration testing would help out? So you actually create tests that will load a certain amount of data to a database and then you run the query up against it and then it comes back with the answer, which you verify it’s the same. And you run all these different tests up against your queries and, hey, look it works because we have tests and every time we do a deployment once a day we run all these tests and they all worked exactly the same way, and then it’s repeatable.
Brent Ozar: What you started that, I thought you were referring to Katie’s question, and I was like, how does that have anything to do with gender and believing…
Richie Rump: Oh yeah, no.
Does Agent pause processing during a failover?
Brent Ozar: Sree asks, “Does SQL Server agent pause processing when it’s failing back or failing over?”
Tara Kizer: Nope. It’s just like all your connections, everything gets severed.
Brent Ozar: Bombed. It pauses in the sense that it stops working, and then it starts working again when it starts over on the other node.
Tara Kizer: Everything gets killed, rolled back, rolled forward, whatever.
Brent Ozar: Daryl says, “Thank you, you guys rock.” Well, you are welcome. And with that, we are at the end of this Office Hours. Thanks everybody for the questions and we will see y’all next week; adios.
