create index in sql server Archives

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

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 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.

Going Further

SQL Server Performance Office Hours Episode 85 – Lisbon is for Lovers

Posted on September 29, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 85 – Lisbon is for Lovers

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:15 – Optimized Locking Discussion
00:03:38 – Server Restart Benefits
00:05:47 – Query Identification and Throughput
00:07:56 – Parallelism and Spills
00:09:23 – Vendor Application Tuning

Full Transcript

Erik Darling here with Darling Data, coming to you today from Lisbon. I’m not sure how much of the background you can see out there, but we’ve got a view of the replica Golden Gate Bridge and the replica Rio de Jesus Nero thing in the background. Also in the background, traffic and a train and an airplane. So again, no matter how far I go from New York, I never seemed to be able to escape city sounds. Except when I was in the Azores and the only thing allowed there was the ocean, which I suppose was a nice change of pace.

Anyway, we’re going to keep clearing out the office hours queue here. And we’ve got five more questions to deal with from my lovely, adoring fans who so far have been kind enough to not stalk me while on family vacation.

anyway I’ve never seen you suggest optimized locking as a solution to a problem do you dislike it no I like it just fine it’s just so few people are in a position where they can use it it there’s not a lot of point to recommending it right it’s like it’s SQL Server 2025 it’s you know I guess if you’re in you’re unfortunate enough to be in Azure you you might find yourself using it by accident. So if it’s solving a problem for you, great, but recommending it, you’re either there or you’re not. It also solves a very, I would say, a very narrow class of problems. Just as a small for instance, when I was redoing some of my locking material for a past webinar and for my session materials in Seattle coming up in November and what it actually broke exactly two of my demos where I had locked there was just a single primary key being locked. So I guess if your problem is with a single primary key being locked, well, there you go. I think the one thing that I do like about optimized locking from an outside perspective, and this is not because of the feature itself, is that the feature documentation pretty explicitly says this works best if you enable read committed snapshot isolation. So further legitimizing read committed snapshot isolation as the wave of isolation level future is, I guess, a nice side benefit to that.

All right. SQL Server sometimes runs better right after a restart. Why does that happen?

Well, do you sometimes run better after a night of sleep? I know I do. I’d like to get one of those sometime. You know, you clear a bunch of junk out. Who knows what was going on on that server?

Who knows how long that server had been up? Sometimes you clear a bunch of stuff out, you get some new plans and cache, SQL Server is feeling good about itself. It’s the same thing. Sometimes that works really well.

I don’t recommend it as a performance tuning thing. Don’t just restart your server every hour, but every once in a while, maybe patch things up. Maybe don’t, I don’t know. Restart your server, give it a fresh perspective on life, let it take a little nap.

sometimes that does work pretty well. How do you identify queries that are killing overall throughput but not necessarily slow themselves? I’ll usually look in query store for things that have a very high execution count because for me those are usually the things that just keep CPUs unnecessarily busy and give other queries some sort of fits when it comes to co-scheduling things.

So that’s usually what I look for. You know, like in there, you know, you might find some stuff that could use a little bit of help, but usually those queries are very fast.

And the options there are, of course, usually, usually, but not always things that you would want to control outside of SQL Server. You would either want to implement some caching for them, or maybe you would want to rate limit your various application APIs and whatever, just to sort of take some of the burden off of SQL Server and not let the queries execute as much.

Those are usually the ones that meet that criteria. you. All right. Why does increasing parallelism sometimes reduce spills? So, well, there are a few ways you could go with this. I would say that that is maybe generally not the experience that I have.

You know, because like, okay, so like in SQL Server, all queries start as serial execution plans. That’s when you get your memory grant. If SQL Server later chooses a parallel plan, the memory grant gets divided up amongst threads.

So, you know, if there’s very uneven parallel row distributions across threads, you can see spills. I would say the only time that that is sometimes different is if you increase max stop and all of a sudden SQL Server thinks oh well with max stop set to 8 instead of 4 the parallelism has greater benefit and now we’ll actually choose a parallel plan and sometimes the parallel plan that gets chosen might have some additional memory consuming operators in it that might increase the memory grant and then you might get like just a plan with a better suited memory grant to what you’re doing. It’s also possible that you know you might see plan changes around like you know you change MacStop you know SQL Server is like well new dot new plans.

