This week, Angie, Erik, Tara, and Richie discuss Veeam, replication, setting up alerts, using multiple instances, and much more.
If you prefer to listen to the audio:
Office Hours Webcast – 2016-05-25
Does turning on trace flag 2861 cancel out optimize for ad-hoc workloads?
Angie Rudduck: Let’s go with Gordon for the first question of the day.
Erik Darling: Oh no.
Angie Rudduck: He wants to know if switching on trace flag 2861 which is cache zero-cost plans he thinks, will that cancel out enabling optimize for ad hoc workloads?
Erik Darling: No, it shouldn’t because optimize for ad hoc workloads affects plans differently. Optimize for ad hoc the first time you run something you’ll get a stub. The next time it will cache the actual plan. So the zero-cost plan cache engine should not change that behavior. You can test it though if you don’t believe me.
Angie Rudduck: How would you test that?
Erik Darling: Run a zero-cost plan more than once.
Angie Rudduck: Hopefully that answered your question, Gordon.
Why am I getting update cascade errors?
Angie Rudduck: We’ll just go down the line. Wes. He is trying to use update cascade and he’s getting an error about it not being able to cascade directly. When he takes it away, it works. Is there a way to get around this error?
Erik Darling: Yes. Stop having foreign keys reference in a circular motion because it will just try to delete in a big, horrible ouroboros until all your data is gone.
Tara Kizer: So you won’t be able to do that specific foreign key. You may need to handle the deleter, the update, in a store procedure instead or maybe a trigger. We don’t recommend foreign keys, cascading foreign keys, anyway.
Erik Darling: Why not?
Tara Kizer: I looked at your slide. The serializable isolation level, the most pessimistic isolation level it is.
Erik Darling: That’s right. Wes, there’s another hidden gotcha to those foreign key cascading actions. That’s whenever they run they take out serializable locks on all the tables that have the cascading action happening on them. So if you’re doing a large deleter update or whatever with a cascading action, you could take out some pretty heinous locks on your objects that really block other processes out. So be very careful when using those.
Angie Rudduck: He’s so smart.
Erik Darling: Nah.
Richie Rump: From an architecture perspective, I’ve always wanted to be explicit with what we’re deleting and/or updating. So from an architect’s perspective, that’s why I just never used any of that stuff. At least not since Access 2.0.
Tara Kizer: Access 2.0.
Angie Rudduck: I didn’t know that was a thing. Moving along.
Richie Rump: You weren’t born yet, Angie.
Angie Rudduck: Speaking of born, tomorrow is my birthday if you didn’t see calendars on Brent’s calendar.
Erik Darling: Happy tomorrow birthday because I’ll forget tomorrow.
Angie Rudduck: Don’t worry I will unless I look at my calendar.
Erik Darling: Brent didn’t send the card for me?
Angie Rudduck: Yeah, we’ll blame Brent. He’s not here.
How do I get around CRLF characters in an input file?
Angie Rudduck: J.H. has a situation. “A user entered a carriage return in a free form text field. Now trying to bulk insert along with format file relevant flat text file.” How can he skip the unwanted carriage return and use only the last column carriage return for that particular row?
Tara Kizer: You just need to pick a different delimiter, right?
Erik Darling: Right but I think the problem is that the delimiter is a carriage return for all the other rows.
Tara Kizer: You’re right.
Angie Rudduck: Can you just edit the file and delete that carriage return?
Erik Darling: Yeah, what Angie is saying, I would probably do a process that pre-cleans files for things like that.
Richie Rump: Yeah you would encode it in a different string type or whatever, like basic C4 or something or other.
Tara Kizer: I wonder if he could get around it by quoting the column so that the carriage return is in between quotes so it won’t read it. I’m not sure though.
Angie Rudduck: You’d still have to edit the actual file before. He’s going to have to do some processing on the file before it goes anywhere it sounds like.
Tara Kizer: But I wonder if you don’t need to do it though. If you select the option to—or not select the option obviously since it’s… well, okay, yeah.
Richie Rump: By quoting it with a pipe or something like that?
Tara Kizer: Something so that it’s surrounded, but yeah, the file does need to be modified to include that. It might just be easier just to get rid of the carriage returns and not allow those carriage returns from the application side.
Richie Rump: Yeah, unless you need them.
Tara Kizer: Yeah.
Angie Rudduck: Carriage returns.
Erik Darling: Yeah, as someone who’s done a lot of horrible file loading in his lifetime, I always try to make my delimiters as unlikely to happen in real life data as possible, either like double pipes or something like that just really, you’re not going to see it pop up too often.
Angie Rudduck: That’s a smart idea.
Could Veeam replication cause 8TB databases to go into suspect mode?
Angie Rudduck: All right, we’ll move on. Eugene wants to know has anyone seen issues running Veeam replication interfering with large SQL Server databases where the databases go into suspect mode? They have an 8 terabyte database and it seems like “each time our server team has enabled Veeam replication it caused issues where the 8 terabyte database goes offline.”
Erik Darling: I think it sounds like what’s happening is it’s freezing IO for a long time. A frozen IO makes SQL think that the database perhaps the drive that it’s on is unavailable and that, you know. So I would check your SQL error log and I would look for frozen IO, I would search for frozen IO and see how long the IO is being frozen for. Because I think if it happens for over a certain amount of time, SQL just starts assuming that the disk that the file is on isn’t available and it just says, “Nope. Not here.” A sort of like a similar thing happened, I’m sure, Tara, you’ve seen it where if a log file goes missing on a drive like that.
Tara Kizer: Mm-hmm.
Erik Darling: It will go right into suspect mode.
Tara Kizer: I wonder if it’s due to the database size because it’s having to freeze it for so long to take a snapshot of an 8 terabyte database. Just might be something that you just can’t do due to the size of the database. I would contact the vendor and see what they say as far as database size and how long that snapshot is going to take.
Angie Rudduck: I learned a trick when you’re search for your error log for your SQL Server error log. We always look for frozen but then if you see it’s frozen how long before it resumes. So you don’t see the resumed ones. But if you search for resume, you see both the frozen and the resume responses. So gives you a little bit more insight to how long your database files were really getting held up.
Tara Kizer: Yeah and it should be less than a second that IO is frozen. I mean we’re talking milliseconds. I don’t know about an 8 terabyte database though.
Can I do both Veeam full backups and native SQL Server full backups?
Angie Rudduck: Yeah, well we’re just going to keep going with Veeam because Sean has a question as well. They have a maintenance plan, the full backup, running every day as well as Veeam doing backups every day. “How do these two tasks affect logs? Don’t both tasks truncate logs? Should one be scheduled before the other? Or should one not be done if the other one is being done?”
Tara Kizer: No, they don’t impact the logs because the logs care about LSNs. You can use either of them too, either of the full backups to do your restore. You just have to make sure you get the right log sequence. It does impact differentials though so if the Veeam backups aren’t using the copy only option, the differential base could be a Veeam backup or the regular native backup. If you’re not doing differentials then it doesn’t matter.
Erik Darling: Yeah. Only transaction log backups will truncate the log file, nothing else will. So backups don’t do that.
Tara Kizer: Right.
Should I run the publisher and distributor together, or the distributor and subscriber?
Angie Rudduck: All right. Paul says, “Currently at my job they are running the distribution service off that same database server that the database is on. I’m going to propose an additional one replication to a reporting server, the original replication is merge. What considerations are there when you do this? Would it be better to run a separate distribution server?” Tara.
Tara Kizer: Yes, definitely.
Angie Rudduck: We all know that answer is yes.
Tara Kizer: The best practice is the publisher needs its own server. The distributor need its own server. The subscriber needs its own server. The distributor does a lot of work and it should not be on the same box with the publisher especially. When I’ve had to share servers, I’ve put it on the subscriber but it really depends upon the load of those boxes. You are going to need licenses for the distributor to be on another server so that’s something to take into consideration but the best practice is that they’re all on their own boxes.
Erik Darling: Heck yeah.
Angie Rudduck: What Tara said. Justin, I will not be turning 21, thank you. I am a tiny bit older than that.
Richie Rump: 19.
Angie Rudduck: I will be celebrating my one and only 29th birthday. Thanks very much.
Tara Kizer: We can do the other replication question from David.
Angie Rudduck: Yeah? All right. I have to scroll way up for that one now.
Tara Kizer: Okay.
How do I know when replication’s initial load is done?
Angie Rudduck: David said they’re setting up replication. How does he confirm that a data loads replication is complete so he can start the job to load the app dbs?
Tara Kizer: So what you can do is you in Management Studio object explorer, expand replication, local publications, then you’ll see your publication. Right click on it and you can do the view snapshot agent status. That’s the snapshot that is snapshotting the publisher. Also right click on the publication for view log reader agent status. Then you get to go into right click on the replication and launch replication monitor. I’m pulling mine up right now. Mine is in failure. I created one as a test. But after you pull up replication monitor, you navigate to the publication and then you click on the all subscriptions tab on the right pane. Then double click on the row that shows up there. Then you could look at the publisher to distributor history, distributor to subscriber history, and you will be able to see where it’s at in the process. You can also insert a tracer token into the publisher. It’s not a row in the table but it’s like a test row basically and once it makes it to the subscriber you know the whole process is complete.
Angie Rudduck: You’re so smart about replication.
Tara Kizer: I don’t know about that.
Angie Rudduck: I’m so glad we have you around.
Tara Kizer: There are definitely smarter people.
Angie Rudduck: To answer all these questions for these poor souls who still have to use it.
Erik Darling: It’s nice because I get to zone out while you…
Angie Rudduck: I know, I’m like, I’ll read the questions to see who should answer it next.
How do I find out all SSIS packages that ran under a specific login?
Angie Rudduck: Nate wants to know if there’s a way to find all SSIS packages that have been executed under a specific server login. He’s trying to remove this login because the employee has left the company. Anybody got any suggestions?
Tara Kizer: If you’re using the SSISDB catalog you probably can go through those tables which has information. If it’s not using that, there is a logging table in msdb somewhere, I don’t remember what it’s called but there is a table there. So you’re going to have to do some sleuthing here to figure out which tables are but these are things that people have blogged about so you should be able to find which tables they are.
Erik Darling: Yeah, we don’t do a ton of work with SSIS in these parts. So if you really want a better answer for that, you might want to go to dba.stackexchange.com and ask around there if nothing in the great blog world turns up for you.
When did my AG last fail over?
Angie Rudduck: Tammy wants to know if there is a way to use T-SQL to determine when databases and availability groups failover last. She’s been looking through DMVs but hasn’t found anything.
Tara Kizer: Yes. I don’t have the DMVs memorized but, yeah, it’s in there.
Angie Rudduck: It’s dmv_ag_failover. Just kidding. Don’t really go to that.
Tara Kizer: I’m pretty sure at least I’m thinking about failed over last. If not, search through the error log because it’s definitely in there.
Are there any drawbacks of setting up SQL Server alerts?
Angie Rudduck: A. Deals wants to know if there are any impacts of setting up alerts from 17 through 25, 823, 824, 825, and 832. Did you get that off of our website?
Erik Darling: I think the bigger impact would be if you don’t set them up.
Angie Rudduck: Yeah.
Erik Darling: And then one day your database is corrupt and you don’t know how long or what happened to it or any of those things.
Tara Kizer: I think that our link says 19 through 25. I’m not too sure what you’re going to get for 17 to 18 so you might get too much to work on. I’m not sure about 832 either. I know 823 to 825 are in there but I’m not positive about 832.
Erik Darling: Yeah, I think if you turn on alerts for 17 and 18 you’re going to get a lot of stuff for failed logins and some of the lower lever old SQL Server errors. So we try to stick to 19 through 25 which are the severe ones and then 823, 824, and 825 which cover the hard and soft IO errors that come along with corruption. What about that one from Dennis?
Should I stack multiple instances on the same physical box?
Angie Rudduck: Dennis, all right. Dennis says he’s got an opinion question. Using SQL 2008 R2 Enterprise what’s better 5 by 8 to 12 cores and 128 gigabytes of RAM or one 60 core with 1 terabyte of RAM server? Oh I see.
Erik Darling: So I would only assume that you’re talking about stacking a bunch of instances on the server which we are all vehemently opposed to. Stacked instances are horrible ideas.
Angie Rudduck: Now if you have a bunch of Lego servers and you can break them apart and have five different servers or one mega Transformer server and you’re looking at spec’ing this out before purchasing, maybe there’s some more questions. What do you guys think if he’s not instance stacking? If he’s legitimately just spec’ing out, should he buy one server or five servers? You should buy five servers if you need five instances.
Erik Darling: Right. So it sounds like you’re either going to stack instances or you’re going to stack applications together on a single instance. Either way I’m opposed to and very. If you have no choice then you have no choice. Or if like you know you’d have like your crappy app server that you stick a whole bunch of like internal apps on. That’s one thing. Servers at the size that you’re talking about though, I’m looking at very special, specific hardware for each one. So if there’s a performance problem with one database or one application, I’m troubleshooting a performance problem with that one database and that one application. Not for that one database or one application and the 28 that you have surrounding it. Make life easier on yourself. Separate things as much as you can.
Angie Rudduck: Dennis follows up saying that he was gifted two of the big servers and now he has to find a use for them. If you have spare hardware sitting around…
Erik Darling: Dev servers.
Angie Rudduck: Yeah, staging. Dev test environments.
Erik Darling: I’ll forward you a DBCC_CHECKDB this way.
Angie Rudduck: Because you’re doing that, right? Nightly? Full checks?
What’s a good way of encrypting data in a database?
Angie Rudduck: What’s a good way of encrypting data in a database? Cameron wants to know.
Erik Darling: Transparent data encryption if you’re on Enterprise edition. If not, there are the various third-party tools out there. I believe one from a company called—well it used to be called GreenSQL now it’s called something I can’t remember the name of, Hexa… Dexa… Donkey something. They changed their name to something.
Angie Rudduck: So look up GreenSQL and see what really pops up.
Erik Darling: Yeah, see what their new name is and if you can remember it and spell it and email, shoot them an email about how to encrypt your database. Unless you’re on Enterprise, then use TDE then you can come back next week and ask us questions about TDE.
Richie Rump: Yeah, I’ve used TDE as well as you know regular third-party or just middleware-type components where we would just encrypt certain parts of data and then throw the encryption stuff into the database.
Erik Darling: Yep.
If I’m having CXPACKET issues, what should I do with MAXDOP?
Angie Rudduck: J.H. wants to know he has a particular database sometimes experiencing frequent CXPACKET. He wants to know if changing MAXDOP recommended—if it’s recommended. If yes, could it hurt performance for other databases running at the same?
Erik Darling: You betcha. MAXDOP, unless you’re on SQL Server 2016 is a server-level setting. So if you change MAXDOP to 1, then you will affect all the other databases. Your best bet is to actually do some work tuning queries and indexes on that database to make sure that the cost of the query stays low enough so that they don’t have frequent CXPACKET problems.
Angie Rudduck: What if he doesn’t have MAXDOP configured at all? Could that possibly be his problem?
Erik Darling: Sure could.
Angie Rudduck: What would you recommend starting out with MAXDOP?
Erik Darling: I don’t know, it depends on how many cores you have, right?
Angie Rudduck: It’s a multi-layered questionnaire.
Richie Rump: Would it be better to just set MAXDOP in a particular query?
Tara Kizer: You could.
Erik Darling: If you change that, sure. If you can change the code, sure.
Angie Rudduck: I like asking questions on questions.
What’s the best isolation level to use with Entity Framework?
Angie Rudduck: Let’s go with developers from Paul. The developers are using Entity Framework to create their application, what’s the best isolation level to use?
Richie Rump: Well what would be the best isolation used without any framework? Same thing?
Tara Kizer: Who is the question by?
Angie Rudduck: Paul. He’s kind of in the middle. Right below all the happy birthdays.
Tara Kizer: I sorted by asker so maybe it will find it.
Angie Rudduck: Oh. So smart.
Erik Darling: There’s really no single best isolation level. It kind of depends on what you end up running into as your application grows and matures. So if you run into a lot of weird blocking and deadlocking and you’ve tried your best to sort it out then read committed snapshot isolation could really help you. Otherwise just leave SQL as is.
Tara Kizer: I don’t think that Entity Framework being used is even related to why you would set the isolation level to be something different than the default.
Richie Rump: Right, exactly. If you are having problems with locking and stuff like that then you need to fix the queries, the Entity Framework queries. Either you need to change the link statement or you just trash the link statement if it’s too complex and then write your own SQL statement to be embedded either in the code or in a store procedure.
Erik Darling: My preference is to have everything in a stored procedure and then just have a new framework called the stored procedure.
Richie Rump: Yeah, it’s up to the team itself. I’ve been on some teams where they didn’t want any stored procedures because they wanted the app to update everything when they do deployments because they’re doing deployments once a day and it’s like, “Oh, okay, we’ll do it that way.” And I’ve been on some other teams where they’re all stored procedures. Where we have thousands of stored procedures sitting out there and everyone is rewriting the same stored procedure over again because they can’t find the old one. So you know, it’s up to the team on how they want to do it.
Angie Rudduck: More horror stories there, Richie?
Richie Rump: Oh, I’ve got many. So many.
I’m getting backup IO errors and CHECKDB errors on my backups.
Angie Rudduck: Let’s move on to Gordon. He says SQL log is reporting VSS backup IO errors and suggesting running DBCC CHECKDB on the VSS backup. Is that relevant and if so, how does he do that?
Tara Kizer: I don’t know how you would do that.
Erik Darling: I don’t think—there’s no way to, currently, to run DBCC CHECKDB on a backup. You would have to just run it on the database itself.
Tara Kizer: You have to restore it.
Erik Darling: Yeah.
Angie Rudduck: Yeah that seems kind of strange. Maybe it’s referring to like what we talked about early about taking your backup, restoring it elsewhere, and then checking db.
Tara Kizer: I’d be interested in the error that is actually suggesting running DBCC CHECKDB on the VSS backups. I wonder if you’re misreading the error. If you can pop the error in the message box, we could take a look at it. We might not be able to answer it though.
Angie Rudduck: Yeah. We’ll see if he gets that error in there.
How can I get a readable replica database without replication?
Angie Rudduck: In the meantime, let’s see what Nate has to say. “Given the complexities and general distaste for replication, what’s the best alternative to maintain a quote ‘read replica type of database’ in Standard Edition?”
Tara Kizer: Wait, I know this answer. Availability groups. Even with Standard Edition you can use synchronous replicas. Enterprise edition gives you asynchronous replicas but I’m right, aren’t I right? That Standard Edition offers synchronous replication? It does not offer synchronous at all?
Erik Darling: No. SQL Server 2016.
Tara Kizer: 16?
Erik Darling: Right. That’s when availability groups came to Standard Edition. They are asynchronous only.
Tara Kizer: That’s what I was trying to remember.
Erik Darling: And you don’t get a readable replica with it. So it really does put the “basic” in basic availability group. So what I would do is sort of depending on how fresh the data needs to be is I would probably try log shipping with a caveat that every x amount of hours people are going to get kicked out so the data can be updated by restoring logs. Or if I’m using mirroring, I’m going to take a database snapshot and go through all the pain of programmatically funneling people off to a different database snapshot.
Tara Kizer: And you have to make sure that you refresh that snapshot on a schedule. Otherwise it’s going to just keep growing and growing. I would actually use replication in this scenario though. Yeah, there are some issues and it can be complex and it takes a lot of time to set up on a large database but it works. If you really don’t like it, move to Enterprise Edition or upgrade to 2016 after it’s released so Standard Edition has the readable secondaries.
Angie Rudduck: Yeah, just because we don’t like replication all the time, it doesn’t mean that it’s not valuable. It just means that it’s not used correctly a lot of the times.
Erik Darling: Yeah, unfortunately a lot of people…
Tara Kizer: And it’s hard to troubleshoot.
Erik Darling: Yeah, there’s that. But a lot of people use it for HADR which it really isn’t a good solution for.
Tara Kizer: I like it for reporting if you can’t use availability groups to do your reporting. You just have to know that you might be spending a lot of time working on replication if that’s the technology you use. It took a lot of my time.
Erik Darling: Yeah, make sure that you are very precise in what you’re replicating over.
Tara Kizer: Yeah.
Erik Darling: If you can put filters on it, put filters on it. If you can get away with only moving certain tables that are necessary for whatever reporting over, only move those tables over. Move as little as possible. Don’t just push everything.
Angie Rudduck: Yeah, that’s good.
Should I disable my SA account, rename it, or both?
Angie Rudduck: Okay, Konstantinos wants to know, auditors are telling them—excuse me, they’re requesting to disable their SA account or to rename it. Is it safe to do either of these?
Erik Darling: Yes.
Angie Rudduck: And when is it the best time to do it? Do you do it in a production environment that is two years old for example?
Tara Kizer: I love that. I mean you’re not supposed to be using the SA account. Disable it. Create a new account and grant that sysadmin or just rename the SA account to something else. Don’t ever use SA. And don’t ever even use this other account. Give it a really complex password, put it in a lockbox, never use it. You should be connecting with your own Windows account or you know if you’re having Windows authentication issues, some other SQL account, but really Windows authentication, you should almost never need the SA account. You don’t really need to do this in a maintenance window unless the other things are using SA which they shouldn’t be.
Erik Darling: Yeah, just a quick word of warning on renaming SA is in the past some SQL updates have broken when SA was renamed because even if the account is disabled it was attempting to do something with the account where it got screwed up so you don’t have to rename it but disabling it is fine.
Tara Kizer: Yeah.
How do I convince my server admin that multiple instances are a bad idea?
Angie Rudduck: All right, I have a good one from Karen. How can she convince the server admin that multiple instances are a bad idea? I’m guessing she means stacked instances.
Tara Kizer: Why do they think it’s a good idea? I mean why do we have to install more? It’s hard to maintain. How are you going to split the resources? You know, memory, CPU, everything is shared. I mean, yeah, you can use Resource Governor and specify our server configurations but I don’t know.
Angie Rudduck: It’s worse performance than it is cheaper, Karen. It will cost them more in headaches for performance, your clients are going to be complaining, than it would be if for instance you had couple VMs and you were being really diligent about not overcommitting your host with resources to all of your VMs.
What’s the right threshold to use for index maintenance jobs?
Angie Rudduck: Let’s see, we’ve got a few more minutes. Did anybody see anybody? I thought I saw one I liked but I’m trying to find it.
Erik Darling: Yeah, there was a Mandy Birch one.
Angie Rudduck: Oh, yes, okay. I didn’t see hers. Mandy wants to know they are trying to determine their optimal thresholds of index fragmentations to use. Deciding for reorg versus rebuilds. They’ve been using the default 5 percent reorg and 30 percent thresholds for rebuilds but they’ve been reading those are probably unnecessarily low. What do you guys recommend? What do we recommend?
Tara Kizer: What are you trying to solve? Is fragmentation really a problem?
Angie Rudduck: Are you on a Standard Edition?
Tara Kizer: Yeah, if you’re on Standard Edition, you’re not going to get online rebuilds so these indexes are coming offline while you’re rebuilding them, but, yeah. I’m not sure why the SQL industry just wants to rebuild their indexes all the time to get rid of fragmentation. It can help you with storage because the more fragmentation you have the more storage it’s using but it’s not helping you with performance. I mean you are getting updated statistics but rebuild indexes less frequently than you probably are doing now and I would set much higher thresholds.
Angie Rudduck: Yeah, I think commonly we say anywhere from 30 to 50 for reorganize and even 60 to 80 percent for rebuilds. Something else I would say is check out Erik’s blog about just updating stats.
Tara Kizer: Update statistics daily or more often and then rebuild or reorg indexes less frequently. I mean I don’t think you even need to do it weekly.
Angie Rudduck: Is there any performance impact to updating statistics?
Tara Kizer: It does add a load, yeah, especially if you’re doing a full scan on a large table but you can have custom scripts to decide on what the sampling is going to be. The bigger the table, the less sampling you may need.
Erik Darling: The other thing that can happen is if you update a lot of statistics and then a lot of queries decide to recompile. Then that can happen. Also updating the statistics themselves can have an IO impact on your server, you know, reading a lot of statistics, big tables, that can certainly make some stuff happen. There is no such thing as free maintenance but updating statistics is a much more lightweight operation than index reorgs or rebuilds.
Angie Rudduck: Yeah. All right, well, it’s 45 after the hour now in everybody’s time zone. Should we call it a day?
Erik Darling: Yeah, might as well.
Angie Rudduck: Let’s all go to the pool, drink, or something, you know.
Erik Darling: Boat drinks.
Angie Rudduck: Bye guys. See you next week.
This week, Brent, Angie, Erik, Jessica, Richie, and Tara discuss backups, failover events, tempdb errors, errors, other errors… oh, did we mention errors?
If you prefer to listen to the audio:
How do you feel about third party backup software?
Jessica Connors: We have a question from Marcy. She says, “Any experience/thoughts on backup software? Do you know will doing a local backup invalidate the restore chain up to time of last full?”
Erik Darling: Yes, unless you do copy only.
Brent Ozar: So copy only, what kinds of backups does that affect? Like if you take a full backup with copy only what does it do?
Erik Darling: It takes a full backup of the database without changing the LSN differential base. So you can take a copy of it, those are really good for restoring to refreshing dev databases or doing other stuff like that.
Brent Ozar: It’s particularly important for differentials if you’re the kind of shop that’s taking differentials. If all you’re doing is transactional backups, it doesn’t matter. Transactional backups can hook up to any full. It just really matters when you’re doing differentials.
Erik Darling: Yep.
Brent Ozar: Often when people ask this, “What kind of backup software are you using?” They’re trying to mix native backups and like Backup Exec or Veritas. But when you’re doing your backups, you want copy only. Often these third-party options don’t have those config settings.
Erik Darling: Or a lot of people if they’re doing something like log shipping won’t be able to use the jobs inside there or like they’ll have their backup software still timed. I’m like, “No.”
Brent Ozar: Yeah, oh, that’s a good point, too.
Erik Darling: That won’t work.
Brent Ozar: If you’re doing log shipping, don’t use the Backup Exec or Veritas log backup jobs. I don’t know that we even need to tell them that because within five seconds of doing it, you’re going to figure out its broken.
Erik Darling: Unless you’re not learning or anything.
Brent Ozar: Well that’s true. That’s true.
Jessica Connors: Hey, Brent. Your email is popping up on the slides.
Brent Ozar: …Twitter. Like how does that happen?
Jessica Connors: Uh-oh.
Brent Ozar: Now that people know it, they’re going to be tweeting like crazy and having it show up on the webcast.
Erik Darling: I’m famous.
What’s your favorite kind of backup software?
Jessica Connors: Do we prefer any certain kind of backups? Like on SQL Critical Care, like the intro or our sales calls, they say they’re either using SQL native backups, they’re not doing transaction logs, or they’re using [inaudible 00:01:58] third-party software. Is there one that’s standard? Better than the other?
Brent Ozar: All right, we should go around through alphabetical order. Angie, what’s your favorite kind of backup?
Angie Walker: Ola Hallengren.
Brent Ozar: Let’s see here. The other Brent Ozar in the black shirt.
Erik Darling: I like Dell LiteSpeed.
Brent Ozar: Why do you like it?
Erik Darling: Because it has cool stuff other than backups. You can read transaction logs with it which I love and you can do object-level restores which I love.
Brent Ozar: Yeah.
Erik Darling: DBA-friendly stuff.
Brent Ozar: I would have to say as the other Brent Ozar, I agree with you there. The ability to pluck objects out of a restore, that’s freaking phenomenal. To be fair, IDERA SQL Safe and Redgate SQL Backup have those same capabilities as well. Richie, how about you? What’s your favorite kind of backup?
Richie Rump: A DBA that does them.
Brent Ozar: Yeah, right. Or Azure? That was your chance to say Azure.
Richie Rump: I said that last time. I said that last time.
Erik Darling: SAN snapshots.
Brent Ozar: Tara, how about you?
Tara Kizer: I used LiteSpeed back when it was Quest. We converted over to Redgate SQL Backup just because of the cost reasons. They were basically the same product but Redgate’s solution was just so much cheaper and they threw in SQL Bundle for the whole DBA team at a job a few years ago. But native backups with compression. As long as your backups are compressed, that’s what I’m happy about. Then having full backup, possibly differentials and transaction logs, very frequent transaction log backups.
Brent Ozar: I like it.
Should you run CHECKDB on a log shipped secondary?
Jessica Connors: All right. Let’s move on to a question from Heather. She says, “Do you need to run DBCC CHECKDB on a log ship secondary that is always in restoring state?
Tara Kizer: So you can’t, right? I mean because you’re not able to run commands on it.
Brent Ozar: Can you take a snapshot on a database that’s in restoring?
Tara Kizer: No.
Brent Ozar: I didn’t think so.
Erik Darling: No. You could bring it into standby and do that but…
Tara Kizer: You can bring it into standby mode every single time log shipping has to do the restore which is generally every 15 minutes or more frequently. Is your DBCC CHECKDB command going to complete in that window, which it’s not on larger databases. So, you’re screwed there.
Erik Darling: Well log shipping won’t pick back up until you take it out of standby. So it will just accumulate logs.
Brent Ozar: Oh, no. It will keep right on going. It will just go to the next log.
Tara Kizer: You’d have to disable the jobs for it to stop.
Brent Ozar: Especially if you’re one of those guys who sets up log shipping to kill all the user connections whenever it’s time to do a restore.
Erik Darling: I’m not one of those guys.
Brent Ozar: Nice.
Why don’t my Extended Events work when I fail over?
Jessica Connors: All right. Nick asks, “Is it possible to hook into failover events? My extended events are never on after it fails over. It doesn’t happen often but it’s a pain to lose all my query [inaudible 00:04:35] events.”
Brent Ozar: Oh, wow. I bet you’re using AlwaysOn Availability Groups. So why wouldn’t you just run it all the time? If it’s an extended events session why wouldn’t you run it on every server?
Erik Darling: Oh, I bet he doesn’t have them set to automatically start when SQL starts.
Brent Ozar: When the server starts?
Erik Darling: Yeah.
Brent Ozar: Oh. Okay. So if it’s a failover cluster, you probably want it to just start every time the SQL Server starts up. If it’s an availability group and you’re failing around the AG from node to node, I would have it run with server startup there too. Just so that, because people could start running queries on the secondary. Tell us more about, if you want to, Nick, follow up with more about your question and your scenario, that’s curious, I’d like to hear more about that. If it’s kind of extended events, I don’t know that I want to hear more about it, but I probably should.
How do you know when Resource Governor is causing a bottleneck?
Jessica Connors: Question from Dennis. He says, “How do you know when the Resource Governor is causing a wait type?”
Brent Ozar: Oh, that’s a good question. Oh, without hitting the Resource Governor DMVs, I don’t know if you could. There’s Resource Governor DMVs where you could look at the pool.
Erik Darling: What wait type specifically?
Brent Ozar: SOS_SCHEDULER_YIELD. So if you’re banging up against CPU. If you have a CPU limit [inaudible 00:05:47] latch, now if you’re dealing with the new storage waits. That is such a good question. I’m actually going to look at that in the transcripts because that’s a great idea for a blog post because I want it to hit it first the Ask Brent. I would want to know during a five-second sample that queries are being throttled by Resource Governor and I don’t know how to do that offhand. If anyone knows in the listeners who is watching, in the chat feel free to post in a question that answers that because we would all love to see that and then you’ll save us from doing work. We don’t really want to do work today. We’d rather surf right now.
Angie Walker: Hey, I have to work today after this.
Richie Rump: I haven’t been able to work today. So I would like to do some work today, how about that?
Brent Ozar: Oh, I’m scheduled in meetings with all of you. I guess that’s actually work.
Angie Walker: For you.
Brent Ozar: Yes, for me.
Are there any bugs with SQL Server 2014 SP1 CU6?
Jessica Connors: Let’s see, do we know of any issues or bugs with SQL 2014 SP1 CU6. We have our buddy, Scott, he is about ready to deploy to production after lab testing.
Tara Kizer: That’s very specific.
Brent Ozar: There’s one with Hekaton. If you’re using Hekaton there’s an issue with backups. I don’t know that the hotfix is available to the public yet. Right now they’re saying if you have Hekaton and you’d like backups, because you know, they’re kind of nice to have. Then you want to stick with CU3 I believe it is. There’s a regression that came in, then 4, 5, and 6. That’s the only one that I’ve heard of though.
Angie Walker: I used SP1 CU6 at my last place. We rolled it out. We did dev and staging, all our environments. We didn’t have any problems but it’s going to [inaudible 00:07:28] vary everywhere you go. So if you tested it in your environment…
Brent Ozar: I call BS on that because this came out April 19th. You’ve been working with us for a while.
Angie Walker: Oh, no, no, no, maybe it was…
Brent Ozar: Are you holding out two jobs? Are you working somewhere else on the side?
Erik Darling: Moonlighting.
Angie Walker: Puppy databases.
Angie Walker: I think I missed the SP1 part. We were just running CU6. I think RTM CU6.
Brent Ozar: There you go.
Angie Walker: Never mind.
How do I move a lot of data into the cloud?
Jessica Connors: Phyllis says, “I’m a developer and have restorative backup to my local machine. I need to remove most of the data so it can be easily copied up to Azure for testing. Most of the data is in three tables. These three tables have between 500 million and 800 million records I need to delete. What is the fastest way to delete this amount of data?”
Erik Darling: Don’t do it.
Jessica Connors: “I have a bunch of a foreign keys in the mix so I don’t think I can just copy the data to keep truncate and move it back.”
Erik Darling: You don’t have to do any of that. You don’t have to a lick of work. You can script your database to statistics only and you can put that anywhere. You can actually just run that as a script and it will create all your tables and it will create the associated statistics. Then every query you run, SQL will act like it’s hitting those tables because the statistics will feed the optimizer certain information. I think there’s a blog post out there somewhere about it that I’ll dig up.
Richie Rump: Right. But if in this, so if you’re moving it up to Azure for testing, you’re probably doing some app testing. How would that work with that, Brent Two?
Brent Ozar: It’s going to fail anyway. It’s Azure.
Richie Rump: Oh.
Brent Ozar: I kid. So what you do and to elaborate on Erik’s just because not everybody is going to get this. You have to go into tools options in SSMS. Scripting the statistics is not on by default. So go into tools, options, and there’s a script set of options. So whenever you go to script out a database, you need to include statistics and things like partitions, partition functions, there’s a bunch of things that aren’t included by default. Then after you change those options, you can right-click on the database and go script, and there’s a little wizard for it. So, Richie, brings a great question. What if there’s—or if there’s some parts of some tables that you want to keep, you can script out those tables. You can script them as inserts with the select, with the data inside there. Or, if you’re a developer, dude, you know how to insert data out. You go through and select the data out and you go insert it in somewhere else. So create the schema up in Azure first because [inaudible 00:09:53] that’s going to fail, you’re going to need some kind of objects that aren’t supported in Azure SQL DB. You’ll go figure out what those are and then after you fix those, then you go insert the data up there, just the parts you need.
Tara Kizer: If you have queries that can determine which parts you need, you could just BCP the data out using those queries, using a view, or a query, and then BCP that data into Azure.
Brent Ozar: Yeah, it’s going to be fast.
Tara Kizer: Or SSIS, you know, import/export wizard, whatever.
Richie Rump: That’s what I would normally do is if I need to copy from one place to another, it would just be a query or something, dump into a clean schema and then away we go.
Jessica Connors: All right, Nick R., extended events, Brent.
Brent Ozar: Oh.
How do I capture queries that last longer than 5 seconds?
Jessica Connors: He said, “It’s actually not an AlwaysOn Availability Group. It’s a window cluster.” He wasn’t aware of setting and automatically start extended events on SQL Server. He is looking at any query that runs longer than five seconds, doesn’t have a DBA, so he uses the extended events to hit any one with a stick. [Inaudible 00:10:47]
Brent Ozar: I like it.
Erik Darling: Five seconds seems a little bit low to me unless there’s something really cool about your environment where you have like an SLA of under ten seconds. But any query over five seconds seems a little punch happy to me.
Tara Kizer: I used to support a system that had an SLA of three hundred milliseconds. It was a big deal. So five seconds, someone would have been at my door.
Brent Ozar: So Tara… she stood there with a kill command ready to fire.
Brent Ozar: We should also say too, so now you learned something. You have the session for setting for extended events to turn it on. While we’re talking extended events, we actually like extended events. Be aware that the more stuff you capture, you can cause a performance problem. So if you get things like the query plan and sometimes if you get the full text of the query you can cause incredible slowdowns. So just make sure that you’re gathering as little data as possible in order to get your stick on.
Tara Kizer: We did our query performance monitoring outside of SQL Server and instead the application logged all this information and we used Splunk to turn through the data and it would send alerts if performance was poor. So we didn’t have to add any overhead to SQL Server.
Brent Ozar: Did you just have like your own inside app you would start logging whenever you called Sequel Server log…?
Tara Kizer: I don’t know how the developers did it but it was all developer magic to me. Then we had Splunk.
Brent Ozar: Smart people like Richie. I would say too, if you’re interested in that and you’re a developer, check out the MVC MiniProfiler. The MVC MiniProfiler, you don’t have to be using the MVC design pattern but the MVC MiniProfiler will log whenever you write queries. It will log what queries they are. You can log it to, I want to say, not just SQL Server but Redis and other caching type layers. So you can then analyze which queries are taking the longest in your application. It was totally open source, totally free, it’s by the guys behind Stack Overflow and it’s what they use in order to monitor their query…
Have you ever seen this TempDB error….
Jessica Connors: A question from Justin. He’s wondering if we have ever seen this error in tempdb. Have you seen a query case cause this error in tempdb? The error is “property size not available.”
Brent Ozar: I have when running SSMS 2016 against an older SQL Server instance. Older being like 2014 or things like that. It has something to do with it expects memory in memory objects in there. So make sure you’re on the latest SSMS 2016 or release candidate or whatever they’re calling it. Or just try with SSMS 2014.
Jessica Connors: Let’s go back to Nick. He says, “Is there a way to know if my extended events are taking too many resources? I don’t log too much but Brent mentioned the query text which I do log.”
Brent Ozar: There’s a bunch of wait types. If you go to brentozar.com/askbrent. Ask Brent will give you your wait types on a SQL Server and if your extended events wait types show up as big on the list, it can be an issue. Doesn’t mean that it is but you may be just tracing a bunch of stuff. That doesn’t mean it’s a bottleneck. What I would say is too, when you set up your, and we talk more about the wait types in Ask Brent’s documentation. When you set up your extended events session, log asynchronously off somewhere else to a file and allow multiple event loss. There are settings to say, “Don’t lose an event no matter what it is.” You don’t really need that. Allow multiple event loss in case the SQL Server is under pressure. That way you can kind of make the increase more likelihood that you’re not going to slow the server down.
How do I know if my network is my backup bottleneck?
Jessica Connors: Let’s go back to backups. Jonathan says, “In my quest to make our backups faster, I found that specifying the buffer count and max transfer size values have increased speed immensely while stripping the backup to multiple files has had no effect. My server is now under significant CPU disk pressure at this time. Is network bandwidth my limiting factor?”
Tara Kizer: You can test it by backing up to the nul device to see what your throughput should be without any external factors such as the network. So do the backup command, do it to the nul device instead and that will tell you what your system can support. Then you compare that to what your backup time is if it’s going to a NAS device and you know what the network is doing at that point. But buffer count and max transfer size can make significant improvements in your backup times. There are specific values you can pass it if your backups are to a NAS or to a SAN or a local drive. I don’t have those numbers memorized but I think there’s some blog articles out there that can tell you what the optimal values are for those two for wherever you’re backing up, wherever you’re sending your backups too.
Erik Darling: Another limiting factor might be the disk you’re backing up to. If you’re only backing up to one disk, you could just be saturating that disk with the backup. So it might be a bottleneck there. Another thing to be careful of is if you’re altering buffer count and max transfer size and you’re also using compression, compression has a [siren wails in background]. Whoa.
Tara Kizer: New York City.
Erik Darling: Crime free. When you use compression, it’s three times the memory penalty. So if you’re altering buffer count and max transfer size, you want to watch your memory usage during backups because there’s three different streams. There’s the read, compress, and write up stream. So there’s three times the memory overhead as a regular backup. Just be careful with that. Don’t set your values up too high, you could crash your server.
Are there issues with the latest Visual Studio database projects?
Jessica Connors: Let’s move on to a question from Sheldon. “Do you know of any issues with upgrading to the latest versions of SQL database projects in Visual Studio that might impact releases?”
Brent Ozar: Richie, any word on that? I don’t think any of use it.
Erik Darling: I’m trying to Brady Bunch point at Richie, but it’s not working.
Richie Rump: Yeah, I know nothing, especially as of the last couple Virtual Studio versions, they’ve done a pretty good job about not breaking anything even in the solution. Because before we used to have different solutions but we could link to the same files underneath because of the different solution. The different Visual Studio versions had different stuff inside of them but now especially, I think starting in Visual Studios 13 and especially now in 15, everyone on those lower versions can use it. So I would test it out, upgrade it, have a couple people look into it, but yeah, you shouldn’t have any problems with it at all. Thank you for using it because not enough people use database projects. I think it’s a neat little tool there.
Tara Kizer: We used it at my last job extensively. It has been five months, so obviously as far as latest versions go, service packs, we went beyond the latest versions from when I left at least. But we didn’t have any issues with the various versions as long as you are running the right Visual Studio version or lower for the SQL Server version, you’re okay. If you are trying to use a higher version of Visual Studio than SQL Server, when I say higher version I mean like let’s say SQL 2014, I forget what the versions was. But like Visual Studio 2013 you would use for SQL Server 2014. If you have 2012, you would use for SQL 2012. But you could also use VS 2010 Limited.
Richie Rump: Yeah, don’t use 2010. Let’s not go back there.
Tara Kizer: We had to have it installed as well as Visual Studio 2008 because we had some SQL Server 2008 R2 things that we still were supporting so we had multiple versions of Visual Studio on our desktops.
Richie Rump: I’m glad I’m out of that game and not having four versions of Visual Studio anymore.
Jessica Connors: All right, another error somebody is seeing.
Erik Darling: Yay.
How do I know if I have a memory problem on a 296GB RAM box?
Jessica Connors: It says, “There is insufficient memory available in the buffer pool during very busy times. It’s 296 GB box of RAM on the box. Is this because SQL tries to allocate a minimal amount of RAM for a query?”
Brent Ozar: You know what you want to do is run sp_Blitz. sp_Blitz will tell you the number of times you’ve had forced memory grants. Forced memory grants are when SQL Server says, “Look, I know you want a Gig of RAM to run the [inaudible 00:18:51]. It’s $3.95 and 14KB of RAM.” SQL Server tracks this in the DMVs. You can see the number of times it’s happened since startup. It doesn’t tell you when they’ve happened, it just tells you that they’ve happened. So run sp_Blitz and it will tell you all kinds of things about forced memory grants. It’s just a nice thing to run in terms of a health check too. It will tell you things, if you have suspiciously high free memory, which can indicate that queries are giving a large memory grant and then releasing it. Lots of neat memory troubleshooting things that we’ve had across the last five or ten releases in that.
Jessica Connors: People are just copying and pasting their error messages now. You can’t do that.
Erik Darling: Jessica has to read this stuff, man.
Brent Ozar: “Have you ever seen a rash this bad?”
Angie Walker: Thank god they didn’t send us pictures.
How do you deploy code with zero downtime?
Jessica Connors: David has an actual question. He says, “Do you know a way to apply a new app release with zero downtime?”
Tara Kizer: Yeah, just make sure your code is backwards compatible. If you’re going to be adding columns that your application is not using SELECT *. You’re altering store procedures. If you’re adding new store procedures, those get added before you change your code. Yeah, you could definitely do it with zero downtime. We did it all the time. We had releases every two weeks for the e-commerce website and that was with zero downtime. They use farms of virtual servers for the web tier and all that stuff and then they just made sure all the store procedure code and all the schema changes, it was always backwards compatible. So no matter what version of the application that they ended up using after the deployment was done, it still worked with whatever. We didn’t have to roll back the databases changes.
Erik Darling: Another thing you can do is only add new features. Don’t fix anything old.
Brent Ozar: The trendy term for developers is called additive changes, that you’re only adding things, you’re never taking things away. If you want to see how Stack Overflow does it, Nick Craver, their site-reliability engineer, wrote a blog post called “Stack Overflow: How We Do Deployment – 2016 Edition.” He goes into insane details about how they do deployments with near zero downtime. Another site if you’re interested in this kind of thing is highscalability.com. High Scalability profiles a lot of websites and how they do database and IIS and Linux-type deployments. There’s a lot of spam in there, there’s a lot of noise. But they’ve got some good signal from time to time, like how Etsy does deployments.
Richie Rump: Yeah, it’s definitely a practice. You’re not going to just like jump into it and say all of a sudden “I’m doing it.” It takes a lot of work to change the way you do development in order to get zero downtime. It’s definitely an effort where all levels need to be bought into it.
Where can I learn more about columnstore indexes?
Jessica Connors: Our friend Nick is back. He says, “Any great resources on understanding columnstore indexes? Trying to wrap my head around them but I can’t figure out when to use them over row storing and how to set them up.”
Erik Darling: Niko Neugebauer, I believe that’s how you pronounce that last name. I’ll get a link to it but he’s done like an 80 bazillion-part series on columnstore indexes which answers more questions than you may even possibly have. He started like when they first dropped and he’s sort of cataloged things through until now. So there’s a lot of good information in there.
Brent Ozar: His website is nikoport.com, N-I-K-O-P-O-R-T dot com. He’s from Portugal. Niko is his first name so that’s where that URL comes from and 80-some parts in there in that blog post series. Where it’s for is especially perfect for data warehouses where you have a fact table that’s really wide, so it’s got lots of columns in it. It’s really deep, it’s got lots of rows in it. You can never predict what users are going to filter on or what they’re going to sort by and the table is highly compressible because it has the same data in it over and over again, like sale dates that have compressrf really well. Quantities, those just compress really well as well. So it’s not unusual to see like an 80, 90, 95 percent compression rate with columnstore tables but it is very specifically for data warehouses. It is not for OLTP.
Erik Darling: And it does a lot of really great things for like aggregate queries too.
Brent Ozar: Yeah.
Erik Darling: So it’s really, whiz bang on that.
How do you make Reporting Services highly available?
Jessica Connors: Let’s talk about HA. Monica had asked, “What are the best options for SSRS HA? We are most likely moving to an HA DR solution using a failover cluster with log shipping. Right now we run SSRS on our main production server.”
Angie Walker: Where’s Doug?
Brent Ozar: I sat in his [inaudible 00:23:36] session so I know this answer. What you do for SSRS is you run it in a bunch of virtual machines. You run it in a whole bunch of virtual machines that are behind a load balancer and if any one of them trips and falls over, you just don’t care as long as there’s others that are all pointed to the same report server DB. So if you want to patch one, you can, totally not a problem. SSRS, relatively lightweight resource requirements so you don’t need physical boxes for it in most cases. You can just get by with relatively small VMs, but a lot of them.
Jessica Connors: Michael keeps copying and pasting that error.
Angie Walker: Jessica is not reading it, sorry.
Why can’t I connect to my server?
Brent Ozar: Connectivity issues. So Michael asked a connectivity issue thing. We’ll be honest, it’s way faster, there’s a slide on here that says, “For multi-paragraph questions, go ahead and ask those at dba.stackexchange.com.” We got that up on the screen right now. Go ahead and put in dba.stackexchange.com in your browser whenever you’ve got multiple paragraphs that are involved in your question or error message. It’s a wonderful site, really love it a lot because other people answer it than us. That’s why I’m one of its biggest fans.
Erik Darling: Yeah, I mean, just generally looking at that, I would just say make sure that SQL Browser is turned on.
Tara Kizer: Browser and then see if you can telnet to the port on the client machine, do a telnet session and telnet to the SQL Server with the port. If it returns a blank screen, it means your connectivity is fine. If you get an error, you’ve got something blocking the access, network firewalls, something.
Erik Darling: Something is amok.
Are there any drawbacks with trace flags 1204 and 1222?
Jessica Connors: Let’s do one more. Marcy asks, “I know I can try this on a pre-production server just wondering if you do or do not recommend setting trace flags 1204 and 1222 to get additional deadlock information?”
Erik Darling: Yes. But if you are on a newer version of SQL, you can get really great information from the extended events session. There are queries out there to do that if you feel like [inaudible 00:25:36 doggeling].
Brent Ozar: Yeah, really good. So if you Google for like “extended events deadlock session” there’s one blog post that’s notoriously great for this. Read the comments of the blog post. I can’t remember the author’s name but if the webpage is black, keep reading through all the comments and there’s lots of improvements to the query inside the comments.
Jessica Connors: Cool. Well it’s 11:45 here in Chicago. So…
Erik Darling: 12:45 New York time.
Brent Ozar: Time to start drinking, whoohoo.
Erik Darling: Water.
Brent Ozar: All right, bye everybody. See you all next week.
This week, Richie, Erik, Angie, and Tara discuss deadlocks, replication, SQL Server 2016 features, and more.
If you prefer to listen to the audio:
Does NVARCHAR(255) Cost More Than NVARCHAR(30)?
Angie Walker: All right. So Steve has a nvarchar(255) column that he knows will never have more than 30 characters. Will it take extra space to store this column and will there be a performance penalty?
Tara Kizer: It’s already using double since you’re using the nvarchar and not just varchar. It’s Unicode so it’s going to be using 60 bytes instead of 30. But is it going to use more space? No. I’ve seen developers want to do this where they want to have every single column standardized to the same data types and size. I don’t understand that reasoning except for laziness. It doesn’t take long to figure out what each column should be. I mean what happens if it ever…
Erik Darling: My only issue with it as like from a development standpoint is SQL error messages for if you try to insert too much data into a column suck. It’s like “string or binary data might be truncated.” And you’re like, oh, I’ll just go figure out which column that was because the error message doesn’t tell you. Nothing tells you. So you have to go back and guess a million times. So I understand why developers are like, “Let’s just make this a 255 because it’s a variable column anyway. It’s not going to mess anything up.” But, you know, figuring out the right length should be a priority at some point because you may have inserted incorrect data into a column if you give it too long of a length if it’s more than it will ever be or more than it could ever reasonably be. But I understand why developers want to do it because tracking down those errors sucks.
Why Is Perfmon Wrong or Missing Counters?
Angie Walker: Okay, Jason says their performance monitoring tool is showing that OS CPU is around 25 percent. The instant CPU that they’re getting from a DMV is running around 80 to 90 percent. “It has been suggested our counters are messed up and to unload and reload these counters. Any experience on doing that?”
Tara Kizer: I have many, many years ago. I don’t remember what it was but back in the day there were lots of issues with performance counters. I just don’t remember what you have to do. You could just restart SQL Server or the box to possibly fix this issue. That was one of the solutions back in the day. But yeah, you’ve definitely got something messed up here because the ring buffers should show the same CPU utilization as what Performance Monitor or Task Manager is showing.
Erik Darling: Yeah, I’ve had to reset them a couple times. Actually when you clone a machine and you turn into a VM a lot of performance counters get screwed up. Like a lot of them just don’t even show up anymore. So I had to like mess with loading and reloading them. If I remember correctly, they were pretty simple DOS commands.
Tara Kizer: Yeah, yeah.
Erik Darling: But they weren’t like… you know.
Tara Kizer: Yeah, I don’t remember what they are.
Erik Darling: I lost my notes on that, sorry.
Richie Rump: Sounds like a blog post, Erik.
Erik Darling: You know, Richie, your blogging has been pretty light lately. If you want to take that, it’s all you.
Richie Rump: Yes, yes.
Erik Darling: I leave that in your capable hands.
Richie Rump: But I’ve been working, so, there’s that.
Erik Darling: Yeah, see all those pictures from Disneyland, hard worker.
Richie Rump: Disneyworld.
Erik Darling: Whatever.
Richie Rump: Disneyland is a different place.
Angie Walker: Folks, if you want your questions answered. You have to type them.
Erik Darling: I see one here about deadlocks.
Angie Walker: I saw that one.
Richie Rump: That movie was great. I loved that movie.
Angie Walker: The difference between Jessica and I reading it, I know when you guys aren’t going to want to answer some of these.
Tara Kizer: You can go ahead and ask it.
Do File Growths Cause Deadlocks?
Angie Walker: All right. So for deadlocks from Adeels Webb, “When there is a file growth we see deadlock and the object identified is one of the indexes. Is there a way to debug this related to storage or IO?”
Tara Kizer: I would be looking definitely at your IO. I mean how long is that growth taking and if it’s on the data files, do you have Instant File Initialization setup because if it’s not setup then it has to zero out the file and that can talk awhile depending on how slow your storage is. The log file, you can’t use Instant File Initialization but the data files can. So take a look at the perform volume maintenance tasks inside the local security policy on your box and see if the SQL service account is a member of that privilege. If it’s not, you should add it and restart SQL.
Erik Darling: Or in your maintenance window. Not like…
Tara Kizer: Yes, not now.
Erik Darling: Not like right now. Don’t tell your boss we told you to do it right now.
Tara Kizer: I would also be looking at performance monitor counters average. Look at the logical disk counter. The average disk seconds reads and writes and if your values are over say 20 milliseconds you potentially have a storage, an IO slowness issue. The values are going to be in decimal though. So it’s .020 I believe is 20 milliseconds.
Angie Walker: Whatever she said, I don’t…
Erik Darling: Sounds good to me.
How Do You Pronounce VARCHAR?
Angie Walker: Yeah. Here’s a good one from Greg. “Is it ‘var’ ‘car’ or ‘var’ ‘char?’ What do you guys say?”
Tara Kizer: I actually say “var” “char” but since you guys had already said “var” “car” I went with it.
Richie Rump: Wow.
Erik Darling: I always say “var” “car” because the variable is character.
Tara Kizer: Yeah, exactly. Yeah, I do say “var” “char” but I see why people say “var” “car.” I think most people do say “var” “car.” I’ve worked with a lot of developers and DBAs in the past 20 years and most people say “var” “car.”
Erik Darling: In Boston, it’s [speaking with a Boston accent] var car though.
Angie Walker: I also recently heard that it should be “vare care” because variable characters. So like you were saying, Erik, but I was like that sounds funny. Like it sounds like a Care Bear character maybe.
Tara Kizer: I think the real question though is do you say Sequel or S-Q-L? That’s an important one.
Erik Darling: Sequel, I don’t have time for S-Q-L.
Tara Kizer: Microsoft is the one who created the product and they specifically said on the Wikipedia page it was pronounced Sequel and not S-Q-L. People from different languages had a problem with it because when they see the letters S, Q, L, it does not pronounce “sequel” to them. So Microsoft changed the Wikipedia page to say it can be pronounced either way. But the original product was pronounced “Sequel” Server.
Angie Walker: Interesting.
Tara Kizer: There was this big debate on the Wikipedia page, I don’t know, like ten years ago or so.
Erik Darling: Which brings us to another interesting question, is it “wi-ki” or
Angie Walker: Wiki. What about “DAY-ta” or “DA-ta?”
Tara Kizer: “DAY-ta.” “DA-ta” drives me crazy.
Richie Rump: Captain Picard called him Data. So it’s “DAY-ta.” Captain Picard is always right.
Angie Walker: I like “DAY-ta” too. It also sounds kind of funny “DA-ta” base because of the off “a” is…
Are There Any Alternatives to Transparent Data Encryption?
Angie Walker: All right. Adeels has another question. “What do you guys think about TDE with mirroring or replication and are there any alternates to TDE?”
Erik Darling: Not within SQL Server. TDE is what you get and that’s an enterprise-only feature. If you use it alongside any other feature, it’s going to be interesting because TDE breaks a lot of stuff. TDE, it encrypts TempDB and it also breaks in some file initialization because you have to write out a bunch of junk. So it’s totally fine to use the features together, just be aware of how they operate next to each other.
What Happens If I Run Standard Edition on 20 Cores?
Angie Walker: Sean says they have a server with 20 cores but they’re only running Standard Edition that only supports 16 cores. Is there a negative performance impact with this configuration?
Erik Darling: There is if you have to cross NUMA nodes for some things.
Angie Walker: So when would you see that scenario? Do they have to have a specific of NUMA nodes for it to come into play or they already have too many NUMA nodes?
Erik Darling: So is it, how many CPUs? I get that it’s 20 cores but how many…?
Angie Walker: So we have two CPUs.
Erik Darling: Oh, wait, wait, wait. Hold on, yeah… VM 1 uses 20 cores…
Angie Walker: No, the one below that.
Erik Darling: Oh.
Angie Walker: The one from Sean. Yeah, I didn’t read the giant one.
Erik Darling: It depends, Sean. Two CPUs, so no, probably not. But it’s not ideal. It’s not something that I’d aim for. It sounds like someone bought like the dual ten core CPUs thinking that they were going to be really fast and awesome but they’re probably like a really low clock speed or something which is what dual ten core CPUs are. So for something like Standard Edition, we usually recommend getting like dual two four six or eight, whatever the highest clock speed is. Because you go up to 16 and you get the fastest processers to push your workload through. Dual ten core chips usually have a much lower clock speed and kind of stink.
Angie Walker: Yeah. I’m just going to follow up, Dennis, sorry, sad face, that license question. That’s a big license question. We’re not Microsoft. We don’t charge you so I would talk to your Microsoft rep or your software vendor rep. Ask them how you’re going to get charged. Someone else, sorry. But there was a question.
How Should I Document SQL Server and Scripts?
Angie Walker: Oh, I think Richie I want to hear from you from Brandon’s question. “Do you recommend any tools for documenting changes in SQL scripts? How about documenting SQL Server? Right now they use Excel and a lot of worksheet tabs to document their SQL Servers.”
Richie Rump: So I don’t have any recommendations for any tools to do this. We always had, at least every organization I’ve been in, we’ve had pretty rigorous change control. So all the scripts would be not only put into a version control system like Git but they would also go down to test, preproduction, and then finally a production area. So we usually didn’t have that big of a problem because all the changes were being tested as it went down the train, the pipeline. So as far as documenting SQL Server, I know there’s a couple products out there. I would just try them out and see how they work for you.
Erik Darling: Yeah, Red Gate has a tool called SQL Doc that works all right.
Richie Rump: Yeah, I’ve written kind of my own tools as I kind of saw fit. But we didn’t have any really big documentation requirements either. So it depends on your requirements and your budget and how much you’re willing to put into it.
Erik Darling: I bet someone out there who really likes PowerShell and really wants to tell you all about PowerShell has written something that would document SQL Servers.
Richie Rump: Yeah, there’s a guy here in Florida, a couple hours up the road here. He spent many years working on a PowerShell documenter, so that’s probably a good one to check out.
Erik Darling: There we go.
Richie Rump: Kendal Van Dyke. Kendal Van Dyke’s, what’s that? SQL Power Documenter or something like that?
Erik Darling: Power Doc is it?
Richie Rump: Power Doc, that may be it.
Erik Darling: Maybe.
Richie Rump: Because everything was being…
Erik Darling: There’s someone walking by your window.
Richie Rump: Dude, it’s the mailman. Oh my gosh.
Angie Walker: He’s already that way.
Richie Rump: He’s at my door. So…
Angie Walker: Wait your dogs are going to start barking.
Erik Darling: There he goes again.
Angie Walker: Okay.
Erik Darling: All right, who’s next?
Can I Monitor for Changing Execution Plans in 2008R2 and 2014?
Angie Walker: So, Jason. He wants to know if there’s a way to monitor when SQL decides to change plans or use a bad plan. He knows in 2016 they’re introducing Query Store but what can he do for 2008 R2 or 2014?
Tara Kizer: I’ll tell you what I implemented at the job I was at for 12 years. We had a very very critical system. We’d have severe performance issues if bad plan would happen for a critical store procedure. Every single time I’d just recompile the store procedure and the entire system would start performing better because the bad plan would cause really high CPU. So what I did is I used the ring buffers DMV that was mentioned in an earlier question and I wrote a store procedure to query that and to monitor CPU utilization because I knew that CPU utilization would remain at say 30 percent during the day when this issue didn’t occur. But it would go above 60 percent, 80, 90 percent. So I would monitor CPU utilization and then check the number over like three minutes. If it’s at a high number across several samples, I would then look at the plan cache. What was using the most CPU in the plan cache and then recompile that object. Then it would wait a minute and then check to see if it improved. If it didn’t improve, it would then recompile the next one at the top of the CB list. That caused it to not have to wake me up in the middle of the night or not have to manually recompile store procedures. So you do have that option, the ring buffers DMV gives you CPU utilization and you just write code to do this work for you.
Erik Darling: If monitoring the ring buffers is difficult or beyond your gasp, sp_BlitzCache can help a lot with that. So what you’ll see, you run sp_BlitzCache by CPU. You may see a line for your store procedure and then you may see a separate line for the text of the store procedure that has higher average CPU or reads or something or max worker time or something like that. As like just sort of different averages that make you think, “Okay, this statement may have gotten the wrong plan or something is amuck because this store procedure has these numbers but the statement has these numbers.” So you could see some differences there. If you’re feeling really fancy, in 2014 you can also use Extended Events to capture when plans recompile. I wouldn’t grab query plans along with it maybe because that’s a dodgy enterprise in Extended Events but it certainly is an option.
Tara Kizer: But maybe storing the results of BlitzCache into a table and then comparing the average CPU average reads and if it’s off by a certain percentage, then you possibly know that a plan difference is enough to have caused an issue and that you have a bad plan.
Erik Darling: Yep. There’s some like really interesting parameters in sp_BlitzCache that I’ve never used. Like you can set up variances for like the difference between those things. If it’s over a certain amount it will warn you about it but usually there will just be sort of a general warning for parameters missing where it will tell you all about that.
Does Replication Work in Amazon EC2?
Angie Walker: Good information guys. So Mike says that they’re addicted to replication, sorry, Mike. There might be pills for that now. But they’re moving to AWS EC2. “Any comments on replication in the EC2 section of the cloud?”
Tara Kizer: I don’t have any specific experience with this but I would probably just be concerned about latency between the publisher and the subscriber. Or I should say between the publisher and the distributor and the distributor and the subscriber. Because the publisher doesn’t connect directly to the subscriber goes to the distributor. Make sure that it’s flowing nicely because any kind of backlog on the distributor subscriber or publisher can cause for you to take production down if you run out of log space where all the replication log records are being stored in the publisher log file.
Erik Darling: Yeah, one thing about EC2 instances is that you have to pay for a pretty large box before you get over the initial networking bandwidth of 125 megs a second I think. So if you’re really pushing a lot of data across, I would pay a lot of attention to the type of box and monitor how much network bandwith and utilize and all the other stuff and just6 any latency, I would really want to keep really close eye on the latency whether it’s network or just between all the boxes.
Does Brent Still Work Here?
Angie Walker: I saw that Richie. Did anybody read the permanently storing objects in TempDB blog?
Erik Darling: No, I only read my own blog posts, sorry.
Tara Kizer: Who wrote it? Was it one of ours?
Angie Walker: It went live today.
Tara Kizer: Okay, then I didn’t read it.
Erik Darling: Brent wrote it.
Angie Walker: I didn’t get to it yet. I’m backlogged on all of Erik’s while I was gone.
Erik Darling: Not “me” Brent. Not Erik. Real Brent wrote it.
Angie Walker: The real Brent wrote it.
Richie Rump: He still works here?
Angie Walker: Yeah. Well… define work.
Erik Darling: He shows up in chat once in a while.
Angie Walker: Oh no, that’s Erica.
Richie Rump: Yeah, that was Erica.
Tara Kizer: Oh, I did read this before it got published. Is there a specific question on it?
Angie Walker: Just what do we think about it.
Tara Kizer: Oh, I mean, I don’t think that TempDB should be a place where you store objects permanently. If you need to store objects permanently, setup a database for it.
Erik Darling: I think, great post, Brent.
Angie Walker: Or Brett.
Erik Darling: Great post, Brenda.
What’s Your Favorite Missing Feature in SQL Server 2016?
Angie Walker: All right. Well since we really have no more questions and seven, eight minutes left, we’ll follow up with Brandon’s “if there are no questions” question. “Does anybody have a favorite feature from SQL 2016 or is there anything that you wish made it into 2016 that didn’t?”
Tara Kizer: I think we can probably all agree to the Query Store. We’re all looking forward to using that but it appears to be an enterprise edition only feature which…
Erik Darling: No, they changed it.
Tara Kizer: They changed it again?
Erik Darling: They changed it.
Richie Rump: Yeah, they announced it. They changed it and they changed it back.
Tara Kizer: They changed it again? Okay.
Angie Walker: So it’s for everybody now?
Erik Darling: Real ding dongs.
Tara Kizer: The product hasn’t RTM’d yet. So June 1st, so they could change their mind again.
Angie Walker: That’s coming close though. Less than 30 days.
Tara Kizer: Yeah.
Erik Darling: So what I’m consistently mad at Microsoft about is their restore stuff. Microsoft spends a lot of time and money investing in like Oracle competitive checkboxes. But we still have the same, clunky, all-or-nothing restores. [Inaudible 00:16:54], right? It’s like if you want to restore like a table, you have to restore the entire file. There are third-party tools you can do object-level restores, you know? Like Dell LiteSpeed and probably some other backup software. Tara, does Red Gate do that object-level restore stuff?
Tara Kizer: I know it used to.
Erik Darling: Okay, so, maybe it still does. But I get continuously annoyed. Especially because Microsoft has embraced this, you know, “We’re going to support you using petabytes and petabytes of data.” But if you take a backup of that and you have to restore a table because some ding dong broke 50,000 rows in part of a table, then you still have to restore your entire database. There’s no object-level restore natively with SQL Server. There’s no way to natively read through a log file in SQL Server without memorizing those crazy fn_dblog and dump_dblog commands where you have to pass into default 64 times. There’s no good, intuitive way to figure out when something bad happened and restore it to that point. Oracle offers stuff like Flashback where you can flashback a table to a point in time. You can flashback an entire database to a point in time. You can do all this stuff and get really easy, really restorable data. You just get all your stuff back really easily. I think it’s sort of obscene that Microsoft is still making you restore a 5TB database just to get one table back.
Richie Rump: And that’s Erik’s favorite 2016 feature that’s not there.
Angie Walker: Yeah. What about you, Richie, since there are still no questions, is there anything from a developer’s side of things that you wish there was? Or you don’t really care about?
Richie Rump: It’s not like 2012. 2012 we got a lot of good goodies. In 2014, there was nothing for us and then 2016, it’s the Query Store, right? A lot of people talk about the JSON stuff. I am not thrilled with it. I haven’t really played with it too much but it’s just going to make things easier to go in and out but I don’t like the XML data type or XML stuff in SQL Server so why should I like the JSON stuff in SQL Server? It just doesn’t feel right. It’s just something else that us as developers can screw up. So it’s probably one thing that I’ll be keeping an eye on over the next few months is the JSON data type and kind of how we could use some of that responsibly and not like in the way we’ve seen some XML data types go awry.
Erik Darling: Yeah, like what developer bones got thrown in 2016 like what DROP IF EXISTS and the string splitter. That was it. Like there’s been no like further improvements to window end functions like making a using range over rows, you know, not be horrible. So no like further improvements to T-SQL to make it more ANSI compliant or add in more like the ANSI standard stuff to it. So it’s pretty underwhelming to me from at least from a development standpoint.
Richie Rump: Yeah, and if us as a community aren’t screaming about it, then it’s going to be low on the totem pole. So I think there was a fair amount of people screaming about the JSON stuff because practically every other database has JSON compliance.
Erik Darling: Practically every other database has a way to concatenate comma delimited strings without using XML path in some convoluted voodoo language too.
Richie Rump: You can always just use .NET for that, dude, come on.
Erik Darling: Yeah, I have .NET, Richie. Me.
Richie Rump: That’s why I’m here, right? That’s why I’m here. No other reason just not to write .NET.
Erik Darling: Just to make my string concatenating life easier.
Richie Rump: That’s right.
How Do You Verify Your Backups?
Angie Walker: We finally got a new question. It’s from Adeels again. He says he understands that restoring and verifying backups is the way to go. So good for knowing it. But he says it’s not always physically possible. Is doing RESTORE VERIFYONLY good enough or do you have another recommendation?
Erik Darling: Good enough for what?
Angie Walker: I think he’s trying to say if he can’t test his backups by restoring them somewhere else, is it okay to just do RESTORE VERIFYONLY and say that your backup is good and not corrupted or something?
Erik Darling: I mean all that does is test the header and makes sure that it’s a usable backup file. It doesn’t actually test the contents of it for anything. So, it’s fairly reasonable to assume that you can restore that backup. That the header and the format of the backup file are correct. The data within that could still be bonkers.
Angie Walker: So still run your DBCC CheckDB, right?
Erik Darling: Run your DBCC CheckDB, turn your page verification on, make sure your backup checksums are on. Lots of stuff to do there. Make sure that you’re getting alerts for your 823 824 and 825 errors. Other things.
Angie Walker: We have that on the web.
Erik Darling: We do.
Angie Walker: On the blog.
Erik Darling: If you go to BrentOzar.com/go/alerts. We have that all setup for you.
Angie Walker: Yeah, some good stuff out there.
Erik Darling: At least the alerts end.
Angie Walker: We’ll tell you how set them up.
Erik Darling: Yeah, basically.
What Tool Should I Use to Read Execution Plans?
Angie Walker: Sean wants to know if there’s a better program that’s free to analyze execution plans.
Erik Darling: Where you have been? SQL Sentry Plan Explorer.
Tara Kizer: That’s what we use. That’s what we use here. It’s what we used at previous jobs too.
Erik Darling: What do you using, Toad? I don’t know.
Richie Rump: That’s still a thing?
Erik Darling: I guess, yeah. I mean there’s still a Toad World website. I don’t know. Maybe someone with MySQL uses it.
Richie Rump: Maybe some of those Oracle guys still use it because that’s when I used it.
Erik Darling: Everyone uses SQL Developer with Oracle. The fancy pants one.
Richie Rump: Not in the 90s, man.
Erik Darling: The 90s are over, Richie. Sorry.
Richie Rump: No.
Erik Darling: Sorry.
Richie Rump: Next thing you know, Nirvana broke up, right?
Erik Darling: No, they’re still together. Don’t look at MTV.
Richie Rump: Whoa, man.
Angie Walker: Just go back and watch I Love the 90s on VH1 on demand or something.
Erik Darling: Kurt Loder will be there. All your friends will be there.
Richie Rump: Daisy Fuentes.
Angie Walker: On that note, folks…
Tara Kizer: I don’t think Angie is old enough for these references.
Angie Walker: Hey, I used to watch I Love the 90s. On that note, we’re going to have to end this episode of Office Hours. Thanks for watching, listening, or reading on the blog. See you all next week.
Erik Darling: Bye.
This week, Brent, Erik, Jessica, Angie, and Tara discuss SQL service packs, partial restores, breaking replication, backups, as well as their favorite TV shows.
If you prefer to listen to the audio:
Jessica Connors: Let’s start with the service pack question from Jay H. He asks, “When patching applying SQL service pack to passive node in a cluster should one put into pause mode or okay to simply run the service pack?”
Tara Kizer: I’ve never put it into pause mode. I’ve patched hundreds, maybe thousands of servers.
Brent Ozar: Well but do you patch the passive? I mean you patch the passive and there’s no services running on it so you really don’t have to worry about it.
Tara Kizer: Yeah, nope.
Brent Ozar: Yeah, am I going to have to worry about something failing over to it in the middle of the service pack? I’ve never done it either. Talk about a first-world problem there. So I’ve never paused it. I don’t know, if I was probably going to get ambitious, like PowerShell script the whole thing out, I would probably put in some kind of pause there but I’m just not that guy.
Jessica Connors: What’s this about Terabyteasaurus Rex?
Brent Ozar: That is a presentation that I do about very large databases. There’s things that you want to do to first avoid very large databases. Then once you have one, how you do to cope with the aftermath. It’s like having your own little pet dinosaur. You have to plan for things ahead of time and don’t bite off more than you can chew.
Angie Walker: Like a Tamagotchi.
Brent Ozar: Like a Tamagotchi, yes. Only you can’t carry it around in your pocket.
Jessica Connors: Is that a … presentation you’re doing coming up?
Brent Ozar: That one’s actually from our regular training classes. I’m doing it for IDERA. IDERA pays us to periodically do free presentations for the community. We give them like our menu of private training courses and just go, “Here, which one do you want to buy for the community?” They go buy one and we give it out for free to everybody and off we go. So it’s really awesome how we work that with vendors.
Jessica Connors: Nice.
Brent Ozar: Everybody wins.
Jessica Connors: All right. This question is pretty vague from Abdullah. He says, “Hello. We have just received new hardware with 124 cores and 1 terabyte of memory to host many of our SQL instances.”
Tara Kizer: Dang.
Brent Ozar: Big spender.
Erik Darling: Bad news: the only licensed …edition.
Brent Ozar: Wow. One word, virtualization. Virtualization. Normally, I’d never buy a host that big but I just wouldn’t do instance stacking. I wouldn’t run one Windows OS and then a lot of instance stacking. Have you guys ever run multiple instances on a server and what problems have you seen from it?
Erik Darling: Not willingly. I’ve inherited them and it’s always been like, okay, we’re going to really stifle this one which is less important because this other one is just choking it out anyway.
Tara Kizer: I used to have a four node cluster with eleven SQL instances on it.
Brent Ozar: Oh.
Tara Kizer: Yeah, the biggest challenge was installing patches. There was always some node that suddenly had to be rebooted. This was back on before 2008. It was horrible. Even after you rebooted the server it would say, “Oh, that server needs a reboot.” It would take an act, a miracle, for all four nodes to agree it’s now time to patch. I would reboot like 20 times before it would say it. It was horrible.
Jessica Connors: Oh boy. Did you end up running them all? Did you end up consolidating?
Tara Kizer: We ended up upgrading to newer versions where you could install it passively. Back then the SQL instance had to be in the right state on all four instances, or not the SQL instance, but you had to patch all four instances at the same time. So all four had to agree that it was ready to be patched.
Brent Ozar: Miserable.
Tara Kizer: It was horrible.
Brent Ozar: Still even today Windows patching is a giant pain in the rear. Do you want to take down all of the instances at once just in order to patch Windows? Windows patches come out kind of fast and furious. So virtualization adds that little bit layer of lower performance, and I’m not even going to go down the is virtualization slower or not. But worst case scenario, it’s a little slower and you deal with that. But then you get so much better management. Holy smokes. It’s way easier.
Jessica Connors: All right. Let’s talk about partial restores. “Can you recommend a good article on doing partial restores? I want to move my historical data to a separate NDF file so I can restore just the active portion right away so the users can use the database then restore the historical part.”
Tara Kizer: Do we have an article? I mean I know someone who does a session.
Brent Ozar: Who?
Tara Kizer: I’ve seen it twice.
Brent Ozar: Is it available publicly? Is there somewhere, a blog we can link to?
Tara Kizer: I don’t know. I’ve sure she has a blog. Kimberly Tripp has a whole session on it. At patch she’ll bring a USB, all these USBs, and she’ll unplug them to simulate losing a drive and the database stays online because it’s all in memory. It’s really cool but it has to do with the partial restores as well.
Brent Ozar: I bet if you go to hit Google and you do like “partial restore site:SQLSkills.com” I bet she’s got blog posts on it too because it’s one of her more famous demos. We talk about it in our training classes but I was just sitting there thinking, I don’t think we have a single public post on it.
Erik Darling: I was working on something similar when I was messing around with some foreign key stuff but SQL outsmarted me so I didn’t end up writing the post.
Tara Kizer: Erik will take care of that in the next 15 minutes though.
Brent Ozar: Erik “the blogger” Darling.
Erik Darling: Oh, get out of here.
Brent Ozar: There’s Bob Pusateri, @SQLBob on Twitter. Bob Pusateri has an article too on his site to help move your stuff into a historical file group. He has really nice scripts that help you rebuild objects onto other file groups which is way trickier than it looks like. There’s things like off-row data that don’t move by default.
Erik Darling: If you’re on a version prior to 2012, a lot of that stuff is still offline, especially for large varchar and varchar types.
Jessica Connors: All right. A question from John. He says, “When looking at statistics properties, it gives the date and time when statistics were last updated. How can I tell if that statistic’s object was last updated using a full scan or a sample scan and what that sample scan value was?”
Erik Darling: I know all of this.
Jessica Connors: Do you?
Erik Darling: Off the top of my head. So if you run DBCC SHOW_STATISTICS on the table and index that you’re interested in, you can either run the whole command or you can run it with stat header which will just give you the top row of output. There will be two columns in the stat header part. One will be rows and one will be rows sampled. If rows equal rows sampled, then you did a full scan. If rows sampled is less than rows, then it used a percentage. You can also hit a function sys.dm_db_stats_properties. If you cross apply that with sys.stats, you can pass an object ID and the stats ID and that will also tell you rows sampled versus rows in the table. So you can figure out all that stuff there. If you want to calculate a percentage, just put a calculation in your queries. That’s the way to tell.
Jessica Connors: Thanks, Brent Ozar.
Why are you still Brent Ozar?
Brent Ozar: My alter ego. He’s logging in first to start the webcast because I’m shlumpy, lazy, and I don’t show up on time. So god bless him.
Erik Darling: Brent is taking cold drugs and hanging around.
Brent Ozar: Yes.
Jessica Connors: Got ya. Okay, question from Cameron. He says, “If you want to purposely break replication…”
Brent Ozar: What?
Jessica Connors: Why would you purposely break replication?
Brent Ozar: Is he trying to drop the microphone on his way out the door?
Angie Walker: Should we continue answering this question?
Jessica Connors: “Is it better to unsubscribe from the slave database or should you delete the publication from the master?”
Brent Ozar: Oh, he wants to do a restore.
Tara Kizer: I haven’t used that term slave database, I assume he’s referring to the subscriber for a restore.
Brent Ozar: Subscriber.
Tara Kizer: I just right-click on the publication and say drop it and it takes care of everything.
Brent Ozar: Like it’s hot.
Tara Kizer: But no, you don’t delete the publication from the… I’m confused by the terms that he’s using.
Brent Ozar: I bet, so one thing I would say is I would do Tara’s approach rather than trying to remove any subscriber’s setups because what happens if you leave the publisher’s setup and somebody doesn’t do the restore? Like I’m assuming you’re trying to restore the publisher, not the subscriber. You can leave a hosed up replication setup behind. So as long as there’s only one subscriber, just delete it at the publisher.
Tara Kizer: If you end up with the hose situation, you can run sp_removedbpublisher, something like that. It’s remove something. Remove db something. That will just clean up anything that was left behind.
Brent Ozar: That’s how you know somebody has worked with replication before.
Tara Kizer: Yes.
Brent Ozar: She’s like looking up the rest.
Tara Kizer: Bailed over to the DR site and forgot to drop replication beforehand and it like orphaned at that point. It’s like, what is that command?
Brent Ozar: Oh god.
Jessica Connors: Tara has the best stories.
Tara Kizer: Lost lots of sleep.
Brent Ozar: God bless.
Jessica Connors: Yeah, you could have your own podcast. Yeah, that’s what he says, he says it’s just to do a restore. He’s not in there just for the sake of breaking stuff now that we know his first and last name.
Tara Kizer: I know how to break replication too. He meant drop it.
Jessica Connors: All right. Let’s talk about stack dumps. Chris has a question. He says, “My SQL error logs show a stacked up. Total server memory is 16GBs, Mac memory is setup just above 13GBs, LPIM is enabled. Available memory went to zero just before the crash according to my monitoring software. I’m thinking I should lower the max memory or disable the LPIM. What do you think?”
Tara Kizer: You need to figure out what is using the memory but I don’t think your 2.5 is enough for the OS. The 2.5 I mean.
Brent Ozar: Yeah, when Tara says find out what’s using the memory, it’s not SQL Server, or at least not the engine. It could be other things in SQL Server like integration services or analysis services or whatever but you set max memory, you set lock pages, and memory turned on. So that’s cool. That amount is locked but now, and people will often say, “I should turn on lock pages and memory. That way I don’t have to worry if something else needs RAM.” Hell yeah you do. You just did. You just suffered a crash because of it. SQL Server couldn’t back down on the amount of memory it needed. So now your fun journey begins to go troubleshoot what is using that extra memory. What would you guys use in order to find out what tools or what apps are using the extra memory?
Tara Kizer: Well I had low memory on my laptop on Monday during a client session. So after I was finally able to investigate it after the call in either the app log or the system log, it told me what the top three processes that were using the memory. It was two SQL server instances and WebEx; those were the top three. I’m not too sure if that would be seen if your actual server crashed though, but maybe. There might be low memory alerts in there leading up to the crash.
Brent Ozar: You’re right on the thing in saying lock pages and memory, should I maybe turn it off. I would while I’m doing the investigating. Just leave it off just to prevent—because this other app is probably going to fire up again before you have the chance to fix it.
Jessica Connors: All right. So let’s talk about what to do when your backups fail. Fred has a question. He says, “Checkdb is successful and our backups always complete successfully but trying to restore the backup gives an error that the backup failed, the data is invalid. Any thoughts on where to look.” He’s running SQL 2008 R2 Enterprise. Not using any Enterprise-only features. Four backups later we usually get a good backup that we can restore from.
Erik Darling: My first question is are you running backups with checksum enabled and do you have page verification turned on for that database? Because you could at least narrow down where the issue is happening. So if you have page verification turned on, SQL will start writing checksums to your pages to make sure that nothing wonky happens to them on disk. Then if you run backups to the checksums, SQL will check those checksums as it does the full backup. So you at least have something verifying there that it’s not SQL and that it’s something with your disk media or like when you’re transferring files over that’s happening. The only time I’ve ever seen that happen was when I was restoring a backup on a dev server and it turned out that one of the disks on the dev server was actually suffering from some malady, some lergy. So that was the issue on that. So I would absolutely verify that it’s not something happening on the primary sequences and then my investigation would be on whatever hardware I have on the instance I’m trying to restore it to.
Brent Ozar: I’ve seen it when you write to crappy storage for your backups, like the backup reports writes successfully but then the data is trash when you go through to read it. But I would like to say, just like the great singer Meatloaf, two out of three ain’t bad. Four out of five successful backups, that’s not such a bad number. 80 percent, that’s a passing score. You probably didn’t need one out of five. It’s probably not that big of a deal. But yeah, I would just try immediately after the backup finishes, try doing a restore with verify only. Either from the same SQL Server or from another SQL Server and that will at least tell you if the backup’s file is hosed.
Jessica Connors: All right. Back to replication.
Brent Ozar: How come nobody ever says, “What do you guys think about flowers? What’s your favorite kind of chocolate?”
Erik Darling: How much do you like [inaudible: Casa Playa]?
Brent Ozar: It’s dreamy.
Jessica Connors: Let’s see. Question from John. He says, “Is it possible to mirror via transactional replication a SQL 2008 R2 database to a SQL 2016 database?”
Brent Ozar: Wow. I bet it would be.
Tara Kizer: I think so since 2016 goes all the way down to 2005 compatibility level.
Brent Ozar: Yeah, I bet you could.
Erik Darling: The only thing I’ve ever seen stand in the way is backwards stuff.
Tara Kizer: Mirroring and transactional replication, replication doesn’t really care about versions. Mirroring does.
Brent Ozar: Yeah, he should be fine.
Tara Kizer: Either way, it should be fine.
Jessica Connors: Can you just upgrade both to 2016?
Brent Ozar: I bet he’s so tired of running with his 2008 R2 box and he’s like just trying to give his users something that has nice, new functionality on 2016. That’s probably what it is. He’s like, “Here, go query over here. It’s really nice and fun.” Maybe he’s got nice nonclustered column store indexes on the table over there, make his queries fast. Maybe that’s what it is.
Jessica Connors: Kanye or Wanye West.
Brent: Wanye. [Laughter] Oh, Wayne you are never going to live that down, Wanye.
Jessica Connors: I think that’s a good question. Where is Richie?
Brent Ozar: Oh, he’s in Disneyworld.
Jessica Connors: Of course he is. He’s always getting lost at Disneyworld.
Tara Kizer: Driving home.
Angie Walker: Yeah, he’s on the road.
Jessica Connors: Let’s see here. Question from Jay H. He says, “Last year after applying a couple of particular Windows KB updates issues arose with JBDC TSL connections and had to be removed. Has Microsoft fixed this and can updates now be applied?”
Brent Ozar: I remember seeing Aaron Bertrand blog about this. This is one of those SSL and the connection string I’ve never paid too much attention to but I think Aaron Bertrand blogged about this. Other people are nodding like we vaguely have seen something along these lines.
Erik Darling: Yeah. I’ve just seen some stuff sitting around 2016 with TLS 1.1 and 1.2 having some weirdness bug things.
Brent Ozar: Yeah, we don’t touch it with a ten-foot pole. If you search for SQL Server TLS Aaron Bertrand and Aaron is A-A-R-O-N Bertrand, I bet you you’re going to find a blog post in there where he went into detail on that. Because like anything else with SQL Server updates Aaron Bertrand looks over those with a magnifying glass and a fine-tooth comb.
Erik Darling: In a kilt.
Brent Ozar: In a kilt. So Rusty Householder already replied with the answer, blogs.SQLSentry.com TLS support. I’m going to put that in the resources for everybody there. But yeah, it is a thing that Aaron blogged about.
Jessica Connors: Let’s see. This is the last comment/question. People have been pretty quiet today. From Chris Wood, he says, “Thanks for the help on the blocking.” You helped Chris with blocking?
Brent Ozar: I believe we did last week I think. I think we did.
Jessica Connors: Via Critical Care?
Brent Ozar: Oh no, it was a question about—I remember this. It was a database restore involving Relativity. He was doing a database restore on Relativity and I think we posted the question on Stack Exchange as well. SP who was active showed blocking and we couldn’t figure out which query it was that was doing the blocking. Turned out it was a system SPID that was a doing a full text crawl. So when you finished doing restore, it did a full text crawl and it locked some of the tables in the database. People weren’t allowed to access them. Awesome. Got to love that. People are like wow…
Erik Darling: I could have answered that one.
Brent Ozar: Oh could you, have you had that same problem?
Erik Darling: Yeah, embarrassingly.
Brent Ozar: Unbelievable.
Jessica Connors: Angie is getting a shout out. Were you posting on #SQLhelp?
Angie Walker: Nope. [Laughter] Apparently I have an impersonator. I didn’t think there were any other Angies out there. Oh, Tara, ah. It’s pretty hard to tell us apart, I know.
Brent Ozar: Just one of us cartoons, all our cartoons look like.
Erik Darling: I get mistaken for Jessica all the time.
Erik Darling: They’re like, “Hey we need to buy some stuff.” I’m like…
Brent Ozar: Unsubscribe.
Jessica Connor: Yeah. Let’s see, Brent. Brent’s an awesome name. He says, “Any experience with Experian Correct Address which has to be installed on the database server for SQL CLR. Do you have any experience there?”
Brent Ozar: Oh, I’m vaguely remembering that this calls a web service. That it goes and validates people’s addresses. The way that it does it, whenever you want to like validate someone’s address you call an extended stored procedure, a CLR stored proc and it goes off and it calls a web service. So it’s possible that this is working on one node and not another because of firewall rules or network permissions. Windows Firewall, UAC, I mean, it could be almost anything that involves accessing the interwebs.
Erik Darling: I hate to say it but this is actually something that Master Data Services is good at.
Brent Ozar: Really?
Erik Darling: Yeah. You can do like address lookups and have like post office integration where you can get whatever like you know a post office valid address for a thing is, it can validate that. I don’t know a ton about it because I’ve only ever seen it in action a couple times but that’s actually something that Master Data Services does well which I feel filthy saying.
Brent Ozar: That’s a couple times more than me. I’m guessing it didn’t require CLR then, it was probably just stuff built into the database server?
Erik Darling: Yeah, but I don’t know how it was called so it still might have been CLR but it was integrated with Master Data Services. So it was like SQL friendly. It didn’t need to call out to anything else. It was already like built in somewhere.
Brent Ozar: Doing its thing.
Erik Darling: Yeah.
Jessica Connors: Let’s see. I think we talked about this last week. Nick Johnson, he says, “I found a couple articles that talk about how compatibility level 90 2005 does not work in SQL 2014. You guys have any confirmation on that even though 2014 in fact shows it, it doesn’t work.”
Erik Darling: You can’t upgrade directly, isn’t that it? Or is that from 2000?
Brent Ozar: I can’t remember either.
Angie Walker: I think in general, you can’t do more than two versions, right? You couldn’t go through 2005 to 2012 or straight to 2014. You’d have to make the hop in between.
Tara Kizer: You can’t restore but the compatibility level is there. So on 2014 you can go all the way down to 2005 compatibility level. Like he’s saying, the option is there but apparently some articles are saying it doesn’t work. I don’t know. I see it.
Brent Ozar: Yeah, I vaguely remember that during the release process, like it worked in SSMS but the ALTER didn’t work, like the ALTER DATABASE didn’t work. I think people thought, “It’s not going to work when it finally releases,” like that they’re going to yank 2005 compat. But I’m pretty sure it still does work because I distribute Stack Overflow in that format too and Doug was asking questions about that this week. It should work fine. I don’t know if it runs in 2016, if 2005 compat mode runs in 2016. No clue. And who the hell would use that? Why would I swear in the middle of a podcast? Who knows. Don’t do that. Don’t do that. By that, I mean 2005 compat mode, not swearing. You should totally swear. You’re a grown person.
Jessica Connors: All right, Abdullah asks or states he’s in progress building a DR site on a multisite topology. Any recommendation for SAN replication?
Erik Darling: The fast one.
Tara Kizer: It’s going to be expensive.
Brent Ozar: And the cheap one.
Brent Ozar: Yeah, you don’t get much of a choice. It’s whatever brand your SAN supports. I think EMC makes stuff as well, like appliances that will do between SANS. But cha-ching.
Erik Darling: Also make sure that you are deleting old snapshots and copies because you can find yourself—depending on like how big these copies are, deleting them could take a very long time so you want to make sure that you have enough room to both copy and have other stuff being deleted off. Because deleting 20 terabytes of SAN replication snapshots is time consuming no matter what.
Jessica Connors: All right, well I guess while we’re on the topic of that, do we like async DB mirroring as a DR strategy?
Tara Kizer: Yes.
Erik Darling: Yes.
Tara Kizer: I do. I used it for years. So when I joined a company three years ago, we were on SQL 2000 using log shipping and only upgraded 2005. We were relieved to get rid of log shipping just due to the amount of work it took to failover to DR site for a lot of servers. It was a lot of work. We were real excited about mirroring. We used asynchronous mirroring between our two sides. There was about 300 miles apart. We could not have done synchronous database mirroring because of the drastic performance degradation that you would have. But async mirroring worked great. We failed over to the DR site regularly, two, three times a year. Ran production out of it for a few weeks then failed back with it. It works great. All you have to do is when you want to failover, set it to synchronous mode, let it catch up, then do your failover and set it back to async.
Jessica Connors: There’s a Trump DBA handle on Twitter. Are you guys familiar with this?
Bret Ozar: Yes. I encourage humor in the SQL Server community. It’s not me doing it. That’s somebody else doing it. But I’m always like, if people want to have fun, that’s kind of cool.
Jessica Connors: What about DBA Reactions? Are you still involved with that?
Brent Ozar: I am. I took a brief hiatus from it while other people kept submitting stuff. Other people were submitting so many things. So I just went in and approved them all the time. Then of course I fell back in love with GIFs. I’m like, “Oh, let me go in and look and see because it’s been a while.” Oh my god, there’s so many good GIFs these days. So I started queueing them up again. I think I’ve already gotten them written through most of next week.
Jessica Connors: Oh, so you’re still doing that.
Brent Ozar: Yeah, I love it. I have a disturbing amount of fun with it.
Jessica Connors: They used to get a newsletter, the DBA Reactions.
Brent Ozar: I cut it back to like only once a week. It was going Tuesdays and Thursdays. Now it’s down to either just Tuesday or Thursday, I forget which one because I didn’t want to overwhelm people’s email boxes. There are like 5,000 people signed up to get this in their email box every week.
Jessica Connors: Yeah. I remember the time that somebody called for SQL Critical Care and they’re like “I heard about you guys from DBA Reactions. I love … I’m like, “we made a sale.”
Brent Ozar: Well and at that point they’re going to call us because anybody who’s crazy enough to go, “I like DBA Reactions, they’re my people,” they already know exactly what we’re like. They know exactly how we work.
Jessica Connors: That’s fair. Cool, well you guys are being fairly quiet today so I think we’ll end there.
Brent Ozar: Thanks everybody for hanging out with us. We will see you guys next week.
This week, Brent, Erik, Jessica, Richie, and Tara discuss database modeling tools, how to learn about database corruption, the new cardinality estimator, and the only question that will be on our certification exams.
What ERD Tool Should I Use?
Jessica Connors: All right, question from Lee. Let’s jump in there. He says, “We have a system where the vendor went belly up. Now I am tasked with getting an ERD for the database so we can move it to a new system. What tools, free if possible, would you suggest?”
Richie Rump: Oh, uh-
Tara Kizer: I mean, Management Studio has it built in, the ERD, but I’ve always, anytime I’ve used the ERDs, I’ve always used Erwin or whatever.
Richie Rump: Technically, that’s not an ERD. That’s just a diagram, right?
Tara Kizer: Yeah.
Richie Rump: An ERD is an all-encompassing tool that will have, it’s essentially like a case tool. Did I just go case, yeah? You can generate databases from it. You can reverse engineer it. I think Viseo still could do it for free in a pinch, which I’ve done, but my favorite is Embarcadero ER Studio and Erwin, still. They both have their plus and minuses. Both of them have the same minus which is a very large price tag. I have actually purchased my own copy of ER Studio because I do data design a lot, and I’m kind of crazy that way.
Brent Ozar: I want to throw something else weird out there. If the vendor went belly up, the diagramming or ERD tool is going to be the least of your worries. Holy cow, buckle up. You’re going to be developing the bejeezus out of that database. If you’re taking it over from here on out, go buy the tool because this is your new career. This is what you’re going to be working on.
Tara Kizer: Hopefully, they have the source code [inaudible 00:03:29]
Richie Rump: Wow.
Brent Ozar: Oh, that would suck.
Jessica Connors: Yeah, what happens when vendors just die like that? It’s just like, oh, sorry customer. You can have the product, but we’re not iterating on it. We’re done.
Brent Ozar: That was my life in 2000 to 2001. We had a help desk product where the vendor was like, yeah, no, we’re not doing this anymore. I had to build a new web front end to their existing database and gradually like change things over. That was two years of my life that I would really like back. The hilarious part is that the company that I worked for then still uses that crappy web help desk that I built because they lost the source code after I left.
How Should I Troubleshoot a Big, Slow Stored Procedure?
Jessica Connors: Oh boy. All right, James says, “I have an SP that has 53 queries in it. What is the best way to approach troubleshooting this slow query?”
Erik Darling: After the execution plan-
Tara Kizer: Execution plan sets statistics I know.
Erik Darling: Then, use it in the SQL Sentry Plan Explorer, it makes it really easy to see which statement in the store procedure takes up the most percentage of work.
Brent Ozar: The other thing you can do if you want to play with it is take it over to development, like restore a copy to production database. Blow your plan cache, run DBCC FREEPROCCACHE. I emphasize, you’re going to do this in development. You’re not going to do it in production. Then, immediately after you free the plan cache, run the query and use sp_BlitzCache. sp_BlitzCache will show you which lines in the stored procedure do the most reads, CBU, run time, whatever.
Should I Update Stats When I Change Compatibility Levels?
Jessica Connors: Question from Tom Towns, I haven’t gotten this one. It says, “Is it necessary or advisable to rebuilt indexes/stats when downgrading compatibility level on a database but staying on the same version of SQL Server?” He’s running SQL Server 2014.
Erik Darling: No, not necessarily, but you should be doing regular maintenance on your databases anyway.
Brent Ozar: That’s actually a good question because it comes from the history of when you went up with SQL Server, there was a lot of advice around. You should update your stats, and I don’t think, for a while there, we were trying to find the root of that, like where that advice came from.
Erik Darling: My best guess on that is that there are slight tweaks and twinges to the cardinality estimator, and when you update versions, that doesn’t necessarily kick in just by restoring the database. Updating the stats just kind of helps push things to the whatever, new ideas that cardinality estimator has for your data.
Will Brent Ozar Unlimited Have a Certification Program?
Jessica Connors: Let’s see here, you guys are quiet today. In terms of certifications, would we ever think about giving our own Brent Ozar stamp of approval? I know we give out those certificates of completion with the funny little cartoons on there, but Brent, we never actually thought about giving our own certification course, classes, all of those things.
Brent Ozar: It would really just consist of, can you write us a check for 200 dollars, yes? You are a member of a very prestigious club. I bet if we all got together and focused, we could probably put together a test that would do it. The problem is, it’s really expensive. It takes a lot of time to do it because you’ve got to do it in a way, I was part of a team that helped build the Microsoft Certified Master, the next version that never ended up going public. I learned so much from Microsoft around that process where the questions have to be legally defensible. They can’t be biased towards English speaking people. They can’t have cultural issues, like you can’t take in certain business knowledge or assume certain cultural knowledge. It needs to be a fair playing field for anybody who touches SQL Server. That’s really hard. I have a lot of respect for people who write certification exams, but I agree with you. The Microsoft one sucked pretty bad.
Richie and I talked about that in the Away from the Keyboard podcast.
Richie Rump: Yeah, on Away from the Keyboard. I think the name of the episode was Brent Ozar Loses His MVP.
Brent Ozar: Yeah. I trash talked the bejeezus out of that, but yeah.
Richie Rump: And yet, we got renewed. He’s trying people. I don’t understand.
Brent Ozar: We don’t even have tests in our classes. What we end up doing is we have group discussions. I’ll give you a homework assignment, and everybody works together at the same time, like some of the assignments you do by yourself. Some you of them you do in groups. Even that, just the discussions of people saying afterwards, I think this answer is right. I think this answer is right can take hours, so it’s pretty tricky.
Should I Install Multiple Instances On One Server?
Jessica Connors: Let’s see here, Steve. Move on to Steve. He says, “We are being given a virtual server to install SQL 2014. Which would be better, install one instance of SQL with all of our databases or several instances of SQL server with fewer databases on each instance?”
Brent Ozar: We’re all trying to be polite, I think.
Tara Kizer: Is this all for one box? If it’s for one box, we don’t recommend stacking instances. One instance per box.
Brent Ozar: Why don’t we recommend stacking instances?
Tara Kizer: I mean, you have to determine max memory setting for them, and you might can figure it not optimal for one instance. Another instance might need more memory. You might be playing that game and just fighting for resources. How many databases are we talking about? Are we talking about just 40 or 50? Are they all critical? Are they all non-critical? Can you have another virtual sever where you can put some databases on that one and other databases on the other?
Erik Darling: A lot of what I would use to sort of dictate where I’m going to put things is RPO and RTO. If you have a group of databases that all have 24/7 uptime, higher level requirements and say like bug tracking or help desk or something along those lines, something you can put off, stuff that needs high availability, stuff that needs better back ups, things like that, things that need more horsepower behind them, I would put those on a server or servers where I’m going to be able to pay special attention to them. I would put some of the less needy stuff on other servers that I can sort of stick in a corner and forget about and not worry about performance and not worry about any performance in one stepping on the toes of another.
How Do I Get Rid of Key Lookups?
Jessica Connors: All right, question from [inaudible 00:10:51]. She reads this, “My key lookup is still showing after the column was added in a covered index. Anything I could do to avoid the key look up?”
Brent Ozar: We’ve seen this with a few. You want to look a little deeper with the execution plan. Sometimes there is something called a residual predicate, where there’s other fields that are being looked up, not just the key. When you hover your mouse over the key, look up, look for two things, the predicate and the output. Maybe SQL Server is seeking for some other kinds of fields or it’s out putting other kinds of fields. If there is no predicate and no output, use SQL Sentry Plan Explorer and then anonymize that plan and post it online. There are known issues, I’ve seen them where I can’t get rid of the look up, and sometimes there’s gurus on there like Paul White who can take a look at the plan and go, oh, of course, it’s the bug right here.
Erik Darling: That happened to me sort of recently. I was helping a client get a computed columns together for a table, and for some reason, we added the computed columns. We started referencing the computed columns, but in the execution plan, there was still a key look up for all of the columns that made the computed column work, right? We had five or six columns that added up, made a computed column. We added the computed column to the included columns of the index, but it still wanted all of the columns that made up the computed column for some reason. That was a very weird day.
Tara Kizer: What did you do to fix it?
Erik Darling: Added the columns it was asking for in the output columns and the key lookup went away. It was very weird though. It was something that I tried to reproduce on 2012 with 2014, and I couldn’t get it to happen.
Richie Rump: He stared at it, and-
Erik Darling: Intimated it.
Richie Rump: Changed.
Brent Ozar: Flexed.
How Do I Learn About Database Corruption?
Jessica Connors: Database corruption, we haven’t talked about that.
Richie Rump: Ew.
Jessica Connors: That sounds fun.
Brent Ozar: It is fun. It is actually fun.
Jessica Connors: Is it? It reminds me- [crosstalk 00:13:05] Now I’m thinking about that song by the Beastie Boys.
Brent Ozar: Sabotage?
Jessica Connors: Yeah, that’s the one. Let’s see, it’s from Garrett. He says, “What’s the best way to lean about how to resolve database corruption with zero data loss?”
Brent Ozar: Steve Stedman’s database corruption challenge. If you search for Steve Stedman, it’s just S-T-E-D-M-A-N, database corruption challenge, he had like 10 weeks of challenges with different sample databases that were corrupt. You would go download them, and you would figure out how to fix them. The quizzes are kind of two part. The download sample database, and then you go try to figure out how to recover as much data as you can, with zero data loss. Then, you go read how other people did it. They share their answers and show how fast they were able to get the data back.
Erik Darling: Get real comfortable with some weird DBCC commands.
Brent Ozar: Forcing query hints to get data from some indexes and not others, oh it is awesome. Even if you don’t do it, just reading how hard it is is awesome.
Erik Darling: You know what always get me is when you have to find binary, but it’s byte reversed, so there’s like stuff with DBCC right page, and oh forget it.
Brent Ozar: Screw that. No way.
Erik Darling: Call someone else.
Brent Ozar: It’s really-
Jessica Connors: All of you, you’ve all dealt with this before? Database-
Brent Ozar: I’ve never dealt with it in real life. I don’t like-
Tara Kizer: I’ve done it two or three times. Do I like it? No. It was probably about five years ago. I was at the on call DBA. Two DBAs got woken up, and this was on a weekend. By seven in the morning, you hadn’t slept yet. They couldn’t even think clearly at this point, so that’s when they called me to take over for the next several hours. We ended up having to restore the database, and we had data loss, and it was all due to the san. You had some san hardware issues. It was bad. It was really bad.
Brent Ozar: It’s one of those where, like you can go your whole career and never see it, but if you have storage that isn’t bullet proof reliable, or if you have a team that like screws around and cuts around corners, then you spend years of your life dealing with that.
Should I Feel Bad for Not Using the Current Compat Mode?
Jessica Connors: Question from Mandy. She says, “Hi. We upgraded to 2014 Standard Edition on new hardware a couple of months ago, but left our databases in 2012 compatibility mode. A few weeks ago, I upped the compatibility mode to 2014 and afterwards had such major contention and blocking problems, we had to change it back to 2012 after about 30 minutes. Is this common? What can we look for in our database to resolve this?”
Brent Ozar: It would be more common if more people were upgrading.
Erik Darling: Everyone’s still on 2008.
Tara Kizer: Is that the cardinality estimator, probably doing that.
Erik Darling: Yeah, it sounds like that kicking in.
Tara Kizer: Is there a different way to turn it off and you’ll still have your compatibility level be 2014?
Brent Ozar: This is tricky, you see, price flags, and you can turn it off at the query level, but Mandy you were bang on. You’re perfect to wait a couple of weeks. That’s exactly what you want to do. Then, you want to flip it on a weekend, like on a Saturday morning. Let it go for just 10 minutes or 15 minutes, but as soon as you can, run sp_BlitzCache and gather the most CPU intensive query plans, most read-intensive query plans, and the longest running query plans with sp_BlitzCache. You’ve got to save those plans out to files, and then switch back to 2012 compatibility mode so that you’re back on the old CE. Then, you take the next week or two troubleshooting these query plans to figure out, what is it about these queries that suck. Is it the new cardinality estimator? So often, people just test their worst queries with the new 2014 CE, not understanding you need to test all of the queries, because the ones that used to be awesome can suddenly start sucking with the new CE.
Erik Darling: Or, in a couple of months, just upgrade to the new 2016 and use the query data store and then just bang that right off. Done.
Brent Ozar: What’s the query data store?
Erik Darling: It’s this neat thing. It’s like a black box for SQL Server. What it does, is it basically logs all of your queries. It’s a good way for cases like you where you had good query plans that were working, and then all of a sudden, something changed and you had bad query plans. It gives you a way to look at and find regressions in query plans and then force the query plan that was working before so that it uses that one rather than trying to mess around and just use the new one. Otherwise, you’re kind of stuck, like you are on 2014, where you could run, switch to the new cardinality estimator, but then you would have to use some trace flags on the specific queries to force the old cardinality estimator, which is not fun. I Don’t recall those trace flags off the top of my head. I never do.
Richie Rump: Can you hit paste flight recorder for queries? Is that about right?
Brent Ozar: Listening in on the cockpit catches people doing things they shouldn’t be doing.
Erik Darling: I was having a drinking contest.
Does the New Cardinality Estimator Get Used Automatically in 2014?
Jessica Connors: Let’s see, question while we’re on the topic of the cardinality estimator. Question from Nate, we may have answered this. “Speaking of the new CE for 2014, does it automatically get used for everything or only for the DBs in the 2014 compatibility level, or is it off by default, and it’s up to the DBA to turn it on at their discretion?”
Erik Darling: Or indiscretion.
Brent Ozar: Yeah, whenever you change your, if you’re upgrading a SQL Server, or if you’re attaching databases to a 2014 SQL Server, the new cardinality estimator doesn’t get used by default. It does get used if you create new databases, and they’re in the 2014 compat mode. Yeah, it’s totally okay to leave your databases in the old compat mode. You can leave them there as long as you want. There’s no botches with that. Totally okay, totally supported.
Jessica Connors: What if it’s in a 2005 compatibility mode?
Brent Ozar: What’s the oldest one that supported? I think it’s 2008.
Tara Kizer: I just looked yesterday, because a client had a question, and 2014 does have the 2005 compatibility level in the list. I was surprised. I was very surprised.
Brent Ozar: So generous.
Should I Use “Optimize for Ad Hoc Workloads”?
Jessica Connors: Question from Robert, he says, “Optimized for query workload’s instant setting, good to turn on by default or only when mad at hot queries are frequent?”
Brent Ozar: This is tricky. You will read a lot of advice out there like you should turn it on by default, because it doesn’t have a drawback. I’m kind of like, if I see that, it doesn’t bother me. It just doesn’t usually help people unless they truly have a BI environment where every query comes out as a unique delicate snow flower. Have any of you guys ever seen problems with optimized for ad hoc?
Tara Kizer: Problems? No.
Richie Rump: No.
Erik Darling: I’ve never seen problems. You know, every once in a while, you’ll see someone with like 80 or 90% of their plan caches is single use queries. At that point, that’s not really efficient use of the memory or what you’re storing in the plan cache.
Jessica Connors: Let’s see, question from Nate. We are still on compatibility mode questions. Last one, “Is there anything wrong with leaving all DBs in lower compat levels when you do a SQL Server migration, server 2014? Like, leave all the DBs at 2005 or 2008 or two compatibility levels and then start upping them later when we have breathing room?
Erik Darling: No.
Tara Kizer: You won’t ge the new features.
Brent Ozar: Totally okay. Totally okay.
Tara Kizer: Eventually, you’re going to have to up them, because you’re going to be upgrading and you can’t go down to that level. I imagine that 2016 doesn’t go down to 2005.
Erik Darling: I mean, just doing that before you up the compatibility level, just make sure that you check any of the Microsoft’s broken features and deprecated stuff and new reserve key words. If your old databases are using keywords that have now become reserved, stuff could break pretty easily.
When Will We Add More Training Videos?
Jessica Connors: James is getting sick of our same old training videos on our website. He is wondering when we will be adding more videos to the training library. If so, what? When? How?
Erik Darling: As soon as you pay us to make them.
Brent Ozar: Doug is doing a new one on advanced querying and indexing. It has a game show theme, back from a ’70s game show. We are aiming to have that one online in June. To give you a rough idea, it takes about three months worth of work to do a six hour class to the level of Doug’s videos. If you’ve watched T-SQL Level Up, it’s phenomenal production value. It takes three months worth of work to do a six hour video.
Brent Ozar: For my level of production values, where it’s a guy talking in front of a green screen, it’s usually about a month. I’m working on performance tuning, when you can’t fix the queries. That one will be out probably in June or July, somewhere in there. The trick with that is, the Everything Bundle price will go up at that time. If you’re interested in getting an everything bundle, I would do that this month. It will include any videos that we add during the course of your 18-month ownership. This month, the everything bundle is on sale, even further the half off. Now, it’s just $449 for 18 months to access to all of our video. That is a one month only sale to celebrate our 5th year anniversary. After that, you are right back up to 899.
Jessica Connors: Then, what’s it going to go up to?
Brent Ozar: It depends. I think we have two more 299 videos. I wouldn’t be surprised if we pushed it to 999.
Jessica Connors: Ah.
Richie Rump: 999? That’s still a deal.
Brent Ozar: Richie says in his sales person voice.
Why Does sp_BlitzIndex v3 Have Less Output?
Jessica Connors: All right, question from Justin. Hello, Justin. He says, “sp_BlitzIndex isn’t recognizing new indexes on a table where I deleted all of the indexes and ran replay trace against, but SSMS is recommending some. So is a third party software. Any idea what would cause this?”
Brent Ozar: I bet you’re running a brand new version of sp_BlitzIndex, version three, that just came out where we ignore crappy little tables that don’t really have that much of a difference in terms of performance. If you want the crappy little tables version, you can run the older version of sp_BlitzIndex, which is included in the download pack, or use it with @Mode = 4, which does the fine grained analysis that we used to do. Just know that you’re probably not going to have that much of a performance improvement, not if they’re tiny little tables, tiny little indexes.
Should I Use Somebody’s HA/DR Software on My SQL Server?
Jessica Connors: Yeah, he said that he was using the new one. Brent, or anyone, has anyone here heard of DH2I enterprise contain HA solution, and what is your opinion? Have you heard of that? It’s from-
Brent Ozar: I have heard of it. I don’t know anybody using it. I don’t know if you guys have either. I have this whole thing where if I’m going to install software that supposed to make my stuff more highly available, my whole team better know how to use it, and it better be like really well tested. I need to be able to talk to other people and get training on how it works. I would just talk to other people who use it and ask for hey, can you give me a run down of what issues you’ve had over the last 12 months. Make sure they’re a similarly sized customer to you, like if you’ve got 100 servers, the person you talk to should have 100 servers.
Erik Darling: What I find helpful is that a lot of vendors like this will have help forums. What I like to do is just read through the help forums and see what kind of problems people are facing, kind of common questions and see if there’s any answers there that kind of help me figure out if this is right for me.
Jessica Connors: At any of the help forums, have any of you guys seen people giving feedback like, don not buy this product. This is terrible.
Erik Darling: You know, you see some questions that are like X product stopped working and this broke and this stopped working and had major outage. What do I do?
Brent Ozar: And you look at the time lag between answers, like how long it takes to get it fixed.
Erik Darling: And then like, no actual company support people are chiming in. It’s all other users that had the same problem.
Tara Kizer: I just don’t know if I would want to use a product that isn’t widely used in industry. Do you want to be the first customer using this product or the first five? I want to use the products everyone else is using.
Jessica Connors: We are doing some upgrades to our CRM right now, and there’s little things I want to change to make it work, and the engineer is sending me, basically these forums of people like, I really want this feature. It’s never going to be turned on. This piece doesn’t work, and then like it’s from five years ago, four years ago.
Brent Ozar: Utter silence. There’s also a follow up question from someone that says, oh, so and so did an online session on that. Just know that often, consultants who are paid by vendors, will do sessions going, hey, this tool is amazing. I would be glad to help you install it. They are selling the software and they are selling their consulting. Look at it just the same as you would an infomercial. I would ask to talk to the customers using it, not to the people you are going to pay money to.
Jessica Connors: Have we done that? Has anyone ever come up to us with another product like, hey, can you do a webcast for their product?
Brent Ozar: Oh, all the time. I’ll be like, because we do webcasts too for like Dell and Idera and all this. I’m going to talk about the native way of doing it, like the pain points of how you do it without software. If you want to talk about how your software does it, that’s totally cool. I can’t talk about your software, because I don’t use it. I just have to jump form one client to another. Every now and then, people will say, “Here’s a bucket of money. How much of this bucket would you like in order to say our product is awesome?” I’m like no. I only have one reputation. What happens if the product sucks butt wind? I will review privately if you want to spend three or four days hammering on your software and see how it works. We would be glad to do it and then give you a private review. I’m not going to go public and say that it’s awesome when it smells like…
Richie Rump: Erik and I will do it. That’s not a problem.
Brent Ozar: Yes.
Erik Darling: My reputation stinks anyway.
What’s the Ideal HA/DR Solution for 5-10 SQL Servers?
Jessica Connors: Let’s see, just for fun. Question if you run out of things to answer. This is really open-ended. What is your ideal HA and DR solution for a SQL Server environment with five to ten instances. That depends.
Brent Ozar: No, we should each answer that. Tara, what’s your ideal for five to ten instances? Say you’ve got one DBA.
Tara Kizer: I don’t know how to answer that. I’ve never worked in an environment where I was the only DBA. I have always worked in an environment where there was probably three to eight DBAs Availability groups is my answer for almost everything. You say HA, I say availability groups. I know you guys don’t like that, but that’s what we implemented. We had large DBA teams. We had larger server teams that understood Windows, clustering, all that stuff. It works well if you have the people that know the clustering, you know, all the features.
Brent Ozar: Tara doesn’t get out of bed for less than 10,000 dollars. Erik, how about you?
Erik Darling: For me, if you just have one to two DBAs, but you may have some other support staff, I would say a fair level clustering and then something like either async mirroring or log shipping. It’s usually pretty decent for those teams. It’s pretty manageable for most people who don’t have the DBAs or the football jerseys on and the schedules and the tackle cards on almost everything.
Brent Ozar: Yeah, how about you, Richard?
Richie Rump: From a developer’s perspective, because I joined this team, and all of a sudden, I have a developer’s perspective. I love that. It’s Azure SQL Database, right?
Brent Ozar: Oh, you’re cheating.
Erik Darling: Wow.
Richie Rump: It’s all kind of baked in there, and I don’t have to think about it. A lot of it’s done fore me. As a developer, I’m going to do the laziest, simplest way out. That would be it.
Brent Ozar: Man, you win points on that. That’s genius. That is pretty freaking smart. I would probably assume that they’re not mission critical if there is stuff that I could stand some down time on. I actually would probably go with just plain old VMware. I would just make them as single instances in virtualization. Then, that way do something like log shipping for disaster recovery, or VMware replication. Now, this is not high availability. It’s just availability. It’s just the A. If you cut me down to just like five SQL Server instances and no DBA or like one DBA who’s kind of winging it and spending most of his time on a free webcast, then I kind of like that. If not, I’m still kind of a fan of async mirroring too. Async mirroring is not bad.
Erik Darling: Much less of an albatross than its sync brother.
Brent Ozar: Yeah, that thing blows.
Jessica Connors: Cool.
Erik Darling: I’ll say one thing though, not replication.
Tara Kizer: Yeah.
Jessica Connors: So many people using replication out there.
Tara Kizer: Replication is mostly okay, but not as an HA or DR feature.
Jessica Connors: Ah, they’re using it for the wrong thing.
Tara Kizer: It’s really reporting feature.
Brent Ozar: It’s funny to see, too, like all of us end up having these different answers, and this ends up being what our chat room is like. In the company chat room, like we all have access to everybody else’s files. We know what everybody is working on. Somebody can be like, Hey, I’m working with this client. Here is what they look like. Here’s what their strengths and challenges are. It’s really fun to bounce ideas off people and see what everybody comes up with.
Jessica Connors: Mm-hmm – well, all right, guys. It’s that time.
Erik Darling: Uh-oh.
Brent Ozar: Calling it an episode. Thanks everybody for hanging out with us, and we will see you next week.
In this week’s free video training sample, dig into the details of dynamic quorum and dynamic witness: what do they do? What’s strange about them at small node counts in a cluster? You’ll also learn how to configure quorum in three common scenarios in this 23-minute video.
(If you don’t see the video above, you’re reading this somewhere that doesn’t support video embedding. You’ll need to read the post on our blog.)
This video is part of our DBA’s Guide to High Availability and Disaster Recovery class that covers clustering, Always On Availability Groups, mirroring, log shipping, and more.
And it’s on sale this month! Use coupon code HighFive for half off our training videos and bundles.
This week, Brent, Erik, Jessica, and Richie discuss the sudden increase in AWS cloud clients, whether you should use cardinality estimator hints, how to do indexing on a former SQL Server 2000 database, and Brent’s hilarious Jerusalem tour guide. (Brent disappears about 90 seconds in, but then shows back up ten minutes later. Love that hotel Internet.)
In this week’s Office Hours Q&A, the whole team shows up – but Doug is the invisible man. We answer questions about hot-add CPU and memory, whether to sync jobs across Availability Group replicas, and how you get a single query to use more than 100GB of RAM.
In this week’s Office Hours Q&A, we talk about the pains of patching lots of SQL Servers, how to move databases from one drive to another with minimal downtime, the difference between NOLOCK and READ UNCOMMITTED, and assign Doug all future calls for Dynamics tuning.