Key Lookups and Index Creation

Key Lookups and Index Creation


Chapters

Full Transcript

Erik Darling here with Darling Data… that happen uh when you create indexes like the index create query plan uses a key lookup it happens it’s in the world uh down in the video description you’ll see all sorts of helpful links incredibly helpful links uh you can hire me for consulting you can buy my training at a discount there are coupons attached to that uh you can become a official supporting member of the channel with money if you if you think that this content is maybe worth a few bucks a month uh and of course you can ask me office hours questions for free at least for now anyway so i’m gonna have to start charging tokens for these because i’m doing some serious thinking over i never thought all this thinking would would be would have an attributable attributable metric to it but it turns out i use tokens too weird right uh and of course like subscribe tell a friend all that good stuff uh if you need absolutely free gratis on the house sql server and or postgres uh performance monitoring my free performance monitoring tool does both of them for free i cannot stress how free it is so if you don’t have monitoring or you’re unhappy with that paid tool that costs a ton of money and sucks because of course it does because it’s a paid monitoring tool and look at them all uh you try mine it doesn’t suck i’ll be in seattle november 9th through 11th for past summit west i have a pre-con there where i’ll be teaching you about advanced t-SQL where even if you you know you’re not writing it anymore you might still have to look at it once in a while and you might still have to tell a robot when it’s being an idiot i can help you they’re gonna help you understand when the robots are being idiots that’s that’s my angle on this um someone still has to do it because the robots are still idiots uh sometimes other times pretty okay what times you got to argue with them a lot right uh and i also have a regular session about isolation levels called read committed is cruel by default it is what i hope to be the last time i will half be forced to talk to you about why you should not use read committed sql server’s default isolation level and why you should use a better isolation level because this time you might listen to me i don’t know crazy out there but it is still october we are still october festing and feasting and so let’s let’s do this thing right so uh in sql server management studio i have i have created an index on just creation date and now i am going to expand that index right i’m going uh and i’m going to add a filter to that index i could have added the filter up here would have would have done the same thing but uh it’s a little bit more interesting uh but normally when you create an index uh you know it’s a nonclustered index usually we can create a non-clustered index uh it it involves scanning the clustered index of your table right and scanning the clustered index of your table and sometimes be awful right most most most of the time when you do this it’s going to scan the whole table it’s going to apply a sort operator uh to sort your data into the order of your new nonclustered index and then bam you’ve got an index and your queries start flying everyone’s happy uh some knucklehead is off in the corner wheedling away about fragmentation and and and your inserts are going to be three nanoseconds slower you you can you can continue to ignore them they are useless people but uh if what now if we go and create this index i’ve got query plans turned on all right and that happens pretty quickly uh mostly this happens pretty quickly because okay look two reasons one no no data meets that filter criteria right it’s a year past anything that happens in the stack overflow 2013 database so it technically does not find any rows uh but more importantly um it’s like the data in the votes table is weird like a lot of the creation date columns in stack overflow have like you know uh apart from like the normal dates and times uh they actually have like millisecond as accurate as milliseconds are for a date time uh not in the votes table though the votes table they’re all there it’s it’s kind of weird but um if you look at the query plan for this index we will see that this this index uh did a key lookup right right right there all right we create we did an index seek into our creation date index right we found uh well zero rows to actually do anything with but had we found rows right uh well we would have uh used this key lookup to output put the post id column and add it to our index right and a lot of people don’t look at this stuff and notice this stuff but i noticed this stuff uh for a while i thought that this was a pattern that only happened under read committed snapshot isolation uh but i was wrong it happens under all anyone can do it it’s probably crappier under read committed because the object level shared blocks that you get with uh lookups and under that one but maybe aside of the point but you know well this is like a cool artifact it’s not really what i want to talk about well it is but in a different way so like where this can be kind of awful is when maybe you have out of date stats uh and sql server is like oh i just have to do a few key lookups but nine fraulein uh you end up doing a lot of them right so uh this is this is something that i’ve seen it uh helping indexes tune in helping it clients tune indexes and i wish indexes tune clients that’d be a fun one right i don’t know how that would work anyway uh what i’ve done is i’ve created a table uh called votes stale uh it’s just a copy of the votes table because it’s 53 million rows and it’s good for this sort of thing uh and i’ve pre-updated some of the rows and i’ve created a couple indexes and then we’re gonna we’re gonna widen this index right here this index was just to make this index create a little fast it’s it’s a whole thing stop bothering me and then just to make this a little bit easier to simulate um i have i turned off auto update stats and i updated 20 of the table rows in a loop and uh that brings us to this point in time where uh well we don’t need this we don’t need max.8 in there that’s it’s a bit silly but let’s just say uh some some developer was just like ah hey we need to um now with our query we need to also return this creation date column and you say all right no problem you do some testing and you’re like crap if we had creation date where we don’t use this index anymore we got to widen it right you got to make sure that we keep using this nice narrow index we’re not scanning our clustered index and so uh you start creating this index and uh we should turn on actual execution plans and you realize that creating this index is rather slow and i seem to have closed my sp who is active window so we’re just going to reuse this one execute dbo dot sp underscore who is active at get plans equals one and if we run this it’ll be just in time to miss our other index getting created uh i’ve said this in other videos if you ever need to bully a query into finishing just open a new tab start typing sp who is active in there and it’ll be like hurry up get going right so uh we’ve created this index and um it took 15 almost 15 seconds and you can see that we have once again done a key lookup right uh because sql server’s estimates are off because the stats were out of date so you know when people say why does this do this why does this why does this happen why god why uh well this is this is why right or this is one reason why there are many many reasons why estimates may be off this is this is but one of them so sql server anticipated uh doing 1069 lookups that was the original sort of um seed update that i did just like ah there are some modifications and then i i blew a bunch of other updates in there i was like ah now we’re in trouble um but this is a case where like index creation might take a lot longer because sql server is making maybe a not good choice about uh what would be happening in here right like scanning the clustered index dot 8 53 million rows ah that’ll go pretty quick doing uh all the the lookups and the the rest of it um not not not really as fast right uh so if you’re ever creating maybe widening a filtered index and you’re like why is this taking so long uh look at the execution plan for it there’s a chance that you’re you’re doing a key lookup maybe your stats are out of date and maybe maybe you have a new thing to think about in your life anyway uh i i thought all this was uh incredibly fun and interesting uh i hope you enjoyed yourselves i hope you learned something and uh well this will this will be the this will be the friday video so i will see you next tuesday for office hours all right thank you for watching

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

DOP and Tipping Points In SQL Server

DOP and Tipping Points In SQL Server


Chapters

Full Transcript

Erik Darling here with Darling Data. And I’ve got a question that I thought was interesting enough to actually answer with a brief demonstration, so I’m going to do that today. But it is about DOP and tipping points, or as I will lovingly refer to them from now on, DOPing points, because why not? Let’s just make things hard for everyone to understand. What’s wrong with that query? at the DOPing point? What’s that? You don’t want to know. It’s going to cost you $10.99 to find out. Down in the video description, there are very many helpful links for you to click on. If you’d like to hire me to fix your SQL Server performance problems, you can do that. It’s right down that way. You can also purchase my training at a discount. There’s coupons down in the doodads there. You can become a supporting member of the channel. Ask me office hours questions like I did yesterday-ish. And of course, like, subscribe, tell a friend, all that stuff. You know, the usual. If you need free SQL Server and Postgres performance monitoring, I’ve got it all under one handy umbrella. Totally free, totally open source, no weird email stuff. Just a whole bunch of perfectly written collector quiz.

queries that go and get all the stuff that you would care about as a performance tuning data professional. I recently added AGs in there, which, you know, personally, I didn’t want to do, but some people like that stuff. So here we are. And if, you know, as we transfer ourselves over to the robots, my tooling, I built it in there on purpose, has all sorts of fantastic read-of fantastic read-only MCP tools, so you can let your robot friends talk to your collected monitoring data and do all the sort of fun analysis and figuring things out for you. And then you come back and look like either a hero or an idiot, depending on what you’re using to do that analysis. So, could go either way. Choose wisely, my friends. Choose wisely. Choose wisely.

Choose wisely. And fewer than one month, I will be at Pass Summit West in Seattle, Washington, November 9th through 11th. I’ve got a pre-con there about T-SQL that doesn’t suck. You should come to that one. Because if you are out there in the world and you are using the robots to do the analysis you need you need to know what you’re talking about so the robots can’t talk past you um you know i still run into it quite a bit where uh you know i’m using my robots to do my stuff and they say something that is absolute nonsense and i’m like hey buddy bring that way back way back over here and and so you still need to know what you’re doing a little bit if you are going to remain a competent human in the loop uh with all these robots flying around doing things but it is time to continue to celebrate the month of october and uh let’s let’s do this old demo thing here so uh we’re gonna we’re gonna hope that uh the robots don’t pop anything up today i’ve asked them to i’ve asked them to take a break from anything that might pop up while i record this we’ll see how well that goes so uh i’ve created a single key column index over here uh the the because i need i want to show you something kind of interesting now uh this all has to do with query costing and at what point lookups become uh to the optimizer theoretically estimatedly more expensive than scanning something all right so if we run these two queries uh that are asking for where post type post id is less than 250 at max.1 which does limit max.1 and max.8 which remember max.1 is not min.1 this this will limit max.1 to 8 but it doesn’t guarantee max.1 will be 8 right just in case right and we did these return a lot of rows so we’re just going to work with estimated plans here when i run this these both uh let me check this up a little bit higher so uh it fills the screen and when i zoom in things don’t look dumb when we run this it for less than two post id is less than 250 they both get serial execution plans and they are both using key lookups right so we’re seeking to where post type id is less than 250 and then we are going back to the clustered index to get that user id column because that is not in our nonclustered index right and it is not the clustered primary key so it is not inherited by our non-unique nonclustered index right and then if we run these two queries for uh where post id is less than 2000 uh things change a little bit uh or rather a lot of bit at max.1 sql server still estimates that doing the lookups is cheaper than scanning the clustered index so with the single threaded query sql server is like uh i don’t know if scanning 53 million rows of the votes table is the best plan with one cpu i think i’d rather do a bunch more lookups when uh we don’t limit max.1 when we say max.1 you can go up to eight sql server is like well if i had eight of them scanning this 53 53 million row clustered index that turns out a little bit better for me right because uh that that seems like less work i don’t like we’re not looking at times or anything here i’m not saying whether the estimate the optimizer uh and its estimates are right or wrong it’s it’s it’s just showing you how it thinks and how it behaves a bit and then finally if we say where post id is less than 5000 these both do scans right sql server is like i don’t know that even at max.1 oh come on grabby thing even at max.1 that’s a lot of lookups for me uh i i think i’m just going to scan this whole thing instead so the the tipping point for these things does change depending on DOP right like DOP does have an effect on the cost models and all the other stuff there are some times where forcing a query to use max.1 retains a better plan across the board i know it’s a weird thing to say because for years everyone made fun of sharepoint and dunked on sharepoint because you had you had to have MAXDOP set to one and this was before we had the database scope configuration to uh change MAXDOP per database this was when MAXDOP was server level so if you had sharepoint on a sql server with other stuff you were like uh it’s a MAXDOP one everything sucks everywhere else well i mean sharepoint always suck but yeah whatever but there are some times where you might be in a situation where you’re like you know what i i know my data’s my data’s dictate that i never want to scan this clustered index and we’re forcing MAXDOP to one will uh be a strong enough guarantee uh for most cases this one it’s not a strong enough guarantee because uh just that less than predicate on 5 000 those are like an equality predicate we could do things differently but uh with a less than predicate like you probably don’t want to do like where where post id is less than 49 million and be like yeah you should still do a 49 million key lookup probably not a good idea but if you’re if you’re using equality predicates or you’re using very tight greater than less than with some equal twos in there maybe uh predicates and you know your data well enough where you’re like i never want to scan the clustered index no matter what sql server estimates um you know there are all sorts of ways to get sql server to do that that don’t involve creating a wider index you know you can uh have a stick a FORCESEEK hint on there uh you can use FORCESEEK with a named index uh or sometimes MAXDOP one is a good choice because for a small enough number of rows a parallel key lookup is not going to buy you right it’s just like like like what are you going to spread like you know 10 thousand rows out on eight threads that’s not a good use of eight of eight cpus why would you do that right uh small numbers of rows you don’t need lots of big numbers of rows lots of threads good small numbers of rows lots of threads not helpful right so um MAXDOP one is one interesting way one interesting hint that you can use to uh affect costing in a way that benefits you but as always test carefully all right thank you for watching i hope you enjoyed yourselves i hope you learned something and i’ll see you in tomorrow’s video uh where i actually i’m going to show you something neat with indexes so that that’ll be fun all right goodbye

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

