[Video] Office Hours 2016/07/27 (With Transcriptions)

This week, Brent, Richie, Erik, and Tara discuss speeding up performance, how to make SSRS highly available, what to do with SSIS packages when making upgrades, Windows Core, and how to hide data from DBAs when they have SA access.

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-07-27

 

Should I use a covering index or an indexed view?

Brent Ozar: Shaun asks, “I have a number of tables that get loaded every night in an ETL process. Most queries only use a limited number of columns and rows from these tables. Should I use a covering index, an index view, or just put that data in a separate table?” Oh, it took me a minute. I had to read it a couple times. So most of your queries only use a few columns in a wide table. So how do you go about making that kind of stuff fast? I should start picking people, and Tara is reading it, so I’ll pick Erik.

Tara Kizer: Yeah, I’m still reading it.

Erik Darling: So an index view doesn’t really help you there. There’s a lot of rules around index views and it’s just hitting a single table. Index views really shine when you need to sort of aggregate stuff and go crazy. So I wouldn’t recommend an index view there just because of the added complexity. Also, there’s a lot—well not a lot of—but there is some overhead when you insert or modify in index view. We have a blogpost about that hanging out somewhere on the site I believe Kendra or Jeramiah wrote it. But there is that. So for you, my good man, I’m going to go with a covering index to speed up those queries. And if they really only use a limited number of columns and rows, you may even be able to get away with a filtered index to further drop down the amount of indexing you have on this table that’s getting cabobbled every night.

Tara Kizer: I was going to suggest a filtered index as well.

Erik Darling: Ha, I’m awake this week.

Tara Kizer: If it’s the same rows at least amongst most of the users.

Erik Darling: Yeah. There are some funky rules around filtered indexes too and we have many blogposts about those on the site as well so give those a read through if you’re interested in more.

 

100% CPU, ASYNC_NETWORK_IO, PREEMPTIVE_OS_PIPEOPS, Oh My

Brent Ozar: Breck says—Breck just throws a whole bunch of metrics out. It’s almost like he’s playing metrics bingo. He says, “100 percent CPU. Top wait. ASYNC_NETWORK_IO 40 percent. PREEMPTIVE_OS_PIPEOPS 31 percent. Would this indicate an application load spike?” Wow, that’s kind of tricky.

[Laughter]

Richie Rump: I don’t have my Magic 8 Ball here. I need it. Where did it go?

Tara Kizer: If SQL Server is waiting on the app, for ASYNC_NETWORK_IO, almost always means SQL Server is waiting for the application to tell it to keep sending rows. The application is doing row-by-row processing. But if SQL Server is just waiting, it’s not turning through CPU during that time, I mean it’s just sitting there waiting, holding the data—I don’t know, are you sure that it’s SQL Server?

Brent Ozar: Yeah, I like that. Could be something else burning the CPU cycles.

Erik Darling: Yeah.

Tara Kizer: And the preemptive OS is external, it’s an OS process for that preemptive wait.

Erik Darling: Yeah, so one interesting thing I’ve seen is that PREEMPTIVE_OS_PIPEOPS sometimes correlates to people using xp_cmdshell for stuff. So if you see high waits for that and someone is using xp_cmdshell, you could be waiting on xp_cmdshell to fire off some other application on some other commands to the OS. So I’d be careful there as well.

Brent Ozar: Good point.

 

Is 140MB usage in TempDB a problem?

Brent Ozar: Shaun says, “I have a SQL 2012 R2 standard cluster. It’s got about 250 gigs of databases. Tempdb has got eight files, 16 gigs of space. During business hours, tempdb only uses like 140 megabytes, is that a problem?”

Tara Kizer: It is not a problem. You could probably shrink down your files but 16 gigabytes is really small. Disks are cheap, right?

Brent Ozar: If it’s a problem, if you would like to fix it, just go create a bunch of tables or queries that dump stuff into tempdb. That problem will be solved right away.

 

Idle Chit-Chat

