Erik Darling Data Archives

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

Posted on October 21, 2026 by Erik Darling

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, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

SQL Server Performance Office Hours Episode 91

Posted on October 20, 2026October 21, 2026 by Erik Darling

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

SQL Server Performance Office Hours Episode 90

Posted on October 15, 2026October 21, 2026 by Erik Darling

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

Learn T-SQL With Erik: Time Zone Confusion

Posted on October 14, 2026 by Erik Darling

Learn T-SQL With Erik: Time Zone Confusion

Chapters

00:00:00 – Introduction
00:02:30 – Time Zone Ambiguity Explained
00:05:15 – Example with Non-Existent Time
00:07:45 – Daylight Saving Time Ambiguity
00:10:30 – Non-Deterministic Nature of AT TIME ZONE
00:12:00 – Conclusion

Full Transcript

Erik Darling here with Darling Data. Coming to you late in the day. I had a very fun problem that I had to deal with earlier, and that prevented me from recording according to my usual schedule, but it will be in a written, a formally written blog post tomorrow, you lucky people. Coming to you live with my Jermaine Dupree bobblehead, Atlanta Braves bobblehead. I did not attend this game. I had to wait until I found it on eBay, but now I have my very lucky Jermaine Dupree, Atlanta Braves bobblehead, who will be guiding me through this perilous video, where we’re going to talk about time zone confusion. This material, like a lot of the other videos that I do, comes from the larger corpus of Learn T-SQL with Erik material. You can buy it down in the video description. You can get a hundred bucks off the full course, which is a fantastic deal compared to other training, because you get this for life. Your entire life. Think about how long and arduous your life is going to be. And think about how more long and arduous your life will feel without proper T-SQL training. Ah, it’s, it’s crazy. You ought to buy it. Small investment, make your life feel less long and arduous. I think anyway. Anyway, down in the video description, on top of the link to purchase this very course, you’ll find other helpful links should you choose to interact with me in more interesting ways. You can hire me for consulting. You can buy even more of my training. I have lots more training than just this. You can become a supporting member of the channel for as few as $4 a month. You can, you can say, thanks, Erik. And that’s it. It’s just, it’s just a thank you. You don’t get anything extra. You just say thanks with money, because that’s if that’s how most people do it these days, right? It’s hard to say thanks with coupons. And of course, if you enjoy this content, and you think that other people might enjoy this content, please do like, subscribe, tell a friend, all that fun stuff, so that I feel some sense of obligation to keep recording this. Otherwise, I don’t know what I’m going to do. Just sit around and talk to robots all day.

If you need free SQL Server and or Postgres, including Aurora Postgres database performance monitoring, boy, do I have a deal for you. Steal of a lifetime. Totally free, totally open source monitors both. It’s a grand time for everybody. And does all the stuff you would want it to do. It’s got fancy WPF Windows dashboard. It’s got a fancy web viewer dashboard. You can you can make custom views and stuff. It’s got MCP tools. It’s the works really. It’s amazing what I’m giving away here. And it’s amazing how much money you can save switching to this from your current crappy SQL Server and or Postgres, including Aurora Postgres monitoring tool.

Just think, imagine how happy your company will be if all of a sudden you were like, hey, I’m going to save you many thousands of dollars every year, and we’re going to have a better monitoring tool where we can actually figure things out. And people aren’t just sitting around staring at it like idiots. It just might happen for you, right? Go to code.erikdarling.com, the performance monitor repo. Everything will be explained there. There’s a wonderful reading file. In just a couple short months, I’ve got to book my travel. I will be in Seattle, Washington from November 9th to 11th at Pass Summit West, taking place this year at the Hyatt Regency, Seattle. I hope they have a good buffet, I think.

I won’t be staying there, but I will be presenting there. Never stay in the hotel where the conference is. It might seem like a good idea at first, but it’s not a good idea. That’s why when people follow you to your room, creepy. Anyway, let’s drink some beer, pitchfork some crows, and I don’t know. Buddy, I’m going to chip your teeth on that corn. I’m going to tell you. All right. Anyway, so when it comes to time zone stuff in SQL Server, one thing that is important to note is that SQL Server does track time zones, and it uses the Windows registry. It got better at this, but it still uses the Windows registry for time zone things.

And one thing that you might notice if you ever look at the sys dot time zone info. Now, there’s no DM in there. I put that into DMV names frequently for no apparent reason. All it tells you is the name, the current offset, and if that is in daylight savings time. There’s just standard time. There’s no daylight times listed in here, right? It only tells you standard time, current offset, and if that current offset is being respectful of DST.

Who knows if this will change if America decides to get rid of one or the other? I forget which one we’re getting. I can’t remember if we’re getting rid of the good one or the bad one. It’s tough to keep up with the news these days. But lots of other places have changed this too over the years. And depending on when you started logging time zone information, it could be right, wrong, or somewhere in between.

I did some research. I think there was maybe a Wikipedia article on all the time zone changes over the years. And, boy, people just keep messing with it. I guess it’s just a fun thing that no one can agree on whether to switch times or not. Personally, I’m not a fan of switching times. Not only because it’s confusing and because I still have things that I have to set manually.

Like the watches that make people mad and the times on the microwave and stove, which are not smart appliances, apparently. But anyway, I suppose we should run a query here, shouldn’t we? So if you’re doing DST handling, you do need to consider all of these things, right?

So let’s look at this query, which is, I don’t know, this query is actually a little bit long for my tastes. Eh, look at this thing. 50 lines? Something like that. So at various points in the year, various times in the year, you will have slightly different offsets and whatnots.

But UTC at least stays nice and faithful to what time it is, right? You can’t have a UTC that switches times on you. That would not be fun. This demo wasn’t particularly interesting, but it sets us up to talk about more interesting things later.

Like certain times that never happened and in certain times that happened twice. And if you’ve ever scheduled SQL Server agent jobs and they’ve ever run around a time switch, you’ve felt this pain. So again, UTC time zones just don’t have a daylight thing, right?

They just like, is currently DST? No, because they just don’t. So that’s one reason, again, in favor of storing everything in DST, because if you’re using UT, sorry, storing everything in UTC because there’s no DST.

And so you save your, you diminish your problems by at least two hours on two days a year, depending on, I guess, it depends. Does everyone switch clocks at the same time? I don’t think so.

But DST does create two problems. When you spring forward, the clock jumps immediately from 2 a.m. to 3 a.m. So the hour between 2 and 3 is like it never happened, right?

Back when yours truly was a bouncer in the clubs, this was a confusing time for people. As is the fallback where you might run into an ambiguous time, right? Because is it 1 o’clock or is it 12 o’clock again?

Like, what do we do here, right? It’s nuts, right? So when we spring forward, right, the first 2.30 never existed on 2026.03.08. And so at time zone, just hands you 3.30 to 4 instead, right?

It doesn’t error. It doesn’t say, ah, hey, careful here. It just says, hey, you’re an hour ahead now. Congratulations, right?

So let’s say that the date time that we wanted was this one. Where is my draggy thing? There is my draggy thing. Let’s move you up a little bit. Actually, you know what?

That wasn’t a perfectly good place before. Why did I mess with anything? I’m an idiot sometimes. So this is our date time that we’re asking for. 2026.03.08 at 2.30 in the manana. And when we say, hey, SQL Server, what time is that in Eastern time?

It says 3.30. 2.30 doesn’t exist. I’m sorry.

It’s not here. It might be somewhere. It’s not here, right? So just a fun little thing that might happen to you. Well, one hour and two days a year. One hour, one day a year.

But the second day of the year is even scarier, right? So 1.30 a.m. happens twice on 2026 1101. That’s right around past.

I’m going to have two mornings where I’m confused. So what do you name the second 1.30 a.m.? What do you call a time that you’ve already experienced? Is this just deja vu?

