This week Brent, Erik, and Richie discuss filestream waits, what to use instead of filestream, Availability Groups & clusters, implicit data conversion, using snapshot backups, MERGE statement bugs, deadlocks, the future of on-prem servers, Erik’s new sp_Blitz lock, and if the SQL Server knowledge base “Virtual Assistant” can answer DBA interview questions.

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 – 12/6/2017

 

Should I be worried about Filestream waits?

Brent Ozar: Shaun says, “I’ve been using sp_BlitzFirst and logging the results every 15 minutes for the last week. I’m using the new Power BI dashboard from the First Responder Kit. I’m seeing a lot of file stream work item queue waits; what causes this wait and should I be concerned that this sometimes gets very high for a 15-minute period?

Erik Darling: I don’t know what causes that wait. I mean, it doesn’t say – what, 2012? So it’s not going to be anything in memory, because I know that Hekaton does have some FileStream stuff going on. I mean, if you’re using FileStream, sure, maybe. But my question would be is it causing a problem? When it gets high, if your server under noticeable load in some other way that’s hurting other user queries? If it’s not causing a problem then gosh, I don’t know what to tell you.

Brent Ozar: Yeah, if you Google for it – and that’s always what I would recommend people do whenever you have a wait stat, because there’s tones of detailed info – there’s one in here, DBA.StackExchange, where Aaron Bertrand elaborates that it doesn’t seem to be anything that you should worry about. I would also check and see, yeah, are you using FileStream? And why are you using FileStream?

Erik Darling: That’s the better question, why are you using FileStream?

Richie Rump: Was the file server not good enough?

Brent Ozar: Yeah, or it’s not expensive enough – wanted something more expensive. He says, “Is there a way to exclude just this wait from the Power BI chart as it’s really throwing off the vertical scale?” You can edit the queries in Power BI. So if you go into data sources, you can edit to exclude specific stuff. Or if you want, just file an issue in the First Responder Kit’s GitHub repository and we’ll take a look at it too there.

Erik Darling: That’s what I was going to say. If it’s a wait stat that we shouldn’t be looking at then we can just add it to our list of exclusions anyway.

Brent Ozar: Plus, besides it’s FileStream …

Erik Darling: [crosstalk] Three people using that in the world.

 

 

Should the AG listener respond to a ping?

Brent Ozar: Julie says, “Regarding availability groups and clusters, what should return results with either ping or NS lookup; cluster name, listener name or nodes?” All of them. You should be able to ping by cluster name, you should be able to ping by listener name, you should be able to ping by node name; the same thing with NS lookup. The Windows firewall will influence that too. If you have Windows firewall turned on, different either IPs or ports – but as long as you have firewall turned off you should be able to do any of those.

Erik Darling: Turn off all your firewalls.

Brent Ozar: Who needs those?

Erik Darling: That $15,000 Dell Sonic Wall just…

Brent Ozar: And so we talked about, briefly, DBA.StackExchange. so you can go over here to DBA.StackExchange and ask a question. We answer questions over there all the time too as well. You can post all kinds of detailed stuff inside there. Feel free to go over there. You can post your long questions and…

Erik Darling: I answered a couple of neat ones recently, actually just a couple of days ago. Let me track on some of those. One was about the number of locks that happen when you update unique key index. Another one – that one was really cool actually. I’ll stick the link in Slack, you maybe want to open it up. But I thought it was really neat because it was something that I had to look at a little bit, because it wasn’t immediately obvious to me what was happening with it. Someone was complaining that when they ran ISNULL on a NULL date column, and they wanted ISNULL just to return an empty string, it was returning a full date, and it was…

Brent Ozar: I saw that question.

Erik Darling: Yeah, it was really cool. That is not it…

Brent Ozar: Yeah, well you didn’t post the…

Erik Darling: I did, it’s in Slack.

 

Reading DBA.StackExchange.com questions