SQL Server Performance Office Hours Episode 93

SQL Server Performance Office Hours Episode 93



To ask your questions, head over here.

Chapters

  • 00:00:00 – Introduction
  • 00:02:58 – Filtering Parallelism Out of Blocking Alerts
  • 00:04:45 – Why SQL Server Ignores Indexes That Look Perfect
  • 00:08:21 – When Schema Design Is the Root Cause
  • 00:10:29 – What Silently Causes Plan Cache Bloat
  • 00:13:12 – Why Deletes Sometimes Block Everything
  • 00:14:51 – Wrap-Up

Full Transcript

Erik Darling here with Darling Data, and it appears that it has become, come to my attention that it has become Tuesday, and I owe you five answers to five questions. Maybe, I don’t know, we’ll see how those line up, right? Maybe, maybe there’ll be more, I don’t know. But down in the video description, all the stuff you can do to help me keep a roof over my head, hire me for consulting, buy my training, become a channel member. There are coupons right in the links to the training and stuff. I don’t know, maybe, maybe I should put some coupons on the consulting. We’ll see what happens. I don’t know.

My birthday is coming up, and I do like to celebrate by spending it on the phone with people, talking about SQL Server. So, it’s always something. You can also use the links below to continue to ask me office hours questions. I do appreciate it. We’re still, we’re still working through things. Lots of questions in there. And you should like, subscribe, and tell a friend if you haven’t already.

And, I mean, if you only told one friend, make a second friend, tell them. If you out there in this wild, crazy, mixed up world are looking for SQL Server and Postgres Performance Monitoring, I’ve got it all. Or I’ve got both. It’s for free. It’s available on GitHub. You don’t need to give me anything for it. You know, it’d be nice if you did. There are ways to, you know, contribute money to the continued development of this project.

But, for now, I just enjoy using it to torture paid vendors who put out really crappy performance monitoring tools because they deserve it. Past Summit West coming up, Seattle, Washington, November 9th through 11th. Glory days, I’m sure. I have a pre-con there, Advanced T-SQL that doesn’t suck. I should have subtitled it, Arguing with Robots or something.

But, you know, it was too late by then. But there’s always next year. And I also have a regular session called Read Committed is Cruel by Default. And it would just bring so much joy into my life if you showed up to both of them.

Anyway, we are still Octobering here, so let’s answer these questions. All right. Hi, Eric, comma. Hello, you, comma.

Loving the performance monitoring tool. Ah, good. Another happy customer. Free customer. We get a lot of blocking alerts where the query has gone parallel, so the blocking session ID is the same as the blocked session ID. Yeah. Is there a way we can filter this out or is this indicating an actual issue other than just parallelism?

So, no, there isn’t a way to filter that out because that is something you should pay attention to. If you have ECIDs within one spid that are blocking each other, that is something that you should look at because one day that blocking might become a parallel deadlock.

And it might not be fun for you having to debug that one. Well, parallel deadlock graphs are beautiful things. They look like, what are the, like Swarovski things.

They’re like Fabergé eggs. They’re quite intricate and lovely. Figuring them out isn’t a lot of fun. So, pay attention to those queries while they’re just blocking.

And look at the query plan. Well, you can get one because you might not if it, if it dies. Sometimes they’re in query stores, sometimes not.

But, crack them open. Look for stuff like order preserving exchanges. Parallel merge joins, stream aggregate, sorts, stuff like that.

Look for any memory consuming operators spilling. You might even see exchange operators spilling in there. And take care of the problem.

Why would I want you to turn a blind eye to a problem? Being silly in all of your pants. Why does SQL Server sometimes ignore indexes that look perfect for a query?

Well, let’s pretend, let’s not answer the boring version of this question. We’ve talked about why SQL Server might ignore a nonclustered index in favor of a clustered index. That’s not that interesting.

Key lookups, random IO, expensive. Let’s pretend that you have two nonclustered indexes. Both of them would result in key lookups. And both of them can result in a seek.

Let’s say that you have three predicates in your where clause. And each of these two indexes leads with a different one. Sometimes, I mean, it’s all going to come down to costing, but that costing is going to come from cardinality estimation.

And SQL Server might be estimating cardinality for one of your predicates either incorrectly or maybe it has inherited it from a different set of parameter values that things were compiled with. And it made accessing very few rows via that other index look cheap and easy. So let’s say that we’re working with the stack overflow database and the vote type ID or post type ID, very uneven distributions in those.

Some of the type IDs, either post or vote, have very few values. Other than have like 37 million, 11 million values attached to them. And let’s say that you compile the plan looking for some type of type ID with a very low cardinality, like say like a couple thousand rows.

And SQL Server is like, oh, sick. I can find a couple thousand rows here and then knock the rest of that out. And great, right?

Like that looked nice and cheap and easy there. SQL Server is like, yeah, I get the right idea. But then the next time you run it and it goes to one of those crazy big numbers, all of a sudden you found 37 million rows instead. Right?

And now you have to do 37 million key lookups to go filter something else. Right? Not a particularly good time. Right? So a lot of the times it is one of those things, right? You have two competing nonclustered indexes.

For one execution, it looks great. For another execution, like the cardinality, one execution, this index looked like way easier to access the data in. And then, you know, all the other normal stuff.

Maybe you’re using local variables or something. Maybe you’re using table variables and, you know, cardinality estimation isn’t great. And like whatever density vector guess you get from local variables, all of a sudden, you know, it’s not so hot for anyone. Yeah.

So there are all those reasons, but it usually, but it’ll come down to cardinality and costing. You know, from there, it is kind of your job to either, you know, force the plan you want in some way. Right?

You could potentially use query store. If you’re feeling real maniacal, you could use a plan guide. If you have good control over the, or you can even use query store. Well, you can use query store hints.

And if, you know, if you, if you’ve got good control over the code, you can add in whatever set of, you know, index join hints for seek and stuff that they get you the plan that you want that works better, more generally across all those. All right. How do you know when schema design is the real root cause of performance issues?

So I’m going to say very generally when your tables are all, how do I do this? No, that’s the wrong one. This wide.

And your indexes are all this wide. That’s usually a pretty good sign. You know, like lack of normalization. That’s a pretty good one.

And, you know, just, I mean, I guess it’s easier to pin it on like, ah, they created a single column index on every column in the table. I mean, that’s, that’s one of the patterns that you, that you sometimes see with those. But, um, another one is, um, you know, just like, you know, very, very narrow indexes that don’t help, they don’t help queries beyond like some limited capacity.

And so they barely ever get used. That’s another good one. Uh, lots of max data types are just, you know, inappropriately sized strings, stuff like that.

Um, you know, um, mismatched columns that should match, uh, the number of databases I’ve seen were like, and I, like, I don’t know. Like, and I, like an ID column in one table is an integer and it’s a VARCAR of some variety in another one. Or like, even like GUIDs, like in one table, it’s a unique identifier and another one, it’s a VARCAR 36.

And you’re like, can we just make up for mine? Can we please just do something, do something useful with our lives? Uh, so there, you know, like there, there are a lot more tells in that, but you know, um, a lot of it, I mean, I’m like, you know, you could, you could try to put it on stuff like, Oh, there’s no foreign keys, but you know, foreign keys don’t really do anything.

Like they, they obviously referential integrity is the thing they do, but like, it’s not like they help SQL Server, like, like, estimate join cardinality better. That’s all, that’s all other stuff anyway, right? Don’t really play in there.

Um, but I, I think, yeah, like, like really wide tables is usually, um, usually the biggest one that I see. Um, do what actually, uh, are you a robot? You send in robots out here.

Actually, what actually silently causes the load bearing crux of plan cache bloat? When does it matter? Um, I mean, tons of things, uh, you know, passing in.

Well, okay. So like the obvious one is when your queries aren’t parameterized, right? Uh, the less obvious one is if you’re using an ORM or, you know, dapper entity framework.

If anyone, I don’t know if anyone’s still using hibernate, uh, JDBC or something, and you are not strongly typing your parameters there. And what happens is the parameter lengths all get inferred, right? So, um, let’s say, you know, the, you run it and you’re like, uh, this query is parameterized.

Everything’s fine. But like the first time you pass it in, it’s like, I’m going to go look for Eric. And it’s like, cool.

And VARCAR four. And then the next time you search for someone, you’re like, I’m going to go look for Bob. And then the print is like, my query is parameterized. This is great.

Uh, but Bob turns out as an VARCAR three. And so you get different copies of all the parameter lengths, uh, in the plan cache. That’s another one. Um, optional spids and temp tables is another one that I see. But, uh, you know, some, sometimes those are, those are a bit harder, harder to alleviate yourself of.

But, uh, when does it matter? Uh, you know, usually the plan cache is just such a jumble anyway. Um, hardly anyone notices.

Right. Uh, and you know, if, if plan cache builds, bloat is like your, your real, like your worst problem. Well, you got relatively easy fixes.

Um, unless you have, you know, you know, like 50 databases on the same server, all having to share the same plan cache. And it’s not an easy problem to solve. But, um, yeah, like it’s not, you know, it’s not like we get a plan cache per database.

I mean, technically we do with query store, but that’s not where SQL Server, that’s not what SQL Server uses for a plan cache, unfortunately. Um, so, you know, uh, when does it matter? Uh, hardly, hardly ever.

Um, you know, I think probably the, the, the person who it matters most to is the person who has to run analysis queries on the plan cache. It’s just like, it’s the same query over and over again. Ah, there’s 10 billion of them.

We can’t get rid of them. Right. Like they’re more hell on analysis scripts than peep. Uh, anyway, uh, why do delete sometimes block everything? Even when removing only a few rows.

Um, this is usually going to come down to, uh, one of three things, but generally I, well, yeah, I’d say generally that stems from a lack of indexing. Uh, you may, may not have a good index to support whatever the where clause of your delete is looking for. Uh, assuming that, you know, well, I mean, if you delete a few rows, assume there’s a where clause there that narrows things down.

Um, uh, that’s, you know, usually one. Um, if you have a foreign key on the table, um, then, you know, uh, if you don’t have good indexes to support the foreign keys, that might, uh, lead to some issues, especially big tables trying to latch onto each other. Um, let’s see what else we get.

Uh, cascading foreign keys would be another one. Those take serializable locks under the hood. Um, perhaps you have some index views going on. Um, if you have created columnstore indexes on your, uh, on your OLTP tables, perhaps.

A non clustered columnstore index to support some reporting queries. Uh, deleting from those and not a lot of fun. Mm.

Yeah. Don’t, don’t enjoy that. Uh, uh, yeah, that that’s, that’s, that’s pretty much the, the stuff that, um, I would, I would say that, that is a good enough. Oh, triggers, triggers, triggers, triggers, triggers.

Yeah. Delete a few rows. Who knows what all those triggers do, right? I have 15 audit triggers on this table. What are you auditing? Yeah.

I don’t know. Who’s looking at it? Got me. Why is it there? Hey. Oh, last, last guy did it. Like, okay. Well, yes, we’re here then. All right.

All right. That’s good enough. Thank you for, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I will see you in tomorrow’s video where, uh, I’m gonna say some unpleasant things. All right.

Goodbye.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