I don’t know. Quite frankly, confused by the whole thing, right? But we have the same local time with two actual real moments in time that happened. And if your table stores that same naive local value, those two rows are indistinguishable forever and ever, right?

You better have some unique constraints, buddy. You better have them, right? So if we run this query again, very long query.

Look at the size of this thing. Gigantic, right? Ah, we got to shrink that in a little bit. Let’s bring that in for a nice hug. Our local time, right?

2026 1101 at 1.30, right? And we say, ah, what about at Eastern time? The first time it happened, right? That’s 1.30 minus 4.

And if we look at that time in UTC, right? What do we see? Well, that’s 5.30 plus zero. Okay.

That seems sensible. But then it happens again at 1.30 minus 5, right? And in UTC, ah, man, you’re going to make me sweat and do all this column clickery, jaggery stuff. Do it!

Now, all of a sudden, it happened again at 6.30, right? So, in Eastern time, when the time switch, it happens once at 1.30. Oh, sorry. It happens once at 5.30 UTC, and then it happens again at 6.30 UTC.

Time was a mistake is really the big deal here, right? But once that transition is behind you, the ambiguity, well, the ambiguity of the moment is gone, but the ambiguity in your data might persist forever, right?

So, you know, once that moment is over, all of a sudden, everything seems back to normal, but the data in your tables might not be back to normal. One other thing to note about at time zone is because of all this foolishness, it is non-deterministic, right?

So, if we try to create a table, and we try to say, we’re going to get ahead of this time zone stuff, we’re going to have good performance, we’re going to stay on top of time zones, and we try to create a persisted computed column, we can’t do that, right?

Because it’s non-deterministic, right? It’s not going to happen for us. So, because it’s not deterministic, that means that, though we can add it as a non-persisted column, that means we cannot index it, because this is also non-deterministic, which is a real, real sad state of affairs.

We can’t even have deterministic time in SQL Server. What kind of fake database is this? Boils the blood.

Anyway, it’s time for me to go. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s written blog post about query store stuff. You’ll love it.

Or hate it. I don’t know. I don’t know what you’re responsible for. But goodbye from me and Jermaine. Alright. Have a good one.

Going Further

SQL Server Performance Office Hours Episode 89

Posted on October 13, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 89

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:30 – Office Hours Overview
00:03:45 – Monitoring Tool Update
00:05:15 – QueryStore and Performance Monitor
00:07:00 – Plan Cache Issues
00:08:25 – Query Store in Cloud Environments
00:10:00 – Conclusion

Full Transcript

Erik Darling here with Darling Data, and thanks for noticing, I did just take a shower. Anyway, it’s time for Office Hours, where I answer five questions from you happy, lovely people, and hopefully make your lives better. So, we’ll do that. Down in the video description, as always, you’ll find very, very helpful links for you. To interact with me in various ways that will be advantageous to the both of us. You can ask me Office Hours questions, down there. You can purchase training from me, which is great training, at a very reasonable price, and you get it for life.

The rest of your God-given life, it’s yours, in your hands, forever. You can also hire me for consulting, which, I mean, if you want to do that for the rest of your life, too. Who am I going to argue? I argue with you. That would be silly.

And of course, if you enjoy this content, please do like, subscribe, tell a friend, and all that good stuff. My free database performance monitoring continues to grow, get better, improve. I am fully reworking, right now, the analysis engine.

SQL Server always had it, but I wanted to rework some of the stuff in there. And as part of that, I am also reworking the analysis engine for the Postgres side as well. Not reworking, implementing, porting over the SQL Server analysis engine to Postgres, the Postgres data collection as well.

So you should see that coming up in the next release this week. Big things afoot here at Darling Data. If you’d like to try it out, go to code.erikdarling.com.

It’s the performance monitor repo. Again, totally free, totally open source, doesn’t require you giving me an email address or any of that stuff. And it’s got all of the best stuff that you would want, desire, stuff that you might not even know you wanted in a monitoring tool.

It’s absolutely phenomenal piece of work. My only remaining speaking engagement of the year is Pass Summit West. That is happening in Seattle, Washington, November 9th through 11th.

I’ve got a pre-con and a regular session there. Pre-con is T-SQL that doesn’t suck. We’re going to teach you how to overcome the dumbness of the robots.

And then I’ve got a regular session called Read Committed is Cruel by Default. And it’s about my least favorite isolation level. But for now, it is still September.

We are still wiping the sweat from our shiny visages. Under carriages and trying to just make it through the day. So let’s go answer these questions.

Well, hello, Eric. Hello, howdy. How’s it going? In our application, in every update, we drop create all procedures and triggers. That’s, you know, you can alter them.

You could do it that way. We do not use QueryStore. What?

God’s green earth is wrong with you. Maybe you don’t use QueryStore because you’re on an older version of SQL Server. I don’t know. Could we trust Performance Monitor or it may get confused result-wise with the drop create thingy? No, it just resolves the object IDs when it collects stuff.

So it’ll figure out your procedure and object names based on the ones that come in. Assuming that you’re actually making changes to procedures and triggers. No, actually, no.

That’s like all, that’s either, yeah, it’s all like query hash, query plan hash. So like if you make changes and stuff generates new plans, then it’ll, you know, it’ll re-register those as new. But no, it doesn’t get confused so easily.

All right. Next question. Is it ever appropriate to put ketchup on a hot dog? Absolutely not.

If you put anything other than mustard on a hot dog, I don’t, I don’t want to know you. All right. Uh, why does SQL Server sometimes switch between good and bad plans every few hours? Well, uh, cause you, you lost a plan.

The plan cache is not, not a very, uh, dependable or reliable place in SQL Server. It being an ephemeral in memory construct, all sorts of things can happen, uh, that make plans disappear. New plans arrive, uh, show up and give you a bad time.

Uh, it could be auto stats updates. It could be manual stats updates. If you’re being aggressive with them, temp tables are a frequent reason for that. But then all sorts of other internal mechanisms and like, like memory pressure may, uh, relieve your plan cache of query plans.

And then SQL Server is forced to compile one on the next go around. And that plan may not be great. Um, I see a lot of, I see a lot of that problem for, uh, the ascending key problem, but, uh, you know, the initial compile time parameter, uh, I don’t know.

Maybe it could, maybe it’s good for that initial compile execution of the query, but, uh, further executions do not do so hot. So, um, that’s usually what I see. Um, if you have, if you have query store, unlike, unlike this person who says we do not use query store.

Um, you might try forcing plans that you know, love, and, uh, are familiar with. Uh, one could also make a reasonable attempt at using plan guides to stabilize things. Um, if the problem is around temp tables, you could try the option keep fixed plan hint.

Uh, otherwise, tune your damn queries. Or hire me to do it. That’s, that’s my job.

Uh, let’s see. Is it safe to leave query store always on and high throughput systems? Uh, for me, that depends a little bit more on, uh, plan volatility than high throughput. Uh, you could have very, very high throughput systems that have, uh, very, very stable execution plans.

And, uh, not see too, too much overhead. Um, if you have very high throughput systems that generate a lot of new query plans. Uh, and you’re constantly having to write new XML gobbledygooks into the, into query store.

Uh, then the overhead does increase. Uh, for me though, uh, the, for me, query store collection has, I mean, it’s not going to say never. Of course it’s had some stuff over the years.

But, um, for me, it’s more query store cleanup and internal maintenance that I see, uh, having problems in SQL Server. Uh, on top of that query and query store data, uh, exposes a lot of plumb foolishness that I don’t particularly care for. But, uh, in general, I like query store.

I use it, use it a lot. I think it’s a good thing. Uh, wish that it would get some of those rough edges cleaned up. Uh, what kinds of performance issues only show up in cloud environments?

Well, uh, I don’t, I don’t know about cloud only. Um, I think very specifically, uh, I mean like, eh, you can underpower. So, like, I don’t even know why you would put SQL Server on that though.

