[Video] Office Hours 2017/03/22 (With Transcriptions)

This week, Tara, Erik, and Richie discuss statistics updates, in-place upgrades, row vs page compression, deadlocks, replacing SQL backup vendors, server load, ETL, and whether they think that database mail should be used by user applications to send their emails.

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.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2017-03-22

 

Can I performance tune statistics updates?

Erik Darling: All right, it’s 12:16 now, a minute late. We might have to go a minute over to answer all you people’s questions. Let’s just start with the top here. Oh, this is a fun question. “This might seem backwards, but is it possible to performance tune a statistics update with full scan?”

Tara Kizer: No.

Erik Darling: No. What’s even kind of crappy about that statistics update is I don’t even think there is a sort in tempdb option like there is for indexes. So, you are just stuck doing all that work.

Tara Kizer: The only thing that I would add, do you actually need to do a full scan? On the systems that I have supported, the number of rows directly pertains to what sampling you might need. As the table gets bigger, you might even get away with ten percent. I have even done one percent sampling on tables that were say were a hundred million rows or over.

Erik Darling: So, what I would say is rather than try to have a giant full scan on a full table’s worth of statistics, I would try creating some filtered statistics on the values that you are interested in. Perhaps if there is a certain range of predicates that has an abnormal skew in your data, I would try creating a filtered, or maybe a few different filtered statistics, on the skewed data to see if that helps at all. Because that way, if you do have to update that particular statistic with a full scan you are dealing with a smaller amount of data or something like that. Something reasonable, right? A reasonable assumption, smaller amount data than the entire table. Who knows though, who knows?

(Note: SQL 2016 has some tricks where you can update multiple stats on the same table at the same time, but that’s left up to you and your simultaneous scripting. Good luck with that.)

 

Do in-place upgrades upgrade everything?

Erik Darling: Let’s see here, we have a question about upgrading, in place upgrades, heaven forfend. “When upgrading SQL 2012 to 2014 will in place upgrading upgrade the reporting databases as well?” I don’t think they get left behind.

Tara Kizer: Yeah, they are part of the upgrade but you need to also take care of reporting services. Make sure that the version that you are running there is going to work as these databases do get upgraded. I don’t even like having report server databases on my production instance. I want it on a separate instance away from my OLTP stuff. Maybe it will have a lesser license server. And, I don’t even like in place upgrades, forget that. For production, heck no.

Erik Darling: No. Let’s answer your question with a list of stuff Tara doesn’t like: in place upgrades, reporting databases on the same server, and even reporting services as a service on the same server.

Tara Kizer: Oh, heck no. Yeah, that never goes on my server. I might [inaudible 00:05:41] the RS databases but definitely not the service.

Erik Darling: So, list of things for you to do this weekend. Move all your stuff to a different server then do your upgrade, but not in place.

Richie Rump: No, never.

Tara Kizer: When I say in place, I mean the actual upgrade wizard where you lose your instance, you know the entire instance is upgraded. If this is like newer hardware or maybe just getting a newer hardware and you want to just install a new instance side by side on the same server, I have done that for sure. A side by side upgrade does not have to be on another server. In the days with virtual machines, everyone is running virtual machines these days, what’s the big deal about setting up a new VM temporarily?

Erik Darling: Yeah, so, to recap don’t do in place upgrades. Don’t keep your reporting service database on the same server, and don’t keep reporting services as a service on the same server. You are going to want to separate that stuff out and not break your entire server and reporting services.

Richie Rump: And run everything in the cloud, he forgot that one.

Tara Kizer: There you go.

Erik Darling: It’s not your money, why not?

Richie Rump: It’s not your server either.

Erik Darling: No, it’s true. Well, you never know, it might be. I might have branded it.

 

Should I compress everything by default?

Erik Darling: John asks, “I read on a blog post by Greg Low that row compression is recommended over none across the board. Would you agree with this or in other words when would none compression be good?”

Tara Kizer: None compression, that’s just it disabled, right? That’s the default.

Erik Darling: Well you squished your nones down so they all fit in a row.

