Tag: Query Tuning

Clampdown

---

Darling Data was born too late for MySpace, Friendster, Facebook, and Make Out Club (MOC).

While I patiently wait for 4chan to make business accounts available, I figured I’d try out something new.

So, you can find bite (byte?) sized chunks of my videos on TikTok here: Darling Data

I’ve also hired my wife (clearly a nepo baby) as a social media intern to slice, dice, edit, caption, #hashtag, and add complimentary gifs to the clips.

If I’m not careful, she’ll be better at SQL Server than me by the time this goes to international media outlets. I would have hired her to write this press release, but she went to bed.

Since I’m clearly not good at these things, please do the TikTok version of liking and subscribing over there, fellow kids.

This post was brought to you by the Senior Executive Management Team at Darling Data, a subsidiary of Beer Gut Magazine, Inc.

Thanks for reading!

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.

A Little About RESOURCE_SEMAPHORE_QUERY_COMPILE Waits In SQL Server

---

Video Summary

In this video, I dive into a fascinating topic: resource semaphore query compile weights in SQL Server. I share real-world examples and demonstrate how these weights can cause significant issues, particularly when dealing with overly complex queries that take an excessive amount of memory to compile. By using tools like SP Quickie Store and spwhoisactive, I show you how to identify and mitigate these problems, emphasizing the importance of writing more manageable SQL queries to keep your database running smoothly.

Full Transcript

Erik Darling here with Darling Data. Recently voted by BeerGut Magazine Board of Editors to be the SQL Server Consultancy most likely to get sued by BeerGut Magazine. I’m not sure exactly what they meant by that. I’m not scared. I’m not scared of you, Joel. Sue me if you want. So, today’s video, we’re going to talk a little bit about resource semaphore query compile weights, because this is one that I’ve run into several times in recent consulting history, and it’s always fun to talk about because, I mean, it’s not happening to my server. It’s, you know, happening to someone else’s server. I just get to tell them why it’s happening, teach them what it’s happening, and I’m not sure what it’s happening. I just get to tell them how to fix it, and occasionally even if I’m really lucky, help them fix it, which is usually a process of breaking up those awful giant monolithic queries that developers insist on writing into smaller, more manageable little chunks.

So, I have in front of me, us, you, we, everyone, everyone who can see the screen, this query, which is a really stupid query. It is just a series of crossjoins from this tiny little post type table, which has like eight rows in it. The thing is, if you do crossjoin enough times, the way I have here, and I want everyone out there in developer land to have a newfound level of respect for me because I started my, my CTE numbering at zero.

Would also like to remark, these are probably the most readable CTE ever written. Perhaps that would be a nice award, Beargut Magazine. Perhaps, stop threatening me. Nice to you. So, the end result of this, and I’m going to show you the query plan for it, is a query that takes, well, I mean, first, let’s, let’s marvel at this estimated subtree cost.

Right? It doesn’t even fit on the screen when I zoom in. It doesn’t, I’m not sure it’ll even fit on the screen. I just have to scroll that one across.

If anyone wants to pause the video and count the number of zeros in there and figure out what this number would actually be, I’m sure there’s a national debt joke in there somewhere, right? Sure.

But the important, semi-important thing here, aside from that gargantuan estimated subtree cost, is that this query takes, well, takes about 740 megs of compiled memory. Which, I mean, you know, 740 megs, whatever, but that’s compiled memory.

That’s like, not a query memory grant. It’s not like a table that exists physically on the server. It’s just 740 megs to figure out what the hell to do with this query plan.

And it takes about 35 and a half seconds to compile. So, like, local factors involved, depending on a few things. This will take anywhere between, like, 25 and 45 seconds to compile a plan.

Again, if you fiddle enough with compatibility levels and cardinality estimation models, you might see something on the lower end. You might see something on the higher end.

So, let’s see. Okay. There are two things about SQL Server Management Studio that have been irking the bejesus out of me lately. One is random window switching positions.

I don’t know why that happens. And the other is sometimes, like, you ever, like, control and F to find something? And, like, you see a bunch of orange stuff on the sidebar highlight?

And then, like, if you, like, let’s say you’re in a store procedure and then you, like, hit F5 to change something in the store procedure. And it brings up the results, the thing where it’s just, like, query completed at whatever time. And then, like, you get rid of the results.

And then, like, the orange dots all move. And all of a sudden, like, where there are hits for things, it’s all wrong. It’s been making me feel like a crazy person. I don’t like it.

Anyway. Let’s make SQL Server. Let’s get revenge. Let’s make SQL Server feel like a crazy person. So what I’m going to do is show you a couple things about how you can spot this happening. And, well, I mean, we can talk a little bit about how to fix it.

But I think I already just told you. Stop raiding gigantic queries, you fools. So if we run these two queries while there is nothing going on, we will see that my server, based on the amount of memory that I have, has available to it 64 slots in the small query compile gateway, 16 slots in the medium query compile gateway, and one coveted spot in the big query compile gateway.

Since nothing is going on right now, nothing is very active. Well, I mean, for some reason, the small gateway is active. Probably some background tasks doing stuff, hanging out, maybe.

I don’t know. Maybe it’s active because something has to be active and nothing else is going on. I don’t know.

Some of these DMVs are just wacky and goofy, and I don’t wish to learn much more about them than I have to. So, based on these numbers, I don’t know why I threw that away.

Based on these numbers, I have 64 plus 16, 6 plus 4, 70, 80, plus 1, 81 total gateway slots for queries to compile in. Now, if you have a bunch of small, low-cost queries, you can compile, like, infinite numbers of those.

But as soon as queries require a little bit more effort and thought by the optimizer to compile a plan, they’re going to start flooding into these gateways. And as the optimizer, like, figures out how much stuff it’s going to put into compiling a plan for a query, they’re going to start traveling through these gateways until they, until, well, I mean, in this demo, they’re going to, one’s going to hit the big gateway.

The rest are going to get stuck in other smaller gateways. So, the way that we’re going to do that is by having a pink cursor, of course. And what we’re going to do is use SQL query stress, again, maintained by the lovely, talented Eric EJ.

And what we’re going to do is we’re going to run 100 and, I guess this should probably be 160. 161’s good enough. I guess with 81, it should be 162.

But, you know, you know what they say about off-by-one errors. We’re just going to roll with this because this will do a good enough job. We’re going to flood this server with requests. And we’re going to be connected into the remote DAC.

So we can see these things really start to have problems. All right. So this looks pretty stable now as far as the results go. So I’m going to hit cancel here.

Normally, I’d be game to let stuff keep running and, like, you know, show you what happens as, like, stuff finishes and moves. But you know what? I have to, I have to do a pre-con on Friday in Nashville.

And I don’t want to brick my laptop between now and then. So I’m going to be a little superstitious here and not push my luck. Just not pushing your luck is probably superstitious enough.

So anyway, what we’re going to see is up in this top result set where I’m interrogating the gateway DMV, we will see that all of these slots have become active and that all of these slots have accumulated a number of waiters. All these queries are waiting to compile a plan.

Right now, there’s only one query on my server that can compile anything. All right. And that’s this lucky. Oh, man.

It was almost number 13. Lucky query number 14. It’s like lucky number 11. All right. So this is the only one. I mean, again, I’m going to cross my fingers and hope that there’s only one null row in here and that every other row is showing resource semaphore query compile.

Ooh, la, la. What a beautiful demo that is. So only this one query is allowed in that big gateway. And if you have more memory available, you will have more gateways open to you.

I think once you get past like 128 gigs, you start getting more of them. I forget the exact number. I’m sure it’s documented somewhere very clearly and somewhere very easy to find.

Those Microsoft docs are just legendary for their goodness. Yeah. So one query can run and one query can do anything useful.

Now, again, you know, this is a demo query designed to do something terrible. In real life, your queries probably, I mean, they might also have nine CTE in them.

Who knows? All right. Y’all are crazy. But really, you would just be looking for like really big, complicated queries that SQL server would have to think particularly hard about when a plan is getting compiled for it. All right.

Now, if you’re using stored procedures or parameterized queries, generally, you only take a hit on this when they initially compile or if they recompile for some reason. That’s why I have the recompile hint on here because if this thing initially compiled a plan, other queries would just start using it and laughing at me.

Right. So like once you get past the compilation phase and a plan gets cached, other queries can be like, ah, gotcha. But in real life, this often doesn’t happen with usually just doesn’t happen with like one big, awful query. Doing stuff is usually like like a whole cavalry of awful queries just riding into your SQL Server and shooting the place up.