Like, like Amazon has like a T series of, of VMs that, uh, or, uh, EC2 instances that, like, uh, throttle CPU and stuff. I’m sure Azure has some of those same, same types of things. Uh, you don’t really want to put your SQL Server workload on a throttle VM though.

That’s probably not a good idea. Um, usually, uh, for me, I think a lot of it would be IO stuff. Um, cloud storage is, uh, often, uh, quite underwhelming, especially in Azure.

Uh, Azure storage is, uh, almost always a nightmare. When you look at it, even in the business critical tier, there are very, very strange, uh, things that happen. Uh, um, it’s rather unfortunate, but I don’t, I don’t think that’s cloud only.

You could have really bad IO on-prem. Um, I, I think probably the, the, uh, the biggest potential for cloud only stuff would be if you are using, uh, like an Azure managed service, like, uh, Azure SQL database, hyperscale managed instance, something like that, because, uh, you, you are essentially guinea pigging for Microsoft.

They might release a new feature. They might change something under the covers, uh, including your hardware surprise. Uh, and, and, you know, uh, you might, you might hit some oddities in there, uh, as, as new code hits, uh, your database that is maybe not as well tested as you would hope.

Um, but as far as like cloud only stuff, um, I can’t really think of anything that, that, that, like performance problems that I’ve only seen in the cloud. Uh, you know, I’ve certainly seen some performance problems get exacerbated by, uh, by the choices of cloud hardware, uh, you know, the underlying infrastructure stuff. But I don’t, I don’t think that I’ve, I’ve ever noticed anything in the cloud that’s like, wow, that could only happen here.

All right. I’m going to ramble long enough. I’m tired.

I’m going to go do something that makes me untired. Uh, 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’m going to talk about some performance monitoring stuff. All right.

Thank you for watching.

Going Further

Free Database Performance Monitoring – The Web Viewer and Custom Views

Posted on October 8, 2026 by Erik Darling

Free Database Performance Monitoring – The Web Viewer and Custom Views

Chapters

00:00:00 – Introduction
00:01:31 – Web Viewer Overview
00:04:20 – Security and Access Control
00:06:37 – Web Viewer Features
00:09:00 – Custom Views and Dashboards

Full Transcript

Erik Darling here with Darling Data, and in today’s video, we, by we I mean me and Batsmaru here, are going to talk about my free database. And I have to say database now, it’s not just SQL Server, since it gained the capability to monitor Postgres and Aurora Postgres as well. The thing is, titling this stuff is like, Free SQL Server and Postgres is it Aurora Postgres, it doesn’t roll off the tongue. So, free database performance monitoring. I can’t see a world where I start bridging into, like, Postgres or, I mean, it’s a bridge into Postgres, MySQL or Oracle, or, you know, some other obtuse, obscure database. But, you know, I think SQL Server and Postgres are probably good enough for me for now. Don’t ask about MongoDB monitoring. I, I, even I have morals and standards. Don’t, don’t give me that. But, uh, I’m going to do this a little bit backwards. Uh, and because, uh, doing a video of an install, uh, turns out not very interesting. Doing a video, doing videos about the stuff you get after you do the sort of, like, boring run of PowerShell command install, much more interesting. So, today we’re going to look at the web viewer for this. Um, If you, if you recall, uh, maybe the earlier versions of the, the, the light and the old full dashboard thing, they only had the WPF viewer, which was very Windows-centric. Uh, moving beyond a Windows-centric view of the world, uh, it turns out web viewers, wonderful for a lot of people. And you can do a lot of stuff in a web viewer that you can’t do, uh, well in, like, a WPF app. So, anyway, down in the video description, you will find all sorts of helpful links to interface and interact with me.

Uh, where you can, uh, hire me for consulting, purchase my training, become a supporting member of this fine YouTube channel. Uh, ask me office hours questions. I do those every Tuesday. If this is your first time here, I do those every Tuesday. Answer five community-submitted questions. Uh, and of course, please do like, subscribe, and tell a friend. Uh, all of the, the helpful links are down in the, your video description. If you would like to take a look at this free SQL, free SQL Server and Postgres monitoring tool, uh, I, I half fixed this slide. The, the headline is fixed, the, the byline is still not fixed, or whatever that’s called. Uh, uh, again, totally free, totally open source, no email signup, no phone home. I don’t try to keep in touch with you for any reason. Uh, unless you want to pay me, that’s different. Uh, but it gets all the stuff that you would care about from, uh, for, from, your servers in order to, uh, monitor, uh, monitor and even troubleshoot, uh, performance issues. Uh, if you are a particularly robot-y type person, as many people are these days, uh, there are built-in MCP servers that, uh, allow you to interface and interact with your monitoring data, much in the same way that you could interface and interact with me, uh, but they can’t hug you. Uh, and you can just, you know, have the robots sort of look at that collected data and, uh, tell you the wrong things.

It’s fun. Anyway, uh, I am speaking at Pass Summit West. I should make this more colorful. I need to get some stuff in here. Uh, I started to mess with this slide and then I got bored. Uh, November 9th through 11th, I have a pre-con there and I have a regular session there. It’s going to be a good time. Uh, this slide is going to be much better in the next video, uh, now that I’ve realized that I forgot to finish working on it. Anyway, let’s go talk about, apparently reaping time has come. We got our beer, we got our size and pitchforks. We got, we got our crows flying. We got, yeah, we’re having a nice time. Anyway, uh, over to the web viewer, right? This is, uh, what you see when you get into it. Now, uh, when, when you say the words web viewer, a lot of people will be like, ah, man, and this, that it does refresh. It blinks when it refreshes. It’s not, it’s not you. You’re not having a brain episode. It refreshes and blinks itself. Uh, when you say the words web viewer, uh, security people tend to get a little worked up, right? Cause they’re like, well, who, who can view it?

So this, the, the way that this works, um, um, the, the, I’m implementing OIDC for this thing, but, uh, you, you also get a bearer token with this and the bearer token, uh, controls, uh, not only, uh, what, like, you know, who gets to see it, but the level of access that they have. Uh, there is like an admin, uh, uh, set, uh, admin token where you can get in and do whatever you want. And then there’s a read only token. Uh, read only is a little bit loose because you can still create custom views with the, the read only token. Uh, but you can’t change like monitoring settings and stuff. You can’t like mess with other stuff, but this is kind of what you get, uh, out of the box. Uh, you get a fleet overview. So this shows you, all right, boinky. This shows you kind of what’s going on. Uh, you, you, you might notice here that I have not only, um, uh, my, my SQL boxes, but I also have, uh, these Postgres boxes being monitored. Uh, these, uh, things are not very active at the moment. Uh, cause I don’t, I don’t generate a lot of Postgres workload here.

All of the Postgres monitoring was done by sort of, um, dog fooding at client sites. So they were just like, yeah, go ahead. Uh, want to monitor Postgres? Yeah. And build, use ours. Take a look, see what you can get. So that’s what I did. Uh, so, uh, get that. Uh, this is the, the, the thing is, is, is, is you see it when you first walk in. Uh, you’ll see SQL Server 2025 needs some attention. I’ve got HammerDB running on SQL Server 2025 at the moment. So there’s some stuff going on. Uh, but the rest of the things are kind of quiet. Uh, there is also, uh, reasonably good availability group monitoring.

Um, if you have one of those crazy, uh, you know, uh, you know, distributed AGs, uh, life gets a little trickier. So, uh, not quite there yet, but here, uh, you, if you have a normal-ish AG, uh, I can tell you all sorts of things about it. Uh, this fleet sweeps thing, uh, this sort of goes through and gives you, um, it, that, uh, whatever cadence you desire, uh, a sort of overview of what’s going on with your entire, uh, fleet at once. So, like, how this changes throughout the day. It’s like, you know, reports and it says, hey, things got weird since the last time we looked.