Brent Ozar: No, it just says /questions. Let’s see here – let’s look at the question first because it was amazing. “I have a date time table called Dock Date. I’m trying to evaluate an ISNULL against it and return a blank value instead of NULL. Here’s the select statement. It returns 1901…” Because he’s saying ISNULL Dock Date, Dock Date empty… Oh, I’m going to guess empty strings parse to 1900?

Erik Darling: Yeah.

Brent Ozar: Oh, that’s so awesome because I didn’t know that the first time I read this question. When I first read the question, I was like I don’t know what’s going on with that. Oh, now I know, that’s genius. This is what a good answer is on DBA.StackExchange.com, when you put in these kinds of repro code so that other people can run your code and understand what the answer is. That’s nicely done there. For your information, the reason for this is implicit data conversion…

Erik Darling: Yeah, but I have that in my answer.

 

AG questions continued

Brent Ozar: Let’s see, Julie says, “For an Ag follow up…” Oh, for some reason my questions window broke. Let me get that back out.

Erik Darling: I can read it. “When listener IP address does not return info, like a nonexistent domain, how do you troubleshoot that?”

Brent Ozar: Oh, like the NS lookup. But most times I’ve seen it is where the computer didn’t have sufficient permissions to edit DNS entries. So you could either pre-stage that – some people pre-stage the computer object, either in DNS or in active directory, but that’s the place that I would check first. You can also see it in startup; it will say SQL Server didn’t have the permissions to create service provider name or DNS entries. You’ll also see, and I want to say ever 24 hours, is an error in the error log as well, but that’s the place that I would start.

Erik Darling: I’ve ran into some other weird ones where older DNS entries actually had to get deleted so that new DNS entries could get put in that reflect the right name and all that other stuff. That’s another good one.

 

Any concerns about snapshot backups?

Brent Ozar: Adam says, “What are the concerns and drawbacks to consider with using snapshot backups like Rubrik or Cohesity?” Veeam is another good one.

Erik Darling: yeah, for me there’s a list of like three things. One is, are the snaps consistent? So making sure they’re not dirty snaps that can cause corruption when you restore them. Two is the quickness of the snaps. SQL Server’s error log will tell you when the snapshots get taken when data is frozen and then thawed. And then if there’s a big gap between the freeze and the thaw, like ten, 20, 30 seconds, then you need to be concerned, because at that point your backup is pausing all database activity for that amount of time. And the third one is restorability. I’ve mentioned the dirty snap thing on this end. On the other end, I would want to make sure that whatever snaps I restore, I can also apply log backups or differential backups to them after I bring them on the line – they’re just not going to like bring me back to one single point in time because at that point I’m putting myself at risk of losing perhaps more data than I’m comfortable with.

 

What should I use instead of filestream?

Brent Ozar: Shaun says, “Don’t use FileStream? I don’t want varbinary max using up my buffer pool, but I want file history and coupling between files and their associated table records. What should I be using instead of FileStream?”  Write to a file share. Whenever you need to up your write files to disk, write to a file share. Ideally, you work with your Windows admin so it’s a file share that people can write new files to but they can’t edit existing files; so people are forced to push in new versions every time. That way, if you need to restore from backup, your SQL data may be in the past but your files are even there for the future; you’re not losing those files. This is sometimes called a WORM file system; write once read many.

Erik Darling: One thing I’ve seen used to help couple up changes and stuff like that is one, like a plain timestamp column, to tell you which is the newest version. And two is a hash of the file, so you can figure out if there were any changes. I mean, you can’t completely deconstruct the changes, but you can figure out if there were changes and when the changes happened.

Richie Rump: And I’ve done both, and if you want to link the file to the actual record, you actually have to put the path in there. So whatever path you want, that’s how you do it. If you find a use for it, then great; use it. Hardly anyone else does because there’s really no problem that people haven’t already got a working solution for with the file system and just using a path in the record. But yeah, I’ve actually used the old, what you were talking about, Erik, the old hash method.

Erik Darling: I love a little hash trick.

