This week, Brent, Tara, and Richie discuss the lack of backward compatibility for SQL Server backups, deploying SQL Server on VMs with shared SAN storage, query tuning, replication, full-text indexes, backup and restore, saying “no” to your superiors as a DBA, clearing your transaction log, and much more.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

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

Office Hours – 2-14-18

 

When the data file fills up, how long does SQL stay up?

Brent Ozar: Let’s see; Ricardo asks, “if the data file has no more space to grow…” This is so good. “How long will SQL Server keep continuing to process transactions as long as the log file has plenty of disk? Will it go down when the log is full, up to the next checkpoint, when exactly will it fail?”

Erik Darling: Is this, like, an exam question? What are those asterisks?

Brent Ozar: That’s so cool. I don’t think any of us would ever test it. My guess is, you would keep running until you needed to add a page in the data file, which you could do inserts, updates and deletes, even. The deletes may be making- enough space on some pages that matter. Maybe inserts don’t require a new page. Updates may not, but if you’re depending on knowing the exact time it’s going to go down, you’re probably screwed to begin with.

Tara Kizer: It’s going to be a short amount of time, I suspect.

Erik Darling: Yeah, I don’t think that’s going to go well.

Brent Ozar: I don’t think it will even be up long enough to get the answer.

Erik Darling: But that would be a fun thing to test, because you could just create a small, like 1MB, data file, cap the size and start banging away on transactions and see how long it takes. Why you got to ask us? I don’t want to go do that demo now.

Brent Ozar: And if you do it, put some time in between the loops. Don’t just go one equals one, because this whole thing’s going to go down like a house of cards. It sounds like the kind of thing we usually do. And you see us do stuff like that during Dell DBA Days, but not always; it’s pretty rare.

Erik Darling: I don’t want to do demos that don’t have a query plan anymore. They’re hard.

Brent Ozar: You’re in love with query plans.

Erik Darling: I just like perf, and I can’t…

Brent Ozar: Amen, that’s me with availability groups. I’m like, oh it’s over there; I know how it works. I’m kind of done with that piece now. Lovely, I like it. Same thing with clustering, like, it’s over there. I’m going to be over here writing query demos.

 

Why can’t I restore to an older SQL Server version?

Brent Ozar: Michael asks, “Why is there no backwards compatibility for SQL Server backups? Why can’t I backup a database from a SQL 2012 instance and then restore it on a 2008 R2 instance?”

Erik Darling: Things change, man; all sorts of stuff changes. I guess the way I’d think of it is like the amount of fundamental changes that take place from version to version, both internally to resource DB, master, DMVs, the way SQL just does things, where it looks for things. There’s just no sense in having a database turn back time just so that you can restore it to an older version. I totally get why they don’t do that. It doesn’t seem fair to you because you have a weird environment where you need to downgrade databases, but I get why they don’t want to switch databases back to something else. That sounds like a nightmare.

Richie Rump: It’s like, you also have to ask questions, like have you ever written software that just works once? And then you want it to work with previous versions – it gets super hard. I take a look at Windows and my mind is blown that you could actually install software that was written 20 years ago, and essentially, it probably will work. That’s insane. It’s just insane. You can’t even open up QuickBooks in the previous version without having to upgrade to the new version.

Brent Ozar: I am so amazed that you can take a database from SQL Server 2005 and restore it to 2014 or 2016 and it just works. And you don’t have to do any steps, the magic just happens; it’s phenomenal.

Erik Darling: But you know, if you think about phone operating systems, which are even worse – you have Android or you have an iPhone, there are apps that don’t work because you upgraded Android. Like, there are apps that stop functioning because they make such fundamental changes to the operating system that those apps don’t do things the same way. So like Richie was saying, you could install something on Windows from 20 years ago that still works, but you can be crap out of luck on phone OS if it’s like six months ahead of whatever app you installed. Compatibility is hard.

Richie Rump: That’s a philosophy, right, when you’re writing software. It’s like, yeah, we’re going to forget everything that’s old, and it’s just new, new, new, new, new and you just need to upgrade to a new version, and that’s it. And that’s just a philosophy – whereas Windows was the complete opposite. Like, I know we need everything to work in previous versions. It’s just really hard to do it.