Uh, there are all sorts of, there’s all sorts of alerting that you can do with the monitoring tool. Generally, it can go to Slack, it can go to Teams, it can go to PagerDuty, uh, all those, the normal things that DBAs sort of rely on to get alerted for stuff. Um, and then, uh, let’s see, let’s just go down to the server that is currently in the red. And we can, we take a look at this and we will see that we have some spikes. Uh, we have our overview here with all this stuff going on.

We can sort of get a, get a sense of what the server is under the covers. Uh, maybe I need to install a CU or something. Um, and then, you know, sort of like, you know, what’s going on in the server? We’ve got some IO latency, we’ve got some blocking, we’ve got some other stuff happening. Uh, you know, from the web view, I can’t do as many interesting things with the web view as I can, uh, with the WPF viewer as far as, like, interactivity goes, but the web viewer does give you a great way to view what’s happening.

So we get wait stats, we get CPU, we get memory, uh, we get blocking file IO, you know, the queries that we’re running, how the server is configured, if anyone’s changing stuff, um, you know, perfmon sort of activity on the server. Um, uh, system events from the, uh, from various, uh, views within the server to look at CPU and other sort of overall server health stuff, system health event type stuff. Uh, and then, you know, something that sort of tells you about the collector health in general, because what good is a monitoring tool if it doesn’t monitor itself a little bit and tell you if things are spitting up, right?

Because, you know, like, ah, everything looks fine. Oh, wait, it’s not collecting anything. That’s not right. That’s not a good time. So, uh, that’s what you get sort of from the web viewer experience. Um, down here are the, a couple of custom views that I have. Uh, this one is, uh, that I put together. This one is showing me how much resources that HammerDB, uh, queries are using and what they are specifically responsible for. And down here, uh, here’s another fun one, uh, the HammerDB TPCC workload stuff going on. Oh, look at all these deadlocks. You can see all the things going on in here. Then these, these are custom views that I created, uh, just within the web viewer itself, putting those things together. Uh, there’s a sort of server health one in general that just sort of, um, you know, puts together a, um, you know, it’s like sort of whatever, whatever metrics you want to mish mash together. Like I put together like the, the sort of bigger dashboard, the grand overview of everything up there. If there’s a bunch of stuff that you make sense to you, for you to look at together at once for a server, go ahead and mish mash it in together here. When you want to create a new view, just go into the new view, choose whatever stuff you want to put in there. Uh, you know, you can add panels, you know, you can do all this fun stuff, right? And you can measure all the cool, all the stuff you want in your SQL Server, highly configurable. Um, I tried to, tried to make this as sort of open-ended as possible, uh, to allow you to, uh, create whatever views make sense for you to keep track of what’s happening on your servers. But, uh, you know, if, again, this is, this is an open source tool. If there’s code you want to contribute, you’re welcome to do so. If you’re, if you’re one of those people who’s like, but I don’t know how to code, you, you can have your robot friends help you with stuff. Um, it’s, it’s fine with me. Uh, you know, if you have, if you find a problem, GitHub, port a bug, I’ll fix it, right? It’s a, it’s a beautiful thing, right? And again, totally free, totally open source, costs you nothing. I don’t want to bother you with stuff. Uh, but yeah, it’s, it’s, it’s been fun working on this, uh, highly gratifying experience. And, uh, uh, everyone who uses it loves it. That’s why it’s called Darling. All right.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I’ll see you in tomorrow’s video where we will, uh, talk about some boring time zone stuff. But then, uh, next week, I’m going to do some more walkthrough videos of, uh, what’s been going on and what’s been, uh, happening with, uh, the, the, uh, development of the monitoring tool stuff lately. All right. Thank you.

Going Further

Learn T-SQL With Erik: Time Zones and Offsets

Posted on October 7, 2026 by Erik Darling

Learn T-SQL With Erik: Time Zones and Offsets

Chapters

00:00:00 – Introduction
00:02:30 – Time Zone Basics
00:05:15 – Using AT TIME ZONE and SWITCHOFFSET
00:07:45 – Examples of Time Zone Adjustments
00:10:10 – Time Zone Storage Best Practices
00:12:30 – Time Zone and Clock Changes
00:14:00 – Time Zone Query Considerations
00:14:55 – Summary and Next Steps

Full Transcript

Erik Darling here with Darling Data, and this week and next are going to be just a couple video weeks for me. It turns out that after being traveling abroad, which is really just a trip because when you have kids and laptops with you, you’re just home somewhere that you like a little bit better. It turns out that you have a lot of stuff to catch up on when you get back, and that’s where I’m at. This week, actually, just earlier today, because it is 9-10. Well, it is September 10th. I just released a new cut of my free SQL Server and Postgres SQL Server monitoring tool. Yes, that even covers Aurora Postgres, the thing that everyone is falling over their willies for. We’ll talk about that a little bit more. I have a bunch of videos that I need to record to talk about where the monitoring tool has gone, all the new stuff in it, because there really is a lot. I’m going to talk this overview a little bit in this video, but this video is going to continue the Learn T-SQL stuff that I did not finish before I went away, and we’re going to finish up talking about time zone stuff.

So after this week and next, we’ll get back into the other stuff. Down in the video description, as per usual, as per my last video, you’ll see all sorts of helpful links. You can hire me for consulting, purchase my wonderful, fairly priced training, become a supporting member of the channel. You can ask me office hours questions for free. I will still be doing office hours. I might even do some extra ones. I was like, I’m going to clear up the office hours queue. I didn’t come close to that. I missed a bunch of stuff. There’s a million questions in there. So I still have to work on that. But as always, please do like, subscribe, tell your friends, tell your enemies, tell all the witches and warlocks that you know. I still have to fix this slide too. My free SQL Server and Postgres performance monitoring tool. You can read more about it at this link over here. There’s also a Postgres. I should probably just add a Postgres page.

I’m going to show you in a second. Totally free, totally open source, no email, no phone home. We are now focusing on the Lite version, which you can just crack open the executable, plug in a server name and immediately start monitoring. If you just, you know, have some stuff that you want to poke around with. But if you are looking for more of an enterprise-ish monitoring tool, then you would get the new Darling version. It is a headless window service connected to a Postgres and timescale backend. Doing that helps to keep things free.

You know, I got asked why I won’t use SQL Server as a backend, and it’s because I don’t want to cost people money. Sorry. This thing being free, I can’t say it’s free and then make you pay for SQL Server licensing. It can either make you steal from poor beleaguered Microsoft using Developer Edition for your monitoring tool.

But it runs 24-7 agentless, collects stuff from all your SQL servers. I’ve got a couple client sites with around 150-200 servers being collected on a single VM, and it’s handling all that well. I had to make some adjustments to some of the compression stuff in there to keep things running smoothly.

But all is looking absolutely, well, I’d say Gucci, but Gucci is just cheap crap these days, right? We don’t care for cheap crap around here. But our databases are feeling the end of summer.

I think, is that corn whiskey or Everclear or something? I don’t know. But anyway, just to sort of give you an idea, just a brief idea where things are. This is the WPF. This is the sort of executable viewer.

You can use this with Windows. And I’m going to talk more next week about the web viewer, because the web viewer for this is really cool and has a lot of neat stuff in it. You can build custom notebooks.

If you’ve ever used Datadog and you’ve made a notebook in Datadog that lets you graph and charts very specific things for specific servers or store procedures or queries or whatever, you can do that with my web viewer now, too. It’s pretty neat.

But, you know, just over here, stuff that I’ve added kind of recently, availability group monitoring. This is just a local Postgres instance that I have. It’s not doing a whole lot. There’s no load on it.

It’s just a sanity check and make sure everything’s working. But this is my monitoring tool, monitoring Postgres now. So good for me. And, of course, if you still just love SQL Server, maybe you don’t love when Windows Update kicks in and messes up your graphs. Thanks, Windows Update.

