Indexing Archives | Darling Data

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

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

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

Going Further

SQL Server Performance Office Hours Episode 80 – No Sleep Till Portugal

Posted on September 16, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 80 – No Sleep Till Portugal

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:02:30 – Office Hours Queue
00:04:45 – Blocking Chains and Max Stop
00:06:15 – Forced Plan in Query Store
00:08:07 – Terrible Cardinality Estimates

Full Transcript

Erik Darling here with Darling Data, and it is horrible once again here, heat-wise. Yesterday it was so bad that my thing actually disconnected from my phone when I was trying to record. So if the end of that video yesterday was a little wonky, now you at least have a reasonable explanation why. I’m managing, but just barely. I’m gonna take a cold white wine bath, probably Sancerre, Chablis, something like that, and then maybe just drown in it. Like a fruit fly, just drown. Like so many of the fruit flies I have envied drowning in a glass of wine in my life, that’s how I’m gonna go out. Anyway, we are going to finish, or we’re not finished, we still have a long way to go on this. But, the next thing we have up is, uh, answering, draining more of the office hours queue here. So, we’ll start off with, sometimes blocking chains disappear if we raise max stop. That feels backwards. Explain, please. Uh, well, it, most likely it’s just that some of your queries are finishing faster. I mean, uh, like I don’t, you didn’t really describe what, what sort of blocking problems you’re having. If you’re under the default read committed garbage, isolation level, uh, where read queries and write queries still fight with each other, then you, you, it very well could be cases like that where things are, uh, are improving and, uh, you know, you see your, your, your queries finish faster, so blocking goes away. For modification queries, of course, the, the, the, the right cursor portion of the query plan is always single threaded, except for in some limited insert cases. Uh, but, I suppose it’s also possible that the read cursor portion of those finish quicker, uh, with a more wide parallel distribution of rows, and that would explain it, part of it as well. So, that’s, that’s what I would go with there, at least, um, if, if I’m thinking about your question correctly, which hopefully I am. All right. We forced a plan in query store. Ah, this computer’s getting dim. Uh, and performance got worse.

Why would that happen if it was the fastest plan historically? Well, two things could be happening to you. Uh, one, uh, your, your, your plan could, uh, fail to force. That’s, that’s one. And two, uh, it might have only been the fastest plan historically under a certain set of circumstances that, uh, that are not shared by all the other, uh, query patterns, uh, that, that are now forced to use that plan. Uh, that’s something that has happened to me in the past that has bitten me, and it wasn’t fun. Uh, so, uh, so, uh, that’s most likely, uh, a parameter sensitivity issue and not just, uh, this query got a bad plan issue, right? So, uh, that’s what I would, that’s what I would hedge that one on.

All right. Uh, what plan operators usually signal something very wrong is happening here? Well, in a select query, uh, an eager index pool would be the first one that comes to mind. Uh, perhaps, uh, a lazy table spool, uh, would be another one that I would look at. Um, any sort of pattern where you have the constant scan, uh, merge interval thing happen that goes into a nested loops join, especially a serial nested loops join that usually indicates that you have a join with an or clause or perhaps some mismatched data types and, uh, correcting those, which I have many, many videos on would alleviate those plans. Uh, parallel merge joins, uh, order preserving operators, uh, order preserving exchanges and parallel plans.

Uh, that, that would include, well, I guess, sorts, uh, and stream aggregates. Never like to see those. Never like to see those. Uh, that’s, that’s, uh, top above a scan would be another one that I would, I would say rounds out that bunch. Those are, those are my least favorite things to see in query plans right off the bat there. Uh, let’s see. Uh, let’s see. Next up. Why do sorts suddenly explode in memory usage when data grows a little bit? Uh, could just be estimates. Um, could just be some, it could be the, uh, stats updated, uh, along with stuff. Um, cause you know, like data growing a little bit could still be influent, but could still require a lot of modifications to your tables where, uh, you know, you would see, uh, you know, stats get updated automatically. Uh, perhaps a stats update job. Um, yeah, that’s, that’s usually how it goes.

Uh, but you know, the thing that, that, that usually drives, uh, sort, uh, memory usage, you know, aside from the number of rows that you’re, uh, that you’re dropping, that you’re putting through the sort operator, estimating to put through the sort operator is of course the width of those rows. And I’m not saying that anyone has gone and changed the width of your string columns, but sometimes adding, uh, just a few, like, uh, like a more rows to the cardinality estimate will also serve to inflate, uh, the size of the data. The SQL Server estimates, uh, will be, uh, passing through the sort and could make memory usage, um, will explode to some degree.

All right. Last question here before I go melt into oblivion in my bathtub. Uh, is it possible? I hate the doors. I don’t want to die like Jim Morrison. Gah. All right. Forget that. It’s not going to happen. I’m going to figure out a different way.