You might you might get a plan that now has batch mode on batch mode in it. you know you might get just any number of things might change you know maybe I don’t know with all the memory grant feedback stuff I you know this stuff generally tends to sort itself a little bit more more easily than it used to or a little bit more automatically than it used to but my general experience has not been that increasing parallelism reduces spills so anyway all right the final question here how do you approach tuning vendor applications oh boy well you can’t change queries or indexes much uh well i i change what i can uh you know if if hardware is uh impacting the way queries run then I’ll usually suggest hardware changes. Sometimes you can change other things about the database, sometimes it’s the compatibility level, sometimes it’s the isolation level.

You know, there are all sorts of things that you can do, but like, you know, if you can change things but not change things much, you know, change things to the greatest degree you can. But, you know, it’s not uncommon for me in my consultancy to start working with customers of software vendors and have those customers refer me to the software company to help them fix the application more broadly. You know, usually the people who call me up are, you know, people who are like one of the biggest consumers of an application. And so when they have problems with it, the application vendors tend to listen. You know, there’s sort of a joke in the, I guess, whatever space that you never want to be an application’s biggest customer.

I think that is somewhat generally true, but it does come with some benefits. Like you can twist their arms a lot more and you can refer young, handsome consultants with reasonable rates to your software vendor to help them tune their applications that deal with SQL Server.

So if you are out there and you’re using a third-party vendor application and it is struggling with SQL Server, you know, we can always chat about how I can maybe work with that software vendor to help them make things go faster for you. Because that’s one of the things that I do. All right.

Anyway, those are the five questions for today from Lisbon. tomorrow we’ve gone to Kashkaj where hopefully it will be a little bit quieter and we’ll have some more wonderful scenery but for now thank you for watching, hope you enjoyed yourselves I hope you learned something and I will see you tomorrow

Going Further

SQL Server Performance Office Hours Episode 84 – Adios Azores

Posted on September 24, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 84 – Adios Azores

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:30 – Parameter Sniffing Basics
00:04:52 – Resource Semaphore Waits
00:06:25 – Tuning Deletes
00:07:30 – Random Latency Spikes

Full Transcript

Erik Darling here with Darling Data, fighting this gimbal thing a little bit, which is trying to stay focused on me, but having somewhat of a hard time. We’re going to do a slightly different view out on the Azores today. Again, this gimbal thing is driving me up a wall. Alright. There we go. Nice view out over the Azores. Alright. Cool. Anyway, this is our last day here. We move on to Lisbon after this. And then, well, I’ll let you know when I get there. Because I don’t want any of my rabid fans to show up in these places and stalk me. It’s happened, well, really that only happens at conferences when people follow me into the bathroom asking me what they should set MacStop to. But it’s a story for another time. We’re going to continue to clear out the office hours questions queue as I travel around. Well, again, I’ll let you know when I get there. So the first one is, let me bring this a little bit closer here. How do you know when parameter sniffing is actually the root cause and not just a symptom? Well, parameter sensitivity does suggest a number of factors that one can consider. I think the primary one is that perhaps your indexes are not all so well aligned to your queries. You know, there’s not a whole lot you can do about data distributions. Right? It’s not like you can’t just delete data that, you know, makes your queries parameter sensitive.

As much as I often wish that I could just get rid of a lot of data that bothers me. Like, I don’t know, credit card bills, that would be a good one. But for me, it usually does suggest that there is some indexing that could be done to give the optimizer fewer choices as far as which plans it can use to execute your query. But you know, not just a symptom. I suppose it depends on where in the query plan, the parameter sensitivity sort of shows itself. Just as an example, let’s say that sometimes you’re the query, the optimizer chooses a nonclustered index seek with a key lookup. And that introduces a parameter sensitive element to the query’s execution. That would be a sign that indexing is more something that you should pay attention to most likely.

But then there are other times when let’s say sometimes you would have just a clustered index, a nested loops join in a clustered index seek. And other times you would have like a clustered index scan and either a hash join or a merge join. Then it’s not really an indexing issue. Then it is just, you know, SQL Server choosing different strategies depending on the amount of data that it has in there. In other words, there’s, there’s, there’s, it’s not a choice between like which index to use. It’s how the index is used and how the data gets joined together.