Erik Darling: That, and as someone who works on scripts that have to work across a wide variety of things, as soon as we don’t have to support an older version anymore, I want constant forward momentum. I don’t want to have to keep thinking about other stuff. I have it on my calendar for 2019 when 08 and 08 R2 are pulled, because I am not doing one more version check.

Brent Ozar: Amen, I think it’s also going to catch people by surprise as they start playing around with Azure SQL DB Managed Instances. Managed Instances are roughly equivalent to Always On Availability Groups. You can upload a database into there, it just kind of works and they manage it for you. But man, heads up, if you ever want to get your data back out, you’re going to be SSIS or BCB…

Erik Darling: There is no SQL Server version, physically, that is the same as the Azure DB version.

 

Should I separate my data and log files?

Brent Ozar: Landon says, “I know these questions come up a lot, but deploying SQL Server on VMs with shared SAN storage – is it worth separating the data logs and tempdb files onto their own disks like traditional best practices advise?” What you want to do is – with your SAN vendor – because there are cases with some SANs where they will handle caching differently, for example. And then they require you to have the logs on separate drives for different ways of doing caching. But it is going to be different for every make and model of SAN. Generally speaking, it’s not going to be the performance difference that gets you across the finish line.

Tara Kizer: I have a different take on it. So, I like to still split them up regardless; especially by database even. So that if one database – it blows out the log, it doesn’t cause storage issues for other ones. So by separating them to mount points or drives, I can avoid production issues that some databases might cause.

Brent Ozar: And if it’s not by database, I also like by business unit too. Like if you’ve got a bunch of accounting databases on the server and they all have their own use pattern, then you can group them together.

 

Why is my query sometimes slow?

Brent Ozar: Let’s see. Chris asks, “Hey, I have a task where I need to update millions of rows in a table. I wrote a script that will batch these updates into smaller 1000 row updates. Sometimes when I run it, it’s smoking fast; other times it’s completely slow. When it’s slow, I stop it, count to three, hit run again and it goes back to being smoking fast. I’m not seeing any blocking and my database is in simple recovery model; any ideas of what to do when it sometimes just runs slow?”

Erik Darling: So is it slow from like the outset, or does it slow down after a few batches? Don’t blame fragmentation.

Brent Ozar: He says from the outset… I’m guessing bad query plan – oh no, because stopping it and counting to three wouldn’t make a difference on that.

Erik Darling: Blocking?

Brent Ozar: He said he’s not seeing any blocking.

Erik Darling: Oh, he did say that. Thanks, guys.

Tara Kizer: Check the waits – what is the query waiting on?

Brent Ozar: And how would you do that?

Tara Kizer: Well, I would do it with sp_whoisactive. Erik might get a little upset though…

Erik Darling: No, I use sp_whoisactive for lots of stuff; sp_whoisactive is superior in its own special ways that I am not trying to compete with. BlitzWho does stuff and it grabs stuff from new DMVs that sp_whoisactive doesn’t touch, and that’s our niche with that. We look at stuff that Adam is too busy running Python to mess with.

Brent Ozar: And one of the things I love about sp_BlitzWho over sp_whoisactive is that it will also show you session waits much easier. So you don’t just see the current wait, you see all kinds of other waits. So he follows up with he thinks it’s usually CXPACKET, but definitely check sp_BlitzWho because it will show you the session waits altogether; which may be a lot other than CXPACKET.

Erik Darling: You know what happens to me sometimes when I run things as a loop? I forget that I have query plans turned on, and that just messes me all off because it makes the query plan for each iteration of the loop nest.

 

Should I do CHECKDB in a maintenance plan or a script?

Brent Ozar: So, let’s see, amigos. Pablo says, “What do you think about CHECKDB? Should I do it on a maintenance task or should I do it with T-SQL and log the output?”

Tara Kizer: I don’t care how you do it, just do it. I’m not a fan of maintenance plans at all, so I won’t use those no matter what. But I’m okay with maintenance plans, just not for my servers. But other people’s servers, I’m okay with them for backups and CHECKDB. I’m not okay with them for index maintenance. Use Ola Hallengren’s Index Optimize.

Brent Ozar: It’s totally free. Ola.Hallengran.com – Swiss Army Knife; all kinds of cool stuff.