Tara Kizer: I like compression. I did extensive testing on compression and we used that one script that’s out there on the internet somewhere that it can test whether or not if you should do page versus row compression on each index. So maybe you don’t only have row compressed indexes, you also do page. Anyways, I liked it for performance. I liked it for storage. I didn’t experience that much higher CPU utilization. I didn’t have servers that were bottlenecked. Honestly, I think I would probably move forward with it out the door. In new databases, I’d enable it. The only issue recently is I had a client that had Enterprise Edition, and that was the only Enterprise Edition feature they were using. They had a server with tons and tons of cores. It’s real expensive to license Enterprise Edition. So, this is an Enterprise Edition feature so be sure that—those licenses are expensive.

Erik Darling: Yeah, I mean as far as I know, the only time I would say don’t compress, or like why I wouldn’t just have it as the default, is because not everything compresses well.

Tara Kizer: Yeah, that’s true.

Erik Darling: So, it doesn’t do you any good to turn it on and then not have it actually compress anything. Then it’s just kind of a waste. But like Tara said, there are scripts out there that can estimate compression, you know, you can run that sp estimate compression stored procedure on all your tables and figure out which ones and which indexes would benefit most from that. The other thing to be a little bit careful of is if you have a really high stress OLTP workload sometimes, I’ve found compression can bog down modifications and stuff. Select and everything would run really great.

 

Do the deadlock trace flags add overhead?

Erik Darling: All right, Mike asks, “I have intermittent deadlocks occurring on a SQL Server 2012 database that I am not able to reproduce in test. What is the performance impact of enabling the relevant trace flags and leaving them on to log the deadlock and to SQL until I have got a few examples of it analyze?”

Tara Kizer: There is no performance concern with enabling those trace flags. Some people just enable it for all new instillations that they do. It’s fine, it just clogs up the error log and it’s really hard to read that information in the error log anyway, but at least you have it. Reproducing it in a test environment is hard because you are not simulating the load and it’s really the production load that causes it. Blocking in a system is normal and the longer those blocks last, the more likelihood of having deadlocks occur and your production hardware is probably better and it loads probably a lot higher. But yeah, trace flags are fine in the error log.

Erik Darling: Turning on those trace flags is fine in that you can get error log information from that. But you’re on 2012 and extended events are a much better thing than they are on at least 2008 R2 and 2008. So, on 2012 well, going back further, but at least on 2012, you do have the system health extended event running constantly, which will have some of your deadlocks in there. It’s not going to have all of them because I believe it hangs out in the buffer ring. So, it’s not going to have all your deadlocks going back historically. But you could probably find some recent ones in there. I am sure if you Google around for “system health session deadlocks” you could find a—there is a script out there from a Microsoft guy that does a pretty good job of parsing things. You could also set up a dedicated extended event session to run and sample and collect deadlocks, because that will only fire off when deadlocks are actually happening. So, there is not a lot of overhead to just having it on waiting for a deadlock to occur. You get the XML, you get the picture graph, where it’s much easier to decipher exactly what deadlocked and why. So, the trace flags are fine and there’s no overhead to dumping stuff in the error log, but I think since you’re on a slightly more modern version of SQL Server, you do have some better options available to you.

 

I have this really long question…

Erik Darling: Darlene, post that on dba.stackexchange.com. That’s way too little information to figure out what’s going on with your view. We would need to see some more stuff than that.

Richie Rump: Here is a little hint about dba.stackexchange.com. We see it, we kind of see everything that kind of goes through there, like all of us are trolling on it. So, there is good chance that one us may actually answer your questions if you put it on there. So, if you don’t pay attention to dba.stackexchange.com don’t worry because we do.

Tara Kizer: Erik gets all the cool query questions. I take the AG questions.

Erik Darling: Yeah, we split things up pretty fairly. No turf wars with that.

Richie Rump: I just wish you would not go into our Slack [inaudible 00:12:34].

Erik Darling: We could have a separate channel for Slack Stack questions—Slack channel for Stack questions.

Richie Rump: We wouldn’t pay attention to it as much if it was a separate channel.

Erik Darling: I would.

Richie Rump: It’s fine where it is.

Erik Darling: I’m horrible. As soon as I see that little green dot [inaudible 00:12:52] go up over the hash sign, I am like, “Who said what?” Who’s in Slack?

 