Usually in those cases, I’ll either force a plan or use a query hint to keep things on track. I think dynamic SQL is wonderful for sorting out a lot of issues like this. And I abuse that because there are just too many times when the optimizer refuses to stay on the right path for things.

And, you know, that parameter sensitive plan optimization just generally doesn’t do a great job. All right. Next question we have here. I see resource semaphore waits, but CPU and memory look fine. What gives?

Well, if you’re seeing resource semaphore waits, then I do doubt a bit that memory looks fine. CPU might look fine because just because a query asks for a lot of memory does not mean that it’s going to use a lot of CPU. They are independent resources and all that.

I would, I would want to know how you’re looking at memory where you’re saying it looks fine. You know, the obvious caveats around task manager not being a great view of how SQL Server is currently using memory. But if you’re seeing resource semaphore waits, then, you know, the obvious ways of dealing with that, if you’re on, well, if you’re on enterprise edition or if you, even if you’re on, if you’re on standard edition of SQL Server 2025, you have resource governor available to you.

The more memory you have in a server, the more useful having resource governor becomes because you can control. But so by default, SQL Server will give any query 25% of your max server memory setting for a memory grant up to. So resource governor allows you to control that.

You can drop it. You can drop that percentage down. That’s one, that’s one way of dealing with it from a workload perspective. But if you’ve just got a few individual queries that could use some help, you know, the, the normal query and index tuning advice follows there. All right.

How do you tune deletes that randomly take forever and block everything else? Well, you know, the, the, the normal ways. If you want to start from like an outside, outside view, you might look at indexes.

You could use my store procedure, SP index cleanup. You could look at indexes on the table, see if any of them can be removed as unused or deduplicated. So that you don’t have, that your delete is not responsible for deleting data from unused and duplicative indexes.

You could look at indexing from the perspective of, does my delete have a good index to locate the data that I’m deleting? That, you know, usually seeing a seek before you delete stuff is a good thing. And then the other thing would be trying to control how many, batching the deletes up.

So controlling how many rows at a time get deleted. Those, those would be the, the, the, the first few things that I would, I would do. All right.

What usually causes random latency spikes that only last a few seconds? It’s a broad question. Let’s just say blocking for that one.

Unless, you know, someone’s unplugging your network cables, which sometimes the gremlins do that. Okay. Final question for today is how dangerous is disabling auto update statistics in production?

Yeah, that’s, that’s not a favorite thing of mine to do. Um, uh, I’ve, I’ve, I’ve not run into too many situations where, uh, disabling, uh, auto update or even auto create statistics and manually managing that, uh, has been, um, a successful enterprise. Um, I, I, I, I’m not saying it doesn’t exist.

I’m just saying, uh, if you’re plugging SQL Server questions into, uh, a random spreadsheet like that, um, it’s probably not the course that you want to take. So, uh, that is, that is not, um, that is not a course that I would follow. All right.

I have to go pack my suitcases because I’m getting on an airplane. Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you back. Well, today is Thursday. So we will be back Tuesday with another office hours video.

All right. Goodbye.

Going Further

SQL Server Performance Office Hours Episode 83 – Special Guest Host

Posted on September 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 83 – Special Guest Host

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:45 – Temp DB Usage Explained
00:03:45 – No Lock Usage
00:05:30 – Order By Optimization
00:06:00 – Indexing vs Query Rewriting

Full Transcript

Erik Darling here, Darling Data. One of our final days in the Azores, and today we have a special guest, temporary co-host. My wife who can’t work a laptop. Click the… There you go. Alright. She’s gonna be asking me questions because I can’t do both today, but once again, this is what I have to deal with, and this is what I’m taking time away from. to answer your questions. So, you’re all very lucky people. Alright. Mrs. Darling, what is the first question? Why does temp DB usage explode even when we don’t use temp tables that much?

Why does temp DB usage explode even when we don’t use temp tables that much? Do you want to fancy a guess at that one, or do you want me to handle it? I’m good. Alright. Well, the lucky thing about SQL Server is there are lots of things that use temp DB aside from temp tables. Even the much mythicized table variables use temp DB quite a bit. You also have other things that might appear in your query plans like sorts and spills and…