Richie Rump: Yeah, you could just throw the hash in there, and if you want to check for duplicates, just check to see if the same hash is there. If it is, then hey, we don’t have to do something to this file.

Erik Darling: Yes, again, that can totally help you from having to like reload duplicative data. The other thing I like about the hash is if you choose a strong enough has, the chances of a collision are almost nil. So like the HASHBYTES function in SQL Server can give you up to SHA512. And for what you’re doing, just generating it once in a while for a file like that, you’re not going to burn up CPU like you would if you were trying to generate like a row-wise hash and keep track of duplicates.

Richie Rump: And I don’t even – I don’t think if I use that in SQL, everything was done on the app side as opposed to some heavy function in the SQL side. So I want my database to handle database stuff and I want my app side to handle app stuff; and that’s kind of an app type thing.

Erik Darling: Sounds good to me.

Brent Ozar: Especially as cheap as app servers are relative to SQL Server; seven grand per core for Enterprise Edition; holy mackerel.

 

Have they worked out the bugs with MERGE?

Brent Ozar: Mike asks, “Have they worked out the bugs with the merge statement from 2008 in regards to indexed views?” Well, so the place you always go to look is if you Google for merge and then Aaron Bertrand. Aaron wrote this post here; Use Caution with SQL Server’s Merge Statement. And he lists the top – not all – but the top connect bugs with the merge statement. [crosstalk] Produces deadlocks By Design. So then you can click on each of there. Note that the blog post here doesn’t reflect the updated status of the connect items, so you’ll want to click through to each of these so that you can feel just how bad it burns to feel Won’t Fix, even now in the year 2017.

Richie Rump: I mean, the best thing to do about merge is understand the issues and if you want to go use it, go ahead. But just understand that you may run into some problems. When you do, if you do, then you say “Hey, there’s a problem with merge, maybe we should go do something else.” But a lot of people use it, a lot of people use it in [jest], and it works, but there are problems. Just understand that there are problems with it.

Erik Darling: Whenever someone tells me that they want to use merge, I just ask them what’s wrong with insert and update.

Brent Ozar: I get that it’s harder to code inserts and updates. You just want to point to one table and you want to point to a source file and a destination and just tell SQL Server, “You work it out, baby.” I get that that sounds elegant, but…

Erik Darling: SQL Server isn’t working it out, though.

Richie Rump: The problem is that now I’ve got to go read it, right. Is this an update? And if it is an update, now I got to go back and I’ve got to do the update; and if it’s not then I’ve got to go do the insert. And that pattern is tiresome to write. So, if I could just write one statement and it does it all, and it does my UPSERT, then beautiful, perfect; that’s kind of how I want it. I’m not saying that it’s in some of my Postgres code that I wrote recently, but it could be.

Erik Darling: Actually, that’s an interesting question. Is merge better or more reliable in Postgres than it is in SQL Server? Like is there a list of Postgres issues like this for merge, or is it better developed or something?

Richie Rump: I would probably say it’s a little more tighter, but I didn’t look for any issues because I know that I’m not going to be dealing with a lot of stuff. It’s not going to be high volume stuff…

Brent Ozar: So if you search for Postgres merge statement bugs, you get the Aaron Bertrand post about SQL Server bugs. [crosstalk] UPSERT is probably it, yeah. Wow, Michael Swart has one too. What? I don’t even remember that one. So let’s see, Postgres UPSERT statement.

Richie Rump:  Yeah, I have a couple. It’s not production code; whoops.

Brent Ozar: it’s okay. We embrace bugs here. We’re all about bugs…

Richie Rump: It’s actually unit test code that I’ve got here that I’m running UPSERTs with.

Brent Ozar: Excellent…

Erik Darling: I think if you don’t have any bugs in your code, you’re not trying to do anything all that interesting. Like you’re just doing boring crap…

Brent Ozar: By that measure, we’re trying to do a lot of really ambitious interesting stuff here.

Erik Darling: We are, and that’s the bar I want to set.