So one way that you can find queries that take a long time to compile is by using SP Quickie Store. And if you use it in expert mode, it will return additional result sets that have compilation metrics in them. And what I mean by that is you’ll have this top section up here and, you know, you might see there’s probably nothing in here that’s so eye catching, except, I mean, maybe the fact that we couldn’t render this this query plan is XML from the from the query store DMV.

I’ve been meaning to do something in here where like if I can’t render the plan, then I’ll like produce some query so you can get it as like just the raw XML as text. So you can save it off as a SQL plan and reopen it. Reopening query plan XML that’s too big to render as XML like in the database works just fine as opening a file.

I don’t know when I don’t know why Windows is so much better at it than SQL Server Management Studio. Who knows? Maybe it’s SQL Server. Maybe it’s Management Studio. It’s hard to tell. I mean, SQL Server has the error about 128 nested nodes of XML, so it’s probably SQL Server as usual.

I’m not going to let SQL Server off the hook. As much as I love to blame and get mad at Management Studio for things, SQL Server is no angel. But if we look here in the results, we’re going to have this section down here called Compilation Stats.

And just because I know the query that I care about, I’m going to look here and I’m going to say, well, you are query ID 14510. And this is ordered just by query ID ascending for some reason. I didn’t really put a lot of thought into the ordering here.

I should probably have some more useful metric and query ID be the ordering principle here. I’m not sure if I would make it total compiles or average compile stuff, but wow, what was 14509? Where are you in here?

Oh, you were the other. See, look at, there it is. There’s another instance of this query from when I was running it. Look at how prepared I was for this demo. But if we look at these two lines in the Quickie Store results for compilation stats, you will see one of these took about 30 seconds to compile. And another one took about 24 and a half seconds to compile.

So neither one of these from my brief tinkering hit the 45 second mark. But in the past they have. They’re just not in these results.

So sorry about that. I’m sure that’s totally disappointing. And you’re going to hate me forever. And you’re never going to forgive me. This injustice. This curse I put upon your land.

So yeah. What I’ve shown you today is sort of how you can figure out which queries are struggling with resource semaphore query compile. You generally want to look for things that took a long time to compile or stuff that if we come over here a bit, we’re going to see.

No, no, no. Where’s the compile memory line? There we go.

Some compile memory. Lots of compile memory in here. So you want to look for stuff that has high compile memory, probably high compile time. You can find that stuff in query store using spquickiestore.

You can see exactly how much you have available gateway-wise for compiling queries. And you can usually see queries that are stuck waiting for memory to compile by looking at spwhoisactive and seeing the resource semaphore query compile weight pile up through a whole bunch of queries. So if I have to leave you with anything, it’s don’t write queries like this.

Unless you need to show people resource semaphore query compile demos. But in real life, you shouldn’t be writing big, complex, monster queries. You should be kind to your SQL Server because it probably has a lot of other problems.

You don’t want to make them worse. Why pile everything on? It’s like making someone in AA quit smoking.

Why would you want to do that to someone? Giving up their only drill in life. Anyway, I’m going to…

I don’t know what I’m going to do. I suppose pack bags or something. I don’t really have a good game plan for tonight. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you like this video, I always appreciate a thumbs up. And if you like learning stuff about SQL Server, I would strongly suggest, before Beargut Magazine sues me into oblivion, subscribing to my channel so that you can get big, glaring, blaring notification every time I finish uploading one of these things.

And hopefully after it’s been processed up to HD, because you don’t want to watch these in low definition. You want to make sure that I am in vivid detail. Don’t let YouTube’s downscaling turn me into, like, Minecraft pixel art.

Turn it up to HD. Erik Darling in HD. It’s the way I was meant to be experienced.

Anyway, thank you for watching. This will probably be the last video I record this week. I’ve gone a bunch. And then next week, I’ll pick back up with some stuff for you, because I care about you.

I want you to know the awful things I know. Anyway, thank you for watching. Let’s do this. ification.

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.

Spring Training

---

This May, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of great content.

Get your tickets here for this event, taking place Friday, May 10th 2024 at the Microsoft Offices in Burlington.

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for this event!

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.

Odd Couple

---

I was quite publicly mystified by the coupling of Azure Data Studio into the SQL Server Management Studio installer. They’re different tools for different people.

This isn’t a tirade against Azure Data Studio, nor is it a victory lap since apparently the feedback item I posted was part of the decision to remove it.

This is purely informational, since the announcement that it’s not in there anymore is nearly as quiet as the announcement that it was being included back in SQL Server Management Studio 18.7, and only slightly louder than the availability of a command line switch to skip installing it.

Back when I initially complained about the inability to skip installing Azure Data Studio, there was a lot of talk about how SQL Server Management Studio would increasingly rely on it for new functionality. I suppose that was either untrue, or the roadmap changed significantly.

Quite cynically, I thought it was a cheap way to increase the install base of a new product, but who knows? I assume Microsoft has better telemetry about usage than binaries just existing. Again, who knows?

A further miffance was that you could download and install Azure Data Studio independently, but not SQL Server Management Studio.

If you read through Erin Stellato’s post about the SQL Server Management Studio roadmap, where version 20 was fully released:

For SSMS 20 we have removed Azure Data Studio from the installation.  In addition to being a highly voted item (Make ADS An Optional Install Alongside SSMS · Community (azure.com)), this aligns with the future extension work for SSMS 21 and we decided to make the change in the current release.

So, hooray! Dreams do come true, etc. When I was but a young boy, I dreamed that someday my desires would be aligned with future extension work for SSMS.

I’d also like to say here that being the public face of SQL Server Management Studio makes Erin Stellato about the bravest soul I know. It’s a tool that millions of people rely on, and nearly everyone actively complains about. Part of my RSS feed is not only posts from Microsoft about data platform related stuff, but also the comment feed. She has saintly patience in her responses to the comments on these things. Comments on the internet maintain their position and title as the worst things on earth. Erin deserves infinite credit for doing the job that she does as well as she does it.

If you’re keen on moving to SQL Server Management Studio 20, you can download it here.

If you’re still stuck using older versions because of a new bug or an old feature, you can skip installing Azure Data Studio by running the installer via the command prompt:

SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1

Thanks for reading!

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.

Join Algorithm Limitations In SQL Server

---

Video Summary

In this video, I delve into some interesting limitations of SQL Server’s join algorithms, particularly focusing on scenarios where the lack of an equality predicate can lead to unexpected errors or suboptimal query plans. I explore these issues through practical examples and demonstrate how implicit predicates work in nested loops joins. Additionally, I discuss a scenario involving right outer joins, which cannot be implemented as nested loops joins due to their nature, highlighting why SQL Server prefers left outer joins for such cases. By walking you through these examples, I aim to provide insights into optimizing queries and understanding the nuances of SQL Server’s query execution engine. Whether you’re working with stored procedures or dynamic SQL, this video offers valuable lessons on how to avoid common pitfalls and improve query performance.

Full Transcript

Erik Darling here with Darling Data. And I’m just now making the rather sad realization that I forgot to re-embiggen myself after recording the last video where I had to shrink myself down. So let’s figure out what an adequate portion of me is. Let’s move this around a little bit. Ooh, ooh, ooh, that’s too big. That’s a little too much me even for me. Let’s get that to a little bit of a bit. more reasonable place. I think this is pretty good. I just have to be really mindful about what I do with my head because when I do that, my head looks preposterously large, I think. I mean, my head is sort of preposterously large. Anyway, if you’ve ever seen me in real life, you know this, but the camera angle and sort of, I don’t know, I don’t know what to call it, depth of field thing just makes it so much worse. But anyway, I don’t know what to call it. Anyway, in today’s video, we’re going to talk about some joint algorithm limitations in SQL Server. These may and probably do exist in other databases. I don’t study other databases as deeply as I study SQL Server. So you’ll have to forgive me if I’m wrong about that. But I can’t imagine that it’s much different anywhere else unless there’s some other database out there that has some totally different joint algorithm that I’m just unaware of. So I’ve got a couple indexes that I created for most of the queries we’re going to be looking at. It’s, I mean, most of the plans are going to be getting our estimated plans, so they don’t matter too, too much. But a couple of them will be actual execution plans. And I don’t want to sit here waiting for those to show up. I don’t want to waste your time, mine, or look foolish.

The eyes of our overlords, whoever they may be. So, first, and this demo is going to play a part in why I’m showing you the next demo, too. But let’s get through this one first.