Brent Ozar: Next up, we have J.H., asks, “Have you had a chance to check out super soldier Captain Randy Cramer as he looks and sounds like your twin clone?” I have no idea who that is, so the answer is no. Immediately Tara goes and takes her headphones off so she’s got to have like a super soldier Randy Cramer toy or something like that. No—is that true? No, she’s not. Tara, I thought you were going to come running back with a super soldier Randy Cramer thing.

Tara Kizer: No, sorry. I was turning off my fan in case that’s why my audio is bad.

Brent Ozar: Funny. Wes says, “Tara sounds like she’s in a bottle.” That’s because she is in a bottle. She lives in one of those build a ship bottles, it’s just a really large bottle.

Erik Darling: She is a resident SQL genie.

Tara Kizer: Does it sound better now?

Brent Ozar: No, I think it’s some kind of like USB audio thing. It’s like echoey but it might be that your gain is up too high or something.

Tara Kizer: I have no idea what to do.

Brent Ozar: Yeah, I know. It’s not that bad. It sounds fine, it’s not that bad. It’s like you’re literally in a bottle.

 

How do you make SSRS highly available?

Brent Ozar: Let’s see, Tommy says—Tommy is like [inaudible 00:04:24] initials champion here. SSRS, SSIS, HA, SQL 2016. All right, “Can you recommend articles to provide a good way to accomplish HA with SSRS?” How do you guys make SSRS highly available?

Erik Darling: I use Tableau.

[Laughter]

Erik Darling: Get my own Tableau server, hang out with that.

Brent Ozar: That’s just mean, man. That’s like Power BI, everybody then has Excel at that point, it’s highly available. So SSRS is not cluster aware, so what you usually do is you stand up a bunch of little cheap VMs because SSRS also isn’t really resource intensive. A bunch of little cheap VMs and then you throw a load balancer in front of it, an F5, an IP… I’ve forgotten what the IP deal is. Then you just round robin across those. If one of them dies, who cares, it doesn’t matter. Licensing isn’t cheap on that but when you’re licensing VMs at the host level then it’s not quite so bad.

 

Is it common to see 8-10 instances on a 3-node cluster?

Brent Ozar: Ryan says—I need to take a deep breath as I ask Ryan’s question—“Our normal configuration is a large three-node cluster with about eight to ten instances setup as failover clustered instances.” I can already see us all kind of like itching away from the webcam.

Tara Kizer: I have him beat on that by the way, I am embarrassed to say.

Brent Ozar: What was your typical when you were dealing with stuff?

Tara Kizer: No, no, I have him beat as far as number of instances on a cluster. I had a four-node cluster with 11 instances. It wasn’t even the worst I saw. It’s the worst I implemented. I was forced to do this.

Brent Ozar: “I was young. I needed the money.”

[Laughter]

Brent Ozar: He says, “I’ve been told by a few different people that this configuration isn’t very common.” You have now been told by four more people. “Some vendors have scoffed at it. Is it really that rare in favor of say a two-node with a single default instance that runs as active/passive?” Think of it as like a bell curve. Normal people are out there and you’re definitely on the edge of the bell curve. It’s fairly unusual.

Tara Kizer: Yeah, I mean the reason why I hated it back then is because we were on SQL Server 2005 and in order to install anything, SQL Server service packs, whatever, all four nodes had to be in agreement that didn’t have to reboot again. It was insane to have to install a new instance or a service pack or hotfix. I had to reboot all four servers, usually like five times each just to get the installation to finally agree, okay, all four nodes are finally fine. It was horrible. But starting with 2008, you could do one node at a time. But yeah, what’s the purpose of having eight to ten on a three-node cluster? Why not just have several two-node clusters instead?

Brent Ozar: It’s a party.

Tara Kizer: Or, do you have duplicate database names, is that why you’re having to spread out across SQL instances? Why not just put them on a three-node cluster, use two SQL instances and then have all the databases from all eight to ten on those two?

Brent Ozar: Wow, you just blew my mind with the duplicate database name thing. I’m like, why would anyone ever do that—then [makes explosion sound].