Richie Rump: If you agree with that, Brent, then I’m going to get a lot more sleep; it’s going to be great/. I’ll get a lot more Switch time.

Brent Ozar: It’s true. If you go to BrentOzar.com/blog, down here, the second most recent one as of this moment – maybe third most recent one – Erik did a release of the First Responder Kit and we fixed all kinds of bugs.

Erik Darling: And added new ones…

Brent Ozar: And added new ones…

Erik Darling: Beautiful new bugs.

Brent Ozar: Very cool. And of course, I always feel better too when I look at Microsoft’s list of bugs. If you go to SQLServerupdates.com and you look at the updates in any of the most recent CUs – although we were just talking about how sometimes people will say, “Is SQL Server ready for primetime before Service Pack 1?” You know, “I’m only going to wait for Service Pack 1…” Well, now you don’t have Service Pack 1 anymore. So starting with 2017, it’s only Cumulative Updates. “So how long should I wait before these cumulative updates?” If you look at CU2, the things that they fixed in CU2, none of them were really that scary.

Erik Darling: It’s a snooze fest.

Richie Rump: They didn’t fix the scary ones, Brent.

Erik Darling: I mean, when people say stuff, I’m like, okay, well does SQL Server have any more bugs in it than your code base does? Then it’s like, how bad is your application? Is SQL Server really worse? What do you stand to lose?

Brent Ozar: Alright, let’s give this a shot. So SQL Server, on the Knowledge Base article here it says, “Hi, I’m your virtual agent. Tell me about the issue you need.” So let’s give it a shot. “Hello…”

Erik Darling: Say does 2017 cause corruption.

Brent Ozar: “Does 2017 have less corruption bugs in it? Is it safe to deploy?” Now, let’s see. I love how it looks like they’re typing. They get the fake typing thing in there. So suggestions – this is what Microsoft is suggesting for us, that we should just go to SQL Server 2016; that’s what they’re saying here.

Erik Darling: Well, question answered.

Brent Ozar: Did this solve the problem? Yes. Great, glad that worked. Alright, what else should we ask? “How many clustered indexes can a table have?” Let’s see if it would pass the interview question for a database administrator.

Erik Darling: the Turing test.

Brent Ozar: Not bead, “Cannot create more than one clustered index, but I don’t have any other indexes.”

Erik Darling: How many databases can I have in my availability group?

Brent Ozar: Oh that’s good, yes. Alright, so, “How many databases can I have in my availability group?” Let’s see what she says. “Get the databases that are not…” yeah, not a lot in there. We’ll try one other one. We’ll say, “How do you spell Always On? With or without a space? AlwaysOn or Always On?” Let’s see what she says… “Could you rephrase the question?” “Is AlwaysOn or Always On right for SQL Server?”

Erik Darling: Have you considered log shipping?

Brent Ozar: So, as we can see here, the first two results have no space and the third result…

Erik Darling: They don’t even have consistent capitalization.

Brent Ozar: This one’s a forum; these two are forums, so I guess I’ll give them passes on that.

Richie Rump: I don’t know that. I’m using virtual agent – how am I supposed to know that?

Brent Ozar: “Are you having as good of a time as I am?” No, certainly not.

Erik Darling: When will Twitter turn that one into an awful racist?

 

When will this SSMS bug get fixed…

Brent Ozar: So let’s see here. Rick – back on our real day job – Rick says, “When will must declare the scalar variable service start mode get fixed?” I don’t know if that’s a bug. What is that? Is that an SSMS bug, or where’s that from?

Erik darling: I don’t know. Now I want to go Google that variable.

Richie Rump: Maybe he was asking us to ask the virtual agent?

Brent Ozar: [crosstalk] “Service start mode…” oops…

Richie Rump: When will America switch to the metric system?