You’re a real, real sport. Then you can continue to monitor SQL Server performance for free with this as well. But we’ve got a lot of cool stuff in here. Anyway, let’s go talk about what I promised to talk about.

And I forgot to go back up to the top of this. So this video, again, part of the Learn T-SQL series, the full course is available for purchase down below if you want to learn a lot about T-SQL from, you know, someone who spends a lot of time writing T-SQL and writing T-SQL monitoring tools and stuff like that. I don’t know.

It might be a good idea. It might be a legitimate, reliable source of information. It might get fewer things wrong than the robots. It might even have the robots help tidy things up sometimes. I don’t know.

You know. I don’t want to get a tangent, but man. It’s been still not a fun time talking to the robots about performance. Still not a fun time.

They follow instructions well sometimes, but they say so many dumb things. Anyway, if you are finding yourself in need of working with time zones, there are some things that you are going to care about if you are working in a database that maybe doesn’t have any time zone information and you need to get it in there or something.

Or maybe you just started a new job where they cared about time zones and then your other jobs didn’t. So any, all kinds of crazy things can happen. But when you’re working with time zones, especially if you have dates that are not time zone oriented in any way, like for example, this one, this is just a date time too.

And getting used to the functions and how they work and behave can be a little tricky at first. It can be a little mind numbing. So if we run this query, what I want to show you is just we start off with a date time too that has no time zone affiliated with it.

And if we convert that to a date time offset, let me blow this up so it looks a little bit better, then it just gets assumed UTC, right? So like, you know, I’m here in New York City, so I’m in Eastern time. And so if it was 1430 Eastern time and I converted that and I said, hey, well, now you’re in a time zone.

SQL Server would be like, you’re UTC. And I’d be like, I don’t think that’s right. And so if you if you try to use switch offset, just converting, you know, a date time or date time to to a date time offset, then it will start subtracting hours incorrectly from it.

What you need to do is use to date time offset instead of just that so that you get the sort of correct time and time zone applied. Right. And then you can do the old double jump. You can say switch offset to date time offset and you can you can it’s sort of like doing at time zone twice, which we’ll talk about later.

But this will actually get you to the right, you know, just to UTC time, you know, because this is 1430 minus five, the correct one. Sorry, 1430 minus five, the correct one. And then we get that to UTC where it’s plus zero, but it’s 1930.

All right. And if you’re American, welcome to 24 hour clocks, I guess. I don’t know. Using switch offset correctly is, you know, you really do kind of have to start with something that already has a time zone attached to it.

Right. So if you look at these queries, right, and we run this stuff, we can see our original time is 1430. Right. And I guess Eastern ish time, I guess. Right now, that might be wrong, depending on daylight saving stuff.

Who cares right now? Videos are forever. Right. This could be any time of year. Right. I don’t know when you’re going to watch this. It’s eventually consistent.

But original time is 1430 and minus five hour from UTC time zone. And then we can get to Pacific time, which is minus eight, which just subtracts the correct time there. And then we can go to UTC and we can even time travel all the way to Tokyo, where it is tomorrow.

Right. We go from 115 to 116 because Tokyo is so far ahead. Right. And all of this stuff ends up, we just do this same instant check between these things just to give us just to make sure that we get everything correct. To date time offset is an interesting one.

And you kind of have to look at why where you attach stuff matters a bit. Right. So if we run this set of queries, right, we can we can not only so go back a little bit here. In this set, we use a mix of things.

Right. We can you can either do like you can either do a string like this where you tell it how many like what the the the hour offset is or you can even use this to do like minutes. Right. So four 80 zero. You can add 540 minutes.

Right. Subtract 480 minutes. Go to UTC by adding zero. So you can do, you know, like do the same thing just, you know, with with numbers across the board. Right. So it doesn’t matter how you do the attach.

It just like what matters the most is that you just get the numbers right. You know, some people will store and we’ll talk about this a little bit, too, is some people will store like the name of the time zone, which is probably the smartest thing to do, because then you can look up what the current offset is and you don’t have to keep track of it.

Right. It’s like daylight savings and all that stuff gets real messy. If you just have a table with like some initial offset, you have to go and change it. You don’t know what the time you don’t know what the actual time zone is like for, you know, for let’s just say Eastern Standard Time.

There are a bunch of other time zone names that have the same offset as Eastern Standard Time, just storing the offset. You don’t know exactly which of the time zones it is. You just know the offset that might be good enough for you.

But usually you want to know. And this is usually what people do when they need to get something right. So we’re going to look at the timeline here where we have learned T-SQL.

We have started drinking because we were on a really long trip. And then we have forgotten T-SQL. And if we run all this stuff, see that this is a pretty good, let’s just call this canonical way to figure out what time things were originally logged.

Maybe, you know, what the original log time zone was. And then, you know, usually you want to, usually you would, so usually you store like, if you’re smart and you’re using time zones, you store everything in UTC, you store the user’s time zone, you do the lookup, and then you come back and you figure out what the offset is, like what the hour difference is there, right?

But that is what this sort of brings up here. The one thing, actually I’ll talk about that thing tomorrow, but let’s say that we have a table of sales orders here, right? And we have, let’s say, two options, right?

You can say what city someone is in and their current UTC offset, or you can store the name of their time zone, which I know strings in a database were a mistake. I agree usually, but, you know, what can we do here? But when you want to do a query that, or rather when you want to query this data, having the name of the time zone stored is much, much smarter, right?

Because at certain points, you might be wrong by an hour because you are not keeping track of time changes, right? Time zones are one part of the problem, clock changes are another part. And so you might be wrong, I should go this way, by some number of minutes sometimes if you are only storing the offset and not the actual time zone name, right?

So if we, you know, use the time zone name here, then we can, like, this will just screw you up, right? It’s not a good time because we’re just using offset minutes, but if we are storing the time zone name, then we can, you know, we can send a time to the time zone name, right?

Even just stored in a column like this, and then we can send it to UTC, and then we can look at the wrong by minutes column that just shows us how, if you’re only storing the offsets, you can be very wrong as clocks adjust throughout the year. And, of course, what was that comment? Ah, there we are.

At time zone returns date time offset, so switch offset works on the result too. These two do the same thing. Pick whichever reads better to you, right? So we could also just do something like this, right?

Switch offset with the time zone name, and we get back presumably all correct results, right? So we can do at time zone, or we could do switch offset. Both of these end up being correct.

You know, whatever query form you like the best, do that. Just as I said in previous videos, don’t do this in a where clause, because that makes Windows API calls, and that’ll really be bad for you.

You won’t have fun with it. All right. That’s enough about time zones. Thank you for watching.

Hope you enjoyed yourselves. I hope you learned something. I hope you will pretty please with free cherries on top. Check out my SQL Server performance monitoring tool.

It’s absolutely free. It’s available on GitHub. Links and stuff are down in the video description. If you want to monitor a SQL Server, availability groups, Postgres, or a Postgres, it’s all here.

It’s all free. Anyway, I’m out of here. I will see you next Tuesday for Office Hours. All right. Thank you for watching. Thank you.

Going Further

SQL Server Performance Office Hours Episode 88

Posted on October 6, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 88

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:03:00 – Lob Data Cleanup
00:06:00 – Page Density and Compression
00:09:00 – Concurrency and Workload Performance
00:12:00 – Memory Pressure Metrics
00:15:00 – Identifying Memory Pressure Sources
00:17:00 – Conclusion

Full Transcript