Uh, is it possible to tune around terrible cardinality estimates or do you usually rewrite queries instead? What does tuning around terrible cardinality estimates infer if not rewriting the queries? Uh, you, you, you have asked a tricky question. Um, so like, I mean, I guess tuning around, I don’t know.

Are you updating statistics? Are you, are you, uh, are you, uh, creating indexes or creating statistics of some variety? Uh, are you forcing different cardinality estimation models? If not, if you’re not doing any of those things, I don’t know what tuning around this means if you’re not rewriting the query.

Uh, but you know, for me, terrible cardinality estimates, um, if you can, if you can root cause the terrible cardinality estimate, um, perhaps it is in outdated statistics issue. Perhaps it is, uh, the cardinality estimation model you’re using an issue there. Uh, perhaps you are doing something non-sargable or, uh, using some query form like a local variable or table variable or even table value parameters, which, uh, mess cardinality estimation up in slightly different ways. Uh, that was an all B possibilities as well.

Uh, and you know, uh, maybe you’re using a common table. A CTE, a popular blog, uh, table, uh, topic this week, uh, CTE not being so great. Uh, and perhaps, uh, you have some very complicated query in there and perhaps, you know, dumping stuff to a temp table is many SQL Server luminaries have suggested over this past week.

Uh, could be a viable exit from your terrible cardinality estimation problems. But, uh, your question, uh, as it stands, it’s quite an odd bird, right? Because I don’t, I still don’t quite get, how do you tune around terrible cardinality estimates?

Or do you usually rewrite the queries instead? That is going to twist my melon for the remainder of my days, which as soon as I figure out an alternative to drowning in a bathtub of wine will, uh, be shortly numbered.

Anyway, uh, the heat is getting to me. There’s a, there’s a moped gang going by and, uh, I, I, I have to go do things. So, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video, which will sadly be my last one from Paris. And, uh, we’ll be, uh, uh, I’ll be picking up next week in, in sunny Portugal, which we’ll see, we’ll see how hot that is and how much my, my will to live is diminished by that heat.

All right. Thank you for watching.

Going Further

SQL Server Performance Office Hours Episode 79 – Dang is it hot again

Posted on September 15, 2026August 20, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 79 – Dang is it hot again

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:28 – TempDb Contention in 2022+
00:03:57 – Serial Plan on a 32 Core Machine
00:06:04 – Batch Mode Considerations
00:08:45 – Memory Pressure Indicators

Full Transcript

Erik Darling here with Darling Data, and it is back to being a sweltering madhouse in Paris. And, uh, yeah, I’m having a real tough one today. It is brutally hot out there. And, um, as good as white wine is, it is not very hydrating. So, I’m just feeling a little weird. Anyway, uh, we’re going to continue to clear out, uh, the office hours, this cache of questions that have built up, uh, while I’m, while I’m traveling on vacation. Uh, we got a few more days left here in Paris and then off to Portugal and, well, uh, destinations beyond, as they say. Anyway, the first question of the day is, is TempDb contention still a real thing in 2022 plus or mostly solved? Uh, it can still be a real thing, but I, well, let’s do two things here. One, let’s hand it to Microsoft because they did, uh, really, cause they have done a lot of TempDb work over the years. Uh, probably most notably the 2019 feature, uh, the in-memory TempDb metadata. That’s good. I, I like that feature. Um, when it works well, it works well. Uh, I, I have run into some, I mean, I think earlier on I did run into some very strange, uh, like memory leak. Everyone says they think they found a memory leak in SQL Server.

I’ve, I’ve, I’ve run into a couple of real bad memory issues in SQL Server. Um, I had one client, uh, who turned it, had to have an availability group and I’m not saying the availability group is part of it, but, uh, I hate availability groups. So, uh, let’s let them share the blame a little bit. Um, then, uh, the primary node, uh, they had the in-memory TempDb feature enabled on the secondary. They did not. And every three, seven, some odd number of days, uh, uh, the, the primary node would, would, would perform it just completely eat, eat, eat, eat, cake, uh, and, uh, uh, performance with tank. And they’d fail over to the secondary. Everything would be fine for a while. Uh, and then they’d be like, ah, everything must be cool. Let’s fail back over to the primary. And then, uh, some modulo odd number of days, uh, would pass. And then, well, things would get weird there again. Uh, so I, I, like I said, I have run into some, some, some not great stuff with it, but you know, I, I deal with, uh, the strangest parts of the SQL Server universe. I do not deal typically with very normal things that most normal people would deal with. So I’d say for you, uh, yes, it probably is solved, especially with the in-memory metadata feature. Um, other people, they need some extra help. All right. Next up. Uh, how bad is it to rely on forced plans for months or years?

And it’s not bad at all. As long as they still work well, what are you worried about? Are you going to enforce them and deal with that hell again? I don’t know. It seems like a bad call to me. It’s not, it’s not how I would want to do things, but you know, maybe, maybe you live a little bit, maybe you sail a little bit closer to the wind than I do. I don’t know. Uh, it could be, could be a real wild and crazy kid over there. I don’t know.

