SQL Server Performance Office Hours Episode 6
| We have an ERP system, the code of which we do not have access to. The system causes locks of DB. We are currently using 2019. Can you give advice on how to avoid these locks? The least we want is to be able to read the data at the moments of locking. Thank you! |
| Hi Erik! I have a problem with indexing and was wondering if you could point me in the right direction to get started. There’s a relatively old database that’s been around since 2011ish that I’ve inherited and there’s two transaction tables that are heavily over indexed (25+ indexes). It’s gotten to the point where the indexes on the tables are 200Gb (across both tables) vs 50Gb of data. There’s a lot of very specific covering indexes that are rather large. I’d like to reduce the number of indexes but there’s so much data flying around on production it’s very hard to simulate on Dev. Creating a new index can take 20 minutes, where do I even start? Kind regards, Nick |
| Do you know of any issues using WAITFOR DELAY ’00:00:01′ in a tight loop. And perhaps having a handful of them at the same time on a the same server. Never mind what happens in the loop. I got that covered. |
| How do I tell if I already asked my stupid question? |
| Columnstore maintenance on 2022, what thresholds do you use and what maintenance do you run? Niko’s blogs are ancient now. |
To ask your questions, head over here.
Video Summary
In this video, I dive into some common SQL Server challenges and provide practical advice on how to address them. We tackle issues like avoiding locks in an ERP system by exploring options such as read-committed snapshot isolation or snapshot isolation at the database level. For those dealing with overly indexed tables, I offer a step-by-step approach using SP_BlitzIndex to identify unused indexes and merge overlapping ones, ensuring that any necessary index changes are carefully managed. Additionally, we discuss potential pitfalls of using `WAITFOR DELAY` in tight loops and how to mitigate CPU usage issues. Lastly, I share insights on columnstore index maintenance for SQL Server 2022, emphasizing the importance of row group size and compression efficiency over traditional fragmentation checks. Whether you’re a seasoned DBA or just starting out, this session is packed with valuable tips and tricks to help optimize your database performance.
Full Transcript
Erik Darling here with Darling Data. And it’s time for Office Hours. My favorite. Alright, if you like my channel and me and this stuff and you want to sign up to support the channel with money, you can do that for as few as $4 a month. If you don’t, I get it. You can like, you can comment, you could subscribe, and you can be nice enough to ask me to do that. If you have any questions here on Office Hours, like, what do you spend four pre-tax dollars on in New York City? That would be a good question to ask. If you would like some real help with SQL Server, so I’m not asking anonymous questions that get answered publicly, you can pay me to consult on your SQL Server. I will do that. I will humbly, happily do that at a reasonable rate. We can do all of these things and more. That’s my job. I do not pay for that. I do not pay rent with YouTube. It has not reached that level of income stream yet. At this rate, I think I would need roughly 994,000 more subscribers in order to make that a reality at the subscriber to membership signup ratio.
So, perhaps someday. If you would like to get your hands on my training content, I have all of it available at that link. And if you use that coupon code, you will get it for 75% off, meaning just about $150 US for the whole kit and caboodle. Lucky you. That lasts the rest of your life. You only need to buy it once. It’s wonderful. If you would like to see me live and in person, dressed up like a lunch lady, smoking cigarettes, flipping flapjacks, I will be at SQL Saturday, New York City, 2025 on May the 10th.
Of course, there is a performance tuning pre-con with Andreas Walter on May the 9th. Full day deal that costs money to show up to. But I’ll be there, too, organizing lunch meats and sloppy joes and American chop suey and whatever other delicacies from your youth you remember fondly from the lunchroom. But with that out of the way, let’s answer some office hours questions.
And boy, do we have some doozies in here today. All right. Let’s see. One, two, three, four, five. Okay. We have the prerequisite number. We have reached the cost threshold for office hours.
So, let’s begin. Whoa. Zoom it. It’s getting a little sloppy on me here. We have an ERP system, the code of which we do not have access to.
Very typical. The system causes locks of DB. Also quite typical. We are currently using 2019. Can you give advice on how to avoid these locks?
The least we want is to be able to read the data at the moments of locking. Thank you. Well, gosh. There are a few things you can do to make your life a little bit easier in this regard. If the locks are happening because there are long-running modification queries, you could look at adding in indexes that help those long-running modification queries run faster.
That would be less locking overall. But more likely, what you are going to want to do is… Well, you do have two options.
How far you want to pursue these options does depend on vendor supportability and other stuff. If you want all the queries to not get blocked by writes, you could turn on read-committed snapshot isolation at the database level, and any read query that comes in and needs to write data would read versions of rows without having to worry about getting blocked.
It’s not dirty reads, of course. Optimistic isolation levels in SQL Server explicitly disallow dirty reads. You are just reading the version of the row prior to that modification query, doing anything with it and completing.
I have lots of videos about that. If you have any questions about it, I would highly recommend the Everything You Know About Isolation Levels is Wrong playlist, which will walk through all of that.
If vendor supportability for that sort of thing is lacking, in other words, if they say, if you turn that setting on, we can’t support you anymore, what you could do is use a setting called just snapshot isolation, not RCSI read-committed snapshot isolation, just SI snapshot isolation.
The difference is that RCSI applies to every read query that enters the database that doesn’t have any more granular locking hints on it, whereas snapshot isolation only applies to queries that ask for it.
So if you have queries that are, like you have added to the workload, let’s say, like you have some custom store procedures that do stuff, or you have custom code that reports on stuff, you could just say for your code only, set transaction isolation level allows snapshot, and you would be the only queries using those versioned rows.
Everyone else, every other query that goes in and hits the database would be subject to the normal rules of either read-committed the default pessimistic locking isolation level, where no row versioning is involved, or whatever locking hints the query supplies up to and including no lock.
So that would be how I would go there. Now we have a long one. Oh, and we have, oh boy, I mean, the crop must not have, let’s anonymize that a little bit.
We don’t need, we don’t need that kind of PII spilling out in office hours here. Hi, Eric. Hi, whoever you are. I have a problem with indexing.
I was wondering if you could point me in the right direction to get started. There’s a relatively old database. It’s been around since 2011 that I’ve inherited, and there’s two transaction tables that are heavily over-indexed, 25 plus indexes.
It’s gotten to the point where the indexes on the tables are 200 gigs. That’s not very much. It’s got to be a huge across both tables, and 50 gigs, versus 50 gigs of data. There’s a lot of very specific covering indexes that are rather large.
I’d like to reduce the number of indexes, but there’s so much data flying around on production, it’s very hard to simulate on dev. Creating a new index can take 20 minutes. Where do I even start?
Well, not on dev, because dev is not going to be a real, dev is not where you’re going to get any useful information for analysis from. I like the SP Blitz Index store procedure for a lot of reasons.
You can point it at these two tables, and you can see what the indexes are on there, and you can see what their definitions are, and you can see what their usage metrics are.
Now, where I would start is by looking for any unused indexes. If there are any of those, I would start by disabling those, not dropping them, just disabling them, because you want to make sure that any index that you get rid of is still maintained in the database metadata in case you need it back in a hurry, unless you’re very good with, you know, the scripting process where you would create, you know, the set of, like, you know, change scripts, and then a set of rollback scripts, so that you could, like, recreate or rebuild indexes, if you find out that you actually did need that index, then I would just start by disabling them.
The second thing that I would do would be to look for overlapping indexes, and that would be indexes where the key columns are either an exact match, so, like, column ABC, or, like, two indexes where the key columns are like columns ABC, and I would start with those, and then look at the includes, and see if the includes need to be merged in together, because the order of include columns in your index definition doesn’t matter, the order of key columns does, and then I would, if, you know, of course pay attention to if those indexes have anything special about them, like uniqueness, or a where clause perhaps, and, you know, factoring in what exactly, you know, you would have to do to come up with, like, like one index to replace multiple duplicative indexes.
The second thing, the second thing you would look at for the duplicative indexes are ones that are sort of superset subset indexes, where, let’s say, you have one index on columns ABC, with some includes, and maybe an index that had, like, only has key columns on columns AB, with no includes, or maybe, like, the same includes, or maybe slightly different includes, merge the includes in, and just keep the, keep the wider index, and get rid of the narrower index, just because when you’re making this first set of changes, it’s often a lot easier to keep the wider index, that’s more useful to more queries, than keeping a narrow index, and hoping that SQL Server still maybe chooses it out of the kindness of its heart.
So that’s where I would start. As far as creating a new index taking 20 minutes goes, I’m not sure where to begin helping you with that one. I would do the index cleanup before I started trying to add new stuff in.
Granted, the index cleanup can involve merging indexes, but it’s up between, it’s between you and your bosses to find a maintenance window for that.
That is, that is not something I can help you negotiate. All right. Next question. Do you have, know of any issues with using wait for delay one millisecond in a tight loop, and perhaps having a handful of them at the same time on the same server?
Hmm. Well, into my country. Never mind what happens in the loop, I got that covered. Well, you know, what happens between loops stays between loops.
But my, my one time messing up with the wait for delay thing was during the, the development of the now deprecated first responder kit store procedure SP all night log, where one of the facets of that store procedure was to like check for databases that needed to be backed up on one end or databases that needed to be restored on another end.
And my initial thing in a wait, it was, I think, I can’t remember if I, if, if I didn’t have a wait for delay of one millisecond in there, or if I had a very short wait for delay of one millisecond in there.
But, um, basically the end result was one CPU spinning at like a hundred percent over and over and over again, while that while loop just kept checking for stuff and kept looking for stuff to do.
And that apparently wasn’t, wasn’t great. Um, so if, if you would like to avoid, uh, a handful of CPUs constantly spinning at a hundred percent, um, or spiking, I don’t know if they’re going to spin at a hundred percent for what you’re asking them to do.
They spun at a hundred percent for what I was asking them to do just to like, look for stuff, uh, look for work to do, then that would maybe not be great. So I would perhaps look at a CPU graph on the server and see if the handful of while, wait for while loops, uh, it, it matches the number of CPUs that are constantly at some high level of utilization and maybe start thinking about giving that wait for delay a little bit more breathing room.
Uh, I don’t, again, I don’t, you got the loops covered, so I can’t give you any advice on how often you should be checking for a change based on, uh, what that loop is intended to do.
But, um, that is, that is what I’ve run into with it. All right. Question number four. How do I tell if I already asked my stupid question? Well, I would have already given you a stupid answer.
That’s an easy one. We got that out of the way pretty quickly. Uh, columnstore maintenance on 2022. What thresholds do you use and what maintenance do you run? Nico’s blogs are ancient now.
Uh, I haven’t really changed much, uh, in this. Um, Nico’s stuff is still, as far as I can tell, the best out there. All the scripts do not take much actual columnstore specific stuff into account. And the Tiger Team stuff is, well, I don’t think anyone actually still works on that either.
Um, you would think maybe Nico, who went to Microsoft, would offer them some help on their, uh, index maintenance stuff in the columnstore realm. Um, maybe he told them stuff to do when someone else did it, but I don’t, I don’t think those still get worked on, uh, really, if, ever, if, if at all.
Um, I, I still find, um, you know, the, the columnstore specific stuff that Nico wrote into his scripts, uh, like, just as applicable today. Um, you know, like, you know, columnstore maintenance is a lot different from rowstore maintenance.
Uh, this is not to answer your question directly. This is just for the other folks out there, uh, where, you know, uh, regular index maintenance, which typically looks for logical fragmentation is a big old waste of time.
Um, if you wanted to make a case for going out and looking for physical fragmentation of rowstore indexes, I would perhaps be a little bit more germane to your arguments for like looking for, you know, indexes that are twice as large as they need to be because there’s a lot of empty space on data pages, but columnstore indexes have a sort of different set of, um, issues.
And like fragmentation isn’t it for, for, for, for, for columnstore indexes either. Um, columnstore indexes, you have to care about row group size. And if row groups are compressed or not, uh, you have to care about like the ghost record tombstone type things.
And you have to care about how big the Delta store is. The Delta store is uncompressed row groups, right? Like that’s which, you know, if those get big enough, those can impact just how efficient your columnstore indexes are.
So those are the things you need to keep an eye on there. And I don’t think anything has changed about columnstore indexes that would make the threshold that Nico, Nico was talking about in his scripts, any less pertinent on SQL Server 2022.
Versus when he was writing them around like 2016, 20, I forget when he went to Microsoft and stopped, stopped existing as a blogger.
Um, I did see a post recently, which my least favorite, my least favorite kind of blog post, which is that I’m going to start blogging again, blog post, which, you know, is like the first post in three years.
And then the last post for another three years. So, um, at least for most people, who knows, maybe, maybe Nico will break the spell, but, uh, I don’t, I don’t really see, uh, a reason to do things any differently, uh, with columnstore because columnstore still has the same sets of issues, uh, for the performance of columnstore indexes, um, that you would, you would look at then that you would want to look at now.
So like, you know, you know, deleted rows, uncompressed rows, um, like row groups, like, like columns, like really small row groups and then some really big row groups. You would like want to get, try to get some uniformity in there if you can.
I think that, that’s, that sometimes helps, uh, things get a little bit better, but, um, yeah, that’s not, not really a lot to say on that. Unfortunately, um, yeah, I can’t really think of anything else on that.
Maybe, maybe I’ll think of something else later, but, for now that’s, that’s it. I might, maybe I’ll come back to this one if I think of something, but right now I get nothing. Anyway, uh, that is the end of these five questions for office hours.
Um, if I’m, if I’m looking at the queue now, I have, I’m up to four questions after this. So as soon as someone asks one more question, I’ll be able to do another one of these.
It should be very exciting for you. Right? Incredibly exciting. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in, uh, well, the next video, I hope about, about something else.
Maybe we’ll figure it out when we get there. I won’t we? Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.