Erik Darling here with Darling Data. Boy, am I happy to see you. Another, I mean, you know, it’s so hard. After doing all those Office Hours episodes where I was in fun places, and then one where I got to read a bunch of questions from people who really don’t like me, I feel like, I don’t know, it’s a little bit of a letdown to just be sitting here doing this here. Maybe I should start, I don’t know, going in my backyard. I don’t want to annoy my neighbors. It’s the problem. I have a real aversion to annoying people publicly. Anyway, this is the time of the week and the time of the day where I answer five very important questions from between one and five very important people. I am unsure of who asks these questions and what their question asking cadence is. It could be any number of things. But anyway, down in the video description, all sorts of great helpful links for you to hire me for consulting, buy my training, become a supporting member of the channel, continue to ask me these Office Hours questions despite my distinct lack of exotic locales. And of course, you can always like, subscribe, tell a friend. I do like seeing the numbers go up in some facets of this channel.

Maybe. When numbers go down, that’s when I start getting worried. I don’t know. Anyway, I finally got around to fixing this slide. If you want free SQL Server and Postgres, including Postgres Aurora performance monitoring, I’ve got some of that for free. If you go to code.erikdarling.com, it’s pretty clearly labeled the performance monitor repo. So, you can download it and you can start using it. It’s MIT licensed. You don’t have to do anything with it. I’ll show some stuff when I’m out of the slide deck and before I go into the other thing. But yeah, all the important stuff that you want to collect to know about performance issues. And that’s about it there. It’s free. I make the same amount of money whether you use it or not.

But now it is time. We are officially Septembering. And because we are Septembering, I should probably unhide this slide for next time. So, I can say, hey, in like two months, past Summit, well, sorry, past Summit West. Not West. West is taking place in Seattle, Washington from November 9th through 11th. I have a pre-con there. And I also have a regular session there. And so, you should come to both. Otherwise, because that’s numbers going up for me.

And when numbers go up for me, look how happy I am. Isn’t making me happy important to you? It should be. Anyway, free monitoring for SQL Server and Postgres. You can see very clearly a mix of SQL Server. We have availability groups. We have various Postgreses. I shrank that window by accident.

And then, of course, SQL Server 2016 through 2025. Also supports Azure, VMs, SQL Database, Hyperscale, Managed Instance. You name it. I support it. I monitor it. I just don’t keep the Azure stuff in there because it costs money. Also, Amazon RDS, of course. All the major stuff. Gosh, you’re lucky to have someone like me in your life, aren’t you?

I think I want to repay me with thumbs up someday. Anyway, let’s answer some of these questions here. Let’s see. First one. Ah, my favorite person. It’s been awfully quiet in Paul Whiteland lately. As someone who seems to know him pretty well, is he happily enjoying retirement, secretly building the world’s greatest query plan, or should we expect him to pop up again someday?

Okay. So, I made a deal with Paul White that I would not ask him any dreary questions. We do talk frequently. And part of that is we get along about stuff outside of SQL. We just happen to meet via SQL Server, right?

We just get along pretty well in many topics and regards outside of databases. And so, when Paul decided to do his life thing, and I’m not comfortable telling everyone exactly what he’s up to. But when Paul decided to go out and do some life adventuring, I said, let’s keep talking, but let’s not talk about databases.

And we both enthusiastically agreed to these terms. I did have to insist on them. I don’t know if he’s going to blog again.

I think after many, many, many years of doing it, the man deserves a break. And, you know, maybe if something catches his interest in a new version of SQL Server or a new feature, you might see something, but I couldn’t tell you. All I know, and what I am happy to let you know, is that Paul is happily enjoying cruising around the wilds of New Zealand and doing very scenic things at the moment.

So, I don’t know. I don’t know. We’ll see.

Maybe, you know, at some point, you got to think, maybe he’s done enough, you know? Maybe the man has earned some respite in his life. I think he has.

All right. Have you found a way to have Nutanix? Jeez.

Nutanix. Are you kidding? Hyperconverged storage provide low disk response times. We keep getting 100 millisecond spike. Yes, by moving off Nutanix. Now, this is one of those things where, you know, I’m sure that you have done your due diligence in working with your Nutanix representatives and tech salespeople in order to align your system with best practices and all that stuff.

And, you know, I’m sure that there are people out there who use SQL Server on Nutanix and are happy with it. In my position, I just don’t talk to people who are in a very happy place most of the time. Most of the time, I talk to people who are sad, angry, depressed, confused, just don’t know what to do with anything that’s going on around them.

And so I’ve met a lot of people having some really, really bad times with Nutanix, you know, hyperconverged or not. And, you know, I think just past a certain point, that’s not the platform for you. You know, if you’ve already gone through the correct steps with your sales and tech people and you are unable to get satisfactory performance, it’s time to go.

So it’s sort of like when someone points me to a view and they’re like, it’s 4,000 lines long and it’s slow as hell. And I’m like, yeah, no kidding. Look at it.

It’s a junk drawer for all your worst sins and ideas. It’s every table in the database joined and, you know, it’s just a mess, right? You have picked the incorrect vehicle for this thing, right?

Like if you had a stored procedure instead of a view, you could express this logic in perhaps more sane, rational and easily, more easily optimized ways than you have chosen to express it. And it’s the same thing over and over again. If you have done everything within your realm of responsibility and you are not getting adequate performance, you are in the wrong realm.

All right. Is it ever worth chasing fragmentation numbers for performance reasons anymore? So I do think that it is worth checking in on page density.

I don’t think it is something that is worth automating. You know, you would like, you know, like the logical fragmentation pages out of order thing. You can’t, you just can’t talk me into caring about that.

But the page density thing, which can be affected by, which is affected by deletes and can be affected by updates, would certainly be something that I would check in on from time to time. And, you know, if you have very big tables with, you know, like weird page density issues, then sure, I would absolutely, I would absolutely say, you know what, throw a rebuild in that. And while you’re throwing a rebuild at that thing, you should apply page compression.

Just like do something good along with it. And the reason for that, and this is something that actually happened with the client thing that I was doing. I was helping a client of mine move indexes from the primary file group to other file groups.

And I didn’t look at, like, page density stuff beforehand because, like, it was immaterial to what I was doing. But there were some very, very large tables that shrunk appreciably in size when things were rebuilt, especially with page compression on the new file group. But I think most notably, there was a table that was around 1.3 terabytes that ended up being, I think, something ridiculous, something like 30 gigs or something.

There were a lot of, there was a lot of lob data in there that got cleaned up and stuff. But, you know, like, there were some other things going on with it. But I think every once in a while, sure, it’s worth checking in on the page density thing and, you know, perhaps rebuilding on the larger objects.

If only because, you know, especially when you double that up with applying page compression, you can end up with much, much smaller objects. And that’s a good thing for most servers because most servers don’t have enough memory to deal with the amount of data they’re carrying around. And so you take up less space on disk, you take up less space in the buffer pool, things are generally in a happier place.

So there are reasons to do it. I wouldn’t say that it would be like, you know, some, like, I wouldn’t call it like a general query performance thing. You know, if your queries are scanning large amounts of data, of course that improves.

But if your queries are seeking to data in a B-tree, then either one is not going to make such a profound, like, you know, it’s not going to make, like, fixing logical or page density fragmentation isn’t going to make a profound difference to those queries. But I think, you know, every once in a while it is worth checking in on that and maybe doing something about it. So why do some workloads completely fall apart under concurrency even though single user tests look fine?

Well, I don’t know. Why do you get, why do you drive somewhere so fast when no one else is on the road? Traffic’s not fun, right?

When you have competition for finite resources, and, you know, I don’t mean to get all Adam Smithy on you here, but when you have competition for finite resources, someone is going to have to win in that. Like, you’re sharing resources amongst many, many queries, some serial, some parallel, others taking up memory, others going to disk and trafficking things back and forth. That’s, of course, not, you know, going to contribute to queries that in isolation run perfectly fine, not running so perfectly fine anymore.