The Joys of Early Exits

The Joys of Early Exits


Chapters

Full Transcript

Erik Darling here with Darling Data. Sort of a talky video this time. There are great demos to show you this, but it’s more something that I find very useful in my query tuning work that I hope you might find useful in your query tuning work too, and that is the joys of early exits. And by that I mean I see a lot of code written that like long store procedures, long triggers, long whatevers, that could be a lot more efficient if people, developers, robots, anyone out there in the world stopped to look and see if there was actually any work to do before running a whole bunch of queries. So I’m going to talk through some examples of that. Down in the video description you will find all sorts of helpful links, very helpful for both of us, I think. Indeed, we could both benefit in tremendous ways if you hire me for consulting, buy my training, become a supporting member of the channel. You know, a little bit less with the office hours questions, but that’s okay. I still like answering those. I also, not only do I find joy in early exits, but I find joy in giving back. I’m not sure exactly how those two things dovetail, but you know, there we are.

If you enjoy this content, please do like, subscribe, tell a friend. I don’t know. Don’t send me any. Not to my address. That’s weird. If you need free, and speaking of giving back, if you need free SQL Server and or Postgres monitoring, or even Postgres and or SQL Server monitoring, code.erikdarling.com has all you need. In life. Totally free, totally open source. MCP enabled for your robot friends to dig into data. I mean, it just collects all the performance stuff you could possibly ever want. You get a nice WPF viewer if you’re a Windows style tradition trad DBA. You get a nice web viewer if you prefer that. There’s just all sorts of great stuff in there. Pretty charts and graphs, AGs, performance, locking, blocking, deadlocking, CPU, memory, memory, disk, you name it. It’s in there. It’s monitored. There is no greater joy than looking at these dashboards. Also, alerting through various channels. Teams, Slack, PagerDuty.

There’s some other stuff too, I think. I can’t keep track. I don’t need alerts myself, because what am I going to wake myself up for? All right. Windows update messed everything up again. No kidding. Every day. I will be speaking at Pass Summit West in Seattle, Washington from November 9th through 11th. I have two sessions there. One of them is about isolation levels. It’s called Read Committed is Cruel by Default.

And the other one is my full day, Magnificent Advanced T-SQL Pre-Con. And you should come to both, because I think you’re pretty, and you’re smart, and you smell great, and I want to be in a room with you. I don’t know what other charms I can break out for this, but probably not anything without not being disinvited from the conference. So, whenever I see a lot of stored procedures, a lot of code in general, a lot of T-SQL in general, where, you know, maybe, you know, a temp table gets populated, or you’re looking for data that meets a particular condition, but, like, the code doesn’t stop if nothing gets found.

So, like, let’s say you do your initial insert into a temp table, and no rows end up in that temp table. You can check that very cheaply and very easily with row count, or add-at row count, or row count big, which is my preference, because I deal with some big row counts in my life. And if you don’t find anything, just stop there. That’s also true in triggers. The very first thing you should do in a trigger before you, you know, set no count on, or whatever other things you want to do, is just say, if row count big equals zero, return, right? Just stop right there, right? Nothing, like, an insert, update, delete, or merge ran, but nothing changed. Just cut it out, right?

Don’t go to the next queries. Don’t, like, say, do some stuff if stuff exists in the inserted or deleted table. Just cut it right there, right? And by extension, I see a lot of code where, ah, where the robots are testing stuff. I guess I got interrupted. God bless, right? No hands, right? They’re doing a click-through before I do a release. By the same extension, like, there are any number of things that that might work for.

Like, let’s say we’re in the Stack Overflow database, and we’re like, ah, run this series of queries and do all this stuff, but only if, like, a post type ID equals, I don’t know, 23, and it’s, like, the current date. But if that doesn’t exist, we don’t have to go do all that work. And this is, this can get you in two different ways. One, queries like this, like, even, you know, just, like, polling queries, stuff like that.

Maybe you have jobs that run and keep doing stuff and looking for work, and they run through all the code no matter what. No one is just saying, hey, if we don’t get anything here, bail here immediately. There’s no need to do all this stuff. Some people run into problems with that because whatever is calling the code might expect some results, and a lot of APIs are like that.

So if you, if your API is still expecting stuff, well, you can just select a list of dummy columns from nothing and return an empty result, right? So instead of just returning, you can just say, select column list, right? You know, null equals all this stuff across the board. Just be careful with data types there because SQL Server thinks nulls are integers.

Might, you might find yourself breaking some API contract if you return an integer maybe where you shouldn’t. Integers are pretty flexible, though, so maybe that’s not the worst thing in the world. But, again, this can bite you in two ways. One is code that executes very frequently.

You know, like, say you have something that runs, you know, hundreds of thousands or millions of times a day. The early bailout is so valuable there because you no longer have to execute lots of tiny little queries that end up not returning anything. There are some times where it’s, you know, pretty cheap and easy.

If it’s just like a couple queries in a store procedure, it might not be the biggest deal in the world. But even if it’s just like 200 milliseconds worth of, you know, CPU or duration or something, it could be zero, right? You can, you can save many, many CPU hours a day by bailing out early.

That number can, can get bigger and it can also, you know, get small. Numbers can be big or small and they can be good or bad in either direction. But, you know, you might have other queries that do a whole lot of extra work.

Or maybe even queries that attempt to take a whole bunch of weird locks when they don’t have to. And, and you can stop all that very early on by just looking for early exits in your code. But I think that’s, that’s probably about all I got for this one.

I promise this would be a short video. So we’re, we’re, we are going to keep this one under 10 minutes because I know that is, you know, smart and pretty and funny and good smelling as you are. You do have a limited attention span.

So I’m going to let you go. But I hope you will keep this in mind as you are looking for queries to tune, right? Because, you know, as, as, as a wise person once said, the fastest query you can run is one, the fastest query is one that doesn’t run.

There we go. Those are the words. And if, if you can run far fewer queries, you can save a whole lot of time in your SQL servers.

All right. Well, I’m going to let the robots get back to testing the walkthrough here. Uh, and I will see you next Tuesday with an office hours video.

All right. Thank you for watching.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

How I Use Claude Code to Build Stuff

How I Use Claude Code to Build Stuff


Chapters

  • 00:00:00 – Introduction and Setup
  • 00:02:30 – Opus and Fable Usage
  • 00:06:00 – Agent Management and Token Usage
  • 00:08:30 – Example Workflow with Opus
  • 00:11:00 – Setup and Tools Overview
  • 00:12:30 – Monitoring and Complex Projects

Full Transcript

Erik Darling here with Darling Data and in today’s video I’m going to talk because I get questions quite a bit about how I work and build on my free database performance monitoring tools. And so I want to share a little bit about that because it’s been a learning and growing adventure with me and my friend Claude working on things. It’s, you know, like there is a learning curve to this stuff and there are things you can do that make your life better and easier and all that good stuff. Down in the video description you will find all sorts of helpful links where you can hire me for consulting, buy my training, I guess I can even come teach you how to use Claude to do stuff because a lot of people are real dumb about it, including me when I started.

If you want to buy my training, become a supporting member of the channel or ask me office hours questions, there are helpful little things that you can click on down there that do all this stuff. And of course, as always, like, subscribe, tell a friend, oh yeah, ba-da-da-da-da-da. We’re going to, you know, go to code.erikdarling.com.

Not only can you get the free monitoring tool that I’m going to show you how I build, but also some of the stuff in this video are all Claude plugins that I built myself to help me do things better, which you can also download there. And of course, Seattle, Washington, November 9th through 11th, past Summit West, I will be out there ranting and raving about SQL Server, which is much closer to my home turf than anything else. But who knows, who knows how much longer that’ll last. We all might need to just be A8 spurts in order to continue on.

But let’s move this thing out of the way. And I’m going to talk a little bit about this. So this is my typical Claude code setup. I’ll have a few open windows. And the one cool thing about what Claude can do is agents can talk to each other across sessions.

So I have basically four tabs open right now. There’s a you can ignore the one that says past Summit West session. That one is in charge of something else.

But the one that I have up now is sort of like my general utility coordinator session. It keeps it keeps track of what all the other sessions are doing. Make sure that issues get assigned as they come in and that the queue gets worked through.

Right. So like this and this is the one that I spend the most time talking to because this is the one that basically is in charge of all the other ones. But that’s not to say that all the other ones are just doing top line work because they’re not.

This one over here is basically in charge of watching pull requests. So as the other two sessions finish working on issues, pull requests come in. This one is tending to those that that involves basically just like watching the CI run in GitHub.

And if anything fails, fixing that and then basically just seeing them through until they merge and then closing issues that are attached to them. So this one here doesn’t do a lot of work, but you know, it stays it stays busy with the with the pull requests. And then these two over here are ones that are directly working issues.

There are at any given point, you know, a bunch of issues open. And these two things are in charge of making sure that, you know, like looking at the issue, triaging it, looking at the code, building code to either make a feature or fix a bug or whatever it is. Reviewing the code changes to make sure that nothing is screwy and also testing them.

In the video that I did last week, there was the background behind me. You could see Claude actually like running the GUI on the performance monitoring tool, looking at stuff in there. That’s another part of what it does, because if I had to click through every chart graph and tab and I had to sit there and be like, how does this look?

Does this look great? Let me go. Let me go. Tell it what looks wrong. I used to have to do that and it was quite painful. How I started off using Claude was basically just like I would have one tab open and I would have this one poor agent doing everything, running out of context constantly, like working on like kind of one issue at a time.

And then I started working on, you know, multiple issues with multiple tabs open and that got turned out to be a mess until I discovered Git work trees and I could have them work separately on stuff without like the weird GitHub. Like, oh, sorry, I switched branches and overrode everything that you just did. It was real messy at first, but I’ve learned how to do some stuff.

Now, one thing that becomes really important when you work on more advanced agent coding setups is keeping track of your tokens and how much you’re spending or how much you’re digging into your Claude Max account. Or some people out there who might have two Claude Max accounts just in case, you know, like there’s a lot of stuff you have to keep track of and there’s a lot of stuff you don’t want to do. For example, like Claude has different levels of agents.

There’s like Fable, which is like like the big powerful one. And then there’s Opus, which is like the medium powerful one. And then there’s like Sonnet and Haiku, which are not very thinky. They’re kind of like soft cheese up here.

But they’re good for like easy mechanical work and they’re much cheaper. They’re much cheaper to use. I don’t recommend using them for anything that like requires like a lot of, you know, I want to say thought because they’re, you know, I thought because they’re LLMs and they don’t think.

But anything that requires a lot of effort, it’s probably a good way to think of it. So what I have set up in my repo and what you can find at the code.erikdarling.com repo in the Claude plugins folders, is you can find how I sort of route agents of the appropriate model type and thinking level to different tasks.

Like you can see in this section here, PM worker helper three has a bunch of the sub agents running. And they’re all sort of at different, they’re all sort of at different levels of stuff. So like a bunch of them are Sonnet five, you know, these ones are high thinking, but the effort level can be adjusted between like high, medium, low, depending on what they’re doing.

And you can see how much each one of these costs as far as like, you know, like for whatever tokens it’s been using. This one down here is thinking about a roll up migration plan. And so it’s using Opus on extra high because it’s got to look at a whole bunch of stuff and a whole bunch of code to figure out if it’s doing things right.

Like if it’s really thought, really thought through everything, all that other stuff. This, this window here looks very similar, right? This one has a bunch of lanes where they’re basically, well, these ones are all using Sonnet a different look.

Actually, these are all high too. I got to have a talk with these robots. They’re spending, they’re blowing their credit cards. This one here is on extra high because it’s got to think about a lot of stuff that it has to do.

But this is how I run stuff and this is how I keep track of stuff so that I kind of know what they’re all getting up to. And it took a long time to get to this setup. You know, like if I just had like one of these things, like all the anything that’s in a tab up top there, those are all using Opus.