Erik Darling: Oh, yeah. Yeah, because we’ve dealt with a couple of clients who use vendor products who all have their vendor name as the database name and they’re like, “No, we can’t separate things out. We can’t do these things because everything is going to this database.” It’s like, ugh, why?

Brent Ozar: That’s incredible.

 

Why does sys.dm_exec_requests show suspended?

Brent Ozar: Greg asks, “Why does sys.dm_exec_requests return a status of suspended when a query is still executing?” That’s a really good question. The first thing I can think of is like a BEGIN TRAN. Somebody does a BEGIN TRAN, executes something, and now we’re suspended. What else would do it?

Erik Darling: If it’s being blocked by something else, would it be suspended? Or is that still runnable?

Brent Ozar: Oh, I bet you’re right, I bet if it is blocked.

Erik Darling: Yeah, that would be my first guess.

Brent Ozar: I like that.

Erik Darling: Because it’s like, suspend is like when it’s waiting on a resource or something else to go through I think.

Brent Ozar: Oh, he says, “It’s in a query that runs in a restore.” Oh, I wonder if it’s waiting for file activity? Or if it’s part of like, I don’t know, or it could be extended store procedures. I wonder if it’s doing something preemptive?

Erik Darling: Yeah, I would make sure if it’s during a restore, I would make sure that I have instant file initialization turned on to limit the amount of things that I would have to wait on during that process. You do that by giving the SQL service account perform volume maintenance tasks permissions and the security policy user rights assignment, secpol.msc, for the curious.

Brent Ozar: Nice.
Brent Ozar: Scott sends in a URL for super soldier Randy whatever-it-was, Randy Cramer…

Erik Darling: I ain’t clicking that.

Brent Ozar: …who claims to have spent 17 years on Mars and spending three years serving on a secret space fleet. You know, we do look a lot alike in this webcam and his picture. I do have to give it to you. Suddenly, I feel like I need to put on a nicer shirt. J.H. says, “After—”

[Erik laughs]

Brent Ozar: You looked at the picture, didn’t you? We are disturbingly alike. That’s totally going in the webcast notes. I’m going to reply to all just so everyone can see it. “This is Randy…” So then everybody can go click on that link. So it’s now in the answered questions for us.

 

Why can’t I shrink this file down from 5GB to 2GB?

Brent Ozar: J.H. says, “After I shrink a datafile to its minimum size…” Stop doing that. “I.e., five gigs within the Management Studio GUI, how come the space used is smaller afterwards?” Oh, wow. So he did a shrink to five gigs in the GUI but the space used… man, I had to read that three times to understand what’s going on. Let me rephrase J.H.’s question. So he’s got a file and when he used the GUI it says you can only shrink it down to five gigs. “But when I look at the space used, it’s only using two gigs, why can’t I shrink it down further?” I bet it’s a log file.

Erik Darling: Yeah, so check in sys.databases and see if there is a log_reuse_wait_desc. So just like look like database name and log_reuse_wait_desc from sys.databases and see if you’re waiting on something weird. Like if you used to have replication but you didn’t remove replication right, it could be waiting on that. If you used to participate in mirroring, if you haven’t taken a log backup, there are a lot of reasons why it may not be. You could also just try running a manual checkpoint and seeing if you can shrink it down if the database is in simple recovery.

Tara Kizer: Why are you trying to go from five gigabytes to two gigabytes is my question. That’s just ridiculous. I can understand if it was 500 gigabytes and maybe you could get it down to 200 gigabytes, but from five to two, that’s just—it just seems like a waste of time to me.

Brent Ozar: He’s running SQL Server on a Raspberry Pi, he’s only got so much space left on his thumb drive. He needs to free that up.

 

What should I do with my SSIS packages when I upgrade from 2008 R2 to 2014?

