SQL Server Performance Office Hours Episode 22

In retrospect, what was the best thing about SQL Server 2022? For me, it’s Query Store hints.

why does index cleanup replace unique constraints with unique indexes?

why table value parameters get a different estimate than table variavles?

why do you not care about logical reads?

I missed you at SQLBits this year. Will you be there next year?

Video Summary

In this video, I dive into answering five user-submitted questions during an office hours session, providing insights and solutions to common SQL Server challenges. We cover topics ranging from the best features of SQL Server 2022 to why unique constraints might be replaced with unique indexes in certain scenarios, as well as the differences between table value parameters and table variables when it comes to query estimates. Additionally, I explain my reasoning for not focusing on logical reads when identifying slow queries, emphasizing that duration and CPU usage are more telling indicators of performance issues. The session also includes a bit of personal reflection on upcoming SQL Server events and community summits, including Pass Data Community Summit in Seattle, where Kendra Little and I will be delivering T-SQL pre-cons. Whether you’re a seasoned DBA or just starting out, there’s something for everyone in this episode!

Full Transcript

Erik Darling here with Darling Data. Nice to see you too. Fancy meeting you here, all that good stuff. It is time for an office hours in which I answer five user submitted questions. I don’t know how many users actually submit these. It could all be one person. Or I don’t know. It could be five different people. Anyway, I hope that I answer your question this week so you don’t feel left out. But before we do, the usual the actual old song and dance, if you would like to support this channel, memberships are available. I have an unlimited supply of those. If you enjoy this content and you would just want to support my efforts to keep caring enough about doing it, you can sign up for a membership. Otherwise, you know, all the other stuff. If you want to ask me questions that I answer on these episodes, I have a slightly different URL up here now. This one, goes actually to my website rather than directly to the Google form because there is some additional information on the website about if you need to ask questions about code or execution plans and you need to share them. So I’ve changed the link there a bit and I suppose I’ll fix it in the YouTube videos as well. Or at least the ones that I, I don’t know. We’ll figure it out. Anyway, I’m available for consulting as well. I got an unlimited supply of that. Never seemed to run out of consulting, health checks, performance analysis, hands on tuning, dealing with performance emergencies, and of course, training your developers so that you don’t have any more performance emergencies. Good Lord, that’s quite a bit of service. And as always, my rates are reasonable. My performance tuning content, all of it 24 hours is available for 75% off, which means about 150 US dollars. You can you can of course go to that link and plug in that discount code to get the everything bundle over there. And if you want to pick up my new T-SQL course, which has all 23 ish or so hours of beginner content currently published, you can do that now for the pre-sale price of 250 bucks. That’ll be going up to 500 bucks after the summer once the advanced material lands.

And speaking of summer, boy, is it hot out. New York City, Dallas and Utrecht will all be graced with my presence over the summerish months with the Pass on Tour events. Redgate has decided that they’re going to smuggle me to various places to talk about SQL Server stuff. So that’ll be fun. Especially fun for you, I hope. And of course, Pass Data Community Summit will be in Seattle, November 17th to 21st, where Kendra Little and I are delivering not one, but two T-SQL pre-cons. So we’ll have a great lot of fun there. But with that out of the way, let’s do this whole office hours shindig. Let’s have some fun here. All right. Our first question. Let me, where is Zoomit? There you are. Where’s my little pink dot buddy? There we go. All right. In retrospect, what was the best thing about SQL Server 2022? For me, it’s query store hints.

I wish I had the same love and affection for query store hints and plan forcing. I suppose they’re great when they work, but it’s less fun when they suddenly stop working and you’re like, wait, what happened to the thing that I just told it to do? It worked for a while. Why is it not working now? And then you have to go do it again and kick plans out. It’s not fun. As far as SQL Server 2022 features, let’s see. I don’t know. That was 2019. No, well, that’s going to be in 2025. Gosh, you got me. SQL Server 2022. I suppose there were some decent linguistic improvements to window functions.