You know, that competition can show itself either worker thread starvation, query memory grant starvation, taxing the paths from servers to the disks. And, of course, you know, unless you are a real smart cookie and you listen to my advice and you use an optimistic or row versioning isolation level, well, you are going to have some pretty annoying blocking and deadlocking issues in SQL Server because read committed is a garbage isolation level.

So, the shortest answer I can give you is competition, resource competition, lock competition, all those things. How do you tell when memory pressure is coming from SQL Server or the OS or VM host? Well, if you’re goofy enough to have other stuff going on right next to your SQL Server, you should probably rethink that.

You know, the thing that I always try, the thing that I always look at, rather, the first order metric that I always look at is wait stats. And the reason why I look at those is because I can at least start to get a sense of a couple of things from a memory perspective. Memory pressure shows up in SQL Server, either at the query level is resource semaphore, that’s query memory grant stuff, or is any of the various page IOLATCH underscore letter letter weights.

And, you know, if you have resource semaphore waits going on, then what you’ll find is very large portions of your buffer pool are likely getting cleared out and swapped to disk, and then queries have to repopulate the buffer pool, and that’s when the page IOLATCH weights kick in. But even without resource semaphore waits, you can see rather high page IOLATCH weights because you might just have way more data than memory, and you might not be using page compression, and you might not have indexes well-tuned to your queries so that you can seek to smaller regions of them rather than having to scan the whole thing.

You might have very, very beefy indexes because of the data types that you’ve chosen, like large strings. You might be holding on to far more data than you are legally required to hold on to, or even professionally hired to hold on to. I don’t know, there’s lots of stuff that can happen out there, right?

And so for me, you know, it’s been quite a while, I think, since I’ve seen memory pressure coming from outside. You know, like every once in a while, I’ll see, you know, like, especially CrowdStrike. CrowdStrike does a lot of awful things to SQL Server, and, you know, so I’ll see, like, you know, some, like, antivirus or some other filter driver ballooning memory and sort of, like, messing with SQL Server, like, from within the box.

So if you’ve got that stuff going on, of course, look into that. You know, a lot of security people do not follow anything close to a best practice when it comes to excluding SQL Server there. But, you know, for me, mostly, you know, it’s just been so long since I’ve seen anyone doing anything too atrociously bad with either a virtualized SQL Server or with installing nefarious, well, installing things on the SQL Server that are obvious memory hogs, like, you know, like some Java application or IIS or something like that.

People still put CrowdStrike on there. In fact, it has dealt with CrowdStrike causing an AV exception on a SQL Server a couple weeks ago. So, anyway, mostly my investigations are things happening within the SQL Server.

And if you just truly can’t explain why SQL Server is experiencing memory pressure by looking at the activity on the SQL Server, then I guess at that point you could look outside of it. But at least in most of my recent experience, I would say that the memory pressure is coming from inside the SQL Server.

Anyway, it’s gone on long enough here. Thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you in another video this week where we will finish talking about time zone stuff in T-SQL. At least to the extent that I care to talk about it.

There might be other things to say about it, but I don’t care to say them. Anyway, thank you for watching.

Going Further

SQL Server Performance Office Hours Episode 87 – Questions From Haters

Posted on October 1, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 87 – Questions From Haters

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:34 – Monitoring Tools Update
00:03:38 – Hater Questions Overview
00:05:25 – Hater Question Analysis
00:07:47 – Final Thoughts

Full Transcript

Erik Darling here with Darling Data, and I have returned home, unfortunately, and, well, time for office hours. This one getting done a little bit late on a Tuesday because, you know, long weekends is long, my friends, and, you know, sometimes, sometimes festive, I guess. But today’s office hours is a special one. This is a first edition of office hours where it is purely questions from haters. There’s some, some, some, some, some attempts to make your friend, your best friend in the world, Erik Darling, feel bad. And we’re gonna, we’re gonna answer those questions today. All right. Down in the video description, because it’s been a while since I had a PowerPoint to read from, down in the video description, there are all sorts of helpful links. where you can hire me for consulting, you can purchase my training, you can become a supporting member of the channel. You can ask me office hours, hopefully nicer office hours questions than the ones you see today. And of course, as always, please do like, subscribe, and tell a friend. This slide I have to fix, or I have to do something with, because very exciting stuff. If you’ve been keeping up with my free SQL Server performance monitoring tool, you may have seen recently, that it is not just for SQL Server anymore. Now it can also monitor Postgres and Aurora Postgres, which, I guess are popular out in the world. So we hit those now too. So it’s not just SQL Server, but it is the same wonderful, free brand of monitoring that SQL Server has just expanded to Postgres. There’s still some like sort of work that I have to do to make sure that everything is kind of equal in the world. The Postgres side needs the alerting and stuff that SQL Server already has. But we now monitor Postgres and Aurora Postgres. So all good things there. The link to check that out is still down in the video description. And because I was away for the month of August, you were all sadly denied the joy of the August database party image.

And so just going over this one, this database seems to have lost an eye. Probably double fisting beer by a fire. That seems like a good way. This, you’re missing your mouth entirely. I don’t know if that’s blood or wine, but it looks a little strange. And I don’t know, this chicken wing has an, this drumstick has an eye. So I don’t know, man. I don’t really know what that is. But this was my month of August database party image. It’s got some Stonehenge-y stuff back here. That’s kind of cool. Look at these flower headband things. That’s, man. How do I get invited to that party? That looks like a good time. I don’t know. Is that a tooth? Just chopping teeth up there. And then this is, um, oh, actually, was that July? I don’t know. I have to go through these. Uh, this, this is, I don’t know. Maybe this was August. Uh, I gotta figure it out. Anyway, let’s go answer these questions from people who don’t seem to like me very much.

First one. Put some clothes on. These videos where you’re in a tank top or shirtless are vulgar and your tattoos look retarded. Well, uh, first of all, sir or ma’am, uh, you did not even see a nipple in any of my shirtless or tank top videos. So, uh, your, your, uh, your assessment of vulgarity, I think, is a bit on the sensitive side. I think, I think you are overreacting to a little bit of skin that, uh, really wasn’t that, really wasn’t all that revealing.

Uh, as for the tattoos, uh, you know, uh, I’m starting to wonder if my mom left this comment because she says similar things about my tattoos. Uh, so, I don’t know. I, I, I like them kind of pretty well. Uh, I, I don’t notice them, I guess, as much anymore because I’m quite used to them. Um, but, you know what? Uh, I, I, I’m, I’m happy with the sort of overall visual effect.

Um, maybe they’re not all the best tattoos because I did start getting tattooed before tattoos were, like, good. It’s not like, and, uh, you know, some of my earlier tattoos were, were not done in the most legitimate or, uh, legal of settings. Uh, there were some kitchen table pieces in there.

So, uh, you know, maybe not all the best work, but I think overall, uh, I’m, I’m happy with the way that, that things have turned out. So, um, you’ll be happy to know, though, that I, I do have clothes on again. This, I, I, you, you still do see some skin and some tattoos, so hopefully this is not too much for you.

All right. Next question. Not even a question. Statements. These are, like, just statements from the haters. Uh, you and Brent show off too much. Him with the cars and you with the watches. My $10 Timex tells time just as well.

Well, I’m sorry that we did well. My apologies. Uh, but to, to, to, to, to, to, just to get to the root of this, uh, this, this final sentence over here is the interesting one to me. Uh, yes, your $10 Timex maybe does tell time just as well, but you don’t wear a watch like this to tell time.

You wear a watch like this to tell other people what your time is worth. So, if you would like an invoice for that, let me know. All right. Third one.

I don’t understand why anyone comes here to ask you questions when they have AI. Face it, you’re a has-been. Well, I’ll take being a has-been over a never-was any day.

Uh, I don’t understand why, okay, you, uh, you know what? Maybe, maybe I don’t either. Uh, that’s, but, that, that they still do it. In fact, I didn’t even get close to running through all the questions that are in my office hours sheet while I was away traveling.

