This week, Brent, Erik, Tara, and Richie discuss whether you need to premake a destination database in order to run sp_databaserestore, restoring production statistics on dev servers to simulate productions behavior, dealing with duplicate SPNs, tools for detecting all SQL Servers in a production environment, SQL performance monitoring, dealing with very large indexes, and Brent’s recent cruise.
You can subscribe to our podcast to listen on the go.
Enjoy the Podcast?
Office Hours Webcast – 2019-1-2
Do I need to create a database before I restore one?
Brent Ozar: Mark asks, “Should I have a premade destination database in order to run sp_DatabaseRestore, or does it create one itself?”
Erik Darling: Well, I mean, the restore should create the database. If it’s not then you’ve got other things going on, perhaps a lack of permissions. If the database already exists and maybe you’re trying to overwrite it – I don’t know. That’s an odd one.
Brent Ozar: Or users have it open for a query maybe. In a perfect world, you don’t want to have the database already there when you go to restore.
Should I copy stats from prod to dev?
Brent Ozar: Pablo asks, “What do y’all think about restoring production statistics on development servers in order to try to simulate production’s behavior?” Have y’all ever moved statistics from one server to another?
Erik Darling: Yeah, but, like, as a joke.
Brent Ozar: What the hell kind of joke is that?
Erik Darling: Like I was messing with someone, I don’t know. No, I mean, I like the idea of it, but I mean, practically, I think you lose out on some aspects of being able to tune queries when you do that. You just don’t have the volume of data in there and you miss out on some of the metrics that might be making queries weird somewhere else or might be making them interesting somewhere else.
Richie Rump: Yeah, you also need developers to know what to do with that information. So they’re creating queries with the statistics that re for another system and they can’t figure out why everything is so slow and they may just go ahead and reset the stats or just rebuild indexes all over the place, and then you’ve got it out of whack a little bit. So they have to understand what statistics are and how you use them and how SQL Server uses them and go through all that. So if you’re willing to teach them about that then by all means, go and do that.
Erik Darling: Once you rebuild indexes, everything goes away.
Richie Rump: Yeah, I prefer creating queries and testing them on real data sizes if I’m doing any sort of perf type stuff. If I’m not, then, you know, we’ll wait until we get into the real thing and we’ll tune it from there.
Brent Ozar: I never understood it, the moving of statistics around, because all statistics are used for is for estimated plans. I mean, sure, they’re used for actual plans too, but all you’re going to be doing is comparing estimated plans. And if the estimates were right, you probably wouldn’t be in trouble to begin with. Usually, where your queries go to hell in a handbasket is where the estimates are wrong in plans and where stats aren’t helping you; non-SARGable queries, people doing LTRIM, RTRIM, stuff like that.
Erik Darling: Yeah, or if you’re dealing with spills or just some other weird problem…
Brent Ozar: Locking…
Erik Darling: Yeah, lots of stuff that, you know, only having the statistics won’t help you figure out, unless you’re truly troubleshooting a cardinality estimation issue, like purely that, then the value kind of dive-bombs.
Have you used Kerberos Config Manager?
Brent Ozar: Teschal asks, “Kerberos Config Manager tells me I have duplicate SPNs. Have y’all used the tool Kerberos Config Manager to get rid of the duplicates?”
Tara Kizer: I’ve never done that, but I’ve certainly dealt with the duplicate SPNs, and I just use the command line. I mean, it’s just simple to do; set SPN whatever it is.
Brent Ozar: I just have one bookmark set up for set SPN, and I always just end up going here, 2008…
Tara Kizer: Oh no, there goes your bookmark. Just set SPN/? in the command line. It will tell you what you need to do.
Brent Ozar: Damnit, I only had one. Oh no, there’s another one. Robert Davis’s kind of works. Yeah, so if you search for set SPN Robert Davis, that will get you there too.
How can I find SQL Servers in my environment?
Brent Ozar: Back over on the questions, Edwin asks, “What are the…” and I’m laughing not at Edwin’s question. I’m laughing at my one bookmark is trash now. Edwin says, “What are the tools or T-SQL scripts that I can use to detect all SQL Servers in my production environment?”
Erik Darling: Microsoft has that Discovery tool. That’s the only one – I know there used to be PowerShell scripts out there that would go and search the network and catalog stuff, but I couldn’t tell you the name of them or who wrote them.
Richie Rump: The one that Kendal wrote. I forget the name of it.
Erik Darling: Oh yeah, power something…
Brent Ozar: Power Doc on Codeplex, which, of course, is dead.
Erik Darling: When’s the last time that thing got updated?
Brent Ozar: You know, he did like a year ago. A year ago, he updated something in here, but I don’t know if it was that he moved it to GitHub or not. There’s somebody else who moved it to – but yeah, SQL Power Doc is another one. Quest had Discovery Wizard. I don’t know if they still do or not.
Richie Rump: So if the software is completed, why do we need to update it?
Erik Darling: Software is never completed, Richie.
Brent Ozar: the developer confuses me there for a second. I’m like…
What functions are involved in performance monitoring?
Brent Ozar: Ronnie says, “Can you give me a brief overview of the functions involved in SQL Server performance monitoring? What data or information am I analyzing to determine if SQL Server is performing better today than it was yesterday or last week?” That’s a good question. How do you know if your SQL Server’s better or worse?
Erik Darling: The way it smells…
Brent Ozar: Describe what I high-performance SQL Server smells like when things are bad.
Erik Darling: Burning dust. What I usually do is lick a thumb and put it up against the CPU fans. And if it feels like it was blowing harder than last week, I know something is a problem. I’ve probably been running queries in the application instead of SSMS.
Brent Ozar: Makes sense. When we talk about a SQL Server, we say it really sucks or it blows, that’s the thing that we’re talking about there.
Erik Darling: Absolutely. That’s when I know it’s time to ask Brent for more RAM.
Brent Ozar: Alright, Tara, do you have a better answer?
Tara Kizer: I don’t know. I mean, for me, a lot of it’s based upon users. If no one’s contacting me then I know that performance is either fine or tolerable. And when they are contacting me, it’s because it’s bad. My three past jobs always had expensive monitoring tools in place that made it easy to take a look at that stuff. There’s a dashboard, you can see it, and bells and whistles all over the place.
Brent Ozar: Yes, and if you were going to go pick a monitoring tool, what are some of our favorite monitoring tools out there?
Erik Darling: I like the old Sentry One. That’s a nice one. It’s got a nice dashboard. I like that you can highlight sections of the graph and zoom into what was running then or what other stuff was going on. So, nice little things that help you correlate things that are actually happening on the server. I find that a lot monitoring tools have disparate information, so it’s really hard to put the puzzle pieces together. It’s like, there are waits and there are queries and there’s a graph and there’s some plaid pants. And you’re like, I don’t really know which one to go with.
Brent Ozar: Like, I think the same way when you talk about functions; what are the monitoring tools supposed to do for me? I want to be able to isolate units of time. Show me what it was like at 8am Tuesday. Now, what was it like at 8am Tuesday three weeks ago? And it shouldn’t just be based on clock time or day of weeks. Sometimes it’s based on business processes. What was the close of month last time? Or you have bursty loads that happen at different times, depending on what was going on, or you want to see what was going on. I think the whole functionality of time replay is huge.
Erik Darling: You know, if you’re just peeking at wait stats as things are happening, you’re going to lose all the granularity that monitoring tools collect for you. They just aggregate and aggregate and aggregate. They’re not per database. They’re not, like, for any window of time. And especially for bursty workloads, servers can look really, really bored when they just have two or three busy hours a day or four to six busy hours a day, because it just kind of blurs out over time. It just kind of smoothes that line out.
Should a 32GB table have a 26GB index?
Brent Ozar: Mike asks, “I’ve found a 26GB index on a 32GB table. This index has 28 includes and two of them are varchar max. This smells bad to me. Where do I start?”
Erik Darling: Drop index – do you need me to spell one of those for you? So that smells to me like it either has DTA in the name, or it’s going to be name of missing index sysname. I would put a guess on one of those. That sounds like one of the missing index request things that comes in where you’re just like, I should never add that. But not everyone realizes that there are downsides to creating. That being said, if there’s one other index on the table and it’s that big, screw it. If it’s the clustered index and then that, I would just leave it alone.
Brent Ozar: the other thing you could look at is could you just create the index without all the includes? Just create it with just the key fields and if it’s being used a lot, then that can end up helping you there. Mike says, “Or crazy developers.” I think, kind of by definition, you have to be crazy to be a developer, or development drives you crazy because there’s so much debugging work involved. Holy moly…
Tara Kizer: Like Erik said, it smells of being a missing index, maybe for an entity framework type query where, by default, they just return all columns, and that’s what you’re going to get from a missing index.
Brent Ozar: Yeah, and, folks…
Erik Darling: The moral equivalent of select star.
Brent Ozar: And, folks, that’s all the technical questions y’all have this week. So y’all are off to a slow start. I’ll give you another minute or two to see if you want to enter in any other technical questions, otherwise, we will disappear off into eating our Christmas leftovers. What did y’all do for Christmas? Did you do home food? Did you go out somewhere?
Tara Kizer: I hosted Christmas Eve for 20 people or so…
Erik Darling: What? You know 20 people?
Tara Kizer: I have a big family. We’re Catholic…
Brent Ozar: Damn, that’s what happens when you get a new house. You have enough space, everybody’s like…
Tara Kizer: Yeah, and Christmas day was at my sister’s.
Richie Rump: That doesn’t sound like Christmas. That sounds like Nochebuena. Where do you come from? I don’t know. Okay, well come on in.
Brent Ozar: Well thanks, everybody, for hanging out with us this week. Adios!