Tara Kizer: The only thing to add is make sure that you are logging the outputs so that if CHECKDB ever does fail, you don’t want to have to run CHECKDB manually in a query window to get the output. So have that output go to a file so that you can take a look at – sometimes the failures are benign, but sometimes it’s going to mean database corruption. And you do not want to have to run CHECKDB when you encounter corruption again because you might be talking about a 10TB database, and that could take hours.

 

I haven’t mastered replication yet…

Brent Ozar: Speaking of which, Daryll says, “I haven’t mastered replication yet…” [crosstalk] It’s like a support group here. “I’ve scripted out my mess and successfully run it and added articles with the GUI. My vendor says don’t use the GUI; only script it and never touch it.” He says he only runs snapshot when the article isn’t flagged. “Can you help me find the column for an article not flagged for snapshot?”

Tara Kizer: I have no idea what any of that meant, but I’m a fan of the GUI when it comes to replication. In my last job, they were fond of scripting it. So luckily, when I arrived they already had everything scripted and I would just change what I needed to. I just followed their template. But previous jobs, my boss always said you should start scripting this stuff – but the GUI works fine here. I mean, I supported replication for many years, but I didn’t love it so I never cared to figure out the scripting.

Brent Ozar: Yeah, if it works. I like – if you know it implodes all the time – so like I had one job where replication just always imploded, so we script it out and we could just go run it whenever it broke. But god, if you’re in this situation that bad, how about you fix it so that it doesn’t break all the time? We couldn’t be bothered.

 

Best practices for full-text indexes

Brent Ozar: Ron asks, “Do y’all have any best practices for using full-text indexes?”

Erik Darling: Yeah, it’s called Elasticsearch…

Richie Rump: Yeah, Elasticsearch…

Tara Kizer: Don’t use it.

Brent Ozar: What’s the difference between Elasticsearch and SQL Server?

Richie Rump: So I always felt that Elasticsearch, you could just do so much more. You could do more with the querying, you could do a heck of a lot more with the functionality it actually brings out. Remember, full-text search really hasn’t been updated in years and years and years and years, so it’s pretty basic. I mean, if you just want to do a simple search, that’s fine. But with Elasticsearch, you could create these crazy search strings and really dig into your data and find, you know, insane stuff. The only problem is that you do have to move your data from SQL Server into Elasticsearch and put the index on top of that. So, if you have something that’s, you know, you want to keep secure, you’ll have to think about how that’s going to work. But other than that, it’s designed for searching. That is what it’s designed to do. And so it’s the best tool out there for doing that.

Brent Ozar: How much does it cost?

Erik Darling: Free 99…

Richie Rump: Free to me.

Erik Darling: It’s funny, I was on the phone with a client and they were like, “Oh well I wrote the poor man’s version of Elasticsearch in SQL Server.” And I’m like, Elasticsearch is free. It is the poor man’s version. It doesn’t get any more poor man than free. You can just go download it; it’s right there for you.

Tara Kizer: That must be the client that we share because I remember hearing that.

Richie Rump: You know, running the poor man’s version of Elasticsearch ids, what, Apache Solar, which they branched off the same thing. I mean, I don’t know.

Erik Darling: Back when I used to support relativity, they had the option to create full-text indexes. And leaving aside the fact that setting off a crawl on a several terabyte table kind of sucked, but I really just hated dealing with the search queries that would come out of that. Like with the contains and the contains table, no one was ever quite sure what to do to get what results back. And as part of the application, they also used dtSearch and they sometimes used Elasticsearch for things. So obviously, even they had been pulling away a little bit or giving people alternatives because full text just wasn’t good for all kinds of searches. What I really like the best about Elasticsearch is that it’s outside of SQL Server, so you’re not wasting all your RAM and other resources on these giant full-text catalogs. You’re putting it outside into a product that was meant for researching that kind of thing.

Brent Ozar: We should probably suffix it to say too, if you’re doing home development of an app that’s 5GB to 10GB in size and it’s never going to hit 100GB, then sure, full-text indexing is probably fine, but you don’t need any best practices. It’s just going to work. But beyond that, that’s when we start asking questions.

 

Do differential backups take longer for striped backups?

