This week, Brent, Erik, Tara, and Richie discuss index fragmentation, Azure disk speeds, Availability Groups, database restores, optimize for ad hoc setting, forced parameterization, BI consultants, index views, SPN registrations, and more.
Enjoy the Podcast?
Office Hours Webcast – 2017-08-23
Can I stop page splits by reorganizing indexes?
Brent Ozar: Larry says, “In Paul Randal’s 2012 article Trimming the Transaction Log Fat, he makes a case for index fragmentation slowing performance due to large numbers of page splits caused by index fragmentation. Is reorg sufficient to avoid this problem, or is this a case for rebuilding indexes?”
Erik Darling: Neither.
Brent Ozar: And why?
Erik Darling: Because that’s never going to be your problem. That’s never going to be the thing that makes your server make or break. It’s one of those, like weird edge case optimizations where, like if you have nothing to do on the rainiest day of the year, you might want to say okay, I’ll look at this; but that’s never going to be the server’s main problem. That’s never going to be what’s slowing you down the most. You’re going to have a whole host of other crap going on that you’re not even thinking of.
Brent Ozar: Yeah, look at your top wait type and focus on what the top wait type is, otherwise you’re kind of shuffling deck chairs on the Titanic.
Erik Darling: Your top wait type is never going to be page splits [crosstalk]. You could always go ask Paul on his free webcast.
Brent Ozar: That’s true, yeah, it’s every – oh wait… No, but he does have the answer. If you go to SQLSkills and you can contact – they will answer any question that you send over. It doesn’t matter how hard, you can put as much evidence as you want in there. They’ll do it all, totally for free. We will not.
Erik Darling: We will send you right to Stack Exchange.
Brent Ozar. Yes, he is super friendly and he loves answering questions like that; so he’ll be all over it. Or, if you’re on Twitter too, you can ask him – it’s probably beyond 140, you probably want to send it in an email, but yeah, free consulting from Paul via email, check it out.
Richie Rump: And now they’re going to get like 50,000 emails… [crosstalk]
Erik Darling: Let’s put their email address up on the screen so everyone has it. Free consulting.
Brent Ozar: I have a good story there, I’ll tell that…
Erik Darling: [crosstalk] You don’t have to spell Jonathan Kehayias’s last name when you send it, it’s just Jonathan@SQLSkills.
Brent Ozar: And at least Kehayias’s last name is pronounceable. I’ve heard like three times in the last two weeks where someone has said “Hola Allengren”.
Erik Darling: “Oly Allengran”…
What should I test with my new AGs?
Brent Ozar: Tammy says, “Thank you all for your generous support…” I’m sure you guys must have donated her query bucks or something. “I’m trying to come up with some test cases for SQL 2012 availability groups with two sync nodes at my primary data center and two asynchronous nodes at the secondary DR data center. I’ll be testing manual failovers and surprise restarting of nodes; what else should I test?”
Erik Darling: Network outages.
Tara Kizer: Yeah, definitely dropping a line between the two sites and see if you’ve got quorum and boding set up properly.
Erik Darling: Pulling quorum disks would be another good one.
Brent Ozar: I like that. Patching is like [crosstalk]
Erik Darling: Oh yeah, patching.
Brent Ozar: Patch Tuesday – people have a tendency to reboot multiple machines at once. Rebuild three machines and see how that works out for you.
Tara Kizer: And then your last test, pretend that the primary site has gone and do a manual failover to the other site, because you’ll have to do the different command, the force allow data loss command in order to do that failover, since primary is no longer available. But I’d do that at the end, because you won’t be able to continue testing without resetting everything else again.
Brent Ozar: And I would go the other way too. After you force failover with allow data loss, then assume that the secondary data center site is live, figure out how to synchronize going back the other way too.
Erik Darling: I would say, one of the most important thing you can do is make sure your agent jobs behave as expected when you failover. Make sure that they either – when they’re on secondaries, they’re not running or trying to run too much; and make sure that when they become primaries, that they pick up and start running as expected.
Is Azure IaaS disk speed getting faster?
Brent Ozar: Let’s see, next up, Larry says – having caught up on the last 18 months of Office Hours, he said he, “Heard a lot about Azure infrastructure as a service disk being slow. I recall Microsoft announcing doubling the speed, but I didn’t hear anything from your team about that. Is disk speed still a major issue on Azure?” It’s really funny, it was just discussed in the MCM mailing list, and people are doing tests on that and it’s still a major issue for folks. It still comes down to your VM speed or your VM type, and then the number of disks that you use.
Tara Kizer: And it’s not even just Azure. That’s just how these cloud providers are. The cheaper your company’s going to spend, the worse your I/O is going to be.
Erik Darling: I kind of like the way Google does it; where the bigger disk you buy, the faster it goes, up to a certain point.
Richie Rump: Well, I mean, that’s the same thing with serverless, right? It’s the higher memory you have, the more CPU you have. It has nothing to do with disk, but it’s the same kind of concept with that.
Brent Ozar: Tied in, it’s just one slider. You’re just moving one slider and faster as your credit card gets larger.
Are -3 SPIDs a concern?
Brent Ozar: Joseph J – we’ll just say, Joseph… Joseph says, “I’m seeing SPIDs of negative three in my database. They disappear on their own; should I be concerned?”
Tara Kizer: I was actually Googling this while we were talking to see what negative three is, because I knew that these were abnormal things. Negative two is orphaned distributed transactions, because it doesn’t know what that other thing is or something like that. But I couldn’t find anything on negative three, but it’s going to be something weird. Should you be concerned about it? I would say no, but it’s probably something weird that’s happening, so maybe contact Microsoft to see exactly what negative three type is. Because once it goes negative – I mean, these are system SPIDs, but once they go negative, I think they have very special meaning.
Brent Ozar: He says, “Deferred transaction…” Holy cow.
Tara Kizer: What does that even mean? [crosstalk]
Erik Darling: Or doing something weird, I wouldn’t – I would probably just leave it alone.
Richie Rump: [crosstalk] Upside down or what, I don’t know…
Brent Ozar: Queries coming from Australia. How would you know if it’s blocking anybody else?
Erik Darling: You could use a free stored procedure like sp_BlitzWho. Or you could use sp_WhoIsActive. You could log sp_WhoIsActive to a table. Tara knows the most about that. If you want to Google Tara Kizer, log sp_WhoIsActive to a table…
Tara Kizer: Oh yes, I’ve got a great article on that [crosstalk] No it wasn’t that one, it was the tempdb one. That was the big one.
Brent Ozar: Oh wow, yeah, the recompile thing.
Should I drop a database before I restore over it?
Brent Ozar: J.H. asks, “If I’m going to refresh a development database from production, do you just back up production and restore over development, or is it better to drop the development database first and then restore over the top of it?” I’m okay with either of those.
Erik Darling: Yeah, clean slate it away.
Tara Kizer: Actually, the recommendation is to leave it in place and restore over it because SQL Server can reuse the files. So if you don’t have IFI set up, instant file initialization, it’s going to have to – when you do the restore and the database doesn’t exist, it’s going to have to grow out those files as data files. So if IFI is configured, it probably doesn’t matter which way you go with, but it’s recommended that you database in place, restore on top of it and SQL Server can reuse that allocation, that disk allocation, I believe it is.
Erik Darling: Can you do that with a log file too? That would be an interesting test.
Tara Kizer: Probably, I bet you it can. So that’s probably faster since IFI doesn’t [inaudible].
Erik Darling: Or, if like the log file is bigger, can already use this part, and then…
Tara Kizer: Yeah, I wonder.
Erik Darling: Just grow a little bit, rather than the whole thing. I want to say that it can do that because that would be so cool, but then I think about all the other things that I think will work because they’d be so cool – I’m like, no never mind.
Richie Rump: I would also ask your devs and your testers if they have any data there that they want to keep, because maybe they’re using for test runs and things like that that they haven’t scripted out. I’ve never been a big fan of moving production over to a dev environment. One, you have a problem of, now I have production data, which may need be encrypted or removed or whatever, I’ve got to handle that. But the second thing is, I now have some other test stuff that should be in there that probably is not in the production database. Some outlying test cases or things that shouldn’t happen but we’re testing for anyway, those type of things really need to be in the database and it probably won’t be in your prod. So I’m not a big fan of going over production, but that’s another level of development that you need to hit and work towards. It’s a tough thing to get to, but it’s worth it when you get there. [crosstalk]
Erik Darling: That’s the developer’s responsibility.
Richie Rump: Oh absolutely, absolutely a developer’s responsibility. But if the DBA says, I’m not restoring production, then they’ll get on that, right.
Tara Kizer: So have a separate environment for this restore, not over dev is what he’s saying. Still, do it, but not over dev.
Erik Darling: Or just have like a post-deployment script that will create the stuff that developers need.
Richie Rump: Exactly, yeah.
Brent Ozar: I’m also a fan of just telling management, alright look, if we restore from production over into development then anybody can take all our data and sell it to, how we say, the Chinese. I don’t know why I say the Chinese; I should say the Russians these days, seems to be sexier. Or Google, whatever.
Brent Ozar: J.H. says – he follows up with, “A recently strange thing is that I used to restore over a database, but weirdly a couple of tables were not in sync. Was that maybe a bug in SQL 2014?” No way…
Erik Darling: Don’t blame it on bugs.
Tara Kizer: No, you did something wrong there.
Brent Ozar: We won’t tell anyone, J.H., whose full name is…
Is Optimize for Ad Hoc ever a bad thing?
Brent Ozar: Hannah asks, “Optimize for ad hoc – other than – is there any time when this is a bad idea to turn on, other than when all of your queries are ad hoc? When shouldn’t I turn it on?”
Tara Kizer: Kimberly answered this question in 2014 at PASS. I remember it specifically because someone was asking – it was either 2014 or 2011, but the only edge case – she said, I think it was [inaudible] had come up with the edge case, but Kimberly talked about it. She said the only time where you wouldn’t do this is when all queries are going to be used twice and only twice. So it’s either – usually, a query’s going to be used once or multiple times, but if all queries are only used twice and never again, then don’t enable this, because there’s no benefit to it at that point.
Erik Darling: I mean, one thing I kind of hate about that setting is it messes – well it doesn’t mess up BlitzCache, but it doesn’t give you a plan that you can analyze. So if you run a query and you’re like, I can’t figure out why it was slow this one time, but it’s just a stub and you can’t do anything with it. There’s no information about it, which kind of sucks.
Brent Ozar: I’ve never seen a time where I go, I turn this on and my server is suddenly okay; like this was the thing that crossed the finish line. I’ve seen a lot of people turn it by default, and I don’t have a problem with it, but it’s just I’ve never seen somebody go, oh thank God we turned on optimize for ad hoc, now everything’s fast again.
Tara Kizer: We actually had that come up three jobs ago. So, I wasn’t the primary DBA on it, so I don’t remember the exact system, but it had a massive procedure cache and enabling this resolved an issue there. It was one specific system, and I’ve supported over 1000 servers in the past.
Brent Ozar: And usually when it is, it’s that some bozo is building strings continuously…
Tara Kizer: Strings are – this was a third party application, so who knows.
Erik Darling: That’s what something like forced parameterization would help.
Tara Kizer: Exactly, I don’t know that that option was available. Do you guys know what version that came out in, or has it been around for a while?
Brent Ozar: 2005, I think…
Tara Kizer: This probably was a 2008 system, I bet. And maybe we just didn’t know about it. Honestly, I didn’t know about forced parameterization until maybe last year with you guys.
Brent Ozar: One of those hammers that you hardly ever see used, and when you need it, it’s amazing.
Tara Kizer: This has solved, I think, three recent clients of mine. One was massive performance improvement and then two other ones it was noticeable.
Brent Ozar: Wasn’t one of them – it was like 80%, 90% CPU and it dropped to like nothing?
Tara Kizer: It dropped to under 50 and they were – they were so excited because we did it right then and there. I think that one was late last year. That was the one that it resolved their primary pain point, which was awesome. And of course, I was the one on the call so I got all the credit. You guys were the ones that told me to go do it, but I’m just the one on the call.
Brent Ozar: [crosstalk] The force, you’re now knighted with amazing…
Tara Kizer: That’s where I learned about it; it was right then.
Brent Ozar: Richie just sent over in Slack, your PC ran into a problem so Richie has disappeared.
Tara Kizer: I had to go through an hour of updates yesterday, it was horrible. And then my – so that was on my laptop, and then my surface did it for like an hour and a half or something. So there’s some kind of massive update [crosstalk]
Erik Darling: Was it Creator edition of Windows…
Tara Kizer: Creator. Did that get forced on everybody?
Brent Ozar: Yeah.
Erik Darling: They keep trying to get me to install it on my desktop server. In fact, I had to use Windows 10 on my fun desktop server because Windows Server 2016 didn’t have the right driver for my network card. So my network card would not work with Windows Server 2016. It worked beautifully with Windows 10. And I searched high and low for this damn driver.
What are you doing with next year’s hardware budget?
Brent Ozar: I have to ask now too – there’s a bunch of questions in the queue but I’m going to interrupt and ask one of my own. So, because speaking of your desktop, so our hardware budgets are coming up – I say coming up, like I’m the kind of person who shops at [inaudible] – have you guys thought about what you’re going to do for your hardware budgets for next year?
Tara Kizer: I was actually just on Amazon today thinking about it, because I thought about it on the cruise, I think I’ve got a bit of money left over, because I only bought my office chair, so there’s a little left over money. So I was just going to browse the electronics section and see what’s out there. I don’t know. I probably need a desktop, but I don’t know.
Brent Ozar: Works fast enough…
Erik Darling: So for me, I think I have like 500 bucks left on this year’s… Brent’s like, what, you’re draining that thing dry… But I think ideally what I’d like to do is replace my two 27s with one big monitor and then get one of those desk-risers, so I can have like a modified standing desk, because I need the L shape… sort of how my office is to keep all my crap on, and I don’t have awesome feng shui office like Brent does. So I’d probably get one of those desk risers so I can just boost everything up and just have one monitor, so I don’t have to worry about …
Brent Ozar: I thought, for sure, when you said – I thought you were going to get like Tim Radney has, another set of racks up above your monitor so you could have…
Erik Darling: It’s going to be like an 80s drum set in here, just monitors.
Will you present remotely for user groups?
Brent Ozar: Let’s see here, Larry asks, “Some other high profile DBAs…” He’s talking about Paul and Kim, “Have offered time for local PASS chapter presentations. Is that something you guys do as well?” Yeah, absolutely. Just go to BrentOzar.com and click contact up at the top, and you can send in requests for presentations. Keep in mind, we usually only do them either during working hours or stuff that’s close to working hours. We have different working hours on the coast, so that makes it a little bit easier for some folks, but it’s tough for us to abandon the families too late at night. Plus I’m also trying to nicely say I go to bed at 7:30…
Tara Kizer: Grandpa Ozar… [crosstalk] can confirm.
Know any good BI consultants?
Brent Ozar: Next up, Eric says, “We’re looking at implementing an Enterprise-wide BI offering. We’re currently looking at the Microsoft BI stack. Can you recommend a consultant resource that has knowledge of the entire BI stack?” He says, “Adam Saxton comes to mind.” Not the whole stack. Like I talked to Andy Leonard for SSIS, Steph Locke for the analytics part of it, like R, Python. Analysis services is ProData.ie out of Ireland. It’s Bob Duffy out of Ireland. Is there anybody else? reporting services, Jessica Moss. Who else, any other BI consultants we know?
Erik Darling: Oh god…
Brent Ozar: Well he doesn’t do BI anymore…
Erik Darling: Scottish…
Brent Ozar: Jen Stirrup.
Erik Darling: Jen Stirrup, there we go.
How should I mask production data in development?
Brent Ozar: Let’s see here, next time – M.M. says, “As far as production data in development, do you have any suggestions for masking software, such as Delphix?” We do have – if you search for masking on our site, I have a post about why masking is so hard and some of the things you should look for. So check out that post first.
Where are you located?
Brent Ozar: Next up, Larry asks, “Where are you located?” I’ll let each of you guys answer that.
Erik Darling: Parts unknown.
Tara Kizer: I’m in San Diego, California.
Richie Rump: Miami, Florida.
Erik Darling: New York-ish…
Brent Ozar: And Chicago. Larry also says, “Seeing you guys for the first time after only listening for a long time. Two things were a surprise: Tara is much younger than I was expecting…”
Tara Kizer: I was just replying to him because I wasn’t sure you were going to get to that one.
Brent Ozar: Oh, what you say?
Tara Kizer: You know, we’re all around the same age. I do look young but I’m not, you know. I’ve been a DBA, full-time DBA for almost 20 years. You can do the math…
Richie Rump: She started when she was nine.
Tara Kizer: I became a full-time DBA in 1999. I did graduate from college though.
Brent Ozar: He says he, “Had the impression that Richie was of Chinese heritage.” Yeah, so now that you’re on the webcast, you understand that he’s Italian, but it’s hard to see, that’s all.
Richie Rump: Totally Italian, maybe a little bit Russian, you just never know.
Do I need schemabinding for indexed views?
Brent Ozar: J.H. asks, “Is it correct that if I’m going to create indexes on a view that the view also needs with schema binding?”
Erik Darling: Yes.
Tara Kizer: Yes, and a whole bunch of other rules.
Erik Darling: There’s no way around that. There’s no top, no outer joins, no cross apply. If you want to – any columns that you want to aggregate can’t be NULLable. There’s just so much crap with indexed views.
Richie Rump: I always thought schema binding was a good idea anyway on views.
Erik Darling: And functions.
Any risk to adding SPN registrations?
Brent Ozar: Larry says, “Are there any risks to adding SPN registrations for existing database engines that are running with active directory service accounts that didn’t have the permissions to register their own SPNs?”
Tara Kizer: No, I’ve had service accounts that did not have the permission. We had to beg and beg and beg to get it eventually, and sometimes we just see errors in the error log that couldn’t register and SPN issues. [crosstalk]
Erik Darling: So glad I don’t have to deal with that. Because you’d always get those emails in the middle of the night and you wake up and be like … Oh it’s that, I’m going back to bed.
How is ARITHABORT messing up queries post-restore?
Brent Ozar: Doug says, “I have a development SQL Server environment where production is restored every night. In some days, in development, we receive insert, update and delete failures due to set options have incorrect settings, like ARITHABORT. The application isn’t changed, I swear, only the database. What might be causing this for some restores and not others?” Wow…
Tara Kizer: I don’t know that this is a restore issue. I wonder if it’s just – it sounds like an application issue to me.
Brent Ozar: And maybe it is – I wonder if, if the application is connecting and when their database isn’t available they’re ending up in the wrong database, maybe? And they have the wrong set ARITHABORT sessions in there maybe, or their connection gets reset and doesn’t set the right ones? I like where you’re going with that, but I can’t think of where I would do that either.
I want to do a linked server to Azure…
Brent Ozar: And then the last question in the queue, Michael says, “I have a linked server from on premises to Azure.” And he’s, “Getting an error that Access to the remote server is denied because no login mapping exists. I tried a provider string thing and I had no luck.” We don’t have any luck there either.
Erik Darling: We are luckless.
Brent Ozar: I would post that on DBA.StackExchange.com. I don’t think there’s a lot of people doing that, the linked server piece, because man, as Jeremiah used to say, hello latency.
Tara Kizer: I mean, these servers are slow enough just to another server that’s sitting next to it. Just imagine going to the cloud, wow, nope.
Richie Rump: That’s about just as fast as I drink my wine.
Tara Kizer: Yes.
Richie Rump: Which is very slowly, everybody, very slowly.
Tara Kizer: Richie is not on the same caliber as us.
Richie Rump: No, and I don’t even try to keep up.
Tara Kizer: Right, doesn’t want to be.
Erik Darling: Well it’s good, because someone’s going to have to be the liver donor in this company.
Tara Kizer: A couple more company retreats and we’re in trouble.
Erik Darling: Staring at Richie like he’s a buffet, like I’m going to take that part. The kidney…
Brent Ozar: We should probably motivate him to jog and eat healthy too, just so he has some real healthy organs along the line.
Richie Rump: Yeah, can I have a second glass of wine? No, you cannot because I need that liver later.
Microsoft says 90% migration to the cloud by 2025…
Brent Ozar: Good living. Larry says, “I just heard that Microsoft’s newest corporate conference has almost no mention of on premises. Instead, they’re defining the IT marketplace as just cloud and Azure. And also, they predict 90% migration to the cloud by 2025. Comments?”
Erik darling: They were high as hell.
Richie Rump: That’s exactly what they want. They want that…
Tara Kizer: They want that, that’s why they’re not mentioning on premises.
Richie Rump: They want you to – hey, we want that constant money flow coming in. pay us a little bit, pay us a little bit, pay us a little bit. That’s exactly where they’re going. If you notice, over the past six months to a year, the dev advocates for Azure, they’ve been hiring like mad. I mean like insane; so you could tell that they’re really ramping up and trying to get more people onto the Azure bandwagon. I think it’s a great platform. I don’t really have that big of a problem with it. But if I have a legacy application that I spent three million dollars building and I’m a small to medium sized company, am I going to spend that effort now, anther three million dollars to dump it in Azure? Probably not; it works, why rewrite it?
Erik Darling: If there was a cloud in that room, I bet I know what it smelled like.
Brent Ozar: The other thing, I mean, their job is to market. I mean, they need to market things so they want to teach things as if they’re already happening. Artificial intelligence is taking over everything, you won’t need staff anymore. Put your 20s in the front of the machine, you know. The same thing they used to say, SQL Server is a self-tuning database, you won’t need any help, things will just work. You’ll put in queries in raw English and the answers will come out the other side. You know, it’s [crosstalk].
Erik Darling: Think about what you want and I’ll give it to you.
Richie Rump: Does anybody remember RAD Development? Rapid application development. Was it fast? Not really, it took the same amount of time.
Erik Darling: Did anything get developed? No. Ulcers, maybe.
Brent Ozar: Yeah. It’s not that we don’t believe in the cloud. We’re huge proponents of the cloud, it’s awesome and fantastic. You’ve just got to take some of those things with a grain of salt.
Erik Darling: The cloud has great fits for a lot of different applications in a lot of different situations, and I’m totally cool with it. Take the DBA responsibility away from people who don’t want to be DBAs, who can’t be DBAs, they have other things to do. Go with it, that rules, but it’s not realistic to expect the entire world to shoehorn into Azure’s cloud or Microsoft’s cloud or any cloud. Some people need that physical landing space.
Should I use four part names in stored procedures?
Brent Ozar: Let’s see here, Larry says, “We have a vendor that uses four port names in stored procedures.” For those of you who haven’t seen, that’s server and database and schema and table. “We installed the app using a SQL alias, but then the references used the alias. I added a second server definition, linked servers…” He’s got all kinds of questions in there. I would just push back on the vendor and go, what the hell are you doing with four port names? Seriously, what sense does that make? That is horrific. And if you want, send examples of those queries to us and copy the vendor and just go politely, hey, what might be the drawbacks of this. And then we can elaborate on it. If you’re comfortable doing it publically, you could post it at Stack Exchange and then you’ll see, but that’s tough to do with vendors.
Erik Darling: I would say that what that reminds me of is a question that a stumbled on when I had food poisoning a couple weeks ago, when you asked me what could cause the same stored procedure to have different plans, and all those different names and references would have all sorts of different plans attached to them because that’s different, it would have different hashes.
Brent Ozar: Alright, well thanks everybody for hanging out with us. There were a few long questions we didn’t get to, like multi-paragraph questions. Go ahead and post those over at DBA.StackExchange.com. You’re going to get amazing answers out there because you put the time into building a really big clear question. And then we will see you guys next week at Office Hours. Adios everybody.
Wanna join us at next week’s Office Hours and ask your own questions? Just put in your name and email address.