Why do I see a residual predicate in my plan?

Erik Darling: Gordon asks an interesting question. “Query filter where ID is not null and code not equal to zero results in residual predicate for the code not equal to zero part of the filter, even though code is an included column of the index where ID is a key column. Why the residual predicate?” Well, because it’s a range. You’re going to have a residual predicate there anyway. You can only seek on one predicate at a time. If you look at any query that has multiple predicates, you see seek keys and then residuals. So, also non-key columns can’t be seek keys because the index isn’t ordered by them. There is no interesting sorting or anything by non-key columns. Included columns just hang out at the very leaf level of the index, very bottom level of the index tree, pyramid, however you want to call it. The data lives there but there is no statistics and there is no ordering and there is no particularly good information about them. It just kind of hangs out there waiting for a select. So, if you wanted to potentially get seek on multiple, you could promote it to a key column or make it a filtered index.

Richie Rump: What I think is funny is that you had leaf level as the bottom level and then some people we know put the leaves on top because the trees on top.

Erik Darling: I always picture it at the bottom.

Richie Rump: Well because that’s where all the documentation goes, right? The documentation, you start here and then you branch off into the leaves at the bottom. If it’s supposed to be a tree, the leaves are at the top.

Erik Darling: Yeah, I know like it’s fundamentally wrong and just like it’s a concept when I explained it at the bottom it’s wrong, but I just can’t… It’s hard for me to be like, “Oh the root of the index is down here and then we add stuff up here.” Get away from me. That’s where mosquitoes come from, I don’t want that.

 

Can I apply SQL 2008R2 SP3 to SharePoint 2007?

Erik Darling: [Inaudible 00:15:04], I don’t know.

Tara Kizer: Yeah.

Erik Darling: I have no idea what you’re asking me.

Tara Kizer: Yeah, you’re going to have to Google for that I think.

Erik Darling: He is asking if we can apply SP3 and SE to SQL Server 2008 R2… I have no idea how SharePoint 2007 interacts with SQL. I don’t know. Upgrade SharePoint. Why is it on 2007? How old is that now? Ten years? That’s ten years. You have a ten-year-old kid.

Richie Rump: …SharePoint thing I don’t understand it at all. Where documents go to die: SharePoint.

 

Erik Darling: Colton asks a funny question about index maintenance. Sorry, Colton, I don’t know if that’s a male or female name so I am going to “they” you. They ask, “We recently adjusted our index maintenance to only rebuild on weekends and update stats during the week. This improved our nightly jobs from five to six hours to two and a half to three hours. If stats only helps choose the maintenance plan, does this imply we just had really bad execution plans? I am trying to understand the reason behind the huge performance increase.” So, are you talking about your maintenance plans not running?

Tara Kizer: I think they meant to say execution plan right there.

Erik Darling: Oh, okay.

Tara Kizer: … nightly jobs, maybe purging data, who knows what. They were taking five to six hours, now they are two to three hours. Yeah, maybe out of date statistics, maybe bad execution plans.

Erik Darling: Yeah, could be all sorts of stuff in there. Tara’s got that one. It could be either one of those things. There you have it. I don’t even have to say anything now.

 

Which backup vendors should I use?

Erik Darling: Kyle asks, “We plan on replacing Idera SQL Safe with Redgate SQL backup. Are there any horror stories? Do you have any tips from past with replacing SQL backup vendors?” God.

Tara Kizer: I haven’t used Redgate SQL backup since SQL Server 2005, ever since compression got put into engine. I know that those tools offer additional features: better log shipping, better GUI, better historical information. All sorts of better stuff, but I didn’t need those features. I just cared about compression, backup compression. So, I ditched them.

Erik Darling: Yeah, I wish I could say that I had replaced the backup product at my last job because I hated it. I’m not going to say what it was.

Tara Kiser: Oh, Commvault?

Erik Darling: I didn’t say anything, but I wish I could say that we had replaced it but, rough.

Tara Kizer: We only use the Commvault to copy our files, our SQL Server backup files. Copy them to the cloud.