Oh, my good, good. Headphone hair in back, you’ll have to forgive me. Another reason not to put my head down too far, I suppose. All right, so this query, just get an estimated plan for this. SQL Server chooses a nested loops join plan.

Well, it doesn’t really choose it. It’s forced to use it. And it’s forced to use it because we don’t have an equality predicate. We don’t have column equals other column here. We have greater than equal to, less than equal to. Okay, so without an equality predicate, we cannot have a merge join, and we cannot have a hash join.

All right, so if we try to get an estimated plan for this, we will get an error. And SQL Server will say, query processor cannot produce a query plan because of the hints defined in this query. Please resubmit your query, and we’ll get back to you as soon as possible.

And the same thing will happen with a hash join hint. All right, so you do this, and look at it. It’s that same error. If you didn’t know you could edit query text in the messages tab, it makes it really easy to sort of frame the text a little bit better. If you’re taking screenshots or something, don’t be afraid to do that, so then you don’t have to send one of those, like, preposterously long, skinny screenshots, or, like, assemble your own screenshot later, and everyone’s going to think you, like, photoshopped it.

Like, wow, that’s not a real thing. Liar. It’s completely unrealistic. Bad demo writer. Bad DBA. Bad developer. Whatever. Whatever they may think of you. So, I think I’ve talked about this a little bit before, but stuff like this is often why, if you’re the type of person who has, like, calendar or date tables, or even number tables, and your database has utility tables, I’m not saying you shouldn’t, but when you join to those, you are often joining on, like, greater than, equal to, less than, equal to, and some arrangement there, and, like, if you’re joining, like, if you have, like, really big results set, and you ended up in, like, you end up with a nested loops join because you have to without an equality predicate, that can really drag a query down because SQL Server can only do a nested loops join, and if you get caught in a nested loops join, I don’t know if you’ve ever, like, done a cross join between, like, a small table and a big table, but those get stuck in nested loops hell, and the same thing can kind of happen here, right?

Because, like, especially if that nested loops join ends up in, like, the wrong side of the query plan, like, you know, somewhere where, like, you know, you end up doing, like, billions and billions of nested loops joins into a table because SQL Server has to, well, that can be a pretty bad time for performance.

I’m not saying not to use those tables. I’m just saying that just be careful where you use those tables in your queries. Sometimes you have to, like, get queries down to a smaller result set, like temp tables, and then join those. Other techniques can work, too, like, you know, cross-apply with a big top in it or something like that and can sort of tell SQL Server where you want things to happen a little bit better.

But moving on to, like, where the lack of an equality predicate can cause some sort of unexpected errors, I learned about this from a Craig Friedman blog post from, like, forever ago, but it still works today, but we’re going to talk a little bit more.

We’ll talk about some stuff. We’ll get there. Just stick with me. Hang on tight. Stick your hands deep into your pockets and try to grin and bear this information. So if we create this procedure, and the deal with this procedure is that we are getting a count from posts joined to itself where the ID in one side is the parent ID in the other side.

So we’re sort of just, like, counting, like, questions and answers, right? We’re associating the answers with the question. We’re joining from the question to the answer, whatever order that happens in. It doesn’t really matter much.

But if we create this, and then, sorry, before we go any lower, then we also have this where clause where p.parentID equals this parent ID parameter, which is set to this number.

And we have an option hash join hint down here. Now, if we try to run this, we’re going to get an error message. Very similar, actually identical to the error messages that we just saw, where a SQL Server is going to say, no plan for you.

No query. Can’t do it. You’re screwed. Now, the way to see why that happens is to run the query without the hash join hint in it. So let’s run this, and now let’s execute the store procedure, and let’s see what happens.

And that is, well, something kind of cool. So this isn’t necessarily the fault of, like, the query writer, and it’s not necessarily the fault of the hash join.

There’s a query optimization that kicks in. You can think of it as implied predicates. You can think of it that way because that’s what it’s called. The predicates are implied because our join columns have an equality predicate, and our where clause also has an equality predicate on a value.

So what happens in the query plan is SQL Server. Let’s zoom in on this a little bit. Let’s see if we can make sure that everything is going to work out all right here.

Let’s frame that up nicely. Let’s act professional for once in our lives. So up at the top, we have a clustered index something. I don’t know why SSMS chooses to cut this off.

It’s not like there’s anything. Oh, that’s from my, I mean, I understand why this is where my hand cuts off. Maybe I should make myself bigger so I can reach more places on the screen. I don’t lose fingers.

But we have a nonclustered index. This is going to be a seek. And I’ll show you that in a second. And then down here we have an index seek on my non-cl. I’ve never had my non-cl seeked into before, but rather nice, actually.

Rather pleasant. So we have a seek on both sides of the joiner. I’m going to zoom in on exactly what happens. We seek on the clustered index to where the ID column is equal to the parent ID parameter.

And that’s on the outer side of the join. On the inner side of the join, where we seek into a non-cl. That is, of course, going to be a seek predicate here on parent ID.

So this, again, implied predicate means that a predicate will apply to both sides of the join because we have a where clause column that matches one of our join condition columns. And it’s all equality predicates.

So when we look at the nested loops join, we actually don’t have any join predicate here. We just output the stuff for the count function. So the reason why the hash join hint throws an error is because we’ve removed the equality predicate from the join.

Rather, the optimizer has removed the equality predicate from the join, pushed it to either side of the join, and then basically just kind of brought back everything because all the rows would match. There’s nothing to actually filter out at the join.

The join can’t get rid of any rows. And SQL Server knows that because of the implied predicate. So it basically just assembles everything and shuffles it off. And that’s cool, but it can also lead to some sort of surprising errors sometimes if you add a hash join hint to a query, and that query starts failing with different parameter values because SQL Server implies the predicates.

So this can—it’s not just stored procedures with parameters. And also, like, you can get around this with recompile hints, but if you declare a variable and do the same thing, we will get the same query processor error.

It is worth noting, I think, that this doesn’t happen for some reason with a literal value. I don’t quite get why. SQL Server does a hash join here, and we still imply the predicate.

All right, it’s right there. You can see it down the bottom, 184, whatever. And we have it down here. So I don’t quite get that, but I’m willing to admit that I don’t quite get it. But this could happen with a literal value if you had forced parameterization enabled for your database.

For some reason—well, I mean, literal values skirt a lot of optimizer stuff, so maybe this is just another one to add to the list. So you’ve seen a couple examples now of limitations with join algorithms where there’s a lack of an equality predicate and even a sort of surprising one where there wasn’t a quality predicate, but the optimizer optimized it away.

Now, I want to talk about something a little bit more interesting because I wrote a post recently about joins. And in that post, I made fun of write joins a little bit, probably a little bit more than I should have.

I’m not actually that militaristic about write joins. I guess. I don’t know. I still think that they’re kind of stupid, but really what I do hate are Venn diagrams to explain joins.

That’s obnoxious. Stop doing it. So I have an optimizer compatibility level hint here just to not get adaptive joins because the adaptive joins just make what I’m trying to explain a little bit more confusing.

It’s really just want to show you the join type and whether it’s left outer or right outer. So I’ve got these two queries, one using a left join and one using a right join.

And when we run these, if you’ll note, SQL Server quite often thinks that right joins are goofy too. And SQL Server has rewritten both of these as left outer joins. Left outer joins.

SQL Server took our query, carefully crafted to use a right join and said, no, thank you. I’m going to do a left join instead. Okay.

Well, that’s interesting. Now, if we write our query and use this set of hints, so now I say loop join and do this, right? So we got hash joins before.

If we write this to use loop joins, SQL Server is going to do the same thing. SQL Server is going to say, right joins are stupid, right? Right join, nah. Left join.

But this is a little bit more of an optimizer limitation than just an optimizer being like, I hate right joins type thing. And I’m going to show you exactly what I mean.

So if I add in another hint here, so there’s a new hint in this query, say force order. All right. So I’m saying you write, you create this query plan in the order that I wrote it.

We can do that successfully with the left join. SQL Server says, no problem. Gotcha, pal. You’re good. If we try to do that with a right join, SQL Server is going to say, no.

We cannot write loop join this query. And if we write force order, then SQL Server is going to obey the order of the joins, but we’re going to go back to a hash join.

We do a right outer join, but it’s back to being a hash join. Now the reason why this is an optimizer, or rather an algorithm limitation, is because these are outer joins.

We need to return a value and another value, or like values and whatever values exist, or values and a bunch of nulls when they don’t exist.

And it gets, it’s a little bit easier to see when you visualize it like this. All right. So let’s run these two things.

