SQL Server Performance Office Hours Episode 37

SQL Server Performance Office Hours Episode 37

---

* Does the order of INNER JOINs in a view impact how the queryoptimizer chooses how to build the execution-plan?

* Is there any performance gains when inserting into temp-tables to use the WITH (TABLOCK) hint? Thanks!

* Hey Erik! Is it data or data? I’ve heard some people saying data but I say data like you, so I think I’m right.

* 1. In our high-volume OLTP fintech environment on SQL 2017 EE, we have poor DB design leading to increasing deadlocks and blocking (readers blocking writers). As a short-term fix, we’ve set low deadlock priority on readers and high on critical writers, to avoid even more NOLOCK. We want to enable RCSI but management worries about safety, especially inaccurate results from in-flight version store data that might roll back. Previous DBA enabled snapshot isolation on a few DBs, but devs likely aren’t using it correctly or understand isolation levels. We have ample tempdb space (rarely used, as devs prefer table variables). How can I prove RCSI is safe? Dev env is limited—only some DBs match prod, with slower HDDs/CPU.

* 2. About 80% of our tables (millions to hundreds of millions of rows) have outdated indexes (5+ years old), despite daily dev work. Queries mostly from EF Core; servers reboot weekly (working to stop), losing index stats—but workload is consistent week-to-week. Biggest issue: slow upserts/deletes on tables with 50+ child tables (no cascading). I propose starting with narrow compressed indexes on FK relationships to avoid table scans. Tried sp_indexcleanup, but low uptime limits results beyond compression. Thoughts on this approach and overall indexing strategy?

To ask your questions, head over here.

Video Summary

In this video, I dive into some pressing SQL Server questions from viewers, covering topics like join order in views and performance considerations for parallel inserts. I also tackle the age-old debate over “data” pronunciation and delve into issues of deadlocks, NOLOCK usage, and snapshot isolation. Additionally, we explore strategies for optimizing upserts and deletes on large tables with many child relationships, discussing indexing foreign keys to avoid table scans. Whether you’re a seasoned SQL Server pro or just starting out, there’s plenty here to help improve your database management skills. If you found this content valuable, please like the video, subscribe to my channel, and share it with colleagues who might benefit from these insights.

Full Transcript

Erik Darling here with Darling Data, and you know as well as I do, it’s Monday, you know as well as I do that on Monday we do office hours where I answer five of your most pressing, I don’t know, impressive, depressive questions, I don’t know, whatever you have going on. And I don’t know, everyone’s happy. Everyone’s happy and everyone lived forever holding hands, right? Anyway, if you look down at this page, the video description, there are all sorts of useful links for you to click on and many of them will result in you giving me money. You can hire me for consulting, you can buy my training, you can become a supporting, and again the supporting with money member of the channel. You can ask me office hours questions, which of course free, you know, I don’t know, maybe I should charge like a dollar a question or something. Some of you ask a lot of, some of you ask a lot of questions and type in ways where I know it’s the same person, I’m like, ah, you owe me a buck. And of course, if you enjoy this content, please do like, subscribe and tell a friend.

I have some exciting conference announcements coming up, but of course I cannot announce them yet as they have not been announced publicly by the presenters. So you will just have to hold on to the very seat of your pants for those. But the one that I can talk about is past data community summit coming up in Seattle, November 17th to 21st, where Kendra Little and I have two days of the best T-SQL pre-cons you will ever see in your life. And with that out of the way, let’s go answer some questions. We will go to the magical Excel file.

And we’ll, we will do our best to answer these. And the first one is, does the order of inner joins in a view impact how the query optimizer chooses how to build the execution plan? So theoretically not, right? Because the optimizer is a cost-based situation and the cost, the costing model will look at your joins and it will, you know, assuming that you get past like, you know, into the right search phase of things. It will start reordering your joins to figure out what is the best way to do it. Where that kind of falls apart sometimes is that SQL Server will set a budget for the number of steps that it will take to optimize your query, which includes join reordering.

And you sometimes you’ll see what’s called an optimizer timeout. That timeout is not in time. It is in steps that it is budgeted based on the cost of your, the sort of like heuristic cost of your query based on complexity. And so SQL Server might not have time to reorder all of your joints. If you know the best way for your joints to be ordered, well, I mean, you’re in a, it’s in a view, so you can’t add a force order to that. But if you know the best way that you want your joints to be ordered, I don’t know, you could go ahead and write them in there.

And then if the optimizer does timeout, maybe it’ll just listen to you, but probably not. So in general, no, but there are situations where, you know, if you write them in some kind of order, the optimizer might timeout before it has time to reorder everything. But you’re still probably not going to see joins specifically written in the order you write them in, unless you have a force order hint on the query. I didn’t highlight that as I was, as I was reading it. I do apologize.

I’ve changed the width of my, my zoom it thing for various reasons. And, um, it’s a, it’s a bit, it’s a bit intrusive at times. Is there any performance gains when inserting into tent tables to use the width tab lock hint? Thanks. Yes. Um, depending on local factors, you might find that using a tab lock hint will result in a fully parallel insert in which the insert operator is within the parallel zone of the query plan.