Erik Darling: Yeah, Redgate I am sure it’s fine. I don’t know of any misaligned abilities between the two, so I am sure it will be all right just make sure that you can restore your old backups with the new tool I guess.

 

Have you seen anyone using Always Encrypted?

Erik Darling: [00:18:22 Gama], who left, it’s okay I am still going to ask the question. I’m still going to put the question out there but he left. Left us all alone.

Richie Rump: What’s up G? What’s going down, man? Come on now. You used to love me.

Erik Darling: What’s going on, fella? “If any, what has been your experience with clients implementing always encrypted in SQL server 2016?” I ain’t seen it.

Tara Kizer: I have only had one client that was using SQL 2016 so far.

Erik Darling: Yeah, no one has wanted to talk to us about always encrypted yet. So, sorry about that. If you are reading this later in the transcription or watching the video, sorry, no experience on that yet.

Tara Kizer: Most of our clients are on 2008 R2 and 2012 I would say.

Erik Darling: Yeah, yeah, that’s pretty common numbers.

 

Why did they take away my database access?

Erik Darling: Kevin asks, “In SQL Server 2008 R2 could a person’s DB owner access to a database suddenly disappear?” Bad news, Kevin, you have been fired.

Tara Kizer: You’ve got a DBA or sysadmin messing with you.

Erik Darling: Yeah.

Tara Kizer: Unless you run into some weird bug, but I have never heard of a bug like that, but somebody has removed your access.

Erik Darling: If Kevin leaves early, we know he has got called into HR.

Richie Rump: Yeah, you need to go talk to your boss like right now.

Erik Darling: Like, “Hey man, I was watching this webinar and they told me I got fired, I was working hard and…”

 

Are there any gotchas with VM backups for SQL Server?

Erik Darling: Graham asks a kind of interesting question, “Our infrastructure team is evaluating Veeam as a replication and backup solution. They want me to evaluate its SQL backup capabilities. While the product is fancy, it essentially appears to be another GUI for backups. What are your thoughts on some third-party backup providers?” Not necessarily Veeam, but just in general.

Tara Kizer: Yeah, I have been at these large organizations that every now and then they have these brilliant ideas to test out this new backup solution. We’ve tested all sorts of them. The answer always come down to, I am a SQL Server DBA, I want SQL Server backups. When I need to do a restore, I want to be able to have access to those files immediately and start that restore. If it’s on tape or it’s some tool I am not familiar with, it’s going to take me longer to do my job and a restore in production is going to be critical. Veeam, I’m fine with using these third-party tools after the SQL Server backup file is created. Then use those tools to copy the files, copy the Windows files, not the actual SQL Servers backups.

Erik Darling: I’m totally with Tara on the SQL native backups. Those really are, unless you have, you know, massive terabyte plus databases where SAN Snapshots make way more sense, SQL native backups are still the bee’s knees. When people are trying out these backup tools, I don’t think that the backup capability should be what they judge. I think the restore capability should be what they judge. A lot of them have really cool stuff with the log reader, object level restore, other things like that. So while they may be perfectly capable of taking a backup, what I want to know is how they restore that backup. I want know if I can restore that backup if that tool isn’t available. One major grip that I have had with a prior backup tool that will still remain nameless by me, is that when it takes a backup it creates a temporary backup device, which is a GUID. So my backup log would say backed up to device equals some GUID. I couldn’t script a restore from that because it’d just go [inaudible 00:21:55] those devices after it was finished. So when it went to restore stuff, it had to do some internal proprietary process. One time the server that that tool was on went down, no fault of mine because I wasn’t the admin on that server. But that server went down and someone needed to restore, and I said, “Sorry, I can’t restore that for you.” So, one thing that you do want to make sure of it is that you can restore without that tool and if that tool offers anything else as far as restores go. So there’s my blabbering answer on that.

 

Does an unused database add load?

Erik Darling: Chris asks, “Does an unused database add any noticeable load to a server?”

Tara Kizer: No.

Erik Darling: No, thankfully. I mean, I guess they could run out of disk space a little faster if it’s big but aside from that, I don’t think it’s going to do anything. If you are running CHECKDB or other maintenance on it, then sure it’s going to suck up some space.