Uh, let’s see here. Uh, well. Why does SQL Server sometimes choose a serial plan on a 32 core machine for huge queries? Well, he said, he said the word sometimes. So I’m going to assume that there are times when it chooses a parallel plan, which most likely means that there is nothing directly inhibiting a parallel execution plan like a scalar UDF or inserting into a table variable, uh, or rather a non-inlineable scalar UDF or, uh, inserting into a table variable or something.

So, uh, sometimes it, again, these questions, every time someone asks, it all comes down to costing. It all comes down to the optimizer costing queries and figuring out if parallelism would be a useful, uh, addition to the query. Uh, remember, remember that, uh, in order for a parallel plan to occur, a number of things have to happen.

Aside from, uh, just there not being any direct inhibitors to a parallel plan being used, uh, the, the cost of your query plan still has to break your cost threshold for parallelism. And then SQL Server has to land on a parallel plan that is cheaper than the cost of the serial plan that it began with. Remember, all plans start as serial execution plans. Parallelism is a choice derived later in query optimization.

So, uh, it, it most likely the cost just didn’t work out. Uh, the cost math just didn’t work out for your query to get a parallel plan sometimes. All right. Uh, next up over here.

Uh, when is batch mode actually a bad idea? Um, so I think, I mean, it’s bad idea. I mean, there are certain queries where batch mode is less useful.

I think one of the things that, that always gets me is, um, uh, batch mode bitmaps in row mode plans or in mixed, uh, like batch mode on rowstore plans. Because, uh, you see like a filter operator after a big clustered index scan and you’re like, well, that’s, that’s nice, but it didn’t quite work out. Um, so I, I would say like, you know, um, mostly with smaller queries where it’s just not necessary, uh, is where things get a little weird.

Um, plans that are prone to spilling, uh, on a sort maybe, uh, cause, uh, batch mode sort spills are, uh, just absolutely delinquent part of the engine. And I’m not sure why that hasn’t been fixed yet because it’s been a known problem for years. They, they are, they degrade like 500 times worse than row mode spills.

Um, so I don’t know. There’s, there’s certainly some times when batch mode is not the best choice. It was like a bad idea.

I don’t know. Query by query basis. Like, like, like a lot of the Mets lineups listed as day to day. All right. Uh, how do you, shut up motorcycle.

I think you’re so cool on your Parisian motorcycle. Uh, how do you identify memory pressure before the server completely melts down? Well, it might start with small clues.

I think probably the, the, the best sign of memory, memory pressure starting to, to line up, uh, would be in your wait stats. Uh, if you have lots of page IO latch, the IO is key in there, underscore SH or EX waits. Uh, that means that, you know, your server is already, uh, potentially under memoried.

Uh, and so when, when you see, uh, queries just routinely, uh, needing to go to disk for stuff, that is most likely a good indicator that some more memory would help. Of course, other things like indexing, compressing indexes, archiving data, stuff like that can, I’ll contribute, but, you know, um, in general, uh, you know, that, that would be a pretty decent place to start.

Apart from that, uh, you know, you might see either the resource semaphore, you might see the resource semaphore waits start to crop up a bit. Uh, those are always, uh, a potential thing to show in there.

Um, so, you know, that, that wait stats is usually where I would start there. Um, you know, especially if you, uh, have queries, like even if you don’t have the resource semaphore, if you have queries that are asking for very big memory grants, that memory has to come from somewhere.

That memory generally gets stolen from the buffer pool. So queries get more reliant on disk. So there’s lots of reasons why you might, um, uh, lots of reasons why you might start looking in wait stats initially to figure that out.

All right. I think we have one more here. Uh, can temp tables cause blocking in user databases or only in tempDB?

Well, technically only in tempDB, right? But, you know, most queries don’t run in tempDB. So if you have contention that is causing blocking in tempDB, uh, like on, on user, on, like temporary objects or whatever, uh, then queries in user databases will might be the ones getting blocked.

So, uh, it’s an interesting question though. Look, what is the locality of the blocking, right? Because the resource contention is in tempDB with the queries running in the user database.

There’s all sorts of interesting things to consider there. But, um, I think unless you’re using a global, global temp table, then, uh, that multiple queries might try to modify, insert, update, delete from, uh, then that would be a different thing. But, uh, if you’re using global temp tables, God, God help you.

You have, you have done something strange in your life. I don’t know that there is help for you. All right.

Anyway, uh, it’s, it’s too hot to keep this going. I need to go into the shade and I need to, to start paying attention to my glass of wine. So 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 continue our Parisian adventure, answering office hours questions. All right. Goodbye.

Going Further

SQL Server Performance Office Hours Episode 78 – There’s No SSMS In Paris