And Claude has this advisor thing, right, that you can say, hey, if you get stuck or if you want to like double check your work, you can use a higher level model to go and like, you know, look everything over and futz about and tell you if you’re right, wrong or in between. And so all of these are Opus and they can use Fable as an advisor. But when I when they’re using sub agents, you don’t need Opus for everything, right?

You don’t need Fable for everything. They’re good for like top level stuff because, you know, like like things that the lower level agents do come back to them and then they review it and they figure out how bad of a job like the little workers did. So like when I when I first started doing this stuff, it would be like, cool, I got Opus, use sub agents and it would be like, I’m going to use Opus sub agents, too.

And then it would be like, you hit your weekly limit and I’d be like, I got three days of nothing to do that. Right. And that’s how you keep having to like, you know, buy the extra tokens like another 250 bucks. Go ahead and take it, burn it away.

Like they’re like as you get to use this stuff more, you have to figure out ways to conserve and do a better job of using the limits attached to either your your account or however your credit card bill or however you’re paying for stuff and doing things. It’s always a good idea to, you know, make sure that you’re not like using Opus to like change a file name. Right. Like there’s stuff you don’t need.

Like what’s the weather? Crappy probably. I’m in I’m in a server. It’s hot in here. Like, I don’t know. I just where’s all the water? I thought I thought we were drinking all the water in the world.

Where is it? I’m dying. So like what you don’t want to do is just have like crazy level agents doing all this stuff that they like they have no place doing. Right. Just like way too much for that.

You it’s perfectly fine to have a top level agent who’s like, you know, is less dumb than the other ones sort of directing them and then reviewing their work and then coming back to you with anything that they might need you for. For example, in this window, I’m trying to figure out how to make the continuous integration that runs in GitHub go a little bit smoother because the problem that I’m having is one that was unfamiliar to me as a database person where like, you know, something would start building and some of the tests would run and like the builds would take a long time and then a test would fail. And then they’d be like, oh, I got to fix that.

I got to rerun the whole thing is another 45 minutes. It just got to be a real pain. So I’ve been I’ve been trying to do some work to make that less annoying by like sharding stuff and like bucketing tests that take longer across like some across the different shards. So it’s not like one shard does all like the tests that take 10 minutes or something.

So that’s sort of what this this this session is helping me with at the moment. And I’ve got this little, you know, it’s got a question for me. I should go answer it before it gets mad at me.

But you can you can see up here where it’s giving me an update on session three and four. There’s nothing big. I restarted them to install an update that probably isn’t going to do much of anything.

But anyway, this is a little bit about my setup and how I do stuff. I’ve gotten some questions about it. So I wanted to just talk a little bit about it because I thought maybe if if a couple few people are asking me the question, then maybe there’s some broader interest in how I do all this stuff.

So that’s about it. I don’t I don’t have anything more formal than that. It was just sort of me rattling off the top of my head some stuff that’s on my screen currently.

Again, if you’re interested in any of the plugins or mods that I’ve built for Claude Code, they’re all at code.erikdarling.com and the Claude plugins folder. There’s a lot of stuff in there that looks like this and, you know, things that you can use to make your bigger building experiences a little bit more tolerable and easy.

And, you know, like if you’re just cracking open an agent session, like, you know, like if I were to like if I wanted to like work on something in quickie store or one of my store procedures, I would probably just open an Opus window and be like, hey, look over all this stuff.

I want to do this thing and then go, you know, like write the code for it and then test it. Right. Because I give Claude access to SQL CMD and my local SQL Server VM. So we can go and run the store procedures, look at the results, figure out if things look good, bad or ugly and say, hey, I think I’m done.

And then I can look things over and say either no, this is screwed up. You go go back to the get back in the mines or, you know, no water for you. Or, you know, like, yeah, it looks good.

Let’s, you know, do some stuff with it. And this is just, you know, that I would do that. But like working on the monitoring stuff, there is way too much to do to work like that. So my, my, my, my setup has gotten necessarily more complex over time.

But, you know, maybe you don’t have that situation. Maybe, maybe, maybe you’re stuck with some, you know, real soup brain like co-pilot. I’m sorry if you are, or any, one of those open AI models that, you know, couldn’t give you a reliable recipe for ice cubes.

I don’t know. But this, this is how I do my stuff with Claude. And now things are fairly good and fairly streamlined.

But there we are. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

And I will see you in tomorrow’s video where I will try to find something else to talk about. All right. Goodbye.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

SQL Server Performance Office Hours Episode 92

SQL Server Performance Office Hours Episode 92



To ask your questions, head over here.

Chapters

  • 00:00:00 – Introduction
  • 00:04:02 – Alerting on Custom SQL Server Alerts
  • 00:08:35 – Design Choices That Lead to Blocking Hell
  • 00:12:03 – Why Forced Plans Disappear From Query Store
  • 00:13:01 – How Exchange Operators Add Latency and Blocking
  • 00:16:15 – How Azure Throttling Masquerades as Query Slowness
  • 00:17:36 – Wrap-Up

Full Transcript

Erik Darling here with Darling Data. I know, I know. How do you just keep getting better looking day by day? I don’t know. It’s a mystery to me. We are Tuesday-ing our office hours. Office hours Tuesday, right? That’s the plan anyway. Who knows what’ll happen by the end of the video. Who knows if something might just pop up and start interrupting me and robo-driving things. You never can tell. It’s an exciting world we live in. You’re just borrowing the computer from your robot friends at this point. Down in the video description, as always, very helpful links for you and I to interact with each other. You can hire me for consulting, buy my training, become a supporting member of the channel, continue to ask me these very office hours questions that keep my Tuesdays so lively and enjoyable. And of course, if you enjoy anything here, please do like, subscribe, tell you old friend group, drop it in the group chat, just slide into random DMs and send people to my YouTube channel. I don’t care how you do it. That’s, you know, all that stuff. If you are personally in the market for SQL Server and Postgres database performance monitoring, you can get mine for free.

It’s at that link right there. It does everything you would want it to do. There are a couple versions of it. There’s a light one that’s very easy to spin up and just start getting stuff from like a handful of databases or servers very quickly. Or you can get a pretty crazy full enterprise-y headless monitoring service that collects 24-7 and you can point it at hundreds of databases and it does pretty well. So I do that because the performance monitoring ecosystem has been stagnant and embarrassing for so long that I wanted to do something to improve upon it. And, well, this is what we ended up with. So enjoy that. In fewer than one month, I will be at Pass Summit West in Seattle, Washington, November 9th through 11th. I will have a pre-con there about Advanced T-SQL, how to make your SQL querying life suck a little bit less.

And I also have a regular session about isolation levels, talking about how cruel read committed is by default. But now that it is halfway through October and I forgot to change the slide from September to October two weeks ago, clearly I’m paying a lot of attention. Yeah. I have not made November and December’s database images yet. So if you have any that you feel, if you have any ideas or suggestions, please do leave a comment with what might make sense.

Because, you know, November, it’s got my birthday, it’s got Thanksgiving, maybe some weird Canadian thing in there. And then, you know, of course, the December festivities are upon us. But, you know, these winter months, I start getting depressed, right? Start getting my sad on, but, you know. Anyway.

Does your monitoring tool allow alerting on custom configured SQL Server alerts? I could not find that sort of option. For business reasons, I can’t send alert emails directly from the DB servers. So I’m not exactly sure what you mean here.

So, like, if you have, like, you can send alerts via email, you can send alerts to a Slack channel, you can send alerts to Teams, you can send alerts to PagerDuty. There are a number of ways that you can send alerts, but I’m not exactly sure what setup you’re asking me about. One thing that I am working on that might align with your question is sending alerts via custom webhooks.

And the reason why I’m building that is because one of the goals that I have for this in the long term, since, you know, you can’t really stop what’s going on in the world. So, you know, I have a feeling that a lot of processes will be moving towards more agent-driven things. Agents respond really well to webhooks.

And so what I’m working on is a way to, if you choose, to set up a persistent agents with your stuff locally. I’m not providing an agent in this thing, that’s not really, that’s not really a possibility for me at this point. But if you want to set up persistent agents locally, you can send alerts from the monitoring tool to those agents, and those agents can do something to respond to them.

How they respond is also up to you. But, you know, stuff that I’ve experimented with, with a couple live sites, are things like forcing plans in QueryStore when an agent can find a better available plan. Hitting the DBCC free proc cache on a specific SQL or plan handle.

Hitting SP recompile on a store procedure, stuff like that. And then also other things. So, you know, you might have an agent receive an alert and be able to respond far more immediately to do some triage, right?

So it doesn’t, you know, doesn’t wake you up in two in the morning. The agent gets a webhook, does some triage on the servers, runs, you know, like, you know, checks the monitoring data, right? Looks at the, like, MCP’s tooling that looks at the monitoring data.

Does some initial triage. And if you, again, if you choose to set it up to this extent, the agent can even open JIRA tickets or whatever system you use. It can open PRs if it, you know, if it, you know, has enough, if it has enough of a setup to, you know, do the triage, see the code and everything, and then, you know, like, come up with a PR fix for it.

So, like, that’s sort of what I want to do with this because, and it’s not like, you know, it’s not one of those, like, you know, robots should just replace everything we do. So, it’s just, like, when alerts come through, I don’t want you to have to wake up at night, right? Like, there should be someone who, a thing that doesn’t need sleep that responds first, then maybe try some stuff before you do get woken up.

And, you know, as the robots get better at things and, well, you know, without getting too deep into it, a lot of it does depend on, you know, how much context and how well trained your local agents are with stuff. You know, like, if you just have some flat-footed, like, you know, haiku on medium effort agent show up and be like, oh, blocking problem, kill them all, right? Like, that’s not going to be a good experience for you.

But as you build up, as you, if you, if you work with stuff the way I do, the persistent agents with sufficient context and, you know, sort of, like, you know, training on this stuff can do a lot better than a lot of people sort of expect. So, that’s where I’m going with the webhook thing. I don’t know if that’s sort of what you’re after, but that is, that is where I’m going with stuff.

What design choices most often lead to blocking hell? Boy, howdy, all of them. You know, it’s, it’s something you have to talk through many levels of, right?

You know, like, if you start at the furthest out level, if the hardware that your SQL Server runs on is not up to the task that you have assigned it, your queries are going to run slower. And that includes your modification queries, and that includes your read queries. And, you know, the slower those things go, the more chances they are, right?

Think of the, the duration that a query runs for is its surface area. And if you, if you ever played battleship, you know, that’s, it’s a lot easier to find those bigger ships than it is to find those smaller ones. And so, if you artificially increase the surface area of a query by having crappy hardware, then you increase the likelihood that blocking is going to be a problem for you.

Then, you know, zooming in a little bit further, the isolation level that you choose for your database, read committed, the default isolation level for on-prem SQL Server, is not a very kind one. So, Microsoft really hurt my feelings with, with that. And then, especially, you know, Azure SQL DB, read committed snapshot isolation is the default, which is much, much kinder on, with read queries and write queries, not getting into big fights with each other.

You know, that’s, that’s another design choice. And then, then you get into other stuff, like, you know, the way your tables are designed. If you have very wide tables, and they are very hot tables, and you have just things trying to update all different, or insert and delete, and all the, all the different stuff, then you, again, you’ve increased the surface area of something.

And, and granted, there are way, there are some ways, at least, with query, with modification queries that involve a where clause, to sort of, like, you know, vertically partition wide tables with nonclustered indexes. You know, you still have your clustered index or the underlying heap to contend with. Inserts tend to, you know, involve all of the columns, and, you know, stuff like that.

So, like, you know, there, there, there is stuff that, there, so there are, like, some things you can do around wide tables, but, you know, that’s, you know, that’s another consideration there. Choosing to store lob data in your, in your tables, right? Any max data types can lead to some weird blocking.

And, of course, then there is indexing, right? If you, your modification queries either don’t have a where clause or do not have a, sort of, sufficient access path to the data via indexing, if they do have a where clause, that’s going to be another set of problems for you. And then, of course, the way that you write your modification queries.