Brent Ozar: Anker says, “Hi, I am Anker.” Hello, I’m Brent. Actually right now, all of us are Brent. “We are upgrading SQL 2008 R2 to 2014. We have some SSIS packages. Should I open them in SQL Server 2014? I’ve heard that if I open them in 2014 it’s going to update them to 2014. What should I do with my SSIS packages if I’m doing this big, fancy upgrade process? Should I move them to the 2014 server or leave them on 2008 R2?”

Tara Kizer: If you open them up in Visual Studio that’s when in the newer version of the tools, that’s when it would upgrade. And you don’t have to save those changes. But I mean, where are you opening up these files? If you modify your SSIS packages using Visual Studio 2008 and just change the data source to point to the SQL 2014 new SQL instance, save that, and you’re good to go. Just deploy that 2008 package to wherever your 2008 SSIS repository is.

Brent Ozar: This is one of the reasons I like having a separate server for it too.

Tara Kizer: Yeah.

Brent Ozar: Build your own SSIS server, leave it in a corner, do whatever you want with it on a separate version upgrade plan.

Erik Darling: This is one of the reasons I like having Tara, because I still haven’t opened SSIS.

Tara Kizer: I used it quite a bit at my last job for [inaudible 00:12:07] type tasks.

Richie Rump: I’m systematically forgetting all of my SSIS information.

 

How common are database snapshots?

Brent Ozar: Ryan says, “I’m on database snapshots. Have you guys used database snapshots? How common are they to see out in the wild?”

Erik Darling: Very uncommon.

Tara Kizer: I’ve used them.

[Speaking at same time]

Tara Kizer: I used them a couple of times. I think on Twitter or on the [inaudible 00:12:32] mailing list there’s people that really like to use them and revert to them if they run into problems with an upgrade. We used them on the asynchronous database mirror for some internal users to run their horrible queries against it so they wouldn’t touch production.

Brent Ozar: Yeah, I’d have to say out of every SQL Server I’ve—we have a warning in sp_Blitz that tells us when there’s database snapshots and I can’t remember the last time I saw that warning. But that probably has more to do with the fact that people call us when they’re having performance problems and they probably delete the snapshots in order to avoid performance problems.

Tara Kizer: I’ve seen it a couple times in Blitz and then I immediately, I just ignore them, because there’s really no issue with having a snapshot. I mean as long as you’re refreshing them, you’re not just leaving them out there.

Erik Darling: Yeah, I’ve thought about using snapshots on a couple occasions for things but my main kind of gripe with them was that there are point and times when you create them and then if you want to keep refreshing them, then you have to create a new one and sort of drain users over. It just seemed like way too much of a process for what I was getting out of it.

 

More About Shrinking Databases and Rare Steaks

Brent Ozar: J.H. says, follow up to his shrink question, he says, “The database was actually 80 gigs in size and he was shrinking it down to five gigs.” Five gigs is good. You can stop there. That’s cool.

Tara Kizer: Yeah.

Richie Rump: Yeah.

Erik Darling: Yeah.

Tara Kizer: Yeah, that makes more sense.

Erik Darling: I have pictures bigger than that.

Tara Kizer: It’s all the detail on the tattoos.

Erik Darling: Yeah, pretty much. I get real close up on those food pictures. I want Brent to see every grain.

Richie Rump: Every spice.

Erik Darling: I want Brent to be able to see my steak’s thoughts when I take a picture of it.

Brent Ozar: Because it’s still alive too.

Erik Darling: Yeah, it still remembers its name. That’s how rare it is.

 

How common is Windows Server Core?

Brent Ozar: Jeff says, “Have you any of you guys stood up a SQL Server instance on Windows Core?” I’ll raise you guys on that question. Have you ever used Windows Core?

Tara Kizer: I have.

Brent Ozar: Tara has, yeah.

Tara Kizer: It was a proof of concept, it was three jobs ago, and as part of our review cycle we had to have these goals. My manager set a goal to on a Windows Core server install SQL Server, figure out all the different commands you’d have to run to say set lock pages in memory, everything that we would normally do. I went and did that and then the Windows team just wasn’t able to support it yet in our very large environment. We had like 700 SQL Servers at this job. So yeah, we had this proof of concept. I suspect that that job now has Windows Core deployed because that was a big push due to having to patch Windows every month. If you get on Windows Core there’s less patching, you might even be able to skip some months. It was painful.