Posted on September 10, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 78 – There’s No SSMS In Paris

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:02:45 – Blocking Chains and Deadlocks
00:05:15 – CPU Low but Response Times Awful
00:07:39 – Plan Forcing Safety
00:09:26 – Statistics Updates Impact Performance

Full Transcript

Erik Darling here with Darling Data. It’s another wonderful day here in Paris, France, and I am coming to you from a slightly different window in the apartment. There are some windows where it would not be feasible to do any of this from, but we get a few good ones with rather nice views of stuff out there that, I don’t know, well, I get to look at and enjoy, so I’m sharing a little bit with you, but I’m probably not going to invite you over. Yeah, anyway, we got some questions to answer here from the fine folks out there in the SQL Server community. So the first one is, we see massive blocking chains during peak loads, but no deadlocks. Is that normal? Is something broken? And the answer is, I would say that’s fairly normal. Well, sometimes blocking does beget deadlocks. Quite often, blocking just happens rather naturally on its own without two queries getting into a fight over the same resources. Note that I said resources there because deadlocks can happen for far more reasons than the usual sort of embrace of death scenario like, you had to update table A, then table B, and this one tried to update table B, then table A. There are all sorts of things that are happening in the same way.

There are all sorts of weird things that can happen in there. Like even on the same table, it’s like you could see two queries deadlock if one tries to update index A and then index B, and the other one tries to update index B and then index A. And queries can even deadlock on themselves in a parallel query plan. Sometimes things get a bit mucked up in there. Most frequently around exchange op, well, I mean parallel deadlocks by definition happen around exchange operators. Usually when they are order preserving. So if you have a parallel query plan with merge joins or sorts or stream aggregates in there, they are more prone to parallel deadlocks. All right, next question. Sometimes CPU is low, but response times are awful. What gives? How does that happen?

Well, I mean, it’s something that I’ve talked about, I think, at least a couple times in recent memory here. You know, CPU being high is actually, I think, better because it means that everything is kind of running. I would take high CPU over low CPU and things being awful. Low CPU and things being awful could be any number of things. You could be looking at, you know, the usual blocking scenarios. You could be looking at threadpool. You know, you could be looking at your, I feel like I just answered this. Just ask me this question. You could be looking at things like your app servers being overwhelmed and just encumbered in ways that they were not meant to be.

So all things worth looking at. All right. Is plan forcing safe long term or just something to buy time until we fix real problems? So I assume by plan forcing, you mean query store plan, plan forcing, which in my experience is quite fragile and I would not rely on it long term. If that query ever gets a new query ID, you fail over, yada, yada, yada, yada. You are looking at plan forcing no longer working.

So from my perspective, it is a temporary bandaid. The one place where it is potentially not a temporary bandaid. And this would go for query store hints as well is if you are using an ORM rather than store procedures or even something like Dapper that allows you to write ad hoc SQL queries into a string. There are all sorts of places where I think link to SQL allows that as well to some extent. I’m not a developer, so I do not traffic well in these things.

But I would say it is a temporary fix unless you are in a position where from your position, you cannot change the code immediately. Query store plan forcing and query store hints are both rather fragile though, so be prepared to monitor that situation closely. All right.

Our app suddenly started timing out after a statistics update. Could a stats update actually make performance worse? Sure. There’s always that possibility. You know, I am in the camp that sticks at least pretty closely to up-to-date stats being the best way to allow the optimizer to make reasonably good choices.

Mostly because of the, like when you have data that gets added to a table and SQL Server does not have a representation of that data in the histogram, but users are querying that, the optimizer doesn’t have a really strong way of figuring out cardinality estimates for those off-road histogram steps. Other database engines like CockroachDB will do things a bit smarter. They do something a little bit more sophisticated where they’ll like look at like how many rows exist for like other values and be like, oh, well, I can infer, you know, cardinality will probably look like this for those newer values.

They project some stuff out. So SQL Server just, you know, on the legacy cardinality estimator, you get a one row guess and the new cardinality estimator, you get sort of like a density vector-ish estimate on the number of rows. It’s like 30% of the table or something like that.

But, you know, so like I do think that stats updates are important for those things. But I also do recognize that stats updates can cause issues in other places. For example, they might cause a plan flip where you get a worse execution plan afterwards.

That’s always a possibility. And, of course, if you run a stats update and all of a sudden a whole bunch of things need to compile plans and, you know, you don’t have a rather – no, I’m sorry. I was going on the wrong path of that.

And if you have to compile plans for stuff and you have a really highly concurrent environment, you can see a bunch of compile locks and all that stuff. So, you know, stats updates, they, you know, well, I do believe they are necessary. They can mess with stuff in new and interesting ways.

So, again, I do think you should update stats, but you should also monitor the situation closely. All right. Last question here.

How do you debug performance issues that only happen under real production load and not under the dev server load? Well, this is why I built a monitoring tool because all of the good stuff happens in prod, right? All the interesting stuff happens in production.

