This week, Erik and Richie discuss monitoring tools, finding all unused tables across databases, query tuning, deleting vs hanging on to indexes, sharding databases, query editors, aggressively-locked indexes, why a plan would not be in the plan cache, and Richie’s current housing situation.
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 Webcast – 2018-08-01
Erik Darling: Let’s see – [Shoab] asks, “In your experience, what is the best third-party tool for monitoring query performance, tracking slow queries, and is also user-friendly?” Well, [Shoab], assuming you’re not talking about something for free – if you want something for free, our First Responder Kit has a Power BI dashboard where you can go in, it will log a bunch of stuff to tables, it will show you wait stats and stuff. Power BI, not necessarily the most user-friendly thing in the world – it’s probably not going to give you all the slices and dices and reports that you want, which is shocking for a tool that’s called Power BI.
If you’re comfortable with spending around $1000-$1500 per monitor instance, SentryOne Performance Advisor is a perfectly good third-party monitoring tool. Quest Spotlight is also another perfectly good monitoring tool. I would check those two out. They’re just about commensurate in, like, you know, dashboards and gidgets and gadgets. It all just comes down to which one you end up being more comfortable with, or whichever – you know, if you are comparing products, you can make the salespeople fight to the death, so whichever salesperson wins is usually the product you go with.
Erik Darling: Boy oh boy, there are some long questions in here – I’m trying to read… Rob asks, “I’m trying to upgrade from 2008 R2 to 2017; having problems with queries using a linked server.” Boy, howdy… Post that one on dba.stackexchange.com. There is no easy way to troubleshoot something like that from here. You’re going to want to include all sorts of linked server details and login details and stuff like that. I’m sorry, Rob, that is past the limit of what I can troubleshoot quickly via webcast not looking at your screen.
Richie Rump: I understand people who have to use linked server, but I’ve really never been a big fan of using linked server. I’ve always been more of a fan of, hey I’m just going to shove the data over to another server and work from it from there.
Erik Darling: But you know, normally I would be like, cool just use SSIS. But then I’m thinking, man, upgrading SSIS packages and all the other stuff, maybe that linked server doesn’t sound so bad.
Richie Rump: Yeah, well to an SSIS guy, they’d be all excited for that kind of stuff…
Erik Darling: yeah, that’s a good source of consulting hours.
Richie Rump: Yeah, a DBA and a dev would be, like, SSIS? No, please no. it works. Don’t touch it.
Erik Darling: When I type in SSIS, nothing comes up in the thing; how do I do it?
Richie Rump: I’m just glad I don’t have to do that anymore.
Erik Darling: Let’s see, [Nika] asks, “We have 2008 R2. Is there a simple way to find all unused tables across databases?” Yeah, all those scripts that do that are kind of liars. So we have sp_BlitzIndex, and sp_BlitzIndex has a parameter called GetAllDatabases. And GetAllDatabases will go and look, just like it sounds like, at all of your databases and it will go and look at indexes and diagnose stuff. The trouble is that index metadata isn’t the most reliable thing in the world. So figuring out if a table is used or unused isn’t really as simple as, like, when was the last use or seek or scan, because that metadata could have gotten knocked out for some reason. It might not be the kind of long-term thing – it might not be long-term enough to have detected some use.
One thing that we’ve run across many times is, like, we’ll be looking at BlitzIndex and be, like, man, we have this completely unused index. This server has been up for three weeks. No one’s touched this thing ever. This thing must stink. But then, it’s attached to some quarterly report or some monthly report that only gets touched once in a while, but when we need it, we really need it. So if you think that you have unused tables across your databases, just start changing the names of them and if anyone complains or if queries start failing then change them back. Just kidding…
Richie Rump: Millions of people can’t access your website anymore…
Erik Darling: No problem, just sp_rename.
Richie Rump: Please call Erik…
Erik Darling: No, don’t call me. But there’s really no great way to tell that and there’s really no simple script to be able to tell that. The only way you’re going to be able to figure that out is really profiling the application and figuring out who uses what. So, unfortunately, no, there’s really not a good way to do that.
Erik Darling: Let’s see here. You can tell there are some humdingers in the list. “On SQL Server 2014 Standard, I have a query that is not utilizing seeks. I am getting an excessive memory grant warning. What is going on? Why can’t I seek on any of my clustered indexes? This is a simple query; no distinct, just joins left outer.” Eric, I know that you spell your name wrong because it’s with a C, but let me tell you, I have no magic crystal ball to peer into to tell you how you have done something ridiculous with your query that is disallowing seeks.
A lot of the times, if you’re just joining tables together, unless you get a nested loops join – like if you’re using a hash join or a merge join – and you don’t have a predicate on that table, a lot of the time, you will just see an index scan because it makes a whole lot more sense to go and read through the data, scan through it, get all the rows and then figure out which ones you’re keeping or getting rid of at the join operation. So don’t be all hung up on seeks versus scans. Scans are not your enemy. You want to be careful of the bad kinds of scans. And the bad kinds of scans come from awful things like non-SARGable predicates and functions and joins in where clauses; stuff like that. also, index key order will matter; so if you do have some predicates in there, the ability for an optimizer to seek into an index is determined by key order.
So there’s a lot of potential issues; a lot of things that could potentially lead to the fact that you are not seeing seeks in your scan. But I don’t immediately look at an index scan and say, oh my god, the world is coming to an end. It’s a bit of a knee-jerk reaction.
Richie Rump: The world turned upside down.
Erik Darling: Things just seemingly spinning out of control…
Erik Darling: Sheila asks, “If an index only has a value less than 10 in user updates and zero seeks or scans, is it worth keeping?” Sheila, I don’t think you have enough information about that index to make a decision about if it should live or die. I think that you should hang onto that index for a little while longer and see what happens with it. A lot of people want to jump to get rid of indexes. I just don’t know how long your server’s been up for. I don’t know, like, what your application looks like. There could be a lot of outside factors that would make having that index around useful; like if it’s a unique index maybe or if it’s a filtered index or if it offers the optimizer – if it’s like on a foreign key. There are a lot of reasons why we keep indexes around even if they’re not getting used a whole lot. So don’t just go and get rid of that index willy-nilly.
Richie Rump: yeah, and you know, the other thing to think about is, if I get rid of the index, what is that going to buy me? Do I have disk issues? Do I have a shortage of memory? And in that case, maybe I should get more disk. Maybe I should get more memory. Having an index is not a bad thing. If it’s duplicate, then yeah, get rid of one of them. But if it’s a small table or even a medium sized table and there’s an index there, ask yourself, what am I getting if I actually delete it?
Erik Darling: You know, a lot of the times, people want to get rid of indexes because I have this gigantic index that never gets used and it’s just taking up a bunch of space when I back it up and I restore it, when I run CHECKDB. Or, you know, if a table is just, like, plain over-indexed and there’s just a bajillion indexes on there and it’s time to start consolidating, chopping some of them off – because having way too many indexes can obviously lead to bad locking problems. You have a whole bunch more copies of that data that you have to lock when you want to modify it, but it doesn’t sound like it’s being written to a whole lot.
And from the amount of time that you’ve been observing it, it doesn’t sound like the index has been used. So I would just keep a closer eye on it. I would probably want to trend that usage over, like, a month or three and just kind of see what happens. also be careful because, you know, rebuilding indexes, adding and dropping indexes on the same table can also reset the stats for how much that index gets used. So don’t just jump to conclusions on that.
Erik Darling: Let’s see here, “Database sharding – is anything coming to SQL Server to shard databases with?” I don’t think so. I mean, you could use merge replication, if you’re an awful, awful person, but no.
Richie Rump: Yeah, I don’t – I haven’t heard anything.
Erik Darling: I would ask, why do you want to shard your database? What problem are you having that you think sharding might solve?
Richie Rump: Sharding your SQL Server database…
Erik Darling: Yes, your SQL Server database. Other databases might already by sharded. What’s the word for it? Shardified?
Richie Rump: I’m not even going to pretend because there’s a lot of jokes that could have come from that one. Like S3 – I mean, it is a database, so you put files in there and it’s up in the cloud and whatnot and you can actually shard it by, I think, the first couple of characters, and that’s how it determines where it decides to store things. So if you have a lot of data in a bucket in S3, what you would need to do is verify those first three characters so that they all go to different places so that you have uniformity in your data. So those types of things are built into more cloudy type stuff as opposed to stuff like SQL Server.
Erik Darling: Like document databases – you see that a lot there. [Oracle does – it pays too much money to shard so…]
Erik Darling: Steve asks, “I’m looking for a query editor that will only allow select statements, no data or object editing at all. I need to give it to a person that shouldn’t be able to edit data or objects directly, but because of their SQL permissions for a third-party application, SSMS and similar tools will allow them to write and run delete, update…” Wow-wee, that’s a humdinger.
Richie Rump: What about permissions? I mean…
Erik Darling: Well he’s saying that because of their SQL permissions for a third-party application, SSMS and similar tools will allow them to run delete, update, and insert queries, along with drop and create. Boy…
Richie Rump: Yeah, there’s – I mean, query editors, all they do is run SQL, so they don’t filter out what kind of SQL you can run and whatnot. They may do some nice things as far as syntax highlighting and other stuff, but they don’t – when they push stuff to the database, there’s usually not a filter for that kind of stuff. So I don’t know of one that would do anything like that. I would remove the third party permission account from this user or make him a DBA. And if something goes wrong, he gets a phone call in the middle of the night.
Erik Darling: It’s a little unfair to ask you to support someone’s ill-conceived permissions in SQL Server. So what I would say is, fine, this person has the ability to do this. If they do any of these things, I am not fixing it. It is up to them to fix it, because that’s messed up. I would just draw that line. I would make a moat around that.
Richie Rump: I would bet you, that user has a title of CEO or something, or C-level…
Erik Darling: Analyst – always the analyst; always.
Richie Rump: But yeah, they have the user ID for the third-party app password; I’m not down with that. My developer bones are jiggling. No, that’s not for you; that’s for the app.
Erik Darling: I would really want to ask why we can’t give them a separate read-only login just to do this stuff and then have them, you know, have the app do its other stuff, like its login with the appropriate permissions. Someone would have a really hard time justifying that setup to me without me, like, starting to throw things.
Richie Rump: yeah, in fact, this week I created a role for Brent and Erik that’s just read-only. I called it neutered admins. All they could do is just read-only and that’s all you could do. Sorry, you’re neutered. He doesn’t play with other puppies anymore. Sorry, Erik.
Erik Darling: Ah, feels good. Actually, in a way, I’m grateful to have that kind of restraint. I’m happy that I can’t accidentally mess anything up. I’m thrilled. I can’t make that mistake. I can’t update or delete that thing. I can insert some things sometimes to very specific places where it’s not going to break anything, but it’s not on me. Richie, in his wonderful responsible foresight, has taken away my ability to do more damage than I should be able to do and I am grateful to have that shock-collar.
Richie Rump: I mean, when I was consulting fulltime, I absolutely told them, I do not want permissions to prod. You can give me read-only, but I will not take any account that has permissions for prod. That is not my job. My job is to write code, development. Give me full access to development. I will fight for that, but anything else, I will not accept that login. I will not open that email. I don’t want anything to do with it.
Erik Darling: Nope, I’m deleting that thing on sight. I have a special thing that checks email for the word SA, for that letter combination; just no. it destroys it.
Erik Darling: Let’s see here – Joe says, “BlitzIndex says aggressive index on PK. Row locks and waits are very high; lots of contention. Many key lookups, no missing indexes, should I tune indexes to eliminate key lookups? Boy, oh boy. Yes and no. When BlitzIndex warns about aggressively locked indexes, it’s basically saying that queries are waiting a long time to get locks on that index. So other locks are being held on that index for a long period of time. There are a lot of reasons for that. Usually, it’s that your modification queries, anything that wants to, you know, update or delete or insert, does not have a really good index to find its way to the data needs. This is particularly true of updates.
I see this a lot. Updates and deletes mostly – inserts, not as much because an insert, you’re just kind of putting stuff in. I’ll talk about that in a second.. But usually, for updates and deletes, there’s usually a where clause and that where clause needs to get supported, just like a where clause for any other query. So most of the time, when I see aggressively locked indexes, it’s for one of two reasons. It doesn’t sound like the first reason’s going to be for you because it doesn’t sound like you have a bunch of other non-clustered indexes on there that are also getting locked. It’s just a primary key, which seems to me like this table is under-indexed for other queries.
So rather than focus on key lookups, which are a totally okay thing to tune for if you find them being problems, mostly what I would want to do is start looking at the modification queries that my app or whatever issues and I would want to start looking at the where clauses for those and making sure that they have a good path to the data they need to get so that locks get in and get out faster.
For inserts, updates, and deletes, batch size is very important. So if you have quite large tables and your queries are looking to modify quite large chunks of data at a time, that’s usually when we start to see stuff like – we’ll see lots of lock escalation or attempted lock escalation or queries that run for a long time because they’re waiting to get locks across all the huge chunks of data. So a fellow named Michael J Swart has – I’ll find the link and I’ll paste it in there because I’m sure it’s up in my do-da bar. Anyway, one thing that you could do to attempt to reduce aggressive locking is to not try to lock as much stuff at once. So if you bring your batch size down to like 1000, 5000 rows, somewhere in there, you usually have less aggressive locking going on behind it.
Erik Darling: Let’s see here. We’ve got one more and we’ll finish strong on this. Teschal says, “My proc cache is almost one year old. If the last execution time of a proc is NULL, is it safe to say the proc has never been called?” No; it’s never safe to say that because someone might recompile something. A plan for that thing could be invalidated and that plan could just appear from cache. It is not safe to say that in the least. This is one of those – again, like with the unused tables and indexes thing, this is not something that you can hit F5 on once and make a call on. This is something you have to profile over time, especially for something as ephemeral as the plan cache which is – wait a minute… Richie, are you thinking what I’m thinking?
Richie Rump: No, I never think what you’re thinking; it gets me in trouble with the wife.
Erik Darling: This server hasn’t been rebooted in a year. This server hasn’t been patched in a year. That sounds suspicious to me. So, Teschal, what I would say is patch your server because it sounds like this thing hasn’t had any patching love in quite a while. But beyond that, no. again, there are a lot of reasons why a plan might not be in the plan cache. If you hit F5 once and you want to drop a procedure or a table or an index, you have lost your mind. This is the kind of thing that you have to, you know, learn over time, profile your app, profile the queries that run in, try to make some determination on there. If you have folks who are spinning up things that are out of use or out of favor and don’t get used anymore then they have to be responsible for the change management procedure to get rid of those things. It is not something that someone should be trying to ascertain from looking at DMV diagnostic data because it can be terribly misleading and it can be terribly inaccurate sometimes too.
Richie Rump: Like me…
Erik Darling: Richie’s very misleading. He’s always wearing these short shorts to the company outings. I’m like, hey, how’s it going? And he’s like, don’t look at me.
Richie Rump: No, don’t.
Erik Darling: Alright, that’s all the time we have this week, folks. Thank you for joining us. We will see you next week. Brent will hopefully be back from his road trip to San Diego…
Richie Rump: Brent Ozar’s Big Adventure…
Erik Darling: Weekend At Brenty’s.
Richie Rump: He’s on a red bike right now traveling across the country.
Erik Darling: He’s got his peewee suit; it’s nice. He’s having a good time. Alright, take care, y’all, bye.