If you’re gonna light a cigarette, you have to give me one. And spools that might also affect temp DB usage. And this is not even to broach the subject of triggers, optimistic isolation levels, many other things that might eventually hit temp DB and cause it to explode. And quite frankly, I’m a little shocked that you would even bother asking me that. You know that it’s not just temp tables. It’s so many things.

Alright. What’s the next one? Is there a safe way to use no lock or should it always be avoided? I reckon I’ll fetch this one as well. Unless you have an opinion on it. No? Alright. Cool.

Well, yes. The safe way to use no lock is in a query that you don’t care about. If you absolutely cannot be bothered to care about the results of your query being consistent, accurate, precise, or any of those things, then sure, go off, fella. Alright. And I suppose one other thing that’s worth saying here is that on the rare occasion when you want your query to use an index allocation map scan, rather than an index order scan, you may provide a no lock hint or a tab lock hint, but in general, for most of you out there, you should just avoid them.

Because they’re not good for you. Like cigarettes. Alright. What’s next? Why do some queries run faster when I add an order by that I don’t even need?

Why do some queries run faster when I add an order by that I don’t even need? Most likely, the optimizer has chosen a parallel execution plan for you because your order by is not supported by an index and SQL Server’s wonderful cost-based query optimizer is no longer choosing a serial execution plan to run your query with.

It has chosen a parallel plan in which we sacrifice on a terrible alter multiple CPUs in order to reduce wall clock time. Alright. What’s next? When is query rewriting better than adding indexes?

When is query rewriting better than adding indexes? That’s the question. Well, the simple answer to that is when the optimizer has a perfectly serviceable index that it’s not using. And you might be able to rewrite your query in a way that nudges it towards using that index.

I’ve recorded many videos about this phenomenon in SQL Server, which I would appreciate if you took the time to watch, like, maybe even pass on to your friends. No. If you have any friends.

No what? No, you’re fine. Alright. Okay. What’s next? I’m going to read this one in French. In French? Yeah. In French. Why SQL Server, Subban, Ignore, Regarde, Perfect Index for a Query?

Ah. Why does SQL Server sometimes ignore a perfect index for a query? Well, again, this all comes down to costing.

And the costing algorithms are not always correctamundo, as they say in France. On Paris. In French.

Indeed. And sometimes SQL Server might misestimate the cost of using your, your so-called perfect index in favor of a different index that is less perfect for your query, and it is up to you, again, this sounds a lot, this sounds quite related to the last question, doesn’t it, Ben? Way.

Way. Way indeed. where you just might have to, again, write your query in a way that makes that index more appealing, more attractive to the optimizer, or…

Quit hamming it up. I don’t know. Whatever you’re doing. Or you might… I forget now.

Anyway, I have one day left in the Azores. That’s tomorrow. Where I will record a fresh new video. Is it all five questions, or do we have another one? Way.

Way. How do you say five in French? Sank. Sank? Sank. Sank? Alright. Well, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that someday you, too, will visit the Azores. It is a lovely place.

I might be living here by then, so you can always not stop by my place and enjoy your time here. Alright. Thank you for watching.

Going Further

SQL Server Performance Office Hours Episode 82 – In the Azores

Posted on September 22, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 82 – In the Azores

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:00 – Lock Pages in Memory
00:03:24 – Memory and Performance
00:04:38 – Row Count Estimation
00:05:30 – Blocking Chains

Full Transcript

Erik Darling here with Darling Data and we are currently in the Azores. You can see that maybe in the background over behind me there. It’s pretty nice. So this is going to be a short one because I’d rather go enjoy some nice stuff. Still going to do five questions but maybe I just won’t ramble on as long as I usually do. So the first question we have is will lock pages in memory? Reduce my latch EX weights. It’s my top weight. The server is a busy one but I’ve query index tuned hard enough to get the CPU down to consistently around 25%. Max.8, cost threshold for parallelism 50, 12 logical cores. Well, no, lock pages in memory won’t help there. But the one thing that you conveniently left out is how much memory your server does have. Lock pages in memory tends to be a better setting for a lot of memory. for servers with a lot of memory where SQL Server sort of negotiating the virtual memory space before acquiring physical memory and doing that whole song and dance is sort of like not necessarily a bottleneck but just adds like a certain to your queries. So I don’t think block pages in memory is going to help with that one. Your princess is in memory.