Brent Ozar: Brain asks an interesting one. “Have you ever seen differential backups take longer – like ten times longer – when the full original backup was striped as opposed to done to a single file?” It’s a no from me…

Erik Darling: No from me…

Brent Ozar: Alright, I think you got a unicorn there.

Richie Rump: I mean, are there unit tests that you could run against it? I don’t know.

Brent Ozar: Yeah, if you could reproduce that, create a database 10GB in size, fill it with random junk data and do the test back and forth, showing it’s [immediately] different and reproduce it on a couple of different SQL Servers, then submit it as a question to DBA.StackExchange.com. And people will up-vote the hell out of that because if it’s reproducible, that would be an awesome bug to find.

Erik Darling: Honestly, the only time I’ve ever seen diffs take longer is when there was more diff to diff. I don’t think the backup striping ever had anything to do with it.

Richie Rump: Choose these DBAs, choose diff.

Erik Darling: I like it. I found myself in a weird situation last week where…

Brent Ozar: Just once?

Erik Darling: Just once. Well, I mean one day, in one instance – one relevant instance of a weird situation last week where the client was doing big data warehouse stuff and they wanted some replication of the data, and I actually recommended differential shipping to them because they were all in simple recovery. And there was just no reason for them to switch to full and start doing log backups, so I was just like look, you can take a diff – because they were doing nightly fulls with snapshots. So I was just like, you could just take these diffs at four to six-hour increments and be okay. They’re going to be big, but you can do it. It might beat the pants off you having to learn transaction log backups at this late place in your developer career.

Brent Ozar: I also just want to point out for people who are watching the video, you see how my webcam is doing this weird thing where it wraps around? I can point it one side and my finger comes out the other. This has happened all morning with my training webcast. I noticed it when I was like doing jazz-hands and they started coming out the other side. I was like, what the hell is going on here.

Tara Kizer: What have you done to the matrix?

Brent Ozar: I know. What I wanted to do is I wanted to get over far enough that I could scratch my own ear, but…

Erik Darling: I thought it was like, is somebody in there with him? What’s going on here?

Tara Kizer: Erik is trying to [crosstalk].

Brent Ozar: I will crush your head.

 

Does SQL Server need 2 IP addresses?

Brent Ozar: Sri asks, “Do we need to have two IP addresses for a SQL Server?”

Erik Darling: For what?

Tara Kizer: Need to? No.

 

Are the 2008 setup files a security risk?

Brent Ozar: Terry says, “We have SQL Server 2012, and it’s” – hold on. They have a SQL Server 2012 but somebody installed the 2008 management objects and the 2008 set up files. “Our security team is asking us to remove them. I’ve Googled and found the people ran into issues. What are your thoughts?” Well, you know what I’d do? I’d tell your security team to remove them. If they’re so confident in how this works.

Tara Kizer: And if they’re requiring you to do this, I’m not sure what kind of security risk there is for having them, I’m sure there’s something that it’s just not aware of, I would require that the company gives me a new server so I can move everything over to this. I would not be doing this on an existing production server.

Brent Ozar: Log ship or database mirror over to it or something. But, yes, it’s a little sketchy.

 

Do log backups truncate the transaction log?

Brent Ozar: Sri asks, “Do maintenance plans transaction log backups truncate the logs once the log backup finishes?”

Erik Darling: Well, any transaction log backup will truncate the log internally. Not externally.

Brent Ozar: As long as – like, there’s some things that will cause it not to.

Erik Darling: Right, like replication or whatever other weird log reuse wait. But – or like, an open transaction that’s keeping things all big and bloaty. But generally, I think if you’re expecting the size of the file to physically shrink after a transaction log backup, you’re going to be waiting a very long time because that’s just not what happens. The transaction log grows as it needs to, to accommodate active stuff, and then internally, marks space for reuse when transaction log backups happen. Apart from like, what we talked about, if there’s a weird log reuse issue. Like, an open transaction or replication or whatever else shows up in that sys.databases log_reuse_wait_desc.

Brent Ozar: Funny. Things you never think you’d learn as like, you know, as a kid, or you’re getting into the industry, you’re like, “What are all these cryptic DMV names?” And now we’re like, “It’s over in log sys.databases log_reuse_wait_desc.”

