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.