I’m going to have to go to another castle and then another castle and as usual my rates are reasonable. Though with a server that size I’m not sure. Maybe. Maybe you don’t care that much. I don’t know. Alright. Next question is why does increasing memory sometimes make performance worse instead of better? You are I think full of it my friend. Why does increasing memory makes it worse? Jeez Louise. Yeah. I just so I don’t think that I’ve ever run into a situation where so let’s just say let’s just pretend that I don’t know what you’re deriving performance better and worse from. In my experience there are certainly times when increasing memory will move your bottleneck from maybe queries are no longer IO bound and now they are purely CPU bound. So if by performance being worse you mean CPU got hotter or something then that I can see but you know.

In general my experience has been rather the opposite. So tell me what do you think maybe performance being worse means? I don’t know. How do you think performance being worse is? I don’t know. How do you explain to management that one slow query can take down the entire system? Usually proof because usually that has happened. You don’t really need to explain that to too many people. If they’re talking to someone like me usually something like that has already happened.

So you know usually the bigger a system is the sort of less likely that is to happen. Usually it takes some amount of workload parallelization before that that starts getting wonky but you know. I don’t know what you’re dealing with. Alright. Let’s see. That was one, two, three. Okay. Number four here. SQL Server sometimes massively underestimates row counts. What patterns usually cause that? Well I mean the usual spade of villains. Your Dick Tracy lineup card. You know you’ll have things that you do to your system in your queries.

Right. You’ll have local variables, table variables, non-sargable predicates, things that are far outside the optimizer’s ability to estimate things effectively. You might have out of date statistics, stuff like that. And of course the cardinality estimation model that you’re in will have an effect on that as well. So do check all those things. All right. How do you debug blocking chains that constantly change shape?

I turn on read committed snapshot isolation and usually most of those blocking chains go away. But you know for, I mean, debug them? I mean, geez. It’s usually the same stuff that you would do anyway, right? If the shape keeps changing then you just keep tuning, right? You have your block process report.

You get to see all the stuff that happens and the entire blocking chains. You know, it’s, I guess it’s, if your workload is that different day to day then you’ve got sort of a weird workload. You know, for me, it’s a lot of it, I don’t run into that particular thing a lot.

Usually what I run into is a very common set of blocking and deadlocking things, where at least, at least from the blocking side, the lead blocker is, lead blockers are usually a pretty common set of core things that happen consistently. If your workload pattern is changing shapes that often then, man, you’ve got some interesting stuff going on.

All right. I promised a short one, so here it is. Now I’m going to go enjoy that instead of this. But I do like you and I’ll do another short one tomorrow, I think, probably.

All right. Thank you for watching. Goodbye.

Going Further

Query Store Cleaning And Mysterious Broker Tasks

Posted on September 18, 2026September 18, 2026 by Erik Darling

Query Store Cleaning And Mysterious Broker Tasks

While looking at a client’s server, I noticed there was, at any given time, 2-3 background sessions with the command column saying BRKR TASK, which I found quite odd.

Only the Microsoft-shipped broker queues were present, and none were activated. Nothing uses Service Broker, Mirroring, or Log Shipping.

Running this query:

DROP TABLE IF EXISTS 
    #s1;

SELECT 
    r.session_id, r.start_time, r.status, r.command, 
    r.database_id, r.wait_type, r.wait_time, r.last_wait_type, 
    r.open_transaction_count, r.open_resultset_count, r.transaction_id, 
    r.cpu_time, r.total_elapsed_time, r.scheduler_id, r.task_address
INTO #s1 
FROM sys.dm_exec_requests AS r 
WHERE r.command = N'BRKR TASK';
WAITFOR DELAY '00:00:05';
SELECT 
    r.session_id, r.start_time, r.status, r.command, 
    r.database_id, r.wait_type, r.wait_time, r.last_wait_type, 
    r.open_transaction_count, r.open_resultset_count, r.transaction_id, 
    r.cpu_time, r.total_elapsed_time, r.scheduler_id, r.task_address,
    cpu_delta_ms = r.cpu_time - s1.cpu_time
FROM sys.dm_exec_requests AS r 
JOIN #s1 AS s1 
  ON s1.session_id = r.session_id
WHERE r.cpu_time - s1.cpu_time > 1000 
AND   r.command = N'BRKR TASK'
ORDER BY 
    cpu_delta_ms DESC;

