📬 Post from Darling Data for 10/27/2026 📬

SQL Server Performance Office Hours Episode 92

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 post SQL Server Performance Office Hours Episode 92 appeared first on Darling Data.