Erik Darling: Yes, things I’m trying to forget so that I can learn other things. There’s only so much space in here and I’m just trying to like, flush some of this stuff out so I can get new things in there.

 

Followup on the security risks

Brent Ozar: Terry says – follows up with, “Ha-ha, that’s a good one. It isn’t how it works. Security is in charge and the DBAs are dirt, so yes, they’re requiring it and we have to do it, and no, we can’t have a new server.” I have a great consulting line for this. I’m just like, “Go ahead and show me how it’s done. I don’t quite understand.” Like, [unintelligible]. “No, I’ve never done that before, I’m not comfortable with that. It’s a risk for me, I’m not comfortable with it.”

Erik Darling: That’s a production box. If you want me to make changes to that, I want something to test those changes on before I push them live into production. I don’t want to just willy-nilly install stuff that might mess up connectivity, whatever, to my server, because then I have to troubleshoot that on top of your stupid security issue that’s probably non-existent.

Tara Kizer: And then also, if you Googled and found that other people have run into issues doing this, this needs to be documented, and that they need to – yes, the security team needs to sign off, and the CTO needs to sign off that this is a potential known issue to cause problems and you’re requiring me to do it in production.

Brent Ozar: And start a support call with Microsoft. It’s 500 bucks, be just like, “Sure, I’ll do it as long as you, you know, start a support call with Microsoft and they walk me through it.” Microsoft will be like…

Erik Darling: No, like, you have to be on the call with me when it happens. Because if you’re asking me to do this thing that I can’t do, like, you can’t just like, physically change that server during business hours. You can’t just like YOLO uninstall crap. Why would you do that to yourself? I don’t know.

 

Why won’t our admins let us do this?

Brent Ozar: Anon emus says, “We have a developer who’s trying to use OA create processes”, this just keeps getting better. The more that I read it gets even better. “For making bots to send notifications about certain jobs running across multiple servers, using linked servers.” I think that’s probably the single sentence with the most bad ideas I have seen in it in quite some time. “We cannot get SA permissions.” Yes, that’s probably a good idea.

Erik Darling: Good.

Brent Ozar: Whoever made that decision [crosstalk].

Erik Darling: Whoever is saying no to you is pretty smart.

Brent Ozar: You should buy them a beer.

Tara Kizer: I have a blog post today that’s how to suck at database administration and in the comments like I added something today that said, “Guess some of us need to talk about the people skills, the personal skills that senior DBAs are very lacking out there.” And one of the issues on my reviews at past jobs is that people would say, “These are third-party reviews”, people would say, “You know, she needs to have a softer way of saying no.” And for this here, I would say, “No, end of story, done. I don’t care what my review’s going to say. This is just dumb.”

Richie Rump: A softer way of saying no? Hell no? I mean [crosstalk].

Tara Kizer: I know.

Richie Rump: And the horse you rode in on.

Tara Kizer: They would say that, even if the answer’s no, she should have more words go around it, make it sound nicer, and it’ll still be no in the end. It’s like, what?

Richie Rump: You know that sunshine, it should go up the rear, that’s where I want to blow it off. Okay, here’s two words. No-o.

Brent Ozar: You smell great today. No.

Erik Darling: Good news anon. Look, I’m going to give you some good news though. Between you and me, I’m sure you can do that in one line of PowerShell, so just don’t sweat.

Tara Kizer: Even Erik could do it in one line.

Erik Darling: That’s it. One line.

Richie Rump: There’s no unit tests around that PowerShell code, I’m sorry.

Brent Ozar: None of us – I think it’s universal, none of us support doing that. We’re not [unintelligible].

Erik Darling: SP create OA things are like, deprecated. They’re like, in the dust. Like, I remember reading like, old like, SQL some workbench articles on Simple Talk with like, someone would use like SP OA create to do all this crazy stuff, and I’ll be looking at it like, “Are you sure? Why would you make a database do that?”

Brent Ozar: I remember stumbling across stuff like that at some point when I was way young and I’m trying to figure out, “Alright, well, it’s out on the web, it must work. This must – someone had to get approval to post this, right?”