And what I have here is the wonderful, lovely, talented generate series function. And this is counting from one to 20, skipping, like taking steps of two.

This is counting from one to 30 using steps of three. So if we look at the values that come back from here, right? And the first result set, we have one, three, five, seven, nine, 11, 13, 15, 17, 19, which is, again, counting to, well, would have counted past 20, but by limited to 20, but we get up to 19, right?

One, three, five. So we’re basically doing every third number. And the second one would, I mean, would have gone up to 30, but, you know, counting by, we’re skipping three. So we get one, four, seven, 10, 13, 16, 19, 22, 25, 28.

So if we were to run this as a left join between this result set and this result set, we would find, what is it? One, seven, 13, and 19.

And the second result set, right? And those would join, but then for three, five, nine, 11, 15, 17, we would get that back with a null, right?

So we would get value plus another value where they matched and then value and null where they don’t match. Now, the reason you can’t do this with a right join is because you wouldn’t know what rows exist. In the outer table, right?

Like, think about if we, if we did this as a right join, you would be like, oh, well, here’s one. Returning one in one. And then you say four. And then you say, well, there’s no fours here.

There is a three here. So I could, but like SQL Server doesn’t know to return three and null because it only has the values that are on the outer side of the join to start going to try to find. So right outer joins technically don’t work as a nested loops join because of the way the algorithm is implemented, right?

Like you, like you can’t possibly get the right results with the right outer loop join because you need to figure out if this matches and this matches and return this plus any matches or return this plus a null. It gets a little bit easier to see if you actually write the join out because what you get here is exactly what I’ve been describing.

When we find a match, we get a value and a value. But when we don’t have a match, we get a value and a null, right?

And we don’t know, like, like whatever values are in here that didn’t match. SQL Server is not going to be like, well, hang on, hang on, back it up. We didn’t have a four, but we had a three. So I get a return to three and a null.

It’s just not going to work well. There’s not like a good efficient way to do that with a right loop join. So right joins is a wise man. Once said, if you never want to see nested loops joins, write all your queries is right joins, but the optimizer might say beat it.

This is just kind of a funny limitation. I don’t expect anyone out there to actually try to avoid loop joins for their entire life. But there we have it.

Anyway, these are just some interesting join algorithm sort of limitations. I mean, they’re not even like bad limitations. They’re just things you should be aware of.

Like, a lot of people say limitation, like a lot of people infer or imply that limitations are bad things. But I think quite often limitations are good things because performance would not be great if we didn’t have some limits on the way some things are implemented.

Some of those limits keep the wheels on. This is, I think, a pretty good example of that.

There’s also, you know, the right outer loop join just couldn’t possibly give you correct results in a timely fashion. It just wouldn’t work. It doesn’t work because you take a row, you’ll look for a row, but if you start on the wrong side, you just, you’re going to miss values that you should be bringing back.

So, it could be a pretty bad time. You wouldn’t, you wouldn’t want any more incorrect results than you get with all your no lock hints anyway. So, that’s about it for this.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. If you like this video, give it the old thumbs up. If you like this kind of content, please subscribe to my channel so that when I publish videos, you can immediately be notified, drop everything, and, and, and watch them and, and learn more sequels because apparently that’s important.

Yeah. Databases, database, data science, all that stuff. Seems to be, seems to be pretty useful these days.

Seems to be pretty in demand. So, the more you know, the better off you are. Especially, you know, anyone can return correct results. If you can, if you can return correct results faster than everyone else, you’re in better shape than a lot of folks would be.

So, yeah. Learn your sequels. I guess. I should, I should learn my sequels too.

Anyway, I’m going to go, I don’t know, do something else fun and interesting now. Maybe I’ll record more. Maybe I won’t. We’ll see what happens. 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.

Spring Training

---

This May, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of great content.

Get your tickets here for this event, taking place Friday, May 10th 2024 at the Microsoft Offices in Burlington.

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for this event!

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.

Reviewing The New DACPAC Code Analysis Rules For T-SQL

---

You can read the announcement post here.

Video Summary

In this video, I dive into a detailed review of the rules in the SQL query stress tool maintained by Erik EJ, a dear friend and colleague. While I’m not as familiar with non-SQL code, I managed to navigate through some of the C#-related rules and offer my insights. We cover a wide range of topics, from ensuring proper primary keys and natural keys to avoiding unnecessary `SELECT *` queries and using explicit column lists in `INSERT` statements. Along the way, we discuss best practices like wrapping transactions, using `TRY-CATCH` blocks for error handling, and avoiding cursors unless absolutely necessary. I also touch on some less common rules such as the use of `NOLOCK`, which can be risky in a production environment, and the avoidance of certain functions in the `WHERE` clause that can hinder query optimization. By exploring these rules, we aim to improve our coding practices and ensure more efficient and reliable SQL queries.

Full Transcript

Erik Darling here with Darling Data. You have to forgive me. I’m very excited about this because it’s not often that I get to do these kind of reviews, so you’ll have to pardon my giddiness at this sort of thing. Anyway, he’s not a dear friend. There goes my hand. But I do hope someday I can call Erik EJ my dear friend. You’ll probably hear me talk about him on this channel in the context of the SQL query stress tool that, you know, Adam Mechanic originally made and that Erik now, not this Erik, this Erik up there, the smart-looking one up there in the corner, now maintains. But you posed a really interesting thing earlier, and it’s a project where when you build a DAC pack, there’s this code analysis that will run on there, and I was checking out some of the rules for it. If you follow this link, you’ll eventually get over to this link. And there’s some very interesting stuff in there.

I’m actually going to shrink myself for this video a little bit to make sure it stays, make sure that we can see as much of the screen as possible. So there I go, down into the corner. I’m going to be much smaller than usual in this one, which for some of you just might be a relief. Might be a welcome relief to have a much smaller Erik in this video. I wish I had a balloon. I would have taken a little hit of helium when I shrunk myself. But we’re going to talk through some of the rules. I’m going to talk through some of my thoughts on some of the rules.

And hopefully we’ll all end up in a better place. So let’s start off at the top there. Missing natural key. So I should probably say at the beginning of this that I don’t read non-SQL code too well.

But if you’re the type of person who does, and you can read, I believe it’s all C-sharp stuff, since it ends in .cs, you can read through what a lot of these rules do. I’m going to sort of stumble blindly through just some of the descriptions. And if I’m wrong about anything, you can just ignore me. I promise. I’m okay with being wrong when it comes to this stuff.

Because, you know, you’ve got to be wrong sometime in your life. So first one, missing natural key. I’m not really sure how you could add a natural key to a table. You may have intentionally denormalized a table. You may be using entity attribute value tables for things.

I’m not really sure how the detection works on that. So we’re going to move on. The next one up is table missing primary key. You know, most OLTP systems, you want that. If it’s a data warehouse, who cares?

If you’re the type of person who has primary keys and foreign keys and unique constraints and stuff in a data warehouse, may we never meet would be unfriendly. Avoid wide primary keys. Yeah, I’m cool. I’m generally cool with that.

Primary keys should avoid using GUIDs or wide VARCAR columns. Sure. Yeah, yeah. It’s not a very good choice, admittedly. The one thing I’ll say in favor of GUIDs, though, is it’s really hard to run out of them.

It’s very easy to run out of integers. I’ve seen that a lot of times. These days, I think, if you’re just not sure, make your primary key. Make that identity column a big int. You’ll have a hard time going wrong there.

If I hear anyone from the peanut gallery talk about storage size and how big ints are 8 bytes and ints are 4 bytes, you can bite me. It’s a wrong thing to concentrate on. Maybe if you were on crappy old spinning disks on a 32-bit system, then…

Well, I mean, you wouldn’t have big ints on a 32-bit system, would you? Anyway, keep going. Index on foreign key columns on both sides of a foreign key should be indexed.

Hell yeah, they should. Avoid long car types. Yeah, absolutely. That’s a dumb thing to do. I’d say that most of the time, you should avoid car and car types anyway.

There’s nothing wrong with bar car and bar car anyway. Let’s see. Avoid using select star. So, there are a couple places where this won’t matter.

And I’m not sure how… Again, me not being able to read. Me no read code good.

I’m not sure how in-depth the rule checks are. But if you do select star, like, in an exists clause like this, or if you do select star in, like, a cross-apply or, like, a derived join or something, but then you name the columns outside of that, it’s not, like, using select star for real.

It’s, like, you know, it doesn’t matter for the exists, and for here you actually pick the columns at some point. So, this is one place if the rule doesn’t go deeper.