Erik Darling: Was that the patching next Tuesday people?

Tara Kizer: It was, but it was a different division. So the one division I referred to was the Thursday after and this other division was the following week.

Erik Darling: So from proof of concept to poof goes the concept.

Tara Kizer: Yeah, I think it was on Windows 2012 and it wasn’t as good then. Was Core available on 2008 R2 for Windows? I can’t remember.

Brent Ozar: I think so.

Tara Kizer: Maybe that’s what it was, it was just a lot harder. You didn’t have any GUI whatsoever. On the new stuff, you can bring in some of the GUI.

Richie Rump: Yep.

Erik Darling: It’s just Microsoft’s attempt to be more like Linux.

Brent Ozar: Yeah. I taught a class on it. Dandy Wey of Microsoft was like, “I need someone to proctor something at TechEd, can you teach it?” I’m like, “Yeah, sure, what is it?” “Windows Core.” “Dang it.” I’m like, okay, all right, I’ll learn it. I go and learn it and the whole class—it was all an interactive lab where all the attendees had their own SQL Servers. The whole class, all the Q and A consisted of people raising their hand going, “Can you tell me how to do this in Core? Can you tell me how to do this in Core?” “No, I don’t know, sorry, I don’t know. I have no idea.”

Erik Darling: “I can only tell you what’s in the book.”

Brent Ozar: Yes, I felt like that. You know, like you always go to New Horizons or Global Knowledge and the instructors have no idea how to do anything. I’m like, I’m that guy. I’m that guy. Ugh. I even asked, “Is there anyone more qualified to teach this?” “No, no one else wants it either.”

 

Is it safe to apply all up-to-date Windows patches?

Brent Ozar: J.H. asks, “Way back, Microsoft had issues with a couple of Windows patches which caused Java security connectivity. Do you know if they’ve fixed this and if it’s safe to apply all up-to-date Windows patches?”

Tara Kizer: Just by the question, it says “way back” and now asking can he apply the Windows updates. If there was a bad patch out there, I think the one that you’re referring to that we did encounter and it was fixed like within the next week or two. So I mean if you’re way far behind on Windows updates, yeah, you can go ahead and roll forward all those patches. I can guarantee my other job have installed them all.

 

How do you hide data from DBAs who have SA access?

Brent Ozar: Anker asks, “What’s the best way to hide data from DBAs when they’ve got SA access?” Oh, I love this question so much. So if someone has SA access, how do you hide data from them?

Richie Rump: Put it in Mongo.

Brent Ozar: Hides it from everyone.

Tara Kizer: Encrypt it. Other than that, there’s really no way that you’re going to be able to hide from us. We can get in.

Brent Ozar: And when you say encrypted, elaborate more on what you mean.

Tara Kizer: I mean either application encryption or is it transparent data encryption, scramble the data for us.

Brent Ozar: And TDE, we can still read it. TDE only encrypts it on disk.

Tara Kizer: That’s true, yeah.

Erik Darling: You could try Always Encrypted but there are so many holes in that and ways around the Always Encrypted columns that that’s not even practical…

Richie Rump: Encrypt it before it lands to the database. Salt it, and you’re good.

Erik Darling: And pepper.

Brent Ozar: A little eggnog in there, some cardamom. When you talk about doing this in the application layer, is it like easy to do for developers? Or is this something that there’s a bunch of different approaches to do and people have to figure it out?

Richie Rump: You know, I just had to do this in Node, which is JavaScript. Literally, it was installing a package and then calling one function and everything was encrypted. I was using it for a checksum but the concept is essentially the same. The encryption has gotten a lot easier, as before, I was doing it 20 years ago and there was a lot of stuff we had to do. Now, there’s packages that really help us out to do this a lot easier than we used to have to, so yeah, it’s fairly easy. All the base encryption loggers are in any major language. You should be able to do it without a problem.

 