And, you know, not having active monitoring there, it leaves you just really blind. You know, the way that I try to tell people is if you can’t see a problem, you can’t solve a problem, right? These things need observability, you know, especially as DBAs are, I think, you know, shifting closer and closer to the SRE family of employees where, you know, you have DBREs and all this other stuff.

I think observability is far more important. You know, you’re not just checking on like, oh, did the backups finish? Did the maintenance finish?

Oh, do I have to patch this thing? Like you’re not doing just like dull ass production work anymore. You’re actually having to do like real stuff that, you know, keep servers up and alive and running. And, you know, having a problem and not being able to see deeper into that problem or have any record of that problem, you know, you might get notified of a problem far after it happened.

And by then, SQL Server may have been restarted and you may have absolutely no evidence of what was happening on the server at the time. So, you know, anything that you can do to increase observability is important for you because like setting up an extended event or something or a trace or something after the fact buys you nothing, right? Like you might catch the next thing if there is a next thing, but you still don’t know what caused the current thing.

So having as much observability into the current state of the database and current database problems, it just gives you such an advantage because you can not only have, you can respond, you can respond reactively more intelligently when you have that, that observability. But also it gives you the ability to, you know, just like forecast these problems, see what’s getting hot, see where, you know, see what’s really lighting up the server, you know, start fixing stuff before it turns into that. It’s just so important to have your SQL Server monitored.

And again, that’s why I built free SQL Server monitoring tools because, you know, I got sick of showing up to client sites and them not having a monitoring tool or them having a really crappy monitoring tool. I won’t name any names. You know who you are, every single last one of you. And like, just like, like, okay, like, well, you have a monitoring tool. Do you use it? No. Why? I can’t figure out what’s wrong.

Okay. Well, you don’t have a monitoring tool then, right? Let’s just over and over again. It’s that. So, you know, from my perspective, like I got real sick of that and that’s why I built my thing. It’s free. And there’s absolutely no reason not to use it. It’s for you to do your job better, right?

I ask nothing of you in exchange for using it except do your job better. All right. Anyway, that’s five questions. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. It wouldn’t be office hours without a siren.

And I will see you next Tuesday with another office hours video. All right. Thank you. Goodbye.

Going Further

SQL Server Performance Office Hours Episode 77 – Paris is nice again

Posted on September 9, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 77 – Paris is nice again

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:25 – Query Plan Changes Explained
00:04:07 – Redesign vs Tuning
00:06:39 – Performance Queues and Delays
00:08:58 – Query Store Analysis

Full Transcript

Erik Darling here with Darling Data, and we’re coming to you from a slightly different Parisian window today where thankfully the temperatures have gotten back to a place where I no longer feel like I’m dying without an air conditioner. So enjoy the view a little bit. There we are. Look at all these wonderful French people doing wonderful French things. Anyway, we’re continuing to clear the air conditioner. clear out the office hours questions queue. And so that’s our job. And we’re gonna do it. Just because I’m on vacation doesn’t mean I’m not working. Do what you love and you’ll never take a day off in your life. Alright. First up, what causes SQL Server to suddenly change join strategies for the same query text? Well, could be any number of things. Alright. Usually all the same things that it always has been. SQL Server came up with a new query plan, perhaps because of a recompilation or what it would be not necessarily a recompile, but a fresh compile because like the cache got cleared or something. Perhaps some statistics updated. Perhaps we have a parameter sensitivity issue. But it is always one of those things, right? Something just changed and we had to we had to make a different decision for some reason. And here we are brand new query plan, brand new join strategy, sometimes better, sometimes worse, but always valid. That SQL Server always valid. You know, there are a number of things you can do to stabilize these plans. Like we talked about in yesterday’s video. I’m a huge fan of query hints. I think they are underutilized by people. I think people are unnecessarily afraid of them. But you know, you can always do other stuff too. You know, if you want to do, I wish, I wish the guarantees of query store plan forcing or hints were better. They’re not. Not that great. Yeah, so that’s not, that’s not really my favorite recommendation. You know, there’s plan guides, I guess, but really what it usually comes down to for me is looking at the two different query plans and figuring out if there is something you can do in order to get SQL Server to choose a more stable query plan naturally. Sometimes that is index changes. Sometimes that is query rewrites. But in general, that’s that’s the first place I go. And if that offers me no, that offers me no real help. Then I will help then I will go to query hints or something else. All right. When should you walk away from tuning and recommend redesign instead? Well, man, that’s a big one. So the thing with redesign is that that means I mean, I guess it depends on like what at what level we are redesigning. Are we redesigning a wide table to normalize it? Are we redesigning some something else about the way that we’re storing data to make it easier to access the data paths that we need?