So, that, you know, if your modification queries, like, you know, look, look at a primary key and just delete more, update, or insert one thing, your, your life is a lot easier than if you’re like, I need to find everything over the last 10 years and update it, because, oops. All these decisions cascade and lead to problems. So, what design choices?

All of them, right? And this is all the stuff that you have to consider when you are trying to design a well-oiled system. Why do force plans sometimes disappear or stop working in query store?

You know, temp tables are one that I see lead to stuff get weird a lot. You know, like, someone mucking about with indexes, that would be another one. You know, people who do goofy things like deploy by dropping, dropping procedures and stuff and then creating them.

That’ll make force plans stop working. There’s all sorts of fun things in there. My favorite, though, is when you’re looking, trying to figure out what’s going on with the force plan and query store, and it’s just, like, general failure.

And you’re like, ah, I salute you, general failure. Enjoy your parade. There’s a lot of reasons why.

I wish that force plans and query store were a stronger guarantee, but, you know, what can you do? How do exchange operators contribute to latency and blocking in parallel plans? Well, in a couple ways.

Let me figure out how deep to go with this. So there are a few different kinds of exchange operators. If we go from left to right, we have gather streams, which is typically at the end of a parallel plan, and that signals the end of a parallel zone.

Those can show up in parallel plans in other places. You might have a serial zone in your plan. Then you have repartition streams, right?

And that is SQL Server trying to reshuffle all your rows and make sure and hopefully make sure that they stay evenly dispersed. And then you even have a distribute streams operator, which you frequently see either after a serial zone in a plan or maybe after, like, a serial, like, backwards scan of a table or something. There’s also a neat optimizer thing called the few outer rows optimization, where SQL Server will do some fun stuff to give you a good parallel plan there.

And then, but, like, usually when I see exchange operators, like, looking like they’re up to something bad, it’s in one of two places. It’s either, well, let’s think of a few different ways to go with this. So, if the rows that are being exchanged are particularly thick rows, right, wide rows, like, you know, like some max data types going through there, you know, the parallel exchanges are all memory-based.

They’re buffer-based. And so the bigger your data is, the fewer rows fit on buffers, and that can sort of jam things up. But, you know, sometimes even, like, you know, like max data types will even mess up batch mode plans without an exchange operator because SQL Server is like, eh, batch mode with a max data type, I’d rather not, right?

So, like, that one’s kind of a bummer. And then, you know, there’s other stuff around parallel exchanges where uneven row distributions can kind of gum things up a bit. But the one that I am on the lookout for most frequently is when exchanges have an order buy in them, right?

Because, again, that does not mean that they are ordering data, but they are preserving the order presented to them. And that’s where you see a lot of parallel deadlocks and a lot of parallel spills come from because you’re creating intra-thread dependencies in a parallel environment. And that is not a good time.

That this is one of the reasons why I rail against query plans that have parallel query plans that have merge joins and stream aggregates and stuff in them because quite often they gum up around the order-preserving exchange operators. So that’s usually the stuff that I am on the lookout for. I don’t know.

It’s probably about as much as I care to go in there. All right. It’s been a day already. How does throttling in Azure masquerade as query slowness? You know, a lot of it, well, I mean, of course, you know, the writing to transaction logs being throttled is quite, well, I don’t know how to, I would say it’s quite well-known and documented.

But I don’t think enough people are aware of just how crappy that gets. I hate that. But then, you know, you also, like, you also have the CPU throttling, and that’s not a very good time.

And that will feel like, you know, like SOS scheduler yield, like CPU busy and can’t do anything late, but really you’re just being governed to death. And then on the other side of that, you know, depending on the tier of Azure SQL that you’re in, your reads from disk might be making HTTP requests, and that feels real bad. So, like, that’s not exactly throttling, but it’s also just mean.

All right. That’s just unkind, cruel, mean-spirited behavior. And, man, I hate that. All right.

Well, that’s more than I planned on talking. I apologize. Took up too much of your time.

An entire going-on-18 minutes here, depending on how much you skipped around. So, I’m going to go do something else. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

And I will see you in tomorrow’s video, where we will talk about some other SQL Cerber-y stuff, unless anyone has a better idea. If you have a better idea, please let me know. All right.

Thank you for watching. Thank you.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

The Perils of Secondary Metrics in SQL Server

The Perils of Secondary Metrics in SQL Server


Chapters

Full Transcript

Erik Darling here with Darling Data, and in today’s video, we’re going to talk about one of my least favorite things to have to deal with in SQL Server, and that is secondary metrics. And by secondary metrics, I mean that when someone starts quoting problems to me, things that are going on with the server, there are a vast array of things that they will bring up that are just not really what you want to be looking at to figure out where your problems are, and what your problems are. And I’m going to talk through at least a few of those today, and then, I don’t know, maybe I’ll need to do some more in another video. But down in the video description, you will find all sorts of incredibly helpful links for you to, well, have me, I can come look at your primary metrics, fix stuff for you, right? You can hire me for consulting. If you want to learn a lot about all of the primary metrics that I care about, you can buy my training. There’s a coupon code down in the description below, even. You can become a supporting member of the channel. So right now, funny enough, in the background, I have, this is like one of those, man, we are cooked things. I’ll show you in a second. But in the background, I have Claude Sessions walking through and touching things in the GUI of my performance monitor, and figuring out if there’s problems and if things look good. I’ll show you that in a second. It’s neat. But anyway, if you would like free SQL Server and Postgres performance monitoring, I have just the treat for you. Totally free, open source, all the stuff that you would care about if you were monitoring a SQL Server, and you care a lot about good metrics that indicate you have problems, and having a tool that actually helps you find and solve your problems, not just one that says, I think there’s a problem, or one that says, hey, there’s a problem. If you buy some consulting, I can fix it, right? Because I don’t sell that with that. If you get this, you don’t need me, right? This is free. This is a labor of love, because the people of the world have suffered too long under the yoke of big, of big, awful, ugly, paid monitoring tools, or ones with in-app purchases that suck. So get a good one that’s free and doesn’t try to bend you over. I will be at Pass Summit West in Seattle, Washington from November 9th to 11th. I will be doing a regular session called Read Committed is Cruel by Default. It is all about my least favorite isolation level in SQL Server.

And I have a pre-con where I will be teaching advanced T-SQL that doesn’t suck. So you know, when you get back to your computer and you need to harangue whatever robot companion is being foisted upon you in your performance tuning endeavors or your performance analysis endeavors, you can be better informed and tell them when they’re being stupid. All right. Anyway, let’s just minimize this. Oh, I minimized the whole thing. That was silly of me. I hit the wrong button. So this is what I have going on in the background. This is some stuff that Claude is working on. At various points, you might see the GUI pop up and you might see it walk through. Otherwise, you’re just going to see a bunch of boring text behind me.

But I don’t really have anything else to put on the screen while I talk about this. So I’m just going to ramble off the top of my head a little bit. So secondary metrics, right? Things that, you know, don’t particularly hold any value when you are doing a SQL Server analysis. But still people maintain their value. These things have the, like this advice has the half-life of like your average Twinkie, like 2005, 2008, like mental level mentality.

People just aren’t looking at stuff that, in metrics that better suits the world around them, the realities of hardware changes. It’s all very strange and parochial. And I don’t quite get it. So like one of the, probably the most famous one is page life expectancy.

You know, like you can, you can tell someone like, like it’s just been so ingrained in internet lore and culture around SQL Server that PLE 300 good, PLE not 300 bad, or under 300 bad. Like people just feel very safe repeating it because it is such a meme that, you know, it’s just like, like they can get away with it, right? Like, like no one’s going to be like, oh, well, I mean, go find an article that disagrees, right?

It’s like, it’s just a very safe thing for people to say. Like, and they’ve been trained to do it. And unfortunately are, you know, the, you know, the, the robots, they have been trained in all of the same ways and on all of the same material that, that a lot of these people have.

And so they will, they will also come back and be like, ah, bro, PLE sucks. And you have to patiently explain to them that PLE is not what we care about. Um, instead of looking at PLE or page life expectancy, you should be looking at wait stats.

They are a primary metric, uh, page IO latch waits, uh, will absolutely tell you when, uh, if you are spending a lot of time reading pages from disk into memory, um, and that could be happening either because your server is far undersized for your workload, or it could be because you have, you know, big memory grants, which could be accompanied by resource semaphore waits, uh, that are popping up and see, this is no hands. This is, we are cooked, right?

This thing is just clicking around GUIs, looking at things on its own. Uh, so, uh, uh, it can do all this stuff, right? But it still thinks PLE is a good metric to look at.

Anyway, uh, yeah, look at wait stats. Page IO latch waits tell you when you are, uh, reading pages from disk. They could be accompanied by resource semaphore waits of query memory grants are ganging up on your server.

Otherwise, it could, you know, it could be either, you know, again, your, your, your data is far oversized for the amount of memory you have, or you have queries asking for big memory grants that might be, uh, knocking a lot of buffer pool data out. Uh, disk queue length is probably another, um, another one that comes up, probably not, obviously not as frequently as, um, as, uh, page life expectancy.

But just one of those things where, like, people are like, oh, my disk queue length, it’s huge. And, like, just no one knows what’s good or bad or what a number is. But, like, just modern storage, like, sans, like, like, like, disk queue length probably made a lot of sense to look at when you could, like, hear your hard drive, like, like, you walk into a server room and play your, you’re, you’re up to something and you can even, like, click, click, click.

Uh, well, actually, when it clicks, you have to replace it. But, uh, like, when you, like, when you could point at a drive and say that’s what, like, it had, like, a little, like, sticky note on it, like, L, right? Like, this is where the logs are.

Like, like, that’s when you would have cared about that sort of thing. Sans, NVMe, um, flash drives, you know, anything where storage is sort of, like, obfuscated or, uh, there’s an indirection or, like, an abstraction layer, uh, disk queue length just doesn’t make as much sense to look at anymore.

Um, query books, you know, uh, anytime you’re looking at a query plan with someone, uh, and they start talking about costs and percentages, uh, just hit them with the proverbial fish because those are all estimates, right? They are pre-execution estimates.

They are how you got to the plan you’re looking at. If you’re looking at a query because it’s slow, there’s a pretty good chance that those percentages are wrong, right? Because that you got a plan that didn’t fit, right? So the percentages were off, the estimates were not correct, and you got a plan that was not good for, uh, your, um, not good for your query.

Uh, logical reads are another one that just will not go away. Um, like, I, I absolutely agree that they tell you if your query is doing logical reads. What it’s not telling you is some other important stuff, like, how long did your query run?

Like, they don’t tell you if your query was fast or slow. If you look at CPU and duration, that tells you, that gives you a better gauge of a lot of things. Uh, one, how long the query actually took duration, that’s wall clock time, and B, CPU effort, which, you know, if you’re in the cloud and you’re tuning for a lot and you’re trying to reduce costs, tuning down logical reads isn’t going to necessarily turn down, tune down CPU.

You have to work on the CPU, you know, if you want to get us and use fewer DTUs or downsize your servers, stuff like that. So, like, like, they just don’t tell you fast or slow. And the better, the even better part about looking at CPU and duration is you can make other assessments along the way.

Like, like, is duration much higher than CPU, right? Because that means that something was in the way of your query doing meaningful CPU work. You were not, like, uh, your query just wasn’t able to make progress on CPUs.

If duration was much higher than CPU, if you have a parallel plan and, uh, CPU, is not, like, close to DOP higher than duration, then you had a pretty ineffective parallel plan. So, there are, like, better things to look at, right?

Because just doing reads, like, sure, sometimes it means you can create an index and do fewer reads, but, uh, for me, it’s just, like, not a very interesting thing. If you want to look at any reads metric, physical reads are really the interesting one because if you start looking at queries that do a lot of those, those are most likely the ones that are pushing the page I.O. latch waits that we were just talking about up.

Uh, uh, what else? Um, uh, index fragmentation, uh, that still comes up quite a bit. Um, people get very, very worried about that.