It might want to. I’m not… I’m terribly, terribly inexperienced writing these types of linters. I don’t know how hard that would be, but…

You know, something to consider anyway. Let’s see. Non-transactional body.

Wrap multiple action statements within a transaction. Sure, I would say… It would even go a step further. I think there should be a check to see if set exact abort is set to on in a stored procedure, because if you’re going to put multiple action statements into a transaction, you probably don’t want to leave any of those hanging if the transaction fails and not have all of them rolled back.

So, that’s probably something you want to think about there. Low identity seed value. Start identity columns used in a primary key with a seed of 1,000 or higher. I have no idea why that’s a thing.

To be very honest, you have no clue why that’s a check. Equality compare with null rule. Equality and inequality comparisons involving a null constant found.

Use is null or is not null. You’re going to have a tough time if you use an equality predicate on null. You’re going to have a bad day.

Let’s see what else we got here. Unused variable. Variable declared but never referenced or assigned. To be honest with you, I’m relieved every time I see a variable get declared and then never used. Usually when that happens, they get used in a where clause.

And I think that should actually be a check. If you’re using local variables in a where clause, that should be in the performance check there. Let’s see.

Unexpected or expected error handling. Wrap multiple action statements within a try catch. Hell yeah, I’m on board with that. Top without order by. Top without order by, you guarantee nothing. Unless you wrote your query very, very carefully to only guarantee one row can ever match for something.

Yeah, you’re going to have a bad time if you write top without an order by. I’ll even go a step further and say if you write a top with an order by, but that column in the order by is not unique, you could also have a hell of a time, especially if your query goes parallel. Let’s see.

What did we get here? Implicit column list. Unused. Always use a column list with insert statements. Hell yes, you should. Don’t be lazy. Unused input parameter. I don’t think I care too much about that, but you know, clean up after yourself, I guess. Avoid deletes without where rule.

And then we have an unbounded update. So kind of two ways of saying the same thing. A delete statement without role limiting conditions and update statement without role limiting conditions. So again, I can’t read the code.

I’m not that smart. But there are a couple ways that you can write delete and update queries that do bound things that might look unbounded if you just focus on what’s underneath the delete or what’s underneath the update. So these would certainly bound any update or delete happening, but it might just seem unbounded if it’s just looking specifically for a where clause.

Redgate SQL prompt does this too, where like if you don’t have an explicit where clause on a modification query, it’ll freak out at you. You might have a join. You might, you know, have whatever going on there.

With the Redgate SQL prompt, you can actually fool it because you can actually put where one equals one. And just because it sees the word where after an update or delete or something, it’ll be like, oh, cool. You got, you got it.

Good job. You can just put some condition that always evaluates the true. And it’s like, oh, well, smart. Did it. You’re never going to delete everything in the table. You’re never going to update everything in the table because where one equals one, good enough for me.

Anyway, let’s get back to the list. Let’s get back to dark mode. SSMS is very bright, especially when I’m this little in the corner. So let’s see.

What else we got here? Avoid joining tables with views. I mean, I agree you should avoid it. Sometimes it is unavoidable. Sometimes it is inevitable. But, yeah, views are often quite deceptive in how bad and how deep.

How awful they are. Now, it’s not that, like, the view itself, right? Like, views as a construct, you can put totally fast queries in a view.

The problem is that people often hide really bad queries in views. So the worse the stuff you put in the view, the worse going into that view gets. And views often end up turning into sort of like a query results junk drawer where you just keep adding left joins and columns and more craziness to it.

And eventually life just spirals out of control. So, you know, avoid joining tables with views. I’m not going to say that’s wrong.

I’m just going to say that, you know, it’s really what lies beneath in the view that is bad rather than just the concept of a view itself. It also, it could be an indexed view, right?

Indexed views are usually pretty spiffy when you join to those. Let’s see. Incomplete or missing join predicate. So I guess you could figure this out if you were looking at, like, foreign keys and they’re like a multi-column foreign key and you’re only joining on one column from it or something.

But I’m really not sure how you would figure out that part. Consider using exists instead of in rule. So consider using exists instead of in when used with the subquery.

All on board with that. In and not in, I’m fine using that. If you have a list of, like, literal values, like, you know, like strings or numbers or something. But as soon as you have to go look at values in another table, absolutely.

Use exists and not exists instead. Let’s see. Avoid exec or execute. Use parameterize sp execute SQL instead. Hell yes, I am all on board with that.

Avoid order by with numbers. Yeah, that’s really annoying. Bush league laziness. You don’t, like, if you have an expression in your query that’s, like, long and complicated, the nice thing is, because of the laws of logical query processing, you can just put whatever you name that expression in the order by, it works.

You don’t have to repeat all that stuff. You don’t have to, like, copy and paste some big long math. Down in your order by, you just say, like, math equals big long math thing and then order by math. And then you’re fine.

Let’s see. Unspecified type length. Do not use string types without specifying a length. Hell yes, you shouldn’t do that. It’s dumb. Unspecified precision or scale.

Do not use decimal or numeric data types without specifying. Yeah. If you don’t do that, you might as well just use float. Lazy son of a gun. Consider column prefix rule.

Consider prefixing column names with table. Yes. Yes. Make your query readable. Make sure people know where things are coming from. The least you can do is basic decency. Avoid use of hints.

I’m big on hints, so I’m not going to touch that one. Sometimes you know better than the optimizer. Avoid using car index. Avoid using car index function in the where clause.

So this one’s a little weird because I know from reading further down that there’s a general check for functions in the where clause, which I assume would cover stuff like is null, substring, replace, left, right, car index, pad index, any of that stuff.

So it’s a little weird that there’s like a solo rule for that up here because car index is not the only thing that’s crappy in a where clause. Also, when we’re looking at stuff like this, it’s not just the where clause.

If you use functions in a join clause or if you use functions in an order by, you can also be in for a really bad time because you screw up SQL Server’s ability to use indexes in a nice orderly fashion.

It’s not like SQL Server can’t use indexes. It just can’t use them as well as it can when you write things cleanly in a way that doesn’t, you know, is null, replace, replace all the letter M with the number 52 and order by that or something. Ridiculous.

What else do we have here? All right. Avoid use of or in a where clause. So if you use or on a single column, like in the context of the Stack Overflow database, if you were to say where post type ID equals one or post type ID equals two, that’s not going to hurt you too much.

I mean, you’re going to be way better off if that is indexed in a way that makes finding ones and twos helpful. Where things get really screwy is when you spread that across columns, especially across columns and different tables.

That can certainly get messy, especially when you start involving things like joins, because if you have a left join and you’re like where like inner table column equals this or outer table column equals something else, you end up with these late filters in your query plan that get really awful.

I would say a better check would be to avoid the use of or in a join clause because if you do like join, like I’ve talked about this a million times on my blog and videos, but if you do like join, like users to post on like, you know, this equals this or this equals that, joins with or clauses really screw things up like 99% of the time or something.

I made that number up, but it’s close enough for me. Let’s see. Avoid cursors.

Well, sometimes you need a cursor, right? Sometimes, sometimes you need a cursor, especially like if you have a store procedure that like dumps a bunch of like stuff into a temp table and then cursors over that temp table to call another store procedure.

There’s not like a better way to do that, right? If you place that with a while loop, it’s not like you buy yourself anything. So, um, I like it as general advice, but you know, sometimes, sometimes you gotta use that cursor. Alright, uh, use of nolok.

Do not use the nolok clause. Hell yes. Don’t do it. It’s bad for you. Uh, especially because if, if you’re examining some code that’s going to end up in a DAC pack, it’s production code.

If you’re using nolokins in production code, you’re not writing production code. You’re not writing production quality code. Uh, let’s see here. Force delay. Do not use wait for delay statement and store procedures functions and triggers. Hmm.

If you have a store procedure with, I mean, the, first off, the good news is you can’t use wait for in a function because that’s a side affecting thing. It’s like you can’t use like, like new ID or new sequential ID or some of the other, uh, built-in stuff for that. Um, so that’s, that’s at least one thing.

You can probably remove the check for functions. But, um, if I have a, I mean, triggers, it’d be kind of stupid to use it, but, you know. But, uh, store procedure, sometimes you put a loop in a store procedure. You should have a wait for in there so you’re not just zooming through things.

Uh, let’s see. What else do we have here? Um, do not use set row count yet. It’s deprecated. It’s kind of silly. Alias tables rule.