You know, for me, you know, for me, it is usually a question of will that redesign take longer and benefit the general performance characteristics more than me just pulling the data that I need out and into the format that I need along the way? Temp tables are a great way of doing that. You know, so I don’t think that I would ever walk away from tuning. I think that the two tasks really have to be done in parallel. Because when you have when you have when you have like performance problems across the board, you need to do something to alleviate them immediately.

But you also want to take the proactive and take good long term steps so that, you know, you like you don’t have to keep doing all the other work to move data around just so a query can run your query should. Your query should, you know, again, the polishing ivory that I usually do here is, you know, store data the way you query it and query data the way you store it. If you have to keep changing data in order to get it out, perhaps you should start storing your data in a format that is more aligned with the results that you are returning.

But this is specifically geared towards like when I would recommend to redesign, not just like, you know, go ahead and start changing tables and doing whatever you want. When I get it willy nilly because that’s that’s painful and often requires application changes. If there’s one thing I know about DBAs, they don’t make a lot of application changes.

So that’s that’s that’s what we got there. Let’s see. Why does SQL Server sometimes just stop like everything queues up for five to 10 seconds and then magically recovers? Well, there’s a there’s a lot of reasons for this.

I mean, the obvious one is locking and block. It’s this usually comes down to resource contention. It’s in it’s someplace along the pipe. So the most common one, especially if you’re using the God forsaken garbage isolation level read committed, locking and blocking between read queries and write queries, I’ll absolutely do it. More severe ones would be things like threadpool waits running out of worker threads, resource semaphore running out of query memory grants, resource, query memory grants space, resource semaphore query compile, or even just beyond that compilation locks.

Like if you’re waiting a long time on like synchronous stats updates and like the same procedure needs to compile a plan across like 100 sessions, that first session sitting there waiting on the stats update and then the 99 other ones are waiting for this one procedure to cache a plan so that they can all use it. That’s another gnarly one.

There’s all sorts of stuff that could do it. Even if you don’t hit threadpool, you can get pretty backed up on just like runnable queues. Like, you know, queries have four different, let’s say four different states in SQL Server.

They’re running, they’re on a CPU, they’re runnable, which means they could run if they could get CPU attention. They’re suspended, which means they’re waiting on something that’s not CPU or they’re sleeping dormant, whatever. You know, if you can stack up your runnable queue pretty high even without hitting threadpool waits.

You can see that a lot when people mess with the max worker thread setting, which is kind of a goofy thing to do for most people. I would say maybe don’t run and do that first. Like that’s maybe not my favorite setting for people to change.

Alright. Query store says average duration is low, but P95 is horrible. Which one should I care about more?

Hmm. Uh, well, that’s an interesting question. Uh, I don’t know if 95% of your queries seem to be unhappy for some reason. Uh, then that’s probably something worth looking into.

Um, the thing with, well, I mean, actually I don’t even know. Cause, cause like it’s just like a query store should have a pretty good detailed query history. And so you should be able to ascertain mostly that like average duration is not good for something.

Um, I would, what I think, so if you’re getting, I would, I would wonder where you are getting your P95 from. There are lots of monitoring tools out there that are not quite as granular as, uh, query store is. Uh, Datadog being one of them.

Datadog is highly sampled and highly aggregated data. And they do not exercise, uh, their data collection across many of the sources in SQL Server that you would expect, uh, query sampling to go from, to, to come from. Not like DMV wise, just the columns and the DMVs.

Um, they do a lot of sort of like averaging over totals. Like I think they only collect total. I don’t think they collect like mins, maxes and stuff. So I would really wonder where you’re getting your P95 numbers from if query store is saying that average duration is a-okay.

Uh, so I would, I would wonder, I would wonder a little bit about that. Uh, let’s see here. We got, that was one, two, three, four.

We got one more. Oh boy. All right. Last one. And then I will, I will give you another view of Paris and then I will say goodbye. Uh, uh, I see memory grant feedback kicking in over several executions, but performance still sucks. Is that feature actually reliable?

Well, it has gotten more reliable. Uh, but if memory grant feedback kicks in and does stuff, uh, perhaps it is not memory grants that are your problem. Your problem could lie elsewhere.

Uh, you know, it, it is, it is amusing how people think that these features, uh, individually will, will just solve all their performance woes and worries. But, uh, it’s not, not often the case. Um, you gotta, you still gotta do some work once in a while.

So what I, what I would look at, I mean, you could look at like what memory grants are affecting about the queries execution. You know what I mean? Like if you’re, if you get to a stable memory grant and things are not spilling, then that’s cool. But you’re, you know, there’s still a million other things that can go wrong on a query plan and you should spend some time looking at what those million other things might be.

Because memory grants are just one very, very, well I wouldn’t say one, one very small, but they are certainly one, uh, piece of the puzzle that make the entire query performance picture whole. Alright. So here you go.