If you don’t have a parallel query plan, uh, then that won’t happen anyway. There are also things that you can do that will mess that up, like having an identity column, having a clustered index, having a primary key, having any nonclustered indexes, um, referencing the table that you’re inserting into and like a not exists in the query. Uh, there are lots of things that will screw that up. Um, if you need something that behaves like an, an identity column and it’s important to you to have a fully parallel insert, just use row number instead.

No, no, no, no inhibitions on that one. So, uh, in general, yes, but also, um, the degree of parallelism can have a big impact on how fast things are. There are some queries where a higher parallelism will higher degree of parallelism rather will result in faster inserts and sometimes in slower inserts. Of course, the sweet spot is generally somewhere around eight, but you’re free to experiment with the max stop hint to see where that best aligns for you.

All right. This looks like a very important question here. Hey Eric, is it data or data? I’ve heard some people saying data, but I say data like you. So I think I’m right. Yeah. Sounds good to me. Good job. I don’t know any other way to pronounce it. So there’s, there’s some alternative pronunciation of data out there. Well, uh, it’s just, this used to be a proper country as they say. All right. In our high volume OLTP FinTech invite you work for FinTech and you’re asking me questions for free.

Oh, my God. The poor, starving consultant. Golly. I’m going to have to take a walk after this one. This is a paragraph.

My Lord. Why don’t you just hire a professional? Uh, we have poor DB design. Oh, gee. Is there a consultant around who could help with that? Leading to increasing deadlocks and blocking. Gosh, do you know anyone?

Is there a short term fix? Oh my Lord. We’ve set deadlock priority high on readers. Sorry. We can say I’m beside myself. We’ve set low deadlock priority on readers and high on critical writers. Why readers are always going to lose. They don’t take up, take up any transaction log.

Like it’s the reader is always going to be the victim. My goodness. To avoid even more knowledge. Oh, gee. Is anyone out there in the world good at talking people out of NOLOC? I don’t know.

The mind boggles. We’re truly puzzled. Is there a doctor in the house? Previous DBA enabled snapshot isolation on a few DBs, but devs likely aren’t… No kidding, they’re not. It’s getting real hard not to curse here.

Are likely aren’t using it correctly or understand isolation levels. We have ample temp DB space. Rarely used. Devs prefer table…

You! You are also wrong. Of course table variables use temp DB. What do you think they are? Magic? I have a million videos where I prove this point a zillion times. My goodness. Okay.

How can I prove RCSI is safe? Well, you’ve got snapshot isolation enabled for a few DBs. As you noted. Why not start having some queries there? Use snapshot isolation. Hmm. That’s a good one.

Only some… Oh, wait. Hold on. Is there… Is there more to this? I feel like there’s maybe more to this and I’m missing it.

Is there more? I feel like there’s something else going on here. No. All right. How do I prove RCSI? No, that’s the end of it. Yeah. Just… You could start by having some queries ask for snapshot isolation and use that.

That would be a good way. You know, that’s probably it there. Anyway. Okay. Yeah. Let’s…

Oh, you are… You… You… You numbered your questions. You… You… Oh, Lord. If I cry on camera…

This’ll be why. About 80% of our tables. Millions to hundreds of millions of rows. Gosh. Does anyone have experience dealing with data of that size?

I don’t know. Of outdated indexes. Well, how does an index get outdated? Does it have an expiration date? Does it go bad?

Despite daily dev work? Well, your developers, if they’re the ones from question number one, you should probably just drag them into an alley and shoot them. Oh, queries mostly from entity framework core. God bless.

Servers Ruby reboot weekly. Mother. Okay. Biggest issue. Slow upserts deletes on tables with 50 plus child tables. No cascading.

I propose starting with narrow compressed indexes on FK relationships to avoid table scans. Tried SP index cleanup, but low uptime limits results beyond compression. Yeah.

Um, indexing foreign keys is one of the, um, the least crazy things that you can do. At least in a database. Some databases are nice. And when you create a foreign key, they create an index for you. Indexing foreign key columns is not terribly controversial.

The thing to be explicit about here is that if you’ve already got an index that leads with the foreign key column or columns, there are some multi key foreign keys. We must include those.

Um, then you, you don’t need another index on that has that leads with that column or those columns. Um, so you don’t need specific index. You don’t like if you name an index FK, it doesn’t give like it magic powers.

The thing to be aware of is that the optimizer is still free to choose the execution plan for your foreign keys. Right? Like, like, like, uh, foreign key validation.

Like you could have all the indexes in the world. SQL Server could still choose a merger hash join with an index scan on the inner side of the join for the foreign key relationship. Sometimes you actually have to add a loop join hint.

Um, I’ve got a blog post about, um, sort of like, about this, but also about how sometimes triggers are, uh, are a more useful approach for this. Because you can control the execution plan of triggers in a somewhat easier way. But since you’ve already got all those foreign keys, you might, may as well, and just, may as well, as long as you don’t already have indexes that perfectly express that relationship, then, then you might want to try indexing them, sure, at your high volume OLTP fintech.

All right. Well, I’ve, I’ve got a bottle of four year old Will It Rye that is screaming my name right now. So we’re gonna go do that.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I’ll see you in the next video. Have a good one.

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.