There’s still a lot of panic at the, panic at the, the, the disco about, um, you know, a lot of pages being logically out of order. But, like, a lot, for the same reason that looking at stuff like disk queue length doesn’t make sense on modern storage hardware.

Like, to the, to the, the same point, like, like, caring about logical fragmentation or pages being out of order doesn’t make sense when there’s not a drive physically spinning around. This leads to people doing all sorts of goofy things like tinkering with fill factor.

Uh, it’s another, like, if you’re at the point in your workload tuning process where you’ve taken care of everything else and there’s not a single other problem and you, the only thing left for you to do is go table by table, index by index and figure out if fill factor is your problem, there’s a good chance that you should just move on to something.

Like, like, like, like, you’ve, you’ve, you’ve crossed, like, you’ve made it to the pearly gates of performance tuning if that’s where you’re at with things. Um, I’m not saying that it’s never anything that you should look at, but, like, if you, the chance, like, most people who I run into who are looking at it, you know, boy howdy, they have, like, 15, 20 things that are actually problems that they should be addressing, but they’re just like, no, no, no, no, this fill factor thing, I gotta, I gotta work this out first. Uh, nothing, nothing, nothing, nothing gets done until fill factor is decided. Um, you know, uh, you know, people still put a lot of, um, emphasis on, like, having a lot of missing index requests and, or, like, like the, like the missing index requests themselves. Um, you know, they’re, like, I guess if you look at the total number of missing index requests, you could sort of use that to take the temperature of the server a little bit, but, uh, you know, like most likely you’re better off looking at the tables themselves and you, because, you know, if you’re looking at a table that just has like, uh, like maybe like a clustered primary key and not really any other nonclustered indexes, um, like there’s probably a whole bunch of indexing you could do, but, you know, just like the sheer number of missing index requests, um, you know, you could, I see missing index requests, like, like 10, 15 of them on a table that already has 25 indexes on it. So, like, sure, it probably means that you could adjust your indexes a bit, but, you know, those missing index requests, they’re very opportunistic. They have, they, they’re judged very quickly. They are not well thought out. Um, so you could use them to correlate a little bit, but don’t, like, don’t go crazy overemphasizing on those. Um, let’s see, uh, you know, uh, for wait stats, you know, people still tend to get worked up about CXPACKET waits and they try to, you know, really over-engineer, um, like the, the best max stop and cost threshold for parallelism settings, uh, you know, it’s, it’s, it comes back to, like, something that I’ve said a lot of times in that, like, a lot of the settings that you are setting globally at either the server level or the database level, they’re not going to be perfect for every query, right? That’s maxed up, that’s cost threshold for parallelism, that’s, you know, isolation levels, so many things. Um, they’re, they’re okay for the majority of the workload.

They are reasonable guardrails for most of the queries that run. It’s our job to kind of get out there and figure out, like, which queries need to break those rules, which queries need to abide by a different set of rules in order to run, like, more, run better, right? Just break free. Um, you know, but a lot of people do still look at CXPACKET waits, freak out, and just start changing numbers randomly. They confuse which one is which. The number of servers, uh, I’ve, I’ve looked at that had maxed up set to 50 and cost threshold for parallelism set to eight. Uh, I’m just amusing at this point. Um, maybe a little, maybe not so amusing. Um, I don’t know. I guess there’s still a place in the world for people like us. Um, let’s see. I don’t know. I think, I think that’s probably good enough for, for now. Uh, but again, um, you know, all of these things are, uh, all of, all of the things that we’ve talked, uh, rather, uh, we’ve talked about both sides of it, so I can’t say all of them, but, uh, all the main points are that, um, there are a lot of secondary silly things that people and robots confuse with, uh, good metrics to look at in the SQL Server to judge performance, uh, what you need to do to fix performance problems. And, uh, I just see people messing this up constantly and, uh, you know, running to all the wrong things and not, um, not paying attention and focusing on the stuff that actually makes a difference to queries running better and people being happier with the server in general. So, uh, that’s probably good enough for now. Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you, uh, next Tuesday for office hours. All right. Thank you for watching.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

Why the Version Store won’t clear if any database has open transactions

Why the Version Store won’t clear if any database has open transactions


Chapters

  • 00:00:00 – Introduction
  • 00:01:32 – Video Description and Links
  • 00:03:15 – The Version Store and Open Transactions
  • 00:05:32 – Explanation and Transaction Management
  • 00:08:45 – Cleanup Watermark and Transaction Rollback
  • 00:10:00 – Conclusion

Full Transcript

Erik Darling here with Darling Data and in today’s video we are going to talk about why the version store won’t clear if any database has open transactions. I realized that I read it a little bit differently than it looks there because I had to say it is what we’re talking about where this asks a question, right, so the words don’t precisely line up. But don’t blame the robots for that. That looks weird in the transcript. Anyway, it sounded weird in my head as I was trying to phrase it because I’m looking at the words and trying to rearrange them. That was tough. I’m not as sharp as I used to be. Anyway, down in the video description, many very helpful links for you and I to interact in ways that are largely profitable towards me, but you might get some sense of kinship or, I don’t know, joy. Something from exercising when you click on them. For instance, you can hire me for consulting. You can buy my very, very reasonably priced training, which you will get for life. You can become a supporting member of the channel. You can ask me office hours questions. And if you really, really like the things that I’m doing here or any portion of the content, like, subscribe, tell a friend, all that good stuff.

That’s all appreciated. If you want some free SQL Server and Postgres, including Aurora Postgres performance monitoring, you can have mine for free at code.erikdarling.com. Go to the performance monitor repo. From there, you can download, you can run, you can get the light version and just double click something and add your server and just immediately start monitoring it. If you need something beefier, more enterprisey, you can get the darling version of it, which is a headless Postgres. Postgres service, then it can monitor like 500 databases. It’s wonderful. Or 500 servers. I mean, if you have 500 servers, you probably have 500, at least 500 databases. That lines up. Anyway, it’s free. It’s great. You should check it out. I make the same amount of money either way. The only difference is how much you spend. I will be attending. I will be attending as a speaker with both a pre-con and a regular session past Summit West this, this Seattle, Washington in November, this, this November in Seattle, Washington from November 9th to 11th at the lovely Hyatt Regency Seattle.

Never been to that one that I can remember. May have probably have walked by it. I won’t be staying there though. Anyway, let’s talk about this here. Now, this is an explanatory post. My dear friend, Brent, wrote a post called, the version store won’t clear if any database has open transactions. The post is, no notes. Everything in here is right, but there’s just no explanation. Much like Brent, it is wildly salacious and utterly bereft of an explanation. It’s a joke. If we didn’t have jokes, we’d have to just fall asleep and die. Why would you want to fall asleep and die? You’d miss out on so many good things.

Anyway, let’s figure out which of these blue icons is Management Studio and let’s talk about why that happens. There was no why. You can’t leave people hanging like that. Getting blue parted various ways. It’s no fun. So, the explanation for it is SQL Server made by very smart people. And very smart people anticipate the wild and crazy things that you out there in the world do. So, at any given time, a transaction, begin transaction. SQL Server doesn’t look forward through the entire transaction and say, ah, this is safe. It doesn’t do that. SQL Server says, well, hang on. This numbskull has begun a transaction. Who knows what they’re going to do with it?

And that transaction could become a cross-database transaction. And if it becomes a cross-database transaction, then the version store across those databases must agree. SQL Server doesn’t know what database you might escalate a transaction to. So, it doesn’t know. It has to keep all of those version stores safe. And I’ve got a bunch of queries here that will show you what I mean here. So, if we run this set of queries, this top section is completely empty for a good reason. There is no query currently doing anything.

The second section shows currently the size of the 10db version store and persistent version store and stuff. I have different demos where I show this buildup, but it’s unnecessary for what we’re doing here. And there’s also this row here, which shows that the Stack Overflow database, it’s the only one that has accelerated database recovery on, which again is unimportant to this because accelerated database recovery does not help with this scenario.

Because the version store watermark has to agree across all the databases. So, ADR doesn’t help. You would think that the persistent version store would help, but it does not. It’s a shame, but there we have it.

Anyway, the reason why that thing has anything to it is because if you’ve ever seen the PVS pre-allocate way, SQL Server pre-allocates pages to the version store. So, when you start putting stuff in there, it doesn’t have to allocate pages immediately. Isn’t that thoughtful? But the important part down here is this final result set where we have a cleanup watermark XSN.

We have the oldest active XSN. It’s probably transaction, but I don’t know. Anyway, oldest active session ID and oldest active database, right? So, we’ve got these columns in here, but they’re all null, right?

But the important ones, well, I mean, there are a few important ones to keep track of. This is kind of one of them right there, right? This one’s important. So, well, these will become important in a moment.

If we come over here to this tab, we can run, we can start a transaction in any database, right? And all I’ve done is run a select query, right? Using snapshot isolation. Now, with snapshot isolation, you don’t immediately start, like, you know, you don’t create your snapshot time when you say begin transaction.

You run it when the first query runs. So, right now, that select count from sysjobs, that marks the start of when we need to start caring about what point in time things are in the database.

And if we come over here to this tab now and we run this query again, things look a little bit different. Now we’ve got this line up here and we can see that we have this old sleepy thing that all it’s done is, I don’t know, some nonsense, right?

Nothing of value. It’s selected a count from a table. This is the same. But now that result set that I told you would become very important is down here, right?

Our cleanup watermark has incremented, right? We can’t clean stuff up until that thing up there goes away. We now have this and this, right?

We have our session ID from up here, right? 52, right? And that is holding this 2686 number hostage. And we can see our oldest active database with the transaction is MSDB.

There wasn’t a great way to just show this as one, but you can see that that’s the thing there. So if you ever want to know why SQL Server behaves this way, it’s because you probably have a bunch of databases on it.

If you don’t have a bunch of databases, well, you don’t have to worry about other databases messing up other databases. You just have the one database. Of course, you probably do have MSDB on there. So someone could technically, even with just a system database, give you a hard time.

But we hope they don’t do that. But this is why it happened. SQL Server has to think about all the dumb things that people do. And one of those dumb things is, of course, cross database transactions where we need to start caring about all of these versions, agreeing at what point in time queries can start reading stuff.

So come over here and we roll back our transaction. Let’s highlight the right stuff. There we go. There all that goes. And we rerun this.

All of a sudden things have returned mostly to normal. This result is gone. These have stayed the same. And this one here. Now we have a new cleanup watermark that is slightly higher than it was before. And we don’t have any transactions keeping the watermark where it is and making other databases unable to clean up the version stuff.

So if you read that post and you said, why on earth would that happen? Now you know. If you didn’t read that post, you could still if you want. If you feel like it.

It’s not a waste of time. Anyway, where was I? Thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you in tomorrow’s video where we will, in longer form, answer a question from yesterday’s Office Hours video. Anyway, goodbye.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

SQL Server Performance Office Hours Episode 91

SQL Server Performance Office Hours Episode 91



To ask your questions, head over here.

Chapters

  • 00:00:00 – Introduction
  • 00:04:30 – Query Timeouts When Blocking Isn’t the Cause
  • 00:06:46 – Using BETWEEN for Date Ranges
  • 00:07:52 – Slow Parallel Repartition Streams and CXPACKET Waits
  • 00:10:32 – Parallel Page Supplier, Row Distribution, and Memory Grants
  • 00:12:28 – When Query Store Hides Real Problems
  • 00:15:53 – Wrap-Up

Full Transcript