Richie Rump: But then it is used, right?

Erik Darling: Then it’s used, not necessarily by people, but you know someone…

Richie Rump: Even if it has pages in memory, then it’s being used for some reason.

Erik Darling: That is true. So, I would temper your definition of unused a little bit. Unused by people or is it unused period? If it’s unused period make it read only, something like that. Drop it, just drop it for fun, you can take Kevin’s job who just lost his DB owner access.

Richie Rump: Don’t be hard, man. Come on now.

Erik Darling: I’m just messing with him.

 

Should I use the GUI for managing lots of users?

Erik Darling: Tom says he went through an exercise “where we needed to map out and duplicate various logins and user rights using Management Studio. Was tedious to say the least as each DB security had to be mapped one at a time,” what’s up with that? This is why you learn how to script stuff. Tom, my friend, this is why scripting is your friend. This why the GUI is not your friend, there’s a lot of right clicking involved in that GUI. But I will give you one tip with the GUI, something that not many people know about. So, when you click on certain note items in the GUI, if you go up to the view menu in Management Studio, you can look down a couple notches and click on “view object explorer details” and that will often bring up a new pane in Management Studio where you can highlight multiple items, right click, and script them. So give that a shot next time you need to script out a whole bunch of stuff. I know it works for agent jobs and other things like that, like indexes and whatever, so maybe it will work for you in this case.

Richie Rump: Hey, if I could use scripting for things like backups and restores, you could do it for logins, man.

Erik Darling: If Richie can do it, anyone can do it, I think that’s the message.

Richie Rump: Senior DBA, that’s what I got.

 

How do I import this 2GB text file?

Erik Darling: Oh boy, Ron asks a good ETL question. Richie, we should thank him for this. We should put some maple syrup on this question, it’s delicious.

Richie Rump: The answer is Python.

Erik Darling: He is right. “I have a 2-gigabyte text file that is fixed length without row terminators and no way to get the source changed. How can I import this file into a table?”

Richie Rump: Without row terminators?

Tara Kizer: Yeah, I don’t even understand the format then. Without row terminators, it’s just going to be row after row all together in one row.

Richie Rump: Is it a government file? That would be my question, because I’ve seen some crappy government file stuff, but you have always had your \n or your \t\n type stuff, right?

Erik Darling: Yeah, or \n\r.

Richie Rump: I have got a new row. So is this I need to read every character at that point? And then after 50 characters I have a new row? I mean if that’s the thing? And if that is the thing, then I am probably going to script that out using PowerShell or some C# or something so I can get it into a format that’s a little easier to kind of read, you kind of do that preprocessing step. Because you don’t want to work your ETL process around that type of thing, it’s kind of dirty. So, normalize the format, even if you want to chunk it out into smaller one meg files or something that’s a little more digestible through your ETL, go ahead and do that too but that’s what I would do, I’d change it up a little bit.

Erik Darling: I used to work for a market research company and I used to get horrible data from ten different sources that had to get munged together into one table and cleaned and all sorts of other stuff. So my best advice is to learn a language that allows you to manipulate text files well so that you can clean them before trying to load them. There is some stuff that is not good to do in SQL and there is some stuff no matter how awesome your bulk insert [inaudible 00:27:31] is with format files, that you just can’t get around. There are all sorts of situations with like ragged left and ragged right and ragged all around.

Richie Rump: He came back and said it’s government data. It’s postal service data. So yeah, how do I know this? I don’t know, I feel your pain, Ron. You and me, we are blood brothers now. We felt the same pain. Yeah, you got to do that little bit of preprocessing. Get your own format, and then start processing in a sane manner, as opposed to some of the insane ways our government data is in.

Erik Darling: Off the top of my head advice, I would open it up in a hex editor or some other text editor that shows you the character mappings for everything. So that if you can see what’s actually at the end of a line, because there might be just some weird character at the end of a line that you could do like a quick regex replace with a row terminator that’s readable by SQL or something else. If it is truly fixed width and every line is the same length, then you could fairly easily write a regex thing that just every x characters, you insert this character. So, that would be my advice on that. I’m sorry, I detoured for little while on that. It has been so long since it was like a funny ETL question that I could answer. I was like, “I used to do this crap, it’s horrible, I’m sorry.”