Can you, can you, just, can you believe that? I mean, it’s a lot of questions on there still. And I just feel, I, I just was scrolling through them all and I, and I, and I noticed that there, there were five of them that were particularly bitter and spiteful.

And you, you, you, you were, you were one of those entries. Uh, I don’t know. Great.

You don’t get it. I don’t get it. But it keeps happening. I don’t know. Maybe I have good answers. Maybe, maybe people like the way I answer better than the, than the way AI answers. You don’t, I don’t know.

I’ve never, I’ve never actually done a poll on it. I’ve never done any market research on that. All right. Oh, look. Another totally unprofessional bad boy, in quotes. Consultant.

You would never make it in the real world. I don’t know what the real world is. I feel like I’ve done pretty okay. Uh, maybe the real world is not for me. Maybe the fake world that I, I traffic in is where, exactly where I belong.

Because that seems to be what allowed me to buy a watch that made someone else mad. So, I don’t know. All right.

Here’s another, here’s another banger here. Uh, why don’t you have AI generate better questions and answers for these? No one seems to have a new or good question anymore.

Uh, well, I’m not a cheater. For one. At least, at least not with these. Uh, these, I, I, there, there are lots of things that I am, I am happy to have AI do for me, uh, because it is tedious, mechanical, uh, or too far out of my realm of expertise to indulge in.

Uh, but there are certain things that I, I do still hold sacred and try to keep AI far away from. Um, you know, like, uh, when I, when I do write blog posts, uh, when I do write things, uh, I tend to, unless it’s like documentation type stuff, uh, I tend to keep, uh, AI out of that, but, you know, being handed a coffee here.

Uh, so, you know, that, that’s why I don’t do that here. Anyway, the mean-spirited people today. Thank you for watching.

Hope you enjoyed yourselves. You probably learned nothing from this, but, um, I had, I had fun. Anyway, goodbye.

Going Further

SQL Server Performance Office Hours Episode 86 – Drinking Champagne in an Algarve Bathtub

Posted on September 30, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 86 – Drinking Champagne in an Algarve Bathtub

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:30 – Managed Instance Discussion
00:03:00 – Misleading Performance Counters
00:04:30 – Diagnosing Specific-Time Problems
00:06:00 – Partitioning and Performance
00:07:30 – Query Store Wait Stats
00:08:45 – Merge Joins Explained

Full Transcript

Erik Darling here, with Darling Data, coming to you live from a bathtub, drinking champagne. That’s this office hours for you. Coming down to the end of the trip here, I didn’t end up recording anything in Kash Kash, because there was really no good place to record it where I felt okay doing this, because I don’t want to annoy people in public. Anyway, we’re going to clear out some more of this office hours question queue, because good lord, do we have a lot of questions. First one, from, well, I mean, I don’t know who any of this stuff is from. That’s great. I don’t have to know your name, and neither does anyone else. How are you feeling about managed instance? So, I get questions about Azure a lot, and I always try to answer them in the same way. Back in World War II, there was a saying that I would rather have a sister in a whorehouse than a brother in the Navy. And if you just replace Navy with Azure, you get the saying. So, that’s how I feel about managed instance. I would not curse anyone with it. I would not wish that on anyone. I don’t know how Microsoft managed a screw managed instance.

Azure SQL DB, Azure SQL DB, and all the other stuff so badly, but, yeah, no. All right. Next question. What are the most misleading perf counters people rely on? PoE? Any sort of queue depth? Logical reads? That’s another dumb one. How fast is a read? I don’t know.

How slow is a read? I don’t know. I tend to look at things that are… So, these are all sort of what I would call like second order metrics, right? The first order metrics would be things like wait stats or a query CPU and duration or other things that are a bit more directly indicate what you’re dealing with and the extent to which you’re dealing with it. All of the second order metrics are just a complete waste of everyone’s time.

You’re looking at something that is not directly measurable in a real way instead of looking at things that are directly measurable in a real way. And you’re using them for… I don’t know what. I don’t know. You people terrify me with the things you’ll cargo cult around.

Let’s see. How do you diagnose problems that only happen at very specific times of day? Well, that’s a great question.

I set up my free SQL Server performance monitoring tool, and I monitor the server. And then when things happen at a specific time of day, I have all of the stuff that happened before, during, and after the event. If you go to my GitHub repo, you can get my free SQL Server performance monitoring tool.

Bathtub and champagne not included. All right. Let’s see.

When is partitioning actually helpful for performance and not just management? You mean data management? Good question. You can also get partitioning. Usually when paired with columnstore. Clustered columnstore indexes.

When you set up partitioning correctly with them, correctly is an important word here, then not only do you get the usual segment row group elimination with your columnstore queries, but you also get the partition elimination of it as well.

So that is the one time when partitioning is actually useful. Let’s see here. Is query store wait stats reliable enough for real troubleshooting?

So, sort of, kind of. It’s certainly an interesting view of the data, and I don’t fault Microsoft for implementing it the way that they did. But there are all sorts of pre-execution wait stats that it does not track well.

So things like resource semaphore, query compile, resource semaphore, threadpool, things like that. They are invisible to query store because those are all things that happen before the query executes. So if you’ve got a lot of, like, pre-executions, and they’re not counting compile time because compilation metrics are in query store.

But if you’ve got a lot of other stuff going on in the server, then you might find that those wait stats are not being captured in query store. But for your usual stock and standard, what is this query doodling around with type wait stats? Yes.

However, and there’s a big however here, and it’s a sad however. However, the query store wait stats capture and cleanup mechanism in query store is very high overhead in my experience. And, you know, a lot of the times when I’m dealing with clients who have very high throughput systems, one of the things that I do, if they’re already using query store, then, you know, we’re seeing some observer overhead there, is to disable the wait stats collection.

Let the clean stuff up as it catches up with things, but stop capturing wait stats. If someone doesn’t have query store turned on, for whatever reason, maybe because they tried to turn it on in the overhead from wait stats was just too much, then I will usually say, well, we can just turn query store on and turn wait stats off.

Because a lot of the times the wait stats capture can be the most painful part of query store stuff. I forget if that was five questions or four questions, so I’m going to answer one more. This can either be the correct number of questions or it can be a bonus question, depending on how you look at it.

Let’s see here. What’s a good one? Why does SQL Server sometimes favor merge joins that look terrible on paper? I guarantee you they don’t just look terrible on paper.

There are very few circumstances where I prefer a merge join, especially in the batch mode world that we live in. Usually batch mode hash joins and even having to sort some data in batch mode is preferable for larger data sets than an order-preserving merge join would be.

There are some upsides to merge joins and stream aggregates and other order-preserving operators where across a query plan you don’t have to resort data at any point, which can be nice if you’re trying to avoid memory grants or trying to minimize memory grants.

But for the most part, especially parallel merge joins, they terrify me. Any sort of order-preserving operator around a parallel exchange, especially if it makes the parallel exchange acquire the order-preserving attribute, that can be really not a good time.

So for me, a merge join and like, or merge, merge, let’s expand that a little bit. Not just merge joins, but order-preserving operators in general in a serial execution plan if your goal and your aim is to preserve order and not have to resort data out of index order.

They can be useful for some things, but for the most part, boy, oh boy. But why does SQL Server choose them? It’s the same reason it chooses anything.

It costs things. It might look at the cost of resorting data or hashing data or all the other things and say, this merge join looks mighty cheap to me.

Those merge joins. Often more trouble than they’re worth, especially in a parallel execution plan. Anyway, that’s either five or six questions.

I forget. Again, it’s either a bonus or it’s not. I am going to continue to enjoy my champagne in a bathtub. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And, well, I probably won’t see you in your bathtub. Maybe you should watch this from your bathtub and then you can pretend that we’re just like Bert and Ernie or something hanging out together.

All right. Thank you for watching.