Brent Ozar: Oh damn, SQL Server 17.3 doesn’t work. SQL Server management studio 17.3 doesn’t display SQL Server agent? Oh, to a 2005 instance – come on now, what year is this? I can click okay – yeah, so it looks like SQL Server management studio 17.3 can’t connect to SSMR, can’t connect to SQL Server 2005’s agent server; oh well, you know, upgrade. How hard can it be? We asked – recently we had a training class and we were asked – at a GroupBy too – we were asking, what is the oldest version of SQL Server that people still support? And some people were still supporting seven and 2000.

Erik Darling: Andy Leonard too, during his SSIS – I was proctoring for Andy Leonard’s SSIS course the last couple of days, and he asked a similar question about versions people still have to support, and there were people going all the way back to 2000, 2005, and I was just like, still; in this day and age? And I had a sales call today with someone and the guy was talking about how he was virtualizing servers in their new environment, and he had moved a bunch of them onto new hardware, but they still hadn’t been able to switch SQL Server versions. So they were still on like 2000 for about half a dozen servers. Like, how do you even function?

Richie Rump: This one grey beard in the back goes, “4.3.”

Brent Ozar: “Running on Windows NT 3.51.”

Erik Darling: You think of a SQL Server 2000 box running today, like that thing can join the army, buy scratch tickets, it’s going into college, it’s been driving for a couple of years, it’s probably gotten a couple of girls pregnant.

Brent Ozar: I was going to say the same thing.

Erik Darling: I could see it in your face.

Richie Rump: Totally deadbeat.

Brent Ozar: SpotlightEssentials.com, you can go click on collective IQ at the top. This is Quest’s free spotlight version, and you can see what versions people are monitoring in 2005.

Erik Darling: That’s a liar.

Brent Ozar: Well I think this is lying.

Erik Darling: 6000 servers though [crosstalk] sample group there.

Brent Ozar: Wow, so 3% of them are still SQL Server 2005. 6213 times 3% – 186 servers out of the 6200 are still SQL Server 2005.

Erik Darling: That’s bizarre. I like that 2017 is on 0%. It still gets a piece of the pie – it’s still part of the pie graph but it’s 0%. [crosstalk]

Richie Rump: And this is why – and it looks like that’s a bigger percentage slice than the 3%. And people wonder why…

Brent Ozar: You’re comparing the…

Richie Rump: [crosstalk] I see, that little slice there. Okay, got it. [crosstalk]

Erik Darling: It’s like when you look at a chart of world economies, that’s Mauritania.

Richie Rump: But again, pie charts suck, right. So it’s still showing the pie chart – my point’s still valid; pie charts suck.

Brent Ozar: So here’s one that’s not a pie chart, although they put both; they put a pie and a bar chart. What percentage of servers are virtual versus… Wow, so 99% of Cisco servers are unhealthy. That’s interesting; okay. 99% are unhealthy; what are they trying to say here?

Richie Rump: What does healthy mean?

Brent Ozar: I’ve been asking my wife that for a long time.

Richie Rump: As you were eating that breakfast burrito, Brent? Is that what it was?

Brent Ozar: Like we go on vacation, and our favorite place for vacation now has breakfast margaritas. I’m like, okay, so it’s breakfast, it’s got fruit jelly in it; that seems healthy and sane.

Erik Darling: “Can you please pass the jelly?”

 

Will Microsoft keep supporting on-premises SQL Server?

Brent Ozar: J.H. says, “Do y’all anticipate Microsoft to continue beyond SQL 2017 in supporting on-premises SQL Server, or do you suspect that they’re going to strategically force people to go up to the cloud?”

Erik Darling: On-prem is still such a big chunk of licensing cash, and there are too many Legacy apps out in the world that just don’t fit in the cloud right now. I mean, maybe that will change in the next three to five years, but as of right now, there’s no way for them to abandon on-prem. They can keep pushing the envelope and keep pushing stuff with the cloud. But until you hit a point where you have like a net new app ecosystem where every app is a new app and it started in the cloud, it’s going to be tough to get everyone up there. Richie…