Gave me results that looked like this:

SQL Server Query Results — You’re a BRKR TASK

Bell Ringing

This reminded me of things I saw at other client sites previously, where Query Store wait stats cleanup and Query Store internal index reorgs were causing some weird jiggery-pokery and burning serious CPU time. And another time where CE_FEEDBACK caused high PREEMPTIVE_OS_QUERYREGISTRY waits.

It would be nice if Query Store got some proper attention paid to its internals one of these days. I do trust it mostly, and many improvements have been made to it over the years. It is not 2016 anymore, after all, and Query Store is far from a forgotten feature. But when you pair this with the pains that accompany querying it (hello you weird union all views that hit slow-ass in-mem functions that can’t push filters down), it does feel a bit left-behind.

On the advice of my legal counsel, I took an ETW trace for a couple minutes. I do not recommend living as dangerously as I do.

wpr -start cpu
/*Listen to one Misfits song*/
wpr -stop c:\last_caress.etl

I won’t bore you with all the gruesome details, but after digging through the numbers, I found some Dear Old Friends:

QDS.DLL!CDBQDS::DoPersistData                      75,009  89.95%
  → QDS.DLL!CDBQDS::CleanupCapturePolicyStats      74,994  89.93%
    → QDS.DLL!::operator()
      → QDS.DLL!CDBQDS::GetStmt                    74,249  89.04% exclusive

Which, in this case, was conclusive.

You may feel tempted to argue with me, and if you have a Being Wrong™️ fetish, that’s totally cool. This is a great opportunity to max it out.

I only kink shame furries.

There’s Another Diamond Ring

Unfortunately there’s not much you can do when this crops up. Various trace flags recommended for Query Store don’t help, nor does recycling or rebooting the server. The task will just start back up.

All you can do is wait for it to be over, like when your wife puts on Love is Blind and opens a fresh bottle of wine.

Anyway, good luck out there.

Thanks for reading!

Going Further

SQL Server Performance Office Hours Episode 81 – A Slightly Disordered Release Schedule

Posted on September 17, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 81 – A Slightly Disordered Release Schedule

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:34 – SQL Server Performance and Key Lookups
00:03:27 – Lock Escalation Issues
00:05:32 – Query Store Runtime Discrepancies
00:06:49 – Latch Contention vs. Locking
00:08:13 – Worker Thread Starvation Detection

Full Transcript

Alright, are we taping here? Is this thing on? Alright, Erik Darling here with Darling Data. One last lovely go around here in Paris, France, where it is still amazingly brutally hot. I don’t know, I understand why people didn’t really argue when they were getting the guillotine. Man, it’s summer here, I would just, yeah, just take me. Maybe we can just get this over with. But anyway, we continue to get rid of office hours questions that have stacked up, stagnated, I don’t know, whatever, other ST words, stuck in this queue for a while. Because I don’t feel like doing demos while I’m on vacation. There’s really no ergonomic way for me to do that. So here we are. And let’s see, what is our first question as soon as, ah, yeah, wow, thanks, Google. Google Sheets, you’re being a real pal. SQL Server sometimes does millions of key lookups. How does that not instantly kill the server? Well, I mean, apart from the fact that SQL Server is, or at least used to be, a rather well-engineered piece of equipment here, there are plenty of safeguards that would prevent it from killing the server. For example, the key lookups might be single-threaded, right?

It’s just one CPU. One CPU is probably not going to kill a whole server, unless you only have one CPU, or you’re in Azure. Sorry, Azure. And then maybe you set a max degree of parallelism correctly. And so your queries don’t use, like, all your cores to do them. You know, there are all sorts of ways in which this might happen. But, you know, coming back to SQL Server itself, it’s a rather well-engineered piece of software in general.

You know, it’s pretty efficient with things. So doing millions of a thing for a CPU, especially a modern CPU, probably not going to kill a whole server. There are other things that will, like you, but, you know, key lookups, probably not going to do it. All right. Next up, are lock escalation issues still common or mostly a thing of the past?

Well, you know, people talk about lock escalation almost in the same way as, like, fragmentation and page splits, as if it is just some boogeyman waiting to come out from under your server’s bed and snatch it and bring your server into the shadow realm where it’s never to be seen again. But so, like, lock escalation is one of those things that, you know, you should obviously try to avoid unless you’re doing it intentionally, in which case, good job. You did something intentionally. Hopefully, well-informed choice there.