Consider alias and tables. Hell yes. Make your queries readable and understandable. Uh, object schema not qualified. Yeah. If you’re not qualifying object schema, you’re a bad person. Uh, avoid select into of temp table or table variables.

Well, I disagree with this one. Um, A, you can, well, I mean, A, you can’t select into a table variable. That’s, this is not allowed. You can’t do it.

Um, but for temp tables, I think it’s a very useful trick sometimes. Um, especially because, uh, with select into like 2016 plus you get nice fully parallel insert plan. It’s usually a good thing.

Um, I think I understand where this check is coming from though, because like if, you know, uh, the way that the query engine might infer some of the data types with select into might not be exactly what you would expect. So like it’s not always, like sometimes that happens, but for the most part, select into is pretty safe. Uh, possible side effects of implicit CAS.

The arguments of the function zero are not of the same data type. I, I don’t, I don’t understand that one. To be honest with you. Uh, raise error.

Uh, I, I don’t think I care much about that. Uh, excessive indexes on a table. Well, for me, indexes are all about quality over quantity. You might have what looks like excessive indexes, but all of those indexes are really useful to a lot of queries.

So, uh, I try not to, I try not to call indexes excessive unless I know a lot about how those indexes are used. Um, let’s see. Use of approximate data type.

Do not use rail or float. Yeah, pretty much. Uh, ambiguous column name. Avoid using columns that match other columns by name, but are different data type or size. That’s a really good one.

Uh, I remember a long time ago, I think I wrote a script or two to find those sort of things in a database. So I’m glad that this is a rule here because especially if you need to compare those columns to each other, then like, you know, you have two string columns.

One’s a varchar, one’s an nvarchar. Bad performance time. Uh, let’s see. Um, uh, expression reducible to constant. Uh, I don’t get it. So we’re going to skip that one.

Uh, do not use deprecated types rule. Yeah, you shouldn’t use text and text or image types. That goes for Microsoft too. Uh, duplicate overlapping indexes. I actually want to read the code for that one at some point because I’m, I would, I would like to see how they do it.

I would like to see if that’s easier in C sharp than it is in T SQL because it’s a real pain in the butt with that. Uh, explicit collation. Eh, whatever.

Uh, object level option override. I don’t know what that one is. Unsafe identity retrieval. Yeah, yeah. Don’t use added identity. Especially if you have multiple rows going into something. Uh, do not mix DML and DDL.

That’s, that’s actually an interesting one. Um, it’s not like it’s going to cause you any problems, but I’m all on board with this because I like when things are nice and organized. I would even go a step further and say like, you know, you shouldn’t be declaring stuff like halfway or most of the way through a store procedure.

You should be declaring as much as you can. Well, you should declare everything up at the top where you set it. It’s up to you.

It was between you and your maker. Um, you know, I guess for stuff like cursors, that’s a little different because you don’t want to like, you know, start opening and declaring and opening cursors up here. If you’re actually never going to use them, you might actually never actually get to a point in the code where those happen. Uh, let’s see.

Um, ordinary. Ordinal parameters used. Uh, always use parameter names. Hmm. So that’s interesting because there’s actually, um, a known performance increase, uh, when you use ordinal, uh, or positional parameters with, with, um, uh, natively compiled code. So, you know, not that anyone uses that anyway.

Uh, permission change in store procedure. I don’t, I don’t, I don’t care about that. Uh, invalid database options. I don’t know what that means.

Past because SQL Server is going to compile an execution plan for all of that, regardless of if, uh, your code path is actually explored. So, um, you get like cardinality estimation and query plans based on like a code path that might never have happened. And so you get like zero row or like one row expected and you get like this dinky little query plan.

But then you execute the store procedure and you actually hit that part and it’s like a lot of roads start jumping around. And yeah, it’s a bad time. Uh, let’s see here.

Uh, consider caching get date to variable. Uh, so if you need, uh, so there are two things with this. One, if you’re like updating a bunch of tables to like have a column be equal to like get date or assist date time, it makes more sense to cache it in a variable because then when you do the update, they’re all going to have the same, like you set the column to the variable.

They’re all going to have the same matching time rather than like the time of when the update happened. But if you’re going to put that in a join, oh, I guess, I mean, I’m going to say join or where clause because it could be either one. But, uh, if you’re going to put that in a join or where clause, you’re going to screw yourself because you’re going to get the local dense, local.

So you can get the density vector estimate and your queries performance probably going to stink and you’re going to stick a recompile hint on it or I don’t know, whatever. Uh, but yeah, so there are times when you should do that and times when you shouldn’t do that. Uh, avoid not for replication. I hate replication, so I avoid replication.

I am not for replication, even though I have two kids. Uh, let’s see. Nested views. Again, you know, views are only as bad as what you do with them, but nesting views, you know, you end up with multiple junk drawers and that’s not fun at all. Uh, unacred string pattern. Uh, try to avoid patterns and start with percent.

And yeah, that’s generally pretty crappy. Sometimes unavoidable, but you know, uh, aggregate of unique set. Avoid using distinct keyword inside aggregate functions. Well, sometimes, again, unavoidable.

Uh, I think the, you know, where things are interesting with that is because, like, if you’re doing that on, like, a single column in your query, uh, it’s generally not, like, that bad. Like, especially, I think I talked about this in a blog post recently. If you’re, like, saying select from users, left join post, and you want to count stuff from the post table, you’re going to want to, well, actually, no, it’s not distinct anyway.

Never mind. Uh, but, uh, sometimes you do need to count distinct things. Where things get tricky with SQL Server for queries that aren’t using batch mode, um, multiple distinct aggregates really make a crappy query plan.

Performance really eats it on that. Like, one, you can, one is usually tolerable, but, like, multiple distinct aggregates, things go out the window. Um, noisy trigger.

Well, I suppose you should put a silencer on that. Uh, so yeah, avoid returning results and set no count on your triggers. Yeah, absolutely. Uh, use of inequality.

Well, that’s not, that’s not quite right. Those, those are perfectly sargable things. If you, if you, if you want to figure out if something is not equal to something, I don’t know what else you’re going to do in a way that would be sargable. This is more about indexing than anything else.

So, um, make sure your indexes match your, match your where clause and things, things will go a lot better for you. Ah, this is probably my favorite one. Dangling cursor.

That’s what I’m going to be for Halloween this year. I’m going to be a dangling cursor. Uh, so yeah, close your cursors, deallocate your cursors. It’s generally, generally a good rule to follow.

Uh, filtering on a calculated value. Avoid wrapping columns within, so this is what I was talking about with car index. So this whole thing, avoid wrapping columns within a function in the where clause. Yes, avoid that.

Uh, let’s see. Function and data modification. Avoid the use of user-defined functions with update, insert, delete statements. So that one’s interesting because you can get around a lot of the Halloween protection issues with that if you use the schema binding hint with your, or the schema binding directive or option or whatever you want to call it. Uh, in your functions, you can get around, you get, you fix the Halloween protection stuff.

So, um, maybe the check looks for that. I don’t know. Uh, non-member test and predicate. Avoid using not in predicate in the where clause.

Well, it’s not exactly right either. Uh, not in is fine if you have a list of, like, literal values, like 1, 2, 3, 4, 5. But, uh, as soon as you go out and touch another table, you might come back with no’s.

It causes a lot of stuff. Uh, let’s see. Unindexed membership test. Consider indexing the column referenced by in predicates in order to avoid table scans. Oh, sure.

Index columns that are in your where clause. Index columns that are in your join. Seems like reasonable advice. I’m not sure why in gets special treatment.

Uh, let’s see here. Existence tested with join. Consider replacing outred. Yes. Absolutely. Do that. Table variable and join. Avoid the use of table variables and join clauses.

I’m on board with that, too. Uh, they tend to not be, not shine so bright when you do that stuff. You know, you know, we don’t want to get into the nitty-gritty of it, but I, on the, in general, I agree with that principle.

Avoid column calculations. Avoid the use of calculations on columns in the where clause. Absolutely.

That should also go for join clauses. Uh, quality test with mismatched types, data types. Yes. Make sure that your data types match. It’s a great thing. Uh, update of primary key.

Who in their right mind would do that? I mean, developers. So, you know, who knows what, who knows what they get up to when you’re not watching. So, I assume there’s a good reason for that check. Um, let’s see.

High join count. Well, yeah. I don’t know how many joins they consider high, but, uh, that, that, that, that, that can, that can cause issues. Uh, missing clustered index.

Hell yes. Yes. OLTP workloads generally should have clustered indexes. Manipulated parameter value. Yeah. Avoid modificational parameters prior to use. I mean, not just in select queries.