Brent Ozar: And they’ve said in the past, SQL Server, the box product, is a billion dollar a year business. If you try to walk away from a billion dollar a year business and push everyone into something else, there’s going to be another competitor waiting there with open arms; you know, Postgres, MySQL, going sure we’ll take your money.

Richie Rump: And everyone will be like, this is awesome, why were we using a SQL Server? And this was free…

Erik Darling: No one’s saying that about MySQL.

Brent Ozar: This thing is overpriced.

Richie Rump: I want my money back, and my data, what happened to both?

Brent Ozar: Ron says, “You can’t drink all day unless you start early.”

Erik Darling: Chin-chin, Ron; chin-chin.

 

Why don’t I get deadlock alerts for parallel queries?

Brent Ozar:  Alex says, “Hi, I’m running profiler with the deadlock class graph events. In some cases, I get a number of parallel worker query thread that was involved in a deadlock, but without an actual deadlock graph. Does that mean a deadlock was about to happen but hasn’t happened, eventually?” You can get a query that happens with a deadlock in itself. I don’t know if your rules weren’t set up quite right on the extended events session to not quite capture them or…

Erik Darling: Profiler.

Brent Ozar: Profiler, oh…

Erik Darling: Your old friend, profiler. Don’t blame my pal, that was your pal That’s intra-query parallelism. And when those deadlocks happen, I don’t know if profiler captures the XML for those reliably. You might need extended events for that. Or, if you wanted to look at deadlocks, Brent, what could you do?

Brent Ozar: I know a guy who knows a guy. I would go to…

Erik Darling: Is he good looking?

Brent Ozar: No, but he wears the same shirt every day, so he’s kind of got that look down. If you go to FirstResponderKit.org or if you go to BrentOzar.com and click on the tools up at the top, our good friend Erik Darling here has just written something called sp_BlitzLock…

Erik Darling: Don’t show them the code.

Brent Ozar: It’s not publically documented yet, but it’s amazing. You don’t have to turn on a trace, you don’t have to start extended events. It uses – Erik, where does it get this magical data from?

Erik Daring: So like since SQL Server 2008 or 2008 R2, there’s been the system health extended events session; which is just constantly running in the background collecting stuff. It doesn’t collect stuff forever; it banishes stuff out pretty quick. But, for a pretty good amount of time, it will keep deadlock information and a chunk of the system health session. So by default, sp_BlitzLock will go into the system health XML and pull out all of the – or part of the – XML deadlock report. If you have an extended event session that’s set up to capture deadlock XML, you can point it at the path for that too. But by default, it looks at the system health thing and it will pull out all the deadlock XML stuff, it will parse it out for you, it will give you details on stuff and it will like roll up information into different tables to tell you which stored procedures, tables, users, applications – all sorts of stuff about what actually deadlocks. So I’m pretty psyched on it. It is V1, and you’ll probably find bugs on it because there is only so much stuff that I can set up and test, and you people have crazy data out there, but it is pretty neat and it does work on my machine.

Brent Ozar: And works on my machine, and worked on a client’s machine as well; because of course, we test it live. This machine that I’m working on here is not set up for demos. I’m just going to run to see out of curiosity. And of course, it doesn’t have any deadlocks, so that’s fine; we won’t demo it here.

Erik Darling: We could create a deadlock real quick and show them?

Brent Ozar: No, that sounds like a perfect thing for us to do next week at office Hours. We’re at the half-hour mark everybody; thanks for hanging out with us. Dorian asks, “When is that coming out?” It’s actually already out if you go grab our First Responder Kit. Erik dropped it yesterday afternoon.

Erik Darling: There’s a blog post coming out this afternoon with details. There is stuff in the read me file that documents the variables and some of the output.

Brent Ozar: Good stuff. And really, you don’t need to read it. Just go run sp_BlitzLock and you’ll be like, oh my god this is everything I ever wanted; so good stuff. Alright, well thanks everybody for hanging out with us this week and we will see you next week at Office Hours. Adios everybody.

Erik Darling: Goodbye.