Erik Darling: Yes, it’s like creating Excel files and then destroying Excel files and doing all this other stuff. I’m like, this just seems dumb. This just seems a bit daft. It has to be – I learned that word because I went to Bits by the way. Daft is an English word. After I got done converting my fat ass to kilos, I learned how to say daft in English.

Brent Ozar: Richie’s been telling me that I should check out ear grey tea. That was what he was talking about.

Tara Kizer: Ear grey? Earl grey.

Richie Rump: My favorite. It’s great. I’ll never live that typo down. That’s exactly right.

Brent Ozar: I’m going to serve him some. I’m going to be like, “Here’s your ear grey tea.”

Richie Rump: Is it Van Gogh flavor?

Brent Ozar: Nice. Terry follows up with, “You guys are great, now I’m ticked at myself for not thinking of all those things.” It’s the – how everything works anytime you talk to anybody else. Everyone else seems to have all the good ideas.

 

How do I clear sleep_bpool_flush blocking?

Brent Ozar: Let’s see. Mahesh says, “My transaction log has grown too large, like, 200GB and as I dig into it, it seems like some internal checkpoint is stuck with sleep_bpool_flush. Any idea how to clear the transaction log and complete a checkpoint? The database is in simple recovery model.

Erik Darling: Look in your error log for messages that say flush cash. Because if you have messages in there that say – like, there’s messages – it’ll be like, flush cash waited x amount of seconds to push x amount of pages to disk. It’s a really nasty, like, kind of I/O issue. You’ll also probably see like, if you search for flush cash and also search for 15 seconds, because you’re probably running into nasty I/O issues where the buffer pool can’t flush stuff from memory to disk fast enough.

Tara Kizer: So while you guys were answering a previous question, I started researching that wait stat, and so – I was keeping myself busy, so Paul Randal said that you may see as a top wait, if a checkpoint is blocked for some reason, and he doesn’t mention slow I/O, but he says an example of this would be trying to start a data backup, which does perform a checkpoint, while the database is being encrypted for a TDE, transparent data encryption.

Erik Darling: That happens to the best of us.

Tara Kizer: Yes, I suspect that maybe, you know, if this isn’t happening during your full backup, that you may have – oh, you said simple recovery model, so you have TDE and a full backup occurring at the same time.

Brent Ozar: Or is someone trying to rotate the keys, which requires it to re-encrypt the whole database and you can’t take a backup while that’s happening?

 

Can you give me a brief overview of query store?

Brent Ozar: Right, the last question we’ll take, can you – Tom asks, “Can you give a brief overview of the query store feature in 2016?”

Richie Rump: Quick rundown? Oh wait, I know this, right? So, this is – this is when you go and you take a look at queries and you select which one to buy. And you install it on your machine, yes. It’s like an [crosstalk], nobody uses it.

Brent Ozar: You especially want to focus on the one that has a little robot hand, and it’s a claw, and it picks up queries and drops them off to you. Just make sure you pick the right one, not the truncate table one. No, none of us are serious. You know what you do? Okay, here you go. Go to GroupBy.org – not as in group purchase, but GroupBy.org, and query store on there and there’s been a couple of sessions on how query store works. Check those out and you can watch like, a 90-minute free video on there about how it works and see demos of it too as well.

Erik Darling: And then if you want to go explore your query store, I have a stored procedure called sp_BlitzQueryStore, which allows you to do very sp_BlitzCache-y things on your query store queries. End of story, sentence, period.

Richie Rump: I was waiting for more.

Brent Ozar: Then comes the robot claw. And make sure that you’re fully patched too, because there’s a lot of known issues around query store that got better with Service Pack 1 cumulative updates 6 or whatever it was.

Erik Darling: Standard issue couldn’t flush plans out of it and couldn’t clear it out. It grew astronomically.

Richie Rump: I’m looking at a sp_Blitz roll right now for query store clean up.

Erik Darling: Look at that.

Brent Ozar: I like it.

Erik Darling: Thanks, Richie.

Brent Ozar: Alright, well thanks everybody for hanging out with us this week, and we won’t see you next week on Office Hours because we’re – three of the four of us are going to be in Kingdom of United, Richie’s staying home and holding the fort down in Miami, but the rest of us are going to be at SQLBits in London. So we will see y’all in two weeks. Adios everybody.