This week, Brent, Angie, Erik, Jessica, Richie, and Tara discuss backups, failover events, tempdb errors, errors, other errors… oh, did we mention errors?
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.[Laughter]
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.[Laughter]
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.[Laughter]
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.[Laughter]
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.[Laughter]
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.[Laughter]
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.[Laughter]
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.[Laughter]
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?[Laughter]
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.[Laughter]
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.