Wait – why would you want to hide data from DBAs?

Brent Ozar: Wes asks, “Why would you want to hide it from DBAs? They should be trusted with all the data.” Here’s the deal, Wes. You didn’t spell trusted correctly. So if I can’t trust you to spell the word trust correctly, I don’t know that I can trust you. We’ve all been in really weird high security type environments so it would be interesting to hear. My approach with that was usually the developer stored stuff unencrypted in the database, it was stored unencrypted the whole way through. Management wasn’t happy about that. Management didn’t trust any of us but they didn’t have a fast way of getting everything encrypted quickly. So it wasn’t just that they didn’t trust the DBAs, they didn’t trust anybody, especially you know somebody rolls in in a really nice car, like DBAs, and they’re like, “Where did you get the money for that?”

Erik Darling: Beep, beep.

Brent Ozar: “Because I sold the data to the Chinese.” Steve says, “Throw money at the DBA not to look at the data.” Man, we’ll not look at the data all day long for free. Tell us what your data is, we promise we won’t look at it. Dollar a month, that’s cool.

Erik Darling: It is safe from us.

 

Should I use a server level trigger to alert about sysadmin role changes?

Brent Ozar: J.H. asks, he says, “Sorry, I asked this a few weeks ago, but I missed the answer.” All right, so what I’m going to do is I’m going to wait like 60 seconds to see if you stick around just as a test. He says, “Are there any things to be aware of, such as downsides, when I’m implementing a server level trigger that emails the DBA team when the sysadmin role gets modified?”

Tara Kizer: I like it. It sounds good. Because you’re not monitoring an application table here, it’s sending an email. It’s a server level trigger, how often is a sysadmin role getting modified. I’m not sure that this is the approach that we took. We did a lot of auditing at my last job making sure that Windows administrator group, someone didn’t slide into there, the sysadmin role, things like that. We just used PowerShell and queried the data. We just queried it say every hour or every five minutes. We didn’t set up a trigger on the instance.

Brent Ozar: If I had to do it for an auditor, I might get nervous because anybody who knows enough about this to go hack you knows enough to disable your trigger.

Tara Kizer: That’s true.

Brent Ozar: They can turn off database mail. They can break database mail if they want to break your alerting. But if you’re worried about your incompetent coworkers or your sysadmin buddies who could screw something up, I love this. I’m all for this. DDL triggers are another thing, I’m not quite as fond of that. I mean, I’m fond of them, they just have drawbacks and gotchas because if the trigger fails then all of a sudden somebody’s code fails and then you know, it causes a mess.

Erik Darling: I ran into a really interesting one with a trigger recently with snapshot isolation. Wherein the trigger there was a temp table getting populated and a clustered index getting made on the temp table. But under snapshot isolation that wouldn’t work because data definition language like that isn’t snapshotted, so it just failed immediately. So you had to just take the index tree out of the trigger. It was crazy.

Tara Kizer: Was the error clear and that was the issue?

Erik Darling: Yeah. As soon as I took the index tree out of the trigger language, it ran fine.

Brent Ozar: Wow, and folks, if stories like that interest you, go onto BrentOzar.com and search for “bad idea jeans.” We have several blogposts in the past where we’ve done crazy, stupid things with temp tables and triggers because there’s one for one third-party vendor, who Erik and I know very well, did some wild things with temp tables. So I knew that they needed an index so I set up a trigger, a server level trigger whenever the table was created in tempdb if it matched this definition I would go add an index to it immediately. Talk about a bad idea.

Erik Darling: Wow. That was worse than my idea.

Brent Ozar: So bad. I had to like give big, written instructions to the client, “I’m leaving here but you better make sure you know this is in place because it’s going to break your codes sooner or later.” Well that’s all the questions we’ve got this week, everybody. Thanks for hanging out with us and we will see you guys next week on Office Hours. Bye, everybody.

Erik Darling: Adios.