Here, here’s, here’s Paris. This is, well this is sort of what I get to wake up and see every morning. Uh, it’s pretty nice. Uh, I like it. Anyway, uh, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something and I’ll see you in tomorrow’s video where we will clear out more office hours questions. Uh, they, they, they really piled up. Uh, and the, the once a week thing was not clearing out the cubes. Uh, you know, anyway.

Alright, goodbye.

Going Further

SQL Server Performance Office Hours Episode 76 – Paris Heatwave

Posted on September 8, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 76 – Paris Heatwave

To ask your questions, head over here.

Chapters

00:00:00 – Introduction
00:01:35 – Hints for Long-Term Solutions
00:04:02 – Performance Issues Beyond Hardware
00:05:30 – Execution Plan Analysis
00:06:28 – Delete Operations and Indexes

Full Transcript

Erik Darling here with Darling Data and today’s video is coming to you from Paris, France. Be here for two weeks with the family. Sounds just like New York out there. And then we will be popping around Portugal for a couple weeks. So I’m just going to use this as an excuse to clear out the office hours question list a bit and hopefully, well, I don’t know. hopefully maintain my high-quality SQL Server output for everyone. It is very, very hot here in Paris today. It is like 96 degrees and there is still no such thing as air conditioning. They have still not invented that yet. I don’t know who is in charge of Paris in this round of civilization, but I wish that they would hurry up and invent air conditioning because the people suffer, your majesty. So the first question that we have is, is there ever a case where hints are the best long-term solution? And the answer is yes, absolutely. Despite SQL Server being a highly advanced LLM, it takes query text in and spits out query plans. It still can’t figure everything out in one go. Sometimes it just needs a little bit of extra help along the way. So I would highly suggest that you, if you’re running into a problem and it feels like only hints can fix it, use the hints. That’s what they’re there for. They’re not just there as a cautionary tale in the docs for people to only certified database administrators can use hints. It’s silly. Anyway, I use hints quite a bit for everything from, you know, fixing production problems all the way down to just sort of like experimenting with different queries to see what different query plans look like and how things get costed.

At the very least, at the very least, they are an educational tool, but I find them quite handy for fixing all manner and variety of issues that would otherwise just not work properly. So there’s that. All right. The next question is, why do some servers feel slow even when CPU, memory, and IO all look fine? The two biggest things I find, it’s either going to be network related, like the results take a long time to get from the server point A to point B, or it is going to be something on the application or web servers that are receiving your results and maybe taking a long time to process or render those.

That is the two most common things that I see aside from anything, you know, let’s just say SQL Server side with something easy like blocking, right? Queries taking a long time to compile, you know, just like, you know, stuff that is not going to be like, hopefully not going to be the normal state of affairs. I guess locking, you know, SQL Server, sure. Yeah.

But, you know, the compile thing, that’s a little outlandish. So don’t, don’t, don’t run off and go tell everyone I said that. All right.

What parts of execution plans do people obsess over way too much? Oh man, it’s always costs and percentages. Those are the things that people will not let go of. I don’t know who told them, who taught them that costs were real things, that they were some sort of durable performance metric, but alas, they’re not, they, they just very misleading at the best of times and as complete liars at the worst of times.

So I think that that’s probably the thing that I would stick with there. Aside from that, you know, I would say individual operators, people hyper-focus on scans. That’s another thing.

I don’t know. Probably about it there. Mostly it’s costs. That’s the, getting people to stop paying attention to costs is far and away the most, the most uphill battle that I fight day to day.

And it’s not just with people. It is also with the robots. That’s why in Performance Studio and the Cloud Query Plan plugin that I built, like it is littered throughout the instructions to not look at costs, right?

If someone gives you an estimated plan, ask for an actual plan. If all you have is an estimated plan, there are like, you know, a variety of query plan patterns that you can look at, but always make sure that it’s grounded and you need to get an actual plan to figure out what’s actually slow.

So, all right, let’s see. Question number four here. What mistakes do people most often make when trying to fix parameter sniffing?

Optimize for unknown hints or local variables. That is it right there. I see people do that all the time, and I guess maybe it gets rid of parameter sniffing, but often the plan that you get is not the most high quality of execution plans.

That’s an easy one. That was like a ringer. Ah, man, I feel a little guilty about that. Why do some deletes take forever even when only removing a few thousand rows?

Oh, God, because you probably have way too many indexes that those deletes have to bounce around in. I mean, perhaps blocking, right? That’s always a thing that could happen.

But usually when a delete takes a very long time, it is because whether the plan is a narrow or wide index plan, it is most likely that you have a buttload of indexes, and all of those indexes have to be deleted from. You’re looking at ordering data stuff that has to go with sorting the data by the index key so you can delete from it, all that stuff.

That is far and away the most common reason why. So, yeah. Well, that or maybe you don’t have an index that allows your delete to find the data that it needs to delete in an efficient, timely manner, and that’s what’s messing you up.