I mean, any, any query that you, you do that could be detrimental. Especially if, again, join. I mean, definitely where clause, though. Procedure level recompile option.

Consider using, yes, that’s actually good advice. Uh, query level recompile hints. Do a lot more than store procedure recompile hints. Enumerating for existence check.

When checking for existence uses, exists instead of count. Well, this one I disagree with a bit. See, when you use exists to check if something exists, uh, SQL Server internally sets a row goal. And when you set that row goal, you can end up with some really wacky query plans.

Or some really slow query plans. So I’m going to run three different queries here. Uh, the first one is the exist check, where you’re going to see row goals kick in and performance not be so great.

The second one is just one that I found amusing. And the third one is where, uh, select count big, uh, is actually, actually performs better than the exist check. So we’re going to look at these query plans now.

I didn’t set no count on. I should, I should be fined by the DACPAT committee. So, uh, when you do a select, so this is the, if, this is the, just the plain if exists. And we get this tiny little loopy little navigational plan here.

And, uh, we get this plan because SQL Server, uh, sets a row goal with these, um, these semi joins of one to just check if something is there or not. And depending on how the optimizer views its odds of finding something quickly, you can get these really, really sort of goofy query plans. And this part is particularly spectacular because we look through 37 million rows trying to find that one magical row, but we don’t actually end up finding anything.

So that drags on for a very long time. Uh, the thing with count is that if you just say we’re greater than zero, you end up with all the same execution plan, right? That’s no better, right?

You end up doing the same thing in here. Where count can be better is when you give SQL Server some loftier goals. So I said where count is between one and the integer max. I should have done the big int max, but, uh, cause I’m doing count big, but pay no attention to the little man in the corner.

But this query, and despite having a very strange query plan, this runs for 12 seconds. This runs for nine seconds. This query down here runs for three and a half seconds.

But because SQL Server is unable to set that, uh, that row goal, uh, in the same way, we get a nice query, uh, well, I mean, come on, get up. Uh, you know what? Screw it.

We’re just going to move that a little bit. SQL Server does some weird stuff with this query plan. I admit this is a bizarre looking query plan, but the performance of this is better. So, uh, I wouldn’t necessarily always want to do exists over count. Um, correlated subquery, avoid the use of correlated subqueries.

So this is sort of like the view thing. Uh, the correlated subquery is only as bad as what you put in it and is only as slow as the indexes you have to support it. So I’m not sure I totally agree with that check.

Uh, the ones down here, uh, UDF with system prefix procedure with SP. Well, I’m guilty of all of that. Uh, use of default schema. Um, I’m not sure, I’m not sure what that is.

And then general naming rules. I’m not sure what those are. So anyway, um, some good, a lot of good in here. Some stuff, I don’t know if it needs to be fleshed out more. I don’t know if there’s maybe some, I don’t know if maybe there is actually some stuff to do the additional stuff that I’m talking about and test first things a little bit further.

But, uh, I do think that this is a very good start to making, uh, setting developers on the right track. This just might need a little bit more detail and a few more levels of figuring out what’s going on before, um, before you flag these things as issues. Now, one thing I do like about this is it has a column called ignorable.

And a lot of this stuff is flagged as ignorable at times. So you don’t necessarily have to, um, punish yourself with, with all of these checks. Uh, especially if you, um, especially if you know that you’re doing okay with some of these things.

So, anyway, uh, a big thank you to, uh, Eric EJ and, um, I’m sorry, I, uh, there was a, I don’t know, actually, is it in here? Let’s see. Um, yes.

Well, actually, that’s just MS SQL build. Uh, so a big thank you to Eric EJ and to Jay Mazak. I apologize if I pronounce that incorrectly, uh, for putting this together because I do think it is a net positive for developers. But, um, you know, some of the rules, a little weird to me, but, uh, I don’t know.

Perhaps, perhaps, perhaps they’re better than they seem on the face of things. But, anyway, uh, thank you for watching. Uh, hope you enjoyed yourselves.

Hope you learned something. You liked the video? Give it a thumbs up. This is a longer one than usual. This is a half hour. Jeez Louise. Windbag I am. Uh, and if you like this sort of SQL Server content, please do subscribe to my channel. Um, that’s all.

All right. Cool. Thank you. Goodbye. I hit the button. Stop recording.

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.

Fixing Parallel Row Skew With TOP In SQL Server (With A Brief Re-Complaint About CXCONSUMER Waits)

---

Video Summary

In this video, I delve into the issue of parallel skew in queries and how it can lead to suboptimal execution plans. After receiving feedback on my previous video about CX consumer weights and detecting parallel skew through CX sync port weights, I decided to create a follow-up focused on fixing these issues. We start by examining a problematic query that took 15 seconds to complete due to uneven parallelism, with most of the work being done on a single thread despite an eight-way degree of parallelism. The solution involves introducing a `TOP` operator within a `CROSS APPLY` to better distribute rows and balance the workload across threads, resulting in a more efficient execution plan.

Full Transcript

Erik Darling here with Darling Data. I did tweak some audio settings, so hopefully there’ll be less noise gate cutoff of things, and only when I’m actually silent will you not hear any of the weird background stuff that goes on in my office or the frequent sirens that go by my house in lovely, crime-free New York. So today’s video, I don’t actually, I’ll tell you here, a while back I recorded a video about CX weights, like CX consumer and CX sync port, how to detect parallel skew in queries and how to figure out if that’s why your query is slow, and some of the feedback that I got from that, both on YouTube and and story and they have spend.

Y Oh,unnoV Mod and they notes3 very, but silk. how do I fix it? So finally got around to recording a video about how to fix it. So let’s do that.

Let’s talk about fixing it. But let’s go back in time a little bit and let’s look at what the problem is and some of the other surrounding stuff. So let’s make sure I have query plans turned on here. Actually, let’s make sure I have query plans turned on here ahead of time too, so I don’t go and screw anything up. And I’m going to run this whole kit and caboodle right now.

And we’re going to get a little bit of execution plan noise on this, but that’s okay because we’re doing stuff up here and then we’re doing stuff down here. But the bottom line is that this query should take around 15 seconds to complete. And there we go, right at the 15 second mark. There we go. If you look under my armpit, you’ll see 15 seconds down there. It’s amazing. It’s immaculate, right? So what happened? Why? What went on with this awful query? This abomination? Well, let’s start by looking a little bit at weight stats. And one of my chief complaints about a lot of things in SQL Server is that, you know, at some point in time, someone at Microsoft said, CX consumer weights, no big deal. Ignore them. Don’t ever think about them. But I think we can see pretty well here that CX consumer weights were a big deal for this query and that we should maybe pay attention to them because it’s a lot of time spent waiting on the old CX consumer.

Now, in the original video, one thing I noted was that the CX sync port weight, the time on that was just about even with the query execution time. And I think that might be another way that no one has said we should ignore yet to detect parallel skew in a query, which is great, you know, awesome, cool. So let’s look at the query plan. And let’s look at where things went wrong. And then we’ll look at how to fix it, how to fix it. All right. So the bad part of this query, or rather where the problems start in this query is over here, when we have this serial zone, it gets flipped to a parallel zone, and then sent into a nested loops join. And what happens is if we look at the properties of this thing, actually, that was really well set up. Usually, I’m not that well prepared, but here we go. All right. Well, actually, this part’s a little bit off. Maybe this wasn’t the greatest idea in the world. Let’s see if I can slide that over a little bit. There we are. All right.

Maybe that’s the secret. Maybe I’m just not loud enough. I should be loud and dumb. I watched the movie American Fiction last night, finally. It’s a very funny movie. And there’s a line in it that really stuck out to me, where it said, I forget, I can’t remember. I’m not good at actor names. I can never remember anyone. Like, I don’t know. I’m just bad at it.

But the star of the movie said something like, the dumber I act, the richer I get. I got a kick out of that. And I was like, wow, I got to try acting real dumb. Because apparently, lots of people who act real dumb make a lot of money. So maybe that’s a good tack for me to take in my career. I’m going to start acting dumb or dumber than usual, maybe. I don’t know. Maybe I’m not acting dumb enough. But if we look at the details here, I can stop moving around. We can see that all these rows end up on a single thread. That’s an eight-digit number. Because I have two fingers left up. So that’s an eight-digit number there of rows. So that’s 10 million rows, I think. Ended up on a single thread. And of course, we have a fully parallel execution plan. But having 10 million rows on a single thread kind of just makes it a serial execution plan. It doesn’t matter how many threads you throw at it. If all 10 million or if rows heavily skew towards a single thread, it’s not going to be a good time for your parallel plan. Especially a parallel nested loops plan.