Erik Darling here with Darling Data. I washed my hair and it looks kind of stupid today. I’m an idiot. Never learned my lesson. Keep showing up on videos with stupid hair. No wonder the AI is winning. Anyway, it is Tuesday, at least as far as I can tell. So, it is time for another delightful, delectable edition of Office Hours, where I answer five, of your questions. How do you submit those questions? That is a great question in and of itself, isn’t it? A question that insists on itself. It’s down in the video description. You can find the link where you can plop whatever text you want in there. Say what you will about me. I don’t know. Just ask me questions about stuff. That’s what I’m here for. I’m here for you. Who else do we have? Not each other. There are also other helpful links for you down there as well. If you would like to hire me as a consultant to make your SQL servers go faster in exchange for money or whatever that stupid line is. I make SQL Server faster in exchange for money. Hey, there we go. Marketing is not my strong suit. You can also buy my training. You can become a supporting member of the channel. Ask me office hours questions like I just said.

And as always, if you enjoy this content, if you would like to receive some form of notification when I post new things here, please do like, subscribe, tell a friend, all that good stuff. Anyway, free for you in the world. Gratis. Gratis. On the arm or something like that. Database performance monitoring for both SQL Server and Postgres. Also covering Aurora Postgres. Very, very popular with the kids these days.

Totally free. Totally open source. There’s no email sign up or phone home stuff in it. I gotta edit this a little bit. It’s, you know, a bunch of collectors going off and doing stuff. I get, you know, the, the light version, good for sort of like one off short term stuff. And then I’ve got the, the full on headless darling monitor that runs as a headless window service logs stuff 24 seven has all sorts of cool MCP web view, alerting, you name it. It’s all in there, making your life better, easier, having things suck a little bit less. If you want to check it out, it’s at code.erikdarling.com. You can head over there and download, install, have a robot download and install it. I don’t care.

However, whatever, whatever, whatever is easiest for you. Pass Summit West is coming up in, ah, boy, just about 30 days. Amazing how that works. I will have a pre-con there about advanced T-SQL that doesn’t suck. Um, we’ll give you, uh, we’ll give you, uh, the, the confidence to not only write your own T-SQL that doesn’t suck, but also, uh, inform the robots when they have done something stupid because the robots, uh, still do plenty of stupid things that need correcting. We need to, we need to remain, uh, a little bit of humanity in all of our loops, various loops in the world.

Well, they also have a session called Read Committed is Cruel by Default, where, uh, I will talk to you about, uh, the, the worst decision Microsoft ever made. And that was, uh, making Read Committed the default isolation level for SQL Server. But, uh, this is no longer valid. It is cold today. I’m wearing socks in the house. It’s cold.

Oh, I hate it. I hate it. Anyway, I don’t know. I hate the heat. I hate the cold. I just want it to be warm enough to smoke cigarettes and underwear. There we go. Uh, all right. Hi, Eric. Hi, you. Uh, there.

Have you ever diagnosed the query timeout issue where blocking was not the root cause? Uh, RCSI is enabled for the user database. If yes, then what was the reason for the query timeouts?

The select query is throwing timeout errors. Uh, I, I’m, I’m assuming that you mean, like, an actual timeout, like, it is, it is, it run longer than, uh, it was budgeted, uh, and allowed to run for, uh, in which case, there are so many reasons. There are so many reasons why a query might timeout. There are, there are, there are hundreds of millions of them. Uh, so I, I, I assume that you have the select query. Uh, the best thing that you could do would be to look at the query plan for the select query. Uh, presumably, uh, you could get an estimated plan for it, uh, and just see if there’s anything, uh, a muck in there.

Uh, if you’re able to get an actual execution plan and let it run to completion, that’s cool. A lot of people have trouble with that. So, uh, what you should probably do, if you, if you can’t, is, uh, turn on actual execution plans, uh, start running it. And then in another window, run SP who is active with get plans equals one, the parameter get plans equals one on, then it will capture the in-flight actual execution plan, uh, uses a different DMV, right? Uh, and when you do that, you can see where time starts accumulating in the query plan, not costs, not other stupid percentages, but time in the query plan starts accumulating. And when you do that, uh, you can start to see where things are slow in there as the query is running. So, uh, I don’t know why your query is timing out. If you would like me to figure that out for you, uh, my rates are reasonable. Uh, if, if, if you want to go this on your own, well, that’s, that’s what, that’s how I would approach it.

Uh, um, if you have query store enabled, uh, you might find the aborted or timed out, uh, query plan in there, which might give you some sense of what was happening. Um, but, uh, I don’t know exactly why yours is. And I’ve seen too many queries run slowly in timeout for too many different reasons to list them all here. Uh, it would far elapse my budget for standing here and talking. Um, I heard somewhere that you should use, uh, I should highlight these, shouldn’t I? I’m not doing a good job. I’m a bad presenter.

I’m, you know, lousy and cranky, fluffy hair. Uh, I heard somewhere that you should use greater than or equal to and less than or equal to instead of between when querying for date ranges. Is this true? Uh, not for any reason other than code clarity. Um, not everyone understands the full semantics of between. Uh, they don’t understand that it is an inclusive, uh, range on both sides.

Uh, and a lot of people, uh, when they, they, they find that out, they’re like, oops, because really they wanted greater than or equal to and a less than, uh, in between it would include too much stuff. So, uh, if, if, if you want to use between and you know exactly the semantics of it, uh, that’s cool. A lot of other people might not.

So, I, I do prefer to use the greater than, equal to, and less than, equal to, um, for that reason. Let’s see here. Ba, ba, ba, ba, ba, ba, ba, ba, ba, this is gonna be a long one.

I have an actual execution plan that takes hours, but the only lengthy operator times are two parallel repartition streams after a relatively small index scan. Repartition streams feed into a parallel many-to-many merge join, and all of the rows are on a single throw. Ah, the query only does 30, I’m not even reading those words out loud because they are useless words to me, uh, and uses seven seconds of CPU.

How do I figure out what it is waiting on? The only significant wait stat is CXPACKET for the whole duration. Everything else is almost there.

All right, so, um, what I would do is, is not actually spend any time trying to figure out what it’s waiting on. I’ve seen this, this situation too many times. It, it’s not a fun one to debug, uh, and, and a, a lot of the, the weights and other stuff that you’ll see aren’t very helpful.

Like, it, it certainly could be, like, a work table in the, in the many-to-many merge join racking up, like, doing all the duplicate-y stuff that it has to do. Uh, I, I, just don’t do it. Um, if you want to do yourself a big favor here, um, just run the query with an option hash join hint.

Uh, get rid of the parallel merge join, the, the, the mystery solves itself. Um, that, that, 99% of the time, that, that is the solution to the problem. Parallel merge joins are a terrible mistake.

Uh, you should avoid them at all costs, because stuff like this gets really hard to figure out. Um, I, you, you could be dealing with, like, some sort of, like, weird exchange buffer thing going on. Uh, you could be getting close to a parallel deadlock, or just, like, you could have exchange spills and stuff.

Uh, it, it all sucks to deal with. Just throw a hash join hint on this thing and be done with it. Um, if there are other order-preserving operators in the plan, like, say, a stream aggregate, uh, a hash group, uh, hint would also be useful.

So, option hash join, option hash group. Um, if you are in a situation where perhaps you would like to take advantage of batch mode, uh, because the, good, batch mode query plans tend to get rid of the, the, the parallel exchanges. You see far fewer, like, repartition streams, distribute streams, and stuff, and then, uh, try to use one of the normal tricks to induce batch mode in there.

Uh, but I, honestly, I would just start with option hash join and watch the problems go away. All right. How does the parallel page supplier affect row distribution and memory grants and real work?

No, I only care about fake workloads, uh, being a fake consultant who could never make it in the real world. Uh, so, the parallel page supplier is an interesting thing. Uh, it’s what’s responsible for handing rows out to parallel, to parallel threads in a parallel execution plan.

Uh, and sometimes the, the hashing mechanism that it uses, well, the mechanism that it uses to distribute threads, uh, usually the one where it ends up, um, uh, unevenly distributed would be a hashing mechanism. Uh, fellow co-blogger Joe Obish has a very cool post on my site about, um, uh, picking a range of values that all end up hashing to the same thread. Uh, so, usually it’s like, you take, like, the set of key values and you say, like, modulo dop, right?

So, your dop is eight, so it’d be modulo eight. And then, like, all those values hash to the same, uh, thread. And they all end, you get, end up with very uneven, uh, row distributions on the parallel threads.

Uh, that doesn’t affect memory grants, but what that does do is screw your memory grants up. Because, as I’ve said many times, all, all query plans start as serial execution plans. Uh, the decision to go parallel, parallel is made later, but the memory grant is derived for the serial plan and then divided by dot for the parallel plan.

So, if you end up with a plan where the parallel page supplier did a not-so-great job and modulo’d all your rows to one or just a couple threads, uh, and they are, but they all get equal amounts of memory, obviously those, that one or two threads is going to, um, uh, not have enough memory potentially to, uh, do everything that it needs to without spilling. So, that’s sort of how that goes. All right.

When does Query Store hide real problems instead of exposing them? Well, uh, there are, there are a couple ways that happens. Um, if your Query Store is set to auto mode, uh, then queries have to meet some certain eligibility requirements before they’re allowed to, before they go into Query Store.

So, you can miss stuff in that way. Uh, but also there’s a lot of stuff that happens before a query even gets going and does stuff. That, uh, Query Store can’t tell you about.

Uh, for example, there are, like, pre-weights that go into things. Uh, not compiling, but, like, uh, say you have threadpool waits or you have resource semaphore or resource semaphore query compile waits. Those are all things that happen, uh, technically before a query even starts executing.

And so, those things don’t show up in Query Store. Like, you can see those in other ways, right? Like, dang it. The secondary metrics, when we start yelling about stuff.

Uh, wait stats will show you threadpool. They’ll show you resource semaphore. They’ll show you resource semaphore query compile. So, you can get all that stuff from someplace else, right? So, like, Query Store doesn’t necessarily need to have everything.

Query Store is a great place to look for query performance problems. A lot of those things are sort of server performance problems. They’re, like, bigger. They’re outside.

They’re, like, just, like, anything that is a problem with a single query. It’s, like, your whole thing is crappy, right? No, the workload stinks. So, like, there’s just stuff that you, like, like, doesn’t need anything.

I’ve long been an advocate for more things going into Query Store. Like, the query performance stuff is great. But then, like, you know, you have the block process and deadlock reports, which are in completely different places, but are completely related to stuff that, like, you know, is part of query performance.

So, like, that, like, I feel like those things should be in there. Like, that should tie in a little bit better. But, you know, there’s some stuff that, you know, like, Query Store is not a full server monitoring tool.

If you want one of those, I got one for free, baby. Code.erikdarling.com. But, yeah, that can, that’s certainly one way. What else?

I mean, you can kind of see that. I think, you know, another thing that I think would be valuable for Query Store, and this is just one guy talking here.

It would be, there’s another DMV that got introduced into SQL Server that keeps a last actual execution plan around. I think it would be cool if Query Store sort of absorbed that and kept a last actual execution plan around, or maybe, like, you know, a number that the users can configure on their own and decide.

I want the last, like, because you can keep, you know, like, I think the default is, like, 200 plans for Query. Like, who would want that? But, yeah, I think it would be cool if you could decide if you wanted to keep some actual execution plans around, because going and getting them later sometimes kind of stinks.

And if you’re dealing with a parameter sensitivity problem, having a query plan around that demonstrates the compile and the runtime values when something may have been parameter sensitive is incredibly valuable, right?

Incredibly valuable stuff. Anyway, I’ve talked for too long. I’m going to go, I’m going to leave it up to you to decide if this is a tiny espresso mug or if I am just an enormous human being holding a regular size mug.

That’s for you to decide. But I’m going to go drink this now. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video.

It’ll be a surprise to both of us. All right. Thank you for watching. Goodbye. Bye-bye.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

SQL Server Performance Office Hours Episode 90

SQL Server Performance Office Hours Episode 90



To ask your questions, head over here.

Chapters

  • 00:00:00 – Introduction
  • 00:04:04 – When Plan Stability Hurts More Than It Helps
  • 00:06:10 – Prioritizing Tuning When Management Wants Everything Fixed
  • 00:09:52 – Spotting Concurrency Problems Before Users Complain
  • 00:12:25 – Tuning Advice That Does More Harm Than Good
  • 00:13:30 – Why SQL Server Makes Obviously Dumb Choices
  • 00:15:42 – Wrap-Up

