This week, Brent, Erik, Tara, and Richie are back in action again to and are discussing sys.dm_db usage stats, Availability Group troubleshooting, ASYNC Network IO issue, reasons for using a contained database, whether you need to add more CPUs if facing THREADPOOL (or is it Deadpool?) waits, auto-tiering tempdb, and query tuning.
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 – 5-16-18
How do I remove in-memory OLTP?
Brent Ozar: Josh says, “I’m using in-memory OLTP in a synchronized AG, but as it turns out, my workload does not warrant in-memory OLTP and I’d like to back it out now, however, I can’t find the Hekaton ejector-seat handle. Books Online says I need to drop the database. Do you have any creative ideas on how to do this while minimizing downtime?”
Tara Kizer: I mean, nothing. Can’t you just not use the feature? I mean, so what if it’s enabled, as long as you’re not using it.
Brent Ozar: That’s true, you could just stop using it. You’ll still leave the file group there, but why cares?
Erik Darling: What I wouldn’t trust is that like any of the weird Hekaton bugs would just affect it having – like, it’s enabled so you’re exposed to these bugs even if you’re not directly using it. There just might be something floating that just having it turned on causes that I would freak out about.
Brent Ozar: There’s the restore one in one in one of the recent 2017 CUs. I want to say there was a restore – if you had Hekaton, you couldn’t do a restore under some circumstances. (CU4: In-memory databases in an AG hang during recovery. Then CU5: in-memory OLTP takes a long time to recover.)
Erik Darling: For the same reason, I won’t hang pictures with Command Strips over the bed because I’m just afraid, like, in the middle of the night just crashing down on top of me. Like, over the bed, I’m using nails, cement, whatever I can get up there. I’m terrified of outside of my control things and Hekaton, you turn that one, you can’t turn it off. It’s outside of your control. You might have yourself exposed to all sorts of weirdness just having it turned on.
Brent Ozar: So then speaking of that, so in that case, you’ll like what we did. At one point in Mexico, we bought a bunch of wooden carved hearts, three dimensional. They’re like probably two or three inches thick and so Erika was absolutely determined to put these over the bed. And they’re not flat; they’re freaking heart-shaped. So she put Command Strips on the back and she was holding them to the wall where the bed is. Everything seemed cool and in the middle of the night, like once a week, a wooden heart would just fall on us. After a couple of weeks of that we’re like, okay, we’re not doing that.
Erik Darling: More superstitious people would, like, start sleeping in different rooms, get divorced…
Brent Ozar: Because hearts are knocking us on the head. Josh says, “That’s why I’m trying to get rid of it, to reduce the exposure to Hekaton bugs.” Yeah, if you wanted to really get rid of it, the closest thing that I can get is to do like SSIS or something to export all the stuff out of your database over to another database. And then, maybe right at the last minute, use something like Redgate Data Compare in order to find the edge case stuff that didn’t get caught, but man, it is not going to be pretty.
Erik Darling: There are a lot of Command Strips on that answer. I guess you could like shrink the file all the way down, set it to like read-only, no auto growth, but still, it’s there and doing stuff. I don’t know, forget I said anything; it’s a terrible idea too.
What do “user lookups” mean in sp_BlitzIndex?
Brent Ozar: Chris says he’s looking at sys.dm_db_index_usage_stats. He says, “On my table, there’s 300,000 user lookups on a primary key. I think that user lookups means the number of bookmark lookups by user queries. Is that true?”
Tara Kizer: I’ve never looked up that column to see what it means.
Brent Ozar: It should be easy enough to understand, to go test, so I’d always recommend to people – because I love demoing that DMV in operation stats – is go create another table. Go create a table with nothing in it, put five rows in it and build yourself several different execution plans and you can keep seeing how those numbers change. In a perfect world, also do it in a different database that you can off-line and online again quickly so that those stats reset.
Tara Kizer: I was just going to say, even if this is the bookmark lookups, that doesn’t mean they’re a bad thing. I mean, it’s going to have bookmark lookups. You know, the fact that he’s saying bookmark lookups means he’s as old as us and has been using SQL Server for a very long time. So I catch myself and say bookmark lookups still, but these days, people call it key lookups. It doesn’t mean it’s a bad thing. You’re not going to be able to cover every single query, you know. Some of your queries need to return so many columns that you don’t want a non clustered index that covers the entire thing, so you have to do a key lookup at that point. So it’s not necessarily a bad thing. You have to examine the execution plans and look for the expensive key lookups. And if you want to improve those maybe consider covering index or look at Erik’s SELECT* information. You have a blog post on that. I know you have a presentation on it, but…
Erik Darling: Yeah, all sorts of things on that.
Tara Kizer: Yeah, that presentation will show you how to avoid the key lookup without a covering index. So it can do index intersection or do it later on so it’s not as expensive.
Brent Ozar: I would just like to point out that everyone sees that I have Chrome now. I have Google Chrome on my virtual machine [crosstalk]. Why, when I search for Erik Darling SELECT* I get Erik Darling, Every Time I go to Town?
Erik Darling: Oh yeah, there’s a folk singer named Erik Darling. He’s dead.
Richie Rump: Lies, it’s just you.
Erik Darling: If I had a burgeoning folk career, I wouldn’t be here.
Tara Kizer: I like when you say Darling though, you have to say it in Rob Farley’s voice…
Erik Darling: Very specific about that. Where is Rob Farley? I feel like I haven’t seen him do anything online in a while, like hiding.
Brent Ozar: Rob’s going to be the only one who gets this joke, but Rob is in Australia. Rob, ha ha ha – he’s probably not even going to hear that.
My ASYNC_NETWORK_IO goes up during heavy load
Brent Ozar: Pablo asks, “Ola amigos, in some times of day when I have high concurrency, async network I/O goes up and users feel like everything is slow. How can I help or what should I check?”
Tara Kizer: And then he says no application code available, because that’s an important thing because I love seeing that wait stat because it means the problem is not in SQL Server. I get to step away as a production DBA. A sysadmin jack of all trades type person is going to have to troubleshoot this, but as a SQL Server DBA, I’m like, oh the problem’s not mine. So as far as I know, there’s three things that can impact it.
It’s almost always an application problem. It can be an application server issue; if you don’t have enough memory or CPU, the application server could be struggling. And then the third one is a network issue, but it’s almost never a network issue. It’s usually an application involved in that row by row processing while SQL Server is trying to fire-hose the data to the application. So, unfortunately, it is not – there’s nothing to do inside SQL Server; you’re going to need to look external.
Erik Darling: Unless you’re just shoveling way too many rows at the application. So you know, if your user has a dashboard where they’re looking at the top 500 things and you’re shoveling like a million rows in there, you could probably page that query or just cut it down a little bit in some other way to stop shoveling that many rows at the app server. But generally, Tara’s right. Some noodle-head just wrote a for each loop to like present things or some weird presentation layer stuff that goes on once the data hits the application that just isn’t processing things as fast as you’d want.
Tara Kizer: I did see a recent server that the queries were sending over something like 50 million rows each time, but the application was then applying paging to it, so it would then filter it down to like 50 rows or something like that. So then it did a filter and the application got the data. I was like, this isn’t the right way to do things.
Richie Rump: [crosstalk] back in 2003…
Tara Kizer: And we didn’t even have 50 million rows back then.
Richie Rump: That’s right.
Erik Darling: In 2003 there were like five, 10, 15,000 rows at most; it was like nothing then.
Brent Ozar: I think one clue too might be when you say it’s really busy you get this. I would go remote desktop into your app servers and they might be doing 100% CPU, swapping to disk, no memory left. We’ve had a couple of those where we go remote desktop into the app server and it’s got two cores and 4GB of RAM and the thing’s just falling over.
Richie Rump: Exactly.
Brent Ozar: Erik’s phone.
Richie Rump: A phone has more.
Erik’s humor isn’t half bad
Brent Ozar: Marci says, “Richie’s comment reminds me that she’s really enjoying Erik’s sense of humor in the recent blog post. They remind her of the early Brent Ozar posts. They make you think, did he really just say that?”
Tara Kizer: Would that be the Jeremiah stuff?
Richie Rump: Let’s see, it wouldn’t be Brent.
Brent Ozar: It’s been a long time since I said anything offensive. I’ve got to dig back up to that.
Tara Kizer: Erik’s got a wicked sense of humor; a smart sense of humor, but wicked.
Richie Rump: I’ve amended your statement, Brent; it’s been a long time since you’ve said something offensive publicly, how’s that?
Brent Ozar: Touché.
Tara Kizer: We do have fun in that company chat room.
Brent Ozar: We do. This is probably the most fun part of our jobs is just we have a company chat room, which is why I stopped spending so much time on Twitter, because it’s so much more fun now in Slack because you can be as offensive as you want to be, throw GIFs in there, which is a horrible thing for a company owner to say. “I can be as offensive as I want in our company chat room.” It’s probably words that I’m going to regret and come back to haunt me later, but yeah, amusing.
Richie Rump: And now we have them recorded; thanks, Brent.
Brent Ozar: Yes, and especially with my wife being the human resources department and she’s way more offensive than I am…
Tara Kizer: She swore he’s not HR by the way; swears. You’re the closest person.
Brent Ozar: If she ever got hit by a car and I had to handle payroll and insurance, you would know just how good of a human resources person she is. Can you guys use 50s to pay for your surgery?
Erik Darling: By the way, I’m going to get 10 surgeries; a lot of stuff going on.
Brent Ozar: Please get a receipt this time.
Why would I want to use a contained database?
Brent Ozar: Ron asks a question that makes me want to be offensive. Ron says, “Why would I want to use a contained database?”
Tara Kizer: That’s a good question; I’ve never used them.
Brent Ozar: And the silence tells you everything. So the idea was that you would be able to deploy a database with all things like agent jobs and logins in it and then you could, like, vMotion it around the way you move a server around in virtualization with Hyper-V or VMware. That ship just sailed though. Microsoft totally missed that trend and we were just so much better off using virtualization for that, so it just died on the fire.
Richie Rump: Well that and cloud, right, so they also tried to move that idea into the cloud, where you deploy the entire database in the cloud and then everyone kind or realized that that’s kind of a silly idea.
Brent Ozar: And there are things that weren’t in the contained database; they all wanted agent jobs, they all wanted all kinds of stuff. Ron says, “I was asked that by a client and that was my response also.” Yeah…
Erik Darling: I guess if I wanted to do that – so what’s the behavior in a contained database that you’re looking for, that you’re looking to emulate, you know, like what do you want to get out of it? Or did you just hear the word and think, oh I want a contained database? Because that happens to people sometimes, like oh yeah, I’ve heard of that, I should do that.
Richie Rump: [crosstalk] database not to be contained, I mean, what if it gets out? I don’t know.
Erik Darling: Put a leash on that thing, man, give it a name.
How do I migrate from 2012 to 2016?
Brent Ozar: Renuka Devi asks, “Please tell us the steps to follow during a SQL Server migration from 2012 to 2016.”
Erik Darling: That will be $7,995…
Brent Ozar: And there’s no decimal point in there.
Erik Darling: There’s a comma.
Tara Kizer: Exactly, not a decimal point.
Brent Ozar: So why is it that when someone asks us to sketch out a new build or a new infrastructure for moving from 2012 to 2016, why can’t we do it in a paragraph?
Erik Darling: You know, it’s one of those things, when you like think about the high-level stuff, you come up with so many, like, just dead on showstoppers for things you can’t do, won’t do, ways you want to do it. And then when you get further into the details of, like, things you have going on in one place and like getting all that stuff moved over, you know, it’s easy to think about the easy stuff, like logins, agent jobs and that stuff. But then you get into using a new cardinality estimator or anything like that and this is just a lot more testing and planning than you would ever want to get into in a single paragraph, or you could ever do justice to in like a single paragraph.
Why won’t you call me back?
Brent Ozar: Guillermo says, “I sent a question through the contact us page at brentozar.com regarding the training season pass about two weeks ago but haven’t seen a reply. What’s the estimated reply time?” I haven’t seen that. If you want, just shoot us another email. There’s also an email address on the page; try that. maybe something went wrong with the web form. And you can also ask us here too, if you like.
Erik Darling: You know, a lot of people send in questions and mistype their email address and we go to reply and just get a bounce back. There’s like no way for us to like fix the email address. It’s not ever often, like, @gamal.com; it’s always just like some name butchering we can’t fix…
Brent Ozar: 42 characters in it.
Erik Darling: Yeah, exactly.
Does THREADPOOL mean I need to add more cores?
Brent Ozar: Pablo says, “If I don’t have any long-running blocking situations, can THREADPOOL be related to the need to add more CPUs?”
Richie Rump: Threadpool or Deadpool?
Brent Ozar: Which reminds me, and I shouldn’t change the question, but are you going to see Deadpool 2 when it comes out or are you going to wait a while?
Richie Rump: It comes out tomorrow. I’m going to see if I can talk my wife into going on our anniversary since we saw Deadpool on Valentine’s Day.
Erik Darling: Nice.
Richie Rump: She’ll be into it, don’t worry.
Brent Ozar: Wow, happy anniversary in advance; congratulations. I mean, one year is a long time to make it. It’s impressive…
Richie Rump: Or 19.
Brent Ozar: It’s no time at all. So THREADPOOL – if you’re facing THREADPOOL waits, do you need to add more CPU?
Erik Darling: Perhaps.
Tara Kizer: You could.
Erik Darling: I would want to take a look at a few other things first before I go and spend those licensing bucks on SQL Server stuff, like do I have MAXDOP and cost threshold for parallelism set right? Have I tuned my queries and indexes so that only the really necessary ones are actually going parallel and doing parallel things because all those parallel queries, they’ll go out and ask for, like, a whole – well ask for dot threads per parallel branch of their execution. So it’s like, they could ask for a whole lot more threads than just, like, you know, DOP.
Everyone’s under the impression that, you know, you set MAXDOP and that’s the number of cores that SQL Server can use and that will also cap the number of threads, but there is no such cap on that number of threads with MAXDOP. MAXDOP will use a whole mess of stuff. So that’s really important to keep in mind, especially if you’re – what sucks to me, like when I think about max worker threads and MAXDOP and all those other things, is when your max worker threads between one and four cores is 512. There’s no change.
So SQL Server thinks that for a single core and four cores, the best thing you can do is 512. And then after that, it doesn’t double. Like, you go up to eight cores, you don’t get 1024, you go up to 16, you don’t get 2048. Like, it just goes up by tiny increments. So like, you don’t even get that many more worker threads per CPU increment, so early on, you’re much better off tuning the server, tuning settings, tuning queries and indexes to keep only necessary things from going parallel from the get-go.
Brent Ozar: There’s a great post there, What Not to do when Adding Always On Availability Groups Databases by Warwick Rudd. And he goes into more detail with that with the little chart. While you were talking, you reminded me too of this question over here from John Smith Senior about SQL Server threads and degrees of parallelism – or was it this one? I could have sworn there was a Paul White one…
Erik Darling: Yeah, there was a Paul White on somewhere on there.
Brent Ozar: Hang on, I’ve got to find that – alright, cool.
Should I use auto-tiering for TempDB?
Brent Ozar: Alright, next up, we have – Daryl says, “We use auto-tiering on our SAN with a few different RAID levels, but I like RAID 10 for our log files as well as tempdb. I’ve noticed an increase in disk I/O after is set up tempdb with four files, but our processes run in the same timeframe. With tempdb, should I auto-tier it so that it moves around SSD?” My thought on auto-tiering, I adore auto-tiering; I’m a huge fan of it because the problem is, you don’t know which servers are really going to be active. You don’t know which servers are going to have a really hot and heavy tempdb, which ones are going to be sitting around bored and it changes all the time. The more SQL Servers that you have, the harder it is to predict that stuff. So I’m all about stuff like auto-tiering.
I know that the more admins that you have, the more people that you have, the more tempting it becomes to micromanage it. Like, I know that this one should be on RAID 10 and I know – because maybe you have a server that’s really busy and it’s worthless. For example, when I was a DBA, there was one server that just sucked at tempdb, just absolutely hammered it, but it was our website tracking software. Every time employees would go surf the web, it would log stuff into the database.
And we’re all like, we don’t care what performance is for that because it’s a backend server that nobody cares about, so we can just relegate that to the crappiest storage.
Erik Darling: You don’t have one of those, do you? Because I let some other people use my computer sometimes – I don’t know what they do so I’m just…
Brent Ozar: I share my hotspot on the street…
Erik Darling: Yeah, it’s weird, I don’t know. It’s like a whole communal thing out there. I just wanted to double check.
Brent Ozar: Yeah, I have a policy around our employee internet – don’t ask and don’t smell. I just don’t want to know. It was funny, when I first got Erika a cell phone way back in the 90s, late 99, early 2000, I got her a cell phone as a Christmas present and she’s like, you just want to track what I’m doing. And I’m like, no seriously, I don’t care. You can do anything you want. It took probably six months before she realized it and she’s like, you’re not mad at me for anything I’ve texted? And I’m like, I don’t know. I don’t want to look at the phone bill, I don’t care; so then she got into it.
We have a long-running SELECT showing sleeping…
Brent Ozar: Joe asks, “We have a long-running SELECT, over three hours, that has an estimated subtree cost of 0.003 and it’s stuck in the sleeping state. It’s going to keep running until we kill it. How should I resolve that?”
Erik Darling: I would love to see the query plan for that?
Tara Kizer: the query plan, what is it waiting on?
Brent Ozar: And, well if you say suspended, I’m curious.
Erik Darling: It says it’s sleeping, so I wonder if…
Brent Ozar: Should be able to do a BEGIN TRAN… I was going to say, or explicit transactions. So I’m going to use Stack Overflow…
Richie Rump: Is there a narcolepsy wait type? I don’t know.
Erik Darling: Tell that query to stop smoking weed, man.
Brent Ozar: Boom, so I got that SELECT running. Now while that’s running, I’m going to go pop over and run sp_whoisactive in another window once that thing finishes. So sp_whoisactive – so I have a query here it shows as sleeping, and it really doesn’t even matter what the cost of the query is, but once that query is sleeping, it’s just because somebody either restarted a transaction or they have an implicit transaction open. It’s not doing anything. It might be holding locks open.
To check to see if they’d like begun a transaction and held locks, you can do get locks equals one and then this is going to return a little property bag, a little XML property bag here. You can click on it and then it’ll show you if they have any tables locked. Odds are they just had a BEGIN TRAN somewhere and didn’t commit anything, but it’s just SELECTs. If it looks like this, where it’s just a schema level lock on the entire database, it’s fine, you can kill it. If they have a whole bunch of objects involved, then that’s where I’d get a little nervous.
Richie Rump: Brent just showed his age by using the term property bag.
Brent Ozar: How old is that too? It’s probably…
Richie Rump: I believe that was like VB – when I first started using VB and VB3, that was there.
Brent Ozar: Alright, well that’s all the questions we’ve got for this week. Thanks, everybody for hanging out with us this week at Office Hours and we will see y’all next week…
Erik Darling: Adios.
1 Comment. Leave new
Some third party backup tools (which shall remain nameless) still can’t back up in-memory databases…