Richie Rump: Listen Erik, if you use regex as your solution, you now have two problems, okay? Just remember that.

Erik Darling: I enjoyed the regex challenge. I wouldn’t say that I was good at it or that I passed with flying colors, but I enjoyed the challenge of writing regex. I thought it was fun to see it work.

Richie Rump: And ladies and gentlemen, every time his regex failed, he got a new tattoo. That’s how that worked.

Erik Darling: I actually just got a line and a tattoo.

Richie Rump: And they all just kind of blended together, you see how that works.

Erik Darling: It’s pretty sad actually. Actually, all my tattoos cover up regex code that I was trying to remember. Like, “Oh, yes, look back and look forward.”

 

Will you be at PASS Summit talking about Azure?

Erik Darling: Karen is asking if we will be at PASS Summit this year, “and do you know if there are any sessions related to SQL Azure?” They have no session list workshop details on the website. Well, they haven’t—I’m not sure if they even have the call for speakers yet but you can bet your bottom dollar Microsoft is going to be shoving Azure at you.

Tara Kizer: It’s going to be months before you see the schedule go up there, September maybe. We don’t even know who the speakers are.

Richie Rump: Listen, there will be so much Azure stuff that you couldn’t walk anywhere without running into Azure [inaudible 00:30:16]. Don’t worry about the Azure stuff, it will probably be there.

Tara Kizer: Who is going? Are you going, Erik?

Erik Darling: Yeah, I’m going but only because I’m bringing everyone with me. So I don’t have to miss the Halloween thing which is incredibly rude of them.

Richie Rump: So, what about you Tara, are you going?

Tara Kizer: No, I am absolutely not going. I’m protesting it actually. Having it over Halloween is just, it’s just not fair to the—I mean so many people that are DBAs and developers, a lot of them are parents with kids that are under say 13 that, you know, Halloween is a big deal. It’s fun. You get pictures and it’s a moment that you don’t want to miss. So, no, I’m not going.

Richie Rump: I’m considering it, I don’t know, I’m back and forth depending on the day. So, we will see.

Erik Darling: If Richie doesn’t go, he loses like some board game title, so I don’t know..

Richie Rump: Well, no, like half of the people I play with aren’t going to go either, so…

Erik Darling: True.

Richie Rump: So, that’s kind of the way that works. You’ve got to think that they must have had saved a ton just on the location and the week. Then like, “Oh no, DBAs, we are gung ho¸ we will go through anything” and that’s just…

Erik Darling: Also you’re going to lose a ton on all the people not showing up because you start your session on Halloween and no one wants to see anyone from Microsoft in a sexy nurse outfit. See Conor Cunningham in a sexy nurse outfit, not going to work for me.

Richie Rump: Well, talk to me more about [inaudible 00:25:06] again. You have my interest now, sir, you have my interest.

 

Should we use database mail in user applications?

Erik Darling: Oh god, it’s 12:45, but we started a little late, so I’m going to ask one more question if I can find a good one.

Tara Kizer: I think this question—I like this question…

Richie Rump: Oh, go ahead then, you ask that one.

Tara Kizer: “Do you believe database mail should be used by user applications to send their emails in?” No, absolutely not. Applications have access to so much better tools than database mail. Just because a feature exists in SQL Server does not mean you should use it for the application. Database mail is for DBAs, sysadmins, to get alerts. It’s for, to do things within SQL Server, not for the application.

Richie Rump: Yeah, and there’s a lot of great services out there that utilize email really really well, I mean the SES in Amazon is great. There’s a whole bunch of other stuff out there that handles emails so much better than freaking SQL Server.

Tara Kizer: Just because you can, doesn’t mean you should.

Erik Darling: Don’t spend thousands of dollars per core on an email server, that’s goofy. All right, we are going to leave, we are going to end on that high note. Thank you all for showing up. We’ll see you next week, maybe Brent will be there, I don’t know.

Tara Kizer: Maybe.

Erik Darling: Maybe, we’ll see. Thank you guys, catch you later.

Wanna join us at the next Office Hours to ask a question? Register here.