All right. That’s five questions. It’s hot, and I need to go take my third shower of the day. I will not be live streaming that. This is not the time.

This is not the channel for it. But thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video, which will also be office hours, but hopefully a less hot office hours. All right.

Goodbye.

Going Further

Learn T-SQL With Erik: AT TIME ZONE Performance

Posted on September 3, 2026 by Erik Darling

Learn T-SQL With Erik: AT TIME ZONE Performance

Chapters

00:00:00 – Introduction
00:01:48 – Timezone Performance Issues
00:03:59 – Query Plan Analysis and Data Types
00:06:27 – Conclusion

Full Transcript

Erik Darling here with Darling Data, and in this video, we’re going to talk about mostly AT TIME ZONE performance, but also some other interesting stuff about AT TIME ZONE. I’m going to be real honest with you, I hate timezone stuff. I hate all of it. I’ve never taken naturally to it. It’s all too weird, and managing it is just such a nightmare. in UTC. Store someone’s timezone, I guess, and deal with it from there. As soon as you have to start mixing things, it gets awful. The absolute pits. Down in the video description, you will find all sorts of helpful links. You can hire me for consulting. You can buy my training. You can even buy the full version of the training that you’re seeing in this video with the coupon code down below. You can become a supporting member of the channel if you feel that the content that you’re getting here is worth $4 a month. And of course, you can continue to ask me office hours questions, and please do always like, subscribe, tell a friend, maybe even several friends if you’re poly-friendulous or something. If you’re in the market for free SQL Server monitoring, you can get mine. Totally free, open source, no email, no phone home, no weirdness. Just a bunch of awesome data collectors getting all the important stuff from your SQL Server, and giving you all the information you need to fix your, well, find and fix your performance problems. Recently launched, recently got some AG monitoring in there, and also recently went Enterprise Edition, and we’ve now got a headless Windows service. You no longer have to worry about not monitoring stuff if the app I still have those. I still have that version, but it’s a headless Windows service backed by a Postgres database. You can monitor hundreds and hundreds of servers for free with it. Gosh, is it pretty darn good. All right, let’s, let’s T-SQL ourselves here. So, at time zone has a lot of weird performance problems. Not weird, very obvious ones once you start looking at it. Basically, it’s bad if you do something like this, and it’s good if you do something like this.

Right? Do, don’t do your date math on your at time zone on a column, because you will beat the crap out of your server. Right? So, I’ve got query plans turned on, and I’m going to run both of these. And I suppose the fun part is that until they finish, we won’t know. We won’t know which one is our problem here. I guess we will, because the results, I lied. So, in this query right here, we are having our where clause where creation date at time zone, Eastern Standard Time is greater than D. And I’ve got even got a recompile hint on here. So, SQL Server, there’s no statistical mystery there. And in this one, I’ve changed it so that I’m just using a time zone that sort of reverses the math, right? So, the minus five UTC to plus five, or actually, depending on what time of year it is, I guess it could be fewer or more hours. But the first query that we see scans the entire clustered index takes 10 seconds doing that, right? And part of that 10 second scan is at time zone calling out to, like, Windows to figure out time zone stuff. Everything, every time is a Windows API call. Right? So, like, it’s tremendously slow there.

And this one is better, but it’s not the plan shape that we want. And it’s not the plan shape that we want, because we were slack with data types, right? Because when you add time zone something, what happens? You don’t, like, so, the creation date column in the comments table is a date time. But when you do this, you add a time zone to something, guess what happens? That’s not a date time anymore. So, we get this query plan shape that I’ve talked about before that happens when you are slack with your data types, right?

So, you would want to write your query like this, and you would want to convert the result to a date time, and then you get the nice simple seek plan like this, right? So, if we just converted to date time without the switch offset here, we’d lose the offset information and the time range, right? And we don’t want to do that, because then we’d get wrong results back.

So, the wrong result would look like this, right? Because if we just say convert date time at time zone this, we lose this, right? That’s the five hours that we want from there.

But if we do the switch offset, use the switch offset function, then we keep that, right? Switch offset adjusts the time zone offset while maintaining the point in time that your data is in. The underlying UTC stuff all stays the same, and it’s really useful for normalizing data to UTC or displaying your user’s local time zone, I guess.

You can even combine it with two date time offset when you need to attach an offset to a date time value, right? So, for our purposes and illustrating that a little bit, this is what we started with, right? If we send that, if we say, if we just add time zone that, then we get the time zone added, which takes five hours, right?

And we switch the offset, we get the five hours in here, and then when we convert that to a date time, right? We lose the time zone portion from these two, then we get the correct time from that. So, just a couple notes about that stuff.

I’ve got a few more videos that will go over time zone things and hopefully help you, because I have just struggled mightily with time zone stuff over the years, getting it to work correctly and getting it to perform well.

So, hopefully, the upcoming videos will also help you if you are also struggling with those things. All right. Thank you for watching.