Full Transcript

Erik Darling here with Darling Data, and we are office hours-ing once again. I apologize for the sudden dearth of videos last week. There were a few technical difficulties with the setup that I just didn’t have time to get to. We’re all sorted out now, though. Everything seems to be back in working order. Functioning camera, microphones on, beautiful background here. What else do we need, folks? What else do we need? Anyway, down in the video description, if you want to ask me questions for office hours like all the other nice people do, at least most of them are nice. Some of them, not so nice. Some of them, I don’t know. Trying to, really trying to get me down. I don’t know why. I don’t know what’s wrong with you. I don’t know what I ever did to you. Link is down below. Other links sprinkled throughout my very helpful video description. You can hire me for consulting. You can buy my training. You can become a supporting member of this channel for as few as $4 a month. You can sacrifice, like, one Midwest coffee a month and give it to me to say thank you for usually producing a lot of videos every week. Last week was weird, though. Anyway, also, if you happen to enjoy this content in any way, shape, or form or, you know, you think someone else might just want to hit mute and, you know, enjoy themselves, like, subscribe, tell a friend, all that good stuff. If you are in the market for something that doesn’t cost any money, I’ve got a deal for you. I’ve got the steal of a lifetime. Free SQL Server and Postgres database performance monitoring. If you go to code.erikdarling.com, there’s more links down below that are actually clickable. You can’t click this one. It’s just, it’s on the screen. I’m not quite that adept at YouTube. Anyway, all the stuff that you would want from performance monitoring tools. I got to do more videos about those. It’s really, really turn it into a thing. It’s amazing when I use it, like, I turn it, like, use it at, like, client sites and stuff and I turn on people just like, that’s free. Like, yeah, I like you. Anyway, I wasn’t free, but that is free. Maybe that’ll, maybe this will all take my place someday. I hope so. That’d be fun, right? Just ruin everyone’s life by, anyway. It’s great if you need that sort of thing. It is almost the end of September. I suppose it’ll be closer to the end of September when this actually publishes, but I will be in November.

I will be in Seattle for Pass Summit West, taking place at the Hyatt Regency at 808 Howell Street in the United States, November 9th through 11th. I have a pre-con there where I’m doing advanced T-SQL, called T-SQL that doesn’t suck, and I also have a regular session there called Read Committed is Cruel by Default, where I will teach you things about isolation levels that you, well, do you need to know any more. I mean, do you need to know anything? Maybe I will be in the middle of September 11th, taking place at the Hyatt Regency at 808 Howell Street, in the United States, November 9th through 11th. I have a pre-con there where I’m doing advanced T-SQL that doesn’t suck, and I also have a regular session there called Read Committed is Cruel by Default, where I will teach you things about isolation levels that you…

Well, I mean, do you need to know? Maybe you can just be better informed when you’re arguing with robots about them. That’s about what I got for you. But anyway, September is wrapping. This is not, this is this, the weather outside right now is not living up to this photo. It is very windy and rainy. The weekend storm. I know you’re seeing this on Tuesday, but right now there is a weekend storm outside, which is why I get to do this on a Saturday. Life’s grand. None of it matters.

Anyway, let’s go answer these questions. When is plan stability actually more harmful than helpful? I don’t know, actually. How about when you have parameter sensitivity issues? Sometimes one plan ain’t enough for a query. You need some, you need some planiamory or something. Some queries just need multiple plans. Other times, you know, I mean, there’s some obvious stuff that you might have going on. You might have some real issues with like, you know, indexes or perhaps the way the query is written or something. But yeah, some, some, some data patterns, some, some shapes of data do require some queries to take on multiple plan shapes in order to run quickly. That’s just the way of the world. A lot of queries, you know, you don’t have that problem with. They just run 10 million times a second in the background and you hardly even notice. Other queries, they, they blow up. And when they blow up, things get bad. Both from the perspective that you might have, like, let’s go a little bit further on this. If you have a situation where like a big plan and a bad plan don’t share well with each other, you, you get the little plan first, right? And all of a sudden, like queries that need big amounts of data really, really slow down and take a long time to run. And then you have the sort of reversi problem where the, the big query plan, uh, uses a lot more resources, right? Cause it’s like, oh, we have work to do. We’re, this is interesting. There’s a lot going on here. Uh, and, uh, you know, you get a big parallel plan, big memory grant, all sorts of other stuff happening. And then all of a sudden, lots of people start running that big plan and your server, uh, becomes bereft of resources rather quickly. So, uh, that, uh, you know, um, that is when plan stability can not be fun. Ah, it moved.

That wasn’t supposed to happen. Uh, how do you prioritize tuning when management wants everything fixed? Ah, that’s easy. Uh, you ask management what they do that’s slow. Cause as soon as they’re happy, they shut up and you can, you can go do all the rest of it. Like, uh, like, Hey, Hey boss, what’s slow. And like, uh, every time I hit this random layoff button, it takes forever. And then you go look and you’re like, ah, I see what the problem is. You’re ordering the whole company by new ID.

Um, that’s why. All right. I can use table sample and some other stuff to fix this up. Don’t worry about it. I gotcha. We’ll get those layoffs faster. Uh, but if it’s, if it’s not a case where management uses the software directly and has an opinion on it, um, I guess you could start by asking them like, um, like what do people who have their phone number complain about?

It was like, if someone like, if like some other honcho type is calling them like the gosh darn tootin cockadoody bird reported slow again, you’re like, well, let me just go find USP underscore gosh darn cockadoody report dot SQL. And you can take a look there. Uh, otherwise, you know, you can, you can talk to other people who have to deal with, uh, customers, uh, support people. Uh, often there is a trail of tickets. If you have a ticketing system where people have expressed their loathing of performance issues. Um, I, I, I have found, uh, robots to be very good at going through, uh, large, vast swaths of data like that and finding sort of repeated, uh, patterns and problems with stuff. They are very good at, um, that sort of mechanical labor. Um, so if you want to give, uh, I don’t know, like you want to give cloud access to your, like Jira, ACLI or whatever, and this is like, Hey, go for it, buddy. Uh, read away. Tell me what’s the worst stuff.

That’s, that’s one way of approaching it. Um, you know, other than that, I would, I would go with my sort of stock and standard methods. Um, there’s actually a very cool new, uh, parameter for, uh, SP quickie store called, uh, find high impact. Uh, I did a video about it where I used the wrong word like 15 times cause I was tired. And then Kevin Fiesel put it on his curated SQL thing. And like, there were a bunch of like, uh, corrections in it. I was like, ah, thanks man. I didn’t realize I screwed up that bad. Uh, there were, there were some things on my mind at the time. I think, I think a Pomodoro or something. Uh, there’s, there’s a hotel in France called, uh, Cheval Blanc and they, they make a drink called the Pomodoro, which is a tomato and mezcal drink. And it is absolutely delicious. And I was thinking about that at the time. Anyway, ah, there we go. Always comes back to Paris. Uh, but anyway, um, SP quickie store has the find high impact parameter and that seeks to score it rather than like, you know, like the normal sort of query tuning method, like sort the thing by CPU or duration or physical reads or memory or whatever. Uh, it scores your queries across a variety of metrics and finds the one, find the, find the ones that hurt you most across like a bunch of stuff. Right. And that breaks down like what percentage of CPU and duration and all the other stuff they use. So you can be like, Oh, I don’t like the looks of this one. Let’s, let’s go tune it. So that’s probably what I would do. Otherwise you could go to code.erikdarling.com, download an absolutely free monitoring tool and figure out what’s really driving your server baddie.

Cause it’s a pretty good way of doing that for free. How do you spot concurrency problems before users start complaining? Uh, well, let’s see. There are many levels to concurrency in the, in, in, in the database world. Uh, you know, at the, at the lowest level you have stuff like latches and latch contention. I guess you could go a level lower and do spin locks, but you know, don’t start there cause you don’t, you don’t want to start. I wouldn’t start with latches either. Um, you know, wait stats are probably a good sort of in the middle, uh, indicator of where you might be experiencing, uh, contention, but, uh, a level up from that would be sort of hardware resource contention. But, but the nice thing is that almost all of this stuff is available in wait stats. For example, uh, if you have CPU contention, you’ll see like SOS scheduler yield to be high up there. If it’s caused by parallelism, the CX weights will be high up there. Uh, if you’re having some form of like disk slash memory contention, uh, then you’ll see, uh, you know, lots of the page IO latch underscore weights because SQL Server ain’t, uh, keeping a lot of the data that it needs in memory.

And that could be because of big memory grants. And if you have a lot of big memory grants and you could be seeing resource semaphore waits. So, um, that’s, that’s usually where I start. Like, you know, um, it did, if you’re, if you’re just looking at a server for the first time and it’s been up for a long time, it’s not as valuable, which is why a good free monitoring tool that trends your wait stats over time would be really helpful for you to figure out where concurrency problems might be bubbling up from. Uh, and then, you know, while, while you’re in there looking at, at weights, you, you, you might spot some weights that start with LCK, the lock weights. Uh, and you might start looking in your free monitoring tool that you got from code.erikdarling.com. And you might start saying, Hey, uh, it’s, it’s got the block process report and it’s got the XML deadlock report in here for me. I can, I can figure out so many things now. All right. So that’s, that’s what I would do if I were me. And if you were me, that’s what you would do too. What an amazing coincidence that is. All right. Let’s see here. What tuning advice do you think does more harm than good overall? Uh, I’m going to talk more in depth about this in another video, because there’s a lot to say here. Uh, I think, um, looking at dumb metrics is, is harmful because a lot of the dumb metrics, um, they don’t really show you what the problem is. And so you end up doing things in sort of a scattered way that don’t address the problems you actually have. I think that’s a, probably one of them. Uh, you know, other stuff like, I just had a covering index, you know, that, that whole genre of dummy. Ah, I hope you fall off a building. Anyway. Uh, yeah, there, there’s a lot of it. Uh, but I’ll, I’ll, I’ll, if you don’t mind, I’m going to park this one and we’re going to, uh, do a longer video about that. I’m going to even write something. All right. Why does SQL Server sometimes make obviously dumb choices and how do you reason about them? Well, there’s, there’s a reason for everything. If, if you’re, if you’re, if by dumb choice, you’re talking about, uh, queries, well, it’s all down to costing, right? There’s only, there’s only so much time and effort that an optimizer can spend figuring a query out, uh, things that may be obvious to you as a human being who has vast quantities of time with which to explore various aspects of the underlying data and the indexes and, uh, many other things involved, uh, that, that make up, that comprise or compose. I forget which one.

One of those, uh, that, that, you know, make, make, make your system a big circle. Uh, they, the optimizer can’t look at all that stuff very quickly and come up with a query plan for you. It’s, it’s on, it’s on a timer. It’s on a budget. Well, not exactly a timer. It’s on, it’s on a budget.

Um, and it doesn’t want to spend a lot of time figuring like, you know, coming up with a query plan. And so sometimes, uh, you don’t get the best possible plan. Uh, sometimes it is, uh, that SQL server, uh, explored a path that you think is, or that you, you might observe is much faster, uh, but it costed that path out for some reason or another. And other times you might just find that the optimizer, uh, exhausted its budget and did not get to that obviously good path that you have surmised from looking at things, maybe testing the query with various hints and all that other good stuff that, that you do because you’re a smart person with unlimited time on your hands and the optimizer is something that has to spit out a query plan quickly so that management doesn’t start yelling about stuff, right? Cause if you’re waiting 30 minutes for a query plan, you probably wouldn’t be psyched. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you off in tomorrow’s video, uh, where I’m going to provide some technical details, uh, that I’ve been, it’s a, it’s a video that I’ve been meaning to record for a while that, uh, I just got around to you. So some technical details about something, well, something special.

Anyway, goodbye. Enjoy yourselves while you can. All right.

Going Further


If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.