This week, Brent, Tara, Richie, and Erik discuss a most excepted solution for SA and securing an instance, how many VLFs should a database have, merge replication issues, query tuning tips, database restores, and Larry Ellison’s self-driving database.
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 9-20-17
(Sorry, no titles for questions this week – Brent’s off in the Isle of Man. Insert joke here about him not being manly enough to be a permanent resident.)
Brent Ozar: Hannah asks a question… I guess we’ll get started with the technical stuff… Hannah says – of course, it’s a tough one to get started with, “In regards to SA, the SA account and securing an instance, what’s the most accepted solution? Should I disable SA and only use Windows Auth, or should I change the SA account?” What did y’all do when you were managing servers?
Tara Kizer: I renamed the SA account and then set the password to something ridiculous that nobody could ever type in unless you got to source control. We had to use SQL Authentication due to the applications. I would have preferred to be Windows Authentication only, but whether or not you had to set up the instance to mix mode authentication to allow SQL Authentication is dependent upon the applications that are connecting. So since I had to have SQL Auth enabled, I wanted to do something about the SA accounts, so we renamed it and then just set a ridiculous password and put that in source control, or the password manager, of course; but never use the renamed SA account to login.
Erik Darling: I was in the same boat as Tara, where that old [inaudible] account was a SQL login, so I couldn’t just use Windows Auth only. But I would at least make an attempt at keeping SA disabled. Other people with SA privs would come on and turn it back on to do things with, which was kind of aggravating. Room full of developers just changing SA settings, and I was like no, put that down, stop. Someone’s popular…
Tara Kizer: No, it was just spam calls; so many spam calls.
Brent Ozar: David says, “I scramble the SA account daily. I had a vendor that kept resetting it and using the [crosstalk].” That’s excellent.
Brent Ozar: Next up, Chris says, “Hi guys, curious what your thoughts are for how many VLFs my database should have. I hear lots of different recommendations, what’s a good number to watch out for?”
Tara Kizer: [crosstalk] I have these numbers in my head, just based upon all of the databases that I’ve gone through and shrunk and fixed the VLFs on. Because I have run into massive downtime because of extremely high VLFs, back when I was an intermediate DBA and learned about VLFs afterwards. Anyway, he says to keep them under 100 or 1000 VLFs. What about – for 100GB database, generally they have less than 2000 VLFs. 2000 is too high in my opinion for 100GB database. 100GB database, I’d expect around less than 200 VLFs. Shrink it down as far as possible, like even to 1MB, then start growing out into bigger chunks. But 2000 is too high, in my opinion, for a 100GB database.
Erik Darling: Yeah, I think the main thing with VLFs, though is that it really is an odd circumstance where they cause a big problem. So, if you’re at the point now where you’re kind of twiddling thumbs and looking for something to fix, go for it. Otherwise, I would probably concentrate on something else.
Tara Kizer: Exactly, that’s what we did at my last three jobs. Because we ran into this massive downtime because of high VLFs, it was just about overall health check of the systems. And so, my last job, two or three times per year we had this checklist of things we would do to ramp up for certain holidays that would cause extreme load on our servers. So, we had this checklist and one of them was check VLFs, so it’s about the health check.
Erik Darling: It’s one of those things like usually when you fix it once, you don’t have to go back and go crazy with it. Like I ran into a 5TB or 6TB database, when I first got it there were like 25,000 VLFs, which even I was like oh, I should do something about that. But even when I got it down to a normal number, I still had a restore take 21 hours with it because the VLFs ended up being…
Tara Kizer: Oh right, yeah that’s the balancing act, yeah.
Erik Darling: Ah that was cute.
Brent Ozar: Roy says, “Am I the only one who hates all the cloud talk around SQL Server? I prefer the simplicity and strength of database integrity, you know, ACID. There are developers struggling to keep up with the latest visual studio, much less constantly being bombarded with the cloud migration suggestions.” Richie, this has you written all over it.
Richie Rump: It kind of does, doesn’t it?
Erik Darling: Richie’s ears started whistling.
Richie Rump: Oh look, my ears are turning red, not that Brent could tell. But yeah, I kind of do, and there’s going to be a big push from Microsoft to kind of get everybody into Azure, because that’s going to be their next big business venture. It’s not going to be from licensing fees, it’s going to be from getting everybody in the cloud. So, it’s not going to go away anytime soon. The only question is, are you going to go there or are you going to stay on-prem? And I can only imagine they’re going to make it harder for us, as time goes on, to stay on-prem. Either through higher licensing costs or something other, because they really want us all to get to the cloud.
I think you still could do ACID in the cloud without a problem. Azure SQL should be able to do that, but things are in flux right now in the Microsoft world with all this stuff with .NET and visual studio constantly being re-upped, revamped and the next version of .NET not being where people expected it to be, and that constantly being turned over. So just hold on tight. If you’re going to stay where you’re at and wait to see what happens, then do that. If you’re going to grab the cloud hard and hold on, try that. But I don’t think there’s anything wrong with sticking with on-prem SQL Server until Microsoft really figures out what they’re going to do in the cloud.
Erik Darling: I think SQL Server’s going to be a tough zoom. I mean, they’re not going back to less frequent releases. From what I read, there’s going to be probably every two years, maybe even every year releases. I mean, if they throw in a 2017 and they called it 2017, not 2016 R2. So maybe even yearly releases, so that’s going to be tough to juggle even as a consultant. Like what version are you on, what can that do?
Richie Rump: For your lying DBA, right, they guy just working on-prem, not a very large stuff that’s going on, is there going to be stuff in the engine that’s going to be worth that upgrade? I mean that’s going to be the real question; do I need to upgrade this year or not, I think, is going to be a question that we’re going to be hearing a lot going down the line as we start to see more and more frequent updates. And a lot more stuff being put into the product, and stuff that we may not need or use.
Brent Ozar: I’m tired of it just because it feels like a sales pitch. Very often it feels like – I know they’re going to get monthly revenue from me, I get it. And they’re shoving it down DBA’s throats to go, “Buy now, save later.” And it just feels kind of old with that, but I get why they’re doing it, but it just feels like a spammy overreach right now.
Erik Darling: Obviously, part of what they want to suck people in for is so they can keep going towards the automated tuning thing. So, all the, “We can fix this problem for you if you just give us the keys to the car. We’ll just go in and watch the engine run and figure out what needs to get fixed and we’ll just keep doing that.” So, like we talked about last week, with the managed instances, a lot of that is just farmed out query tuning. So, they do the automatic query tuning, they do the automatic index tuning, they fix regression. So, all the tooling that they’re building in with query store, with query store wait stats, with the memory grant feedback, all the cool new batch mode stuff. So, all that stuff that comes back is like, oh okay, we just – the more people we can get into this, the better we can get the automation to be.
Brent Ozar: Alex has a question. Alex, I’m just going to tell you, whenever you see more than multiple sentences in there, go ahead and put follow the instructions on the screen. Go to DBA.StackExchange.com. It’s a great place to go in and post really long involved questions. We do the same thing for ourselves. Erik posted a question today over at Stack, we answer questions over there all the time, but whenever you find multiple paragraphs involved, go ahead and post that over as Stack.
Erik Darling: We answer questions there too.
Brent Ozar: Sri says, “I’ve got merge replication problems, erroring at night due to virtual memory. I’ve increased the RAM on the VM to 32GB, unfortunately, I’m still having problems with SQL Server running low on virtual memory, please restart the merge agent. What should I do?” That’s actually a good one for Stack as well, I think probably. Have any of us used merge replication?
Tara Kizer: Just transactional. On this week’s client, I’m using it and I’ve had one other client using it.
Richie Rump: I’ve used it once, that was it. That was my first and last time, that was it.
Erik Darling: Oh wait, but if it’s virtual memory, couldn’t that be that the VM is getting sucked dry?
Brent Ozar: Yeah, it could be anything from outside, external memory pressure…
Erik Darling: I might try doing that…
Tara Kizer: But this is the error from within merge replication though, so it’s referring to virtual memory at the OS level, isn’t it? Wouldn’t that be the page file?
Erik Darling: Right, but if it’s a VM then something else is sucking memory away from the VM then.
Tara Kizer: Right, but I mean the virtual memory in the error is not necessarily referring to the virtual memory on VM.
Erik Darling: I think what I might try – the first thing I would do, off the top of my head, is try turning lock pages in memory on, to see if I could keep memory on the box and keep memory for SQL stuck in there.
Brent Ozar: Also, go Google for this, because I Googled and there’s a whole bunch of people with the same issue. So, go Google it and there’s a bunch of advice out there.
Erik Darling: Brent Google Ozar.
Brent Ozar: Joseph says, “Please don’t laugh. Have you ever seen a SQL Server database repeatedly fry an SSD drive?”
Tara Kizer: I wonder if it’s the drive that’s the problem and not the server.
Brent Ozar: I wonder if it’s not the server. Like a crappy RAID controller. Tell us ore about what fry means, because I’m curious. What kind of symptoms are you seeing? He says, “RMA.” Is that remotely Madonna assisted or?
Brent Ozar: Samuel asks, “How accurate is the tempdb estimate while running CHECKDB with estimate only?” Wow, I’ve never done that.
Tara Kizer: I haven’t either.
Erik Darling: I’ve done it, I just don’t remember how accurate it is.
Brent Ozar: He says, “Can you simply resize tempdb with the estimated value?” Tell us more information. Tell us how big the database is, like how many GB or TB, then how large is your tempdb, just ballpark. That would be interesting…
Erik Darling: Also, how big is your largest object in the database? I mean, I’ve run that before and I want to say the thing that they use is kind of like what they use to estimate – when you run that stored procedure that estimates like page RO compression; where it takes a rough guess at how much it will compress by looking at some of the data.
Brent Ozar: J.H. says, “Is it accurate when enabling TLS 1.2 support we would need to install a specific patch?” Your best bet there is to go post that on Stack Exchange as well. We don’t get involved with client-side encryption stuff at all.
Brent Ozar: Rex says, “Pedro Lopes of Microsoft has a solution to your VLF problems…” I’m going to say thanks just so I can stick that in everyone’s Q&A, if you want to go hit that link. There’s actually a really good post by Tracy Boggiano this week that will be in the weekly links this coming week. She has a script to automatically fix your VLFs. She checks VLFs, then shrinks your database down to nothing and regrows it out to an appropriate amount, and just runs it in a SQL agent job, which scared the hell out of me until I remembered it was Tracy Boggiano. Then I was like, I actually kind of trust. And it’s a big script. It’s huge.
Erik Darling: I was going to say, if you wanted to like take into account all the weird stuff, like is it okay to shrink a log? I’m like, what time is it?
Brent Ozar: Yes, and she’s got a lot of logic into there, and I was like – working hard on that.
Erik Darling: That’s cool.
Brent Ozar: Graham says, “I have high CXPACKET and latch waits. My application is slow, the database size is 90GB, the server has 32 cores and 64GB of RAM, and I can’t put any more RAM on the server. I see a lot of parallelism in query plans from my top ten most expensive queries. Should I play around with MAXDOP and cost threshold?
Tara Kizer: I mean, you need to make sure that they’re set to – yes, not play around but, I mean, make sure that they’re set appropriately for your server, probably eight for MAXDOP and 50 for cost threshold for parallelism. But the fact that queries are going parallel doesn’t mean that there’s a problem. I would tune the expensive queries.
Brent Ozar: And what would you look at in the queries? What are the kinds of things that you look for?
Tara Kizer: I mean, that’s more of an Erik question, but I mean, for my level – because he gets into the individual operators. I’m just like, I’m out. So, I’m looking for like, glaring problems, you know? The warnings, implicit conversions, compilation timeouts, missing indexes. I’m looking at the scans, but scans aren’t necessarily a problem. Expensive key lookups – you know, I always pull my plans into – what is it called – Plan Explorer, Century One to Plan Explorer, and then if the query is large enough, I’ll go into the top operations tabs so I can see exactly which one is the highest estimate percentage, because I can’t always find it if it’s so big and a lot of our clients have some really large plans. At least I’ll know what to be on the lookout for and then go back to the graph.
Brent Ozar: He says it’s an ISV app where he can’t change the queries. I would still index though.
Erik Darling: Yes, index tuning would be the biggest part of that.
Brent Ozar: Joseph says, “See howsmyplan.com.” Alright, I got to go look this up. If this is – there was a SME – oh yes, this is – I think Daniel, I forget his last name. Dan Janick has a plan analysis tool howsmyplan.com. I haven’t seen it improved a lot lately, like he just launched it once and that was kind of the end of it, but it was interesting.
Brent Ozar: Let’s see. Hannah says, “Is it possible to add schema binding to a current existing view or do I have to completely recreate it. My goal is to add a clustered index to speed up performance. The view returns four billion rows.”
Erik Darling: I mean, you have to alter the view to add schema binding, but you don’t have to like recreate the view.
Tara Kizer: Aren’t you adding a where clause when you query this view?
Erik Darling: Also, I’d be curious to know why you think adding a clustered index to the view would help you return four billion rows.
Brent Ozar: Well, imagine you got [crosstalk]
Richie Rump: Security reasons? I don’t know. That’s Brent’s question.
Brent Ozar: Say that the big cost of the view is joining you know, one million rows,cross-applying or cross-joining another million rows and producing billions of rows.
Erik Darling: You can’t make an index view with apply.
Brent Ozar: No, I mean, like an inner join, whatever, million times a million, inner join, one equals one. This is horrifying for so many reasons because it’s going to write four billion rows to disk. It’s going to write four billion rows, whatever space that takes in order to do the clustered index. Hannah, we like you, we want to know more about this.
Tara Kizer: It doesn’t matter how many rows the view returns when you just query the view without a where clause. Once you apply the where clause, it’s not churning four billion rows and then applying the where clause. It’s not like a linked server where it pulls all over the data first, then filters.
Brent Ozar: No, but if the view has four billion rows and you put an index on it, it’s going to take enough space for the [crosstalk].
Tara Kizer: I’ve tested index views, I’ve never ever gone down the route of deploying those to production because indexing the underlying table has been sufficient in all cases for me.
Erik Darling: Yes, unless you’re doing like really, like, you know, kind of big, involved aggregations, I’ve never seen really great returns on index views either. [crosstalk] so many rules.
Brent Ozar: Yes, it’s messy. It was for us, it was the overhead of joining tables together was so high and we were trying to get stuff down into milliseconds, but super rare.
Richie Rump: Yes, the only time I’ve had to deal with you know, four, five billion row tables, I’ve actually used table partitioning and then used the partitioning key to get into the data where I needed to, but on a view…
Brent Ozar: Somehow your audio went flawless again.
Erik Darling: Somehow.
Brent Ozar: And you’re not – I’ve seen you, you haven’t even gotten near the computer. You’ve been standing over by the couch. It’s a random number generator, maybe it’s the pacemaker that’s in your pocket maybe.
Richie Rump: It works better now.
Brent Ozar: You’re totally good. It’s flawless.
Brent Ozar: Samuel says his database size – this is about that CHECKDB with estimate only. Samuel says, “My database size is 100GB, the largest table is 90GB. My tempdb was 10GB. Don’t ask.” Yes, that’s going to be a problem. That’s going to be a problem. Especially if you have any indexes on that thing, you’re going to need – it’s not like you’re going to need 100GB, but I wouldn’t like, try to get by with 15 or 20.
Erik Darling: Yes, but read that again. His database is 100GB and his largest table is 90GB.
Tara Kizer: It’s basically one table.
Erik Darling: But I was going to say, does it have audit in the name? Like, log, what’s – because…
Richie Rump: Images, maybe? I don’t know.
Erik Darling: Because what I’m curious about is – because I see this a lot with databases of this type, where you will have that auditing or that logging table, and that table will be a heap, and that heap may have a whole bunch of empty space in it. So, I would be curious if that’s part of why that table is so massively weird, or this has some crazy like XML column in it. I don’t want to talk about it.
Brent Ozar: Mike says, “If I restore database to a temporary server to run as production for a day or two, do I need to restore any of the system databases with it?”
Erik Darling: No? I have never had to do that.
Brent Ozar: And you think about how you want to move your jobs around. Like agent jobs or stuff like that, but SSIS packages, security on them.
Richie Rump: I mean, if it’s restored to a temporary store just to do some dev testing, like unless you have objects that you rely on in system databases, then no. Even then, I would just script those out and stick them on the server.
Tara Kizer: I mean, but if you can take the downtime and you install on that second server using the same paths as the original server, stop SQL Server and copy all database files over, I mean, you know, the system databases over, and then both servers have to be stopped for this and that gives you a quick way to get all the logins and jobs and everything. That way you don’t have to script stuff out, but that requires the downtime to do it.
Brent Ozar: Srejath says – and I’m going to take this one because I know this – Srejath says, “Larry Ellison reveals self-driving database.” No. He says he’s going to build one. He doesn’t have one yet. Similarly, I would like to announce – I don’t know, I was going to come up with something. I can’t do it on the fly without being horrible. He says, “Fully autonomous Cloud database service, sounds cool, what’s your take?” My take is that it’s just like every time that Larry Ellison says things like, “My database is unbreakable. This database is self-tuning. Oracle is amazing, it’s really cool.” But you notice how it’s always surrounded by highly paid experts, you know, gently massaging it, feeding it cheese, whatever. It is a highly tuned database that requires highly paid professionals. I am sure that Larry is hearing the calls of people saying, “Come to SQL Server. Any knucklehead can manage this thing” and going, well, I better make it easier to manage. So, he’s going to paint a rosy picture of what it’s like down the road, it’s going to take years, or lots of releases to get there. But will Microsoft announce something similar? They already have. As your SQL DB, they make the same kinds of claims about automated index tuning and automated query tuning, and it doesn’t work yet.
Erik Darling: The other thing is that Larry has been making similar claims since I think, Oracle 9, that you know, doesn’t need a DBA, fixes itself, has like, you know, little settings you can change to just make everything fly right. So, you know, just another one of those things that will not happen down the line.
Brent Ozar: Samantha says, “He also says, buy our Cloud credits.” Yes, I mean, they got caught behind the game on in terms of the Cloud, it’s just like with Microsoft got caught behind with Internet Explorer. Netscape Navigator came, took out over the world, Microsoft had to backpedal fast and they were able to get past it, build out a web browser and did fine, but I don’t’ know how Oracle is going to do long-term in terms of sales, but they’ve got a fantastic database. I’m sure they’re going to do well.
Brent Ozar: Roy says, “What are we going to do when Ola Hallengren stops upgrading his maintenance solution? Like when he retires?” Great news is, he’s got it on GitHub. He put the whole thing out on GitHub, so you can make changes to it. We’ve already branched it ourselves and made changes to send back to Ola, so it’s cool. If he retired, I think we would probably be willing to take it over. It would be a lot of fun. I think that where we would fork it into like the DBA tools project, I think the community’s adopted it so much that it’ll keep going.
Tara Kizer: I felt bad when I retired from my eight solutions because I was one of the competitors back in the day. I had actually had mine out there longer but it was so hard to keep it up to date with all the versions and editions and all the various things, and eventually I gave up and we stopped using my solution at my work and deployed out all those codes to – I mean, there was about 700 servers we had it deployed to, and I knew from my emails and the blog comments that my solution had been used tons of servers around the world, and I’m just like, sorry. I just don’t have the time to do this anymore.
Erik Darling: Yes, I mean, like I’m going to be curious how Ola decides to keep up with releases. Just in 2017, there are a couple of really cool new editions to views for log files and for data files where it will tell you how much has changed. So, you can actually make a decision to flip to a full backup if enough stuff has changed. Like you don’t want to take a differential now, you’re going to go ahead and take a full backup because enough stuff has changed. So, I’m interested to see how he decides to keep up with that.
Tara Kizer: Brent’s frozen, and his audio. Maybe he’s gone.
Richie Rump: We got him.
Erik Darling: I was sitting there and I was looking at him, I was like, I’m saying something stupid. He’s just – he’s making that like school mom face. I’m like, crap, I’m saying something bad.
Richie Rump: Make fun of my audio one more time, Brent. One more time.
Erik Darling: Make fun of my video one more time.
Tara Kizer: That’s all the questions we have anyway, so we can either end the call here…
Erik Darling: All we know is that North Korea has bombed Chicago.
Richie Rump: Wow, let’s not mess with that.
Erik Darling: Well, we’re out of questions and just about out of time anyway, and I think Brent is actually silently called the end of this. So, let’s boogie people.
Tara Kizer: Alright, see you.