But for me, what’s more likely the thing is lock escalation might get attempted a lot, but there have to be no competing locks going on in order for that escalation attempt to be successful. So, like, just because lock escalation is attempted does not mean it’s happening. So, you know, is it common? I don’t know how terribly common it is.

Is it a thing of the past? No, it still exists in the product. But for me, I just think with a lot of highly concurrent workloads where a table or object level lock would really, like, show and hurt you, there’s probably a lot of other competing stuff going on that prevents that from happening.

You can do all sorts of boneheaded things that would, you know, make it likely to be attempted and keep being attempted and, you know, maybe get lucky and happen or unlucky and happen. But, like, you know, in that case, I don’t know, maybe at some point you would want it if you’re doing one of those boneheaded things like updating 10 million out of 12 million rows or deleting 10 million out of 12 million rows. Because I don’t know, right? All sorts of reasons why you might want it.

All right. Why does Query Store sometimes show totally different runtimes than what users experience? Well, I think in general, when the database is showing one set of numbers for sort of how things are going for users and like, wait, like how things are going within itself.

And users are experiencing something else. Then it’s probably one of those things where it’s not the database anymore. It’s not our friend SQL Server anymore.

Most likely there is something happening over on the app server side. It’s making it’s like, you know, you’re seeing that big, long spinny wheel. Maybe your company is AI ready and they have changed a loading screen to say thinking.

But most for me, mostly like when if I’m seeing everything very, very fast in Query Store or at least reasonably fast in Query Store, but users are still like, I don’t know, I don’t know, Eric. Every time I run this query, I’m sitting over here waiting for a half hour. But, you know, demonstrably in Query Store, you know, or like when I run the query, it is is not taking a half hour.

Then to me, that smells like an app server problem, either perhaps an overwhelmed or under hardware budgeted app server or perhaps the software itself doing some sort of terrible stuff to that data. Once it shows up, crunching it or doing whatever to it, calling out to external services. I don’t know the millions of things in an application might do.

So you have to start looking generally somewhere else when that is the case. All right. When should you suspect latch contention instead of locking?

Well, it’s an easy one when the weights are latch and not lock. Right. So if the primary thing you see queries waiting on is something that starts with the word latch and all uppercase with some underscore.

Other two letter thing after it, then you’ve got latch problems. If your weights all start with LCK and an underscore and a bunch of other mishmash letters afterwards, then you’ve got locking problems. It’s a good one there.

All right. Final question for today. Lucky number five. Why? Why? Oh, sorry. I keep hearing about worker thread starvation, but I’m not sure how to actually detect it. Well, that’s the funny thing about worker thread starvation is if you’re trying to detect it, you’re going to need a worker thread.

And if your server is starved of worker threads, it might not be so easy to detect. So what you would need to do is either on your own, of your own volition, you would need to start sampling weight stats and looking for jumps in threadpool waits and probably looking for where the average milliseconds per weight is rather on the high side. The other thing that you could do is perhaps, you know, a young, handsome SQL Server consultant who makes a free open source monitoring tool.

And, well, I can’t claim that my monitoring tool can work around thread starvation because it can’t. I don’t, like, try to prioritize my queries above all the others. But what that will do is show you weight stats and it will show you deltas of weight stats.

So you can look at a weight stat graph and probably what you’ll see is things going, you know, looking weight statty. And then there might be some blank space in there because my queries won’t be able to run because worker threads are starved. And then what you’ll see after that is a big spike in weights, perhaps threadpool waits.

So you can either do it yourself or you can trust me to do it for you. You know, again, free monitoring tool. It’s pretty good.

Does all the stuff that the big fellas do, but at a fraction of the price. And by fraction, I mean free. So that would be my recommendation there. All right.

Anyway, this is the last one from Paris, leaving for Portugal. And hopefully have some other at least scenic backgrounds. I don’t quite have the lack of shame that it requires to go out in the world and record one of these. But I’ll do my best.

All right. Thanks for watching. Hope you enjoyed yourselves. I hope you learned something. And I will see you in Portugal. All right. Goodbye. Bye.