And you should watch my video about parallel nested loops to learn more about why. It’s a good video. If you like this video and you subscribe to my channel, you will get notifications every time I pass on one of these remarkable nuggets of knowledge to you for free.

Amazing things will happen to your career. You might go from junior DBA to regular DBA to senior DBA to staff DBA to principal DBA to president of the DBA society. You never know. You never can tell. But the thing here is that this situation proceeds throughout the query plan. The 10 million rows still all on one thread. There you can see that. I can zoom in a little bit to make life a little bit easier than some of you old fogies who watch with your bad eyes. And if we highlight this arrow right here, I mean, it’s going to be fewer rows on this one. Oh, what happened there?

Oh, you know what? The compute scale I messed things up. You can look at this one. But still, all of the rows are on a single thread, right? So we had essentially a parallel query where one thread did all the work, right? And that’s not good because this query ran at a degree of parallelism of eight. The ocho, as they say. Eight, right? And the reason why I brought up the weight stats over here, right, where you can see CX consumer loud and clear as a session level weight stat is because if you look in the query plan, CX consumer, at least I hope it’s going to not be there because it’s never been there before. If it’s there now, I’m going to lose my mind. But it’s not.

Okay, great. So I was actually writing a blog post because when this stuff originally started coming around, one of the things that I complained about was that lock weights didn’t show up in query plans. And then I went to complain about it in another blog post. And apparently they show up now. So I should probably actually write a blog post to correct my other blog post. That’s not a bad idea.

If someone can leave a comment on this video reminding me to do that, I would really appreciate it. So yeah, so CX consumer weights do not show up in here, but those CX sync port weights do, right? So we can see all that going on in there. That’s 15 seconds about, right? That’s about how long our query executed for. And if we look there, if we actually come back to the results, CX sync port, 14,779 milliseconds, which is 14.779 seconds. And well, that’s, you know what? Horseshoes, hand grenades, government work.

We’ll take it. That’s close enough for me. All right. I don’t know why this window moved. It had no business moving. So we’re going to pop that back over there. And then, actually, before I move on, I should show you the part of the query that caused the problem, right? Because we have a problem area in this query. And that’s right here, this cross join, where SQL Server’s like, yep, one row is going to come out of there. And sometimes cross joins can be fine. Other times like this, we end up with, I don’t know what we just saw, where we have a fully parallel plan. SQL Server didn’t do a good job of splitting rows out to all the parallel threads.

And this is one of those unfortunate things, because we can’t really control what SQL Server does when we write things so plainly, right? We have very little control over what, how SQL Server is going to generate a query plan for these things. And with a cross join, I mean, there’s really nothing to correlate in here, right? We can’t do anything in here, because this max is an expression here, and we can’t really do anything down here. Like we could try with having, but that just gets weird and awkward. So the only place we can really apply the filter that we get from here is down in here, in the where clause. And now, this is where our trouble lives, right? It’s this whole thing. SQL server gets a single row from this and passes it along to the whole rest of the query. If we go back to the query plan, this area here, oops, I don’t need that tool tip. This area here is where SQL Server is getting that max, right? We have the index scanned, and then we get a top one, and then we aggregate that down to the, to get the max creation date. And then we use that max creation date is a filter in here, right? So we have where the post creation date is less than that scalar operator, which is an expression, which is how SQL Server has identified that max aggregate inside the cross join. All right. Cool. Got that sorted out. We think we know what the problem is. This query has very uneven parallelism. We know the part of the query that is causing that issue, right? It’s this part here, where it goes from a serial plan to a parallel plan. And then that’s where one, or rather one thread does all the work with all 10 million some odd rows. So what we can do to fix that in this, in this situation, and this is a technique that works quite well in many places, is to introduce another top into the query. So what we’re going to do is rather than cross join, we’re going to cross apply.

And inside that cross apply, because it’s safe to do so here, because we’re getting a single thing back from here, we’re just going to say, I want the top one. Oops, that’s not what I wanted. Wait, where did you go? That was strange. We’re going to say, we just want the top one from here. And then inside the cross apply, we’re allowed to do something that we’re not allowed to do inside of a cross join, which is have this inner correlation, right? So we can push that up into here. And I don’t know if you noticed from when I just ran this, but it didn’t take 15 seconds. It took zero seconds, right? So we can do more work inside of this, and we end up with a different execution plan overall, right? Rather than have that serial zone that turns into, come on, man, help me out. There we go. Nope, I almost had it.

There we go. Rather than have that serial zone coming out of the cross join, we now have a parallel zone down in this part of the query, right? And the nice thing about top, the great thing about tops is, even if this didn’t happen, what top does, aside from when it’s on the inner side of a nested loops join, what top will often do is force a serial zone in your query plan. And if your query goes from parallel to serial to parallel, one thing that it has to do when it comes out of the serial zone is distribute stream, right? It has to go send all those rows out to multiple threads so that you can, or rather so that your query can process rows with all the parallel threads that are available to it, right? Great, good stuff. So, excuse me, the Microsoft documentation on top, or rather on parallelism kind of sucks because it seems to infer that top forces your entire query to run single-threaded, but it doesn’t. There is just generally a serial zone going into a top operator, except when that top is on the inner part of a nested loops join, which it is here. We have our nested loops up here, we have our outer part of the nested loops up here, and then our inner part of the nested loops is down here. But this ends up pretty good for us. And it’s a pattern that you can generally apply. It might not always result in a query plan that looks like this, but what adding another top in can do is gather streams where it goes into the serial zone for the top, and then distribute streams where it comes out of the serial zone and back into a parallel zone as it leaves the top. And the results are all the same, and the weight stats for this, of course, look quite a bit better, even though we somehow managed to rack up five seconds of CX Consumer in a query that executed for 668 milliseconds. It’s a fun time, right?

Bit of a mind bender there, I admit. What were all those CX consumers consuming? I’d like to consume some of what they consume. But anyway, this was, I thought, a reasonable solution and a pattern that you can apply generally to your queries. If you’re examining a parallel query and you’re wondering, why the hell is this thing so slow? And you look at any of the sort of little arrows or any of the operators where data is passed along, and you don’t see something like this.

All right. This is a pretty even good parallel row distribution on parallel threads, right? There’s about 3,000 on each. That means every thread is going to do a pretty normal amount of work, or pretty equivalent amount of work. If you see all or most, or even like, like if you’re looking at the numbers, and let’s just say for the sake of having something to talk about that thread four had like, I don’t know, like two, three, four more digits than the rest of them, then that would be a pretty good, pretty good place to start with. Your plan is to figure out, well, how can I get better thread distribution so that I can have my parallel plan, my parallel threads, my parallel workers all doing an equal amount of work? Because if you don’t do that, one of them’s going to start expecting a raise, and you give one of them a raise, they’re all going to want a raise, and it’s going to be really hard to explain to them that, well, this one thread did 10 million rows worth of work, and you did zero. You don’t deserve a raise. Sorry. It’s not in the cards.

We had record-breaking fiscal year, but, you know, you get a bowling party, and you’ll get laid off next week. Fun times, right? All right. Anyway, I got other stuff to do, so I’m going to wrap this up. Top is your friend when you have uneven parallel threads, and you want a way within your query to naturally gather streams into a serial zone and redistribute them, hopefully in a more even manner, going back into a parallel zone. Cross-supply is another very useful thing for this, because, again, cross-supply will quite frequently be optimized out to a nested loops join, and when you get a parallel, well, I mean, when you have a, I’m going to say this very specifically, when you have a parallel nested loops join, and you have a top on the inner side of the nested loops join, that top will no longer introduce a serial zone into the plan. So, use your tops wisely. All right. Cool. Thank you for watching. Hope you enjoyed yourselves. Hope you learned something. If you like this video, please do give it the old thumbs up. If you like SQL Server performance tuning content, well, this is a good channel to subscribe to for it, as far as I can tell. It’s all I talk about, so might be worth doing that. Ever closer to 3,000 subscribers. I don’t know what I’m going to do when I hit that number. I might just drink on camera. You can all see what I’m really like.

All right. Anyway, that’s enough for today. Thank you for watching, and I will see you in another video at another time in another place. Actually, most of that was a lie. I’m going to be here. We’ll, might probably be another time.

Same bat channel, though. I’m not, not moving out yet. Anyway, that’s good enough.

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.