But like features, I don’t know. Let’s just let’s just throw it out there for for again. It’s cool when it works, but probably the parameter sensitive plan optimization is a nice, as they say, down payment on, you know, fixing quite a quite a pernicious issue in databases generally. So that’s that’s that’s about it there. 2022. Kind of a kind of a bummer. Kind of 2014 ish, kind of 2017 ish in that it’s it’s not very interesting generally 2025. I don’t know. All right. Here’s a good one. Why do index cleanup replace unique constraints with unique indexes?

Well, my friend, you’re you’re you’re referring to my store procedure SP underscore index cleanup. And the reason why it replaces unique constraints with unique indexes, which is only sometimes is if you have a unique constraint on, let’s say, column a to get, you know, real, real, real worldy there. And you have, let’s say, an either a unique or non unique nonclustered index on column a, maybe with other key columns or actually, no, not with other key columns. I lied. Other key columns would mess it all up on column a with like other included columns.

Then, then, then SP index cleanup will either make either either drop, give you a script to get rid of the unique constraint because unique constraints are backed by an index anyway. So if you already have a unique nonclustered index on that column with some includes, then like you don’t really need the unique constraint still. But if you have a non unique nonclustered index and what it’ll do is give you a script to make the non unique nonclustered index unique and also get rid of the unique constraint because it’s sort of a duplicative facility at that point.

So, um, like, you know, when, like, there is like, I guess a question that sometimes comes up, it’s like, oh, should I use unique constraints or unique indexes? And, um, you know, I, I do prefer the unique index because you have a bit more flexibility with the unique index than you do with just unique constraints as far as like included columns and like some other options go. So, so that’s, that’s, that’s about that there.

All right. Next up. Let’s see here. Um, got it. The same person, right? This one, uh, why table value parameters get a different estimate than table variables? Well, um, I’m going to guess you mean table variables.

Uh, so table value parameters are of course backed by, uh, table variables or rather presented to store procedures with table variables. Uh, but since they are presented to store procedures as parameters, uh, table valued parameters tend to get parameter sniffed the way that, uh, other parameters do. But, you know, it’s, it’s really only for like the table level cardinality.

So like you might find that you execute a store procedure with a table valued parameter. And when you pass it in with, let’s say like 10,000 rows, then you get a 10,000 row table cardinality, table level cardinality estimate from it. Um, and then it’ll keep that until you, you know, so recompilation occurs for whatever reason.

Uh, but then if you were to pass it in instead for first compilation with a table valued parameter that has a thousand rows in it, it would just use that thousand rows over and over again. So table valued parameters are a little bit different in that they tend to get sniffed like parameters rather than, um, you know, being treated like table variables, which, uh, the, the cardinality estimates that you get, get from those do depend a bit on, uh, version edition and, uh, database compatibility level among some other things. All right.

Hey, let’s look at this well, well, well-formed, well-structured question. Why do you not care about logical reads? Well, just because logical reads don’t tell me how long if a query was fast or slow. They don’t indicate that.

Uh, I want to find queries with performance problems. So I go looking for queries that, you know, uh, have a high duration and or a high CPU. Uh, so that’s, that’s it.

Logical reads don’t, don’t tell you if a query was slow or not. I want to find slow queries. So I find queries that use a lot of some mixture of wall clock or CPU time. I don’t know.

That, that, that seems, that seems fairly straightforward to me. Uh, anyway, let’s go on. Uh, oh, oh, how sweet are you? Hey, look at you. Someone, some lucky person.

Hey, wait a minute. Come on, zoom it. Uh, some lucky person out there got to go to SQLBits. I missed you at SQLBits this year. Will you be there next year? Another well-structured question. Good job out there.

Uh, I don’t know if I’ll be there next year. Uh, SQLBits has changed the way they do their pre-cons. And, uh, they, they are now curated. Uh, they curate the speakers.

I was not curated for this past SQLBits. So if you would like to see me curated, uh, for SQLBits, then you are, of course, you know, welcome to express that opinion to the SQLBits organizers.

I don’t know how much good it will do. I don’t know. I don’t know, uh, what their curation process is. But, um, I don’t know. Maybe, maybe there’s a cure for it.

Anyway, uh, that, that, that gets us through five questions here. Uh, they’re short ones this time around, I guess. It’s easy for me then. Uh, anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I will see you… …soon. Somewhere. Somehow. All right. 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.