Query Tuning Archives | Page 81 of 147

sp_PressureDetector: What SQL Server Looks Like Under Memory Pressure

Posted on June 14, 2023January 22, 2026 by Erik Darling

sp_PressureDetector: What SQL Server Looks Like Under Memory Pressure

Video Summary

In this video, I dive into how SP\_pressure detector can help identify memory pressure issues in SQL Server. I explain two primary forms of memory pressure: queries requesting large memory grants and the buffer pool being depleted to fulfill those requests. By walking through a demo with a specific query that requires a 10GB memory grant, I illustrate how SP\_pressure detector displays real-time data on stolen server memory and SQL reservations, highlighting the impact on overall system performance. Additionally, I demonstrate how concurrent workloads can exacerbate these issues by competing for buffer pool resources, leading to increased page IO latch weights and resource semaphore waits. This video aims to provide practical insights into managing memory pressure in your SQL Server environment.

Full Transcript

Erik Darling here with Darling Data, the hippest, hottest, most fire SQL Server consultancy on the entire planet. Get my lawyers involved if you say otherwise. And I meant to record this video yesterday, but I don’t know, I hit various weird things and some time constraints and then my wife forcing me to watch Vanderpump Rules, did not get a chance to do it. So here we are, day two, and we’re going to talk about how SP underscore pressure detector can help you identify memory pressure in SQL Server. Now, memory pressure generally comes, or I, as a consultant, I generally see memory pressure in two forms. The first form is queries asking for big memory grants, or even just lots of queries sort of asking for like this swarm of memory grants, and other queries not being able to get memory to execute SQL Server.

And we’ll talk about this in a little bit more detail. SQL Server has only so much memory it is willing to give out to queries to run at a time. The second form of memory pressure is that that memory has to come from somewhere, and that somewhere, because most servers just don’t have enough memory in them to begin with, that somewhere is the buffer pool. Now, memory grants don’t use the buffer pool. Memory grants share memory with the buffer pool. So there’s this tug of war between queries who are like, hey, I need some memory to run, and the buffer pool being like, hey, I need to cache all these data pages to give to queries. And, I mean, you know, there’s query memory grants come along, a bunch of pages get knocked out of the buffer pool, queries finish running, pages come back into the buffer pool, maybe, if you’re lucky.

And then queries run and then queries run and then queries run and ask for memory grants, pages get knocked out of the buffer pool. So please try to give your SQL Server enough memory so that you don’t have this constant gas station sushi effect of memory just dumping in and out various things. So, by default, SQL Server will give any query up to around 20-25% of your max server memory setting to run.

And also, by default, the single query memory grant is something that you can tinker with a bit, either with resource governor or with the max grant percent hint. Another default I find that is rather cruel but is not currently a setting that you can change is that SQL Server is willing to give out around about 75% of your max server memory setting to queries to execute as a whole. So if you have three queries come along and ask for that, like, 20-25% mark, SQL Server will give it to all three.

The fourth query asking for that big memory grant comes along, it’ll get stuck. But other queries that ask for smaller memory grants may be able to sneak in depending on how much is currently loaned out to those. There are some good ways to deal with that.

You know, resource governor, if you are on Enterprise Edition, the max grant percent hint is one way to deal with that. If you are not or if you, you know, want a more focused, targeted approach rather than forcing every query to use a lower grant. But resource governor is something that I end up using quite a bit, especially on client servers that have lots and lots of memory, like terabyte, two terabytes of memory in there, because 20-25% of two terabytes is a pretty gosh darn big memory grant.

Standard Edition is, you know, a different story. You get that 128 gig cap for the buffer pool, but you can use memory over that for other stuff. So my general advice is if you’re on Standard Edition and it’s a server you care about, it should be Enterprise Edition.

But barring that, Standard Edition, you should give 192 gigs of memory. I was really disappointed that SQL Server 2022 didn’t increase the 128 gig buffer pool limit on Standard Edition. But, you know, I can’t fight the bean counters, dorks.

But anyway, you give SQL Server Standard Edition 192 gigs of memory. Set max server memory around the 180 or so gig mark. You have 128 gigs for the buffer pool and then 50 or so gigs for other memory consumers like query memory grants, lock manager, plan cache, all that other stuff to sort of deal with.

So I have, even though I have like 96 gigs of memory assigned to this humble virtual machine, I’ve turned max server memory down on this one because I want to exacerbate my problems. 96 gigs of memory is, you know, pretty good for the size of the database that I’m dealing with.

So I turned it down a bit to make things a little more close to what I see in real life. So if we look at this execution plan and we look at what this query got for a memory grant, it is 10 gigabytes. All right.

So this query gets 10 gigs of memory. This is about the most that a query can get on my server with max server memory set to around the 55 gig mark or so. So and this query returns zero results.

Why does it why does it return zero results? Because I am I have a row number function in here and I am filtering to where row number equals zero. So I want nothing to come back at all, because I don’t want to wait for SSMS to show me a bunch of stupid results.

But I want this whole thing to have to be expressed before this filtering happens. So the query plan is, you know, do all of this work in here. Right.

And this is what this is the work that happens inside that CTE and then filter on that work there where we get nothing back because SQL Server for some reason doesn’t know that a row number cannot be zero. So in this context, which is a little odd, but works to my advantage. And I don’t want to ever.

I don’t ever want that to change. Microsoft, please don’t file that as a bug. Now, I have this query running in batch mode because I really want because it’s just better that way. A lot of things are.

But I have this query running in batch mode. So it finishes pretty quickly. It finishes in about six seconds. This helps with the demos. But in row mode, this thing runs for like 20 seconds. And it makes the demos less interesting.

So let’s come over to this window. And let’s clear out wait stats so that we don’t have to care about anything. And oh, come on.

Get out of there. There we go. Now we’re really clear. So let’s start sort of with how this thing looks with nothing going on. We have a little bit of SOS scheduler yield because even though I just cleared out wait stats, there’s always something going on.

SQL Server is always up to something. It’s probably that customer experience improvement program thing running, ratting on me, ratting me out to Connor about all the things I’m doing in SQL Server. Trace flags.

Using the debugger on things. So some stuff that SP underscore pressure detector will show you about your SQL Server off the bat. Top memory consumers.

So we have this section up here. And the first thing that I want to show you is how much is consumed by the buffer pool. I want to show you. Let me get rid of this blue highlighting. That just makes the pink look weird.

All right. There we go. I want to show you how much memory is currently kind of being taken away from the buffer pool. And then under that, I have the top five non-buffer pool memory consumers on your SQL Server.

One thing that I find a lot in here, which is something that I have a script to help with in my GitHub repo, is a memory clerk called UserStoreTokenPerm. And that’s a security cache.

And I find lots of weird things happen to SQL Server when that gets above like the 2 to 4 gig mark. And clearing that out often alleviates a lot of weird issues. If there were any queries running that required a memory grant, we would see those in here.

And then we also have this low memory section. And this will tell you any point in which SQL Server had a low memory warning inside of it. So this can be useful to figure out how often your SQL Server is kind of tanking out on memory.

Down below that, I have a whole bunch of information about database size, how much memory is in the server, what max server memory is set to. Some of this data is duplicative just because I wanted it all to be in one place.

And so there are some extra rows in there. But sue me. I don’t know. Whatever. I should. I don’t know.

I like it. Works for me. If you’re using lock pages in memory, you’ll see that here. And then you’ll have information about various memory pools in SQL Server. Typically, the ones that we see action in are the zero pools right here, like this one.

This one here is usually the one that I see fluctuate as queries ask for memory grants and such. But you see the target memory of the pool, the total memory, the available memory in the pool. If any memory has been taken out of the pool, we’ll see that here.

Lots of good information. And I don’t know. I guess pool ID 2 is the better thing to sort of keep in mind there. I show all of these.

I used to filter these down some. But then I found in, like, some servers, the pool that I was filtering down to was not the one that was in use. So you’ll see this especially a lot in Azure, various Azure implementations, SQL DB, stuff like that.

Because that has a whole bunch of extra things in here for some reason. So I try to be kind to Azure SQL DB, even though I sort of hate it. Aside from the fact that it uses read committed snapshot isolation by default.

All right. So let’s dive in to what SQL Server looks like when queries have memory grant pressure on the box. Now, I have that same CTE query in here.

And we’re just going to – we don’t need 10 threads. We don’t need 10 iterations. But I want to do this so that you can see – well, we kind of want you to see a bloodbath a little bit. So now let’s kick this off.

And let’s give this a run. And we’re going to see some stuff change in here. All right.

We have this section here. We still have – I don’t know, we’re running about 9 gigs there. But now we see this has popped up to about 10 gigs. So the two lines that I want to focus on sort of off the bat are these. All right.

And so stolen server memory is, I mean, in this case, like in isolation, how much memory – like queries have used of their memory grants that have, like, really taken stuff away from the buffer pool. And the second line, SQL reservations, is how much memory – these queries have been given for memory grants. If we come down here, and we’re going to focus on this section first.

We’re going to look at the line that I was talking about before, where we have 40 gigs available. Well, we have 40 gigs in total, but we only have about 10 gigs available now because about 30 or so gigs has been granted out. All right.

31 gigs or so has been granted out here. All right. So we have all these queries running. All right. And of that 30 gigs that has been granted – oops, let me focus on that a little bit better. Of the 30 gigs that’s been granted, about 12 gigs has actually been used by the queries.

We have three queries that have been given their memory grant and seven queries that are waiting on memory grants. If we give SP Pressure Detector another run now, we’re going to see this resource semaphore weight show up. And we’re going to see this is the result of queries waiting to get memory grants to execute.

All right. So if we focus in a little bit on this line, we have had 24 tasks wait on average about 27 seconds to get a memory grant here. Now, all well and good, except I also want to show you what happens when we have queries sort of fighting to get memory on top of queries that are sort of fighting to get data pages into the buffer pool.

So I’m going to clear out wait stats because I don’t want any remnants in here. And now I’m going to kick off a second workload. And this second workload is going to just select counts from these tables.

Now, I don’t have any nonclustered indexes on these tables. So in order to do the count, we hit the entire clustered index. I realize that there are other ways to get a full count of like rows from a table that don’t involve getting a count from the table.

It’s not always realistic in all circumstances. Sometimes you need to filter certain values in the table, which querying DMVs will not allow you to do. So let’s let’s go.

Let’s run this by itself first for a minute. And let’s look here. Now, none of those queries are asking for a memory grant. So there’s nothing in here. But we’re going to start to see these page IO latch weights crop up.

And we’re going to see buffer pool memory start to come up as well, because we’re going to be reading data pages from disk into the buffer pool here. Right. Cool.

Now, let’s throw this one back into the mix. And as we do that, we are going to see this buffer pool slowly drop down as stolen server memory and SQL reservations crop up. All right.

So we went from 40 something gigs to 30 something gigs. Now we’re down to 20 gigs here. And I guess we went down to 24. So I think this stabilizes around here. But now we have a whole bunch of queries that are asking for page IO latch.

Well, that are emitting page IO latch SH weights. Right. So now all these queries that are running, they need to go read pages from disk into memory all over the place. And, you know, granted, this is a laptop with good local storage on it.

So the page IO latch weights aren’t going to be too, too terrible. All right. We, you know, we have about 2.5 milliseconds on average per weight, which isn’t awful. But, you know, these resource semaphore weights are still cropping up.

So right now, this is a, I think, what I would consider probably one of the more common scenarios that I see in SQL Server, where we have queries trying to put data pages in the buffer pool. They can’t because SQL Server has given chunks of the buffer pool out as memory grants to queries.

And so queries have to go out to disk, which is, of course, even with good local storage, not as fast as going to main memory. Right. And we can run this and kind of observe things a little bit as they go.

And, you know, we’re going to see things pretty, I don’t know, they’re going to stay pretty, pretty much the same where, you know, the buffer pool is going to stick around the 20 gig mark. The stolen server memory and, you know, all these reservations are going to stick around the 30 gig mark.

And, you know, the page IO latch and resource semaphore weights are going to keep sort of ticking up. And, you know, we’re going to see more waiting tasks. We’re going to see more wait time.

The average milliseconds per wait on resource semaphore, of course, over time has dropped down to about nine and a half seconds. Oh, there’s my, there’s the end of the green, there’s the end of the camera right there. That doesn’t look weird at all.

So, but anyway, that’s sort of the deal with that. So let’s kill these so that you can’t hear the laptop fans running in the background. And that’s probably, it’s probably good enough of an example of what happens when SQL Server is under memory pressure.

And, of course, these are the two, what I, in my experience, most common types of memory pressure, where we have queries that are fighting with each other to get memory to run. That’s a resource semaphore.

That’s memory grant stuff. And then those memory grants directly affect other memory consumers in SQL Server, like the buffer pool, because they reduce the size of the buffer pool while they’re running. All right, because memory has to come from somewhere.

SQL Server doesn’t work with pages directly on disk. Any pages, any queries that execute and ask for data, if that data isn’t in the buffer pool, we go to disk, we get it, we put it in, we start handing it out to queries, and then we go from there.

So, this is how SP, this is what you should look for in SP underscore pressure detector in order to figure out if there is indeed memory pressure on your server. Resource semaphore weights are a good sign.

Lots of page I.O. latch, underscore, probably most commonly, underscore SH and underscore EX. You might see those pop up to the top. Of course, we only see the underscore SH weights here, because all we’re doing is reading data.

We are not taking any exclusive latches on data pages to modify them. So, that’s kind of what you should look for. Of course, this is another good section to review in here.

If you are seeing any, if you see your buffer pool consistently a lot lower than, say, your max server memory setting or something like that, you see lots of stolen server memory, and the SQL reservations memory clerk might occupy a large percentage of that.

And then down here, you might see stuff along one of these lines where the available memory line drops off significantly. The granted memory line goes up significantly. You might see some queries that are granted memory, some queries that are waiting to get memory to run, and other stuff like that.

Another good counter to look at when you’re looking at this is the forced grant count. This happens when there is such significant memory pressure on the server. The SQL Server tells queries, well, screw you.

You’re just going to go run anyway. You’re just going to run with the minimum memory grant that you can get. So, that’s nice, isn’t it?

It’s really nice. Cool. All right. So, that’s it for this one. Thank you for watching.

I hope you learned something. Hope you enjoyed yourselves. Please, like and subscribe, if you don’t mind. If you have a YouTube account, if you have a Google account, you can do that.

And I will see you in another video sometime soon. Thanks 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.

sp_PressureDetector: What SQL Server Looks Like Under CPU Pressure

Posted on June 13, 2023January 22, 2026 by Erik Darling

sp_PressureDetector: What SQL Server Looks Like Under CPU Pressure

Video Summary

In this video, I delve into the intricacies of CPU and memory pressure in SQL Server, focusing on how to identify these issues through practical examples. We start by examining what CPU pressure looks like in a server environment, specifically looking at thread pool weights and SOS scheduler yield weights. I walk you through calculating worker threads for different versions of SQL Server, explaining why the calculations changed with newer versions. Additionally, I demonstrate how parallel queries can impact your server’s thread pool and provide insights on using SP Pressure Detector to monitor CPU usage effectively. By the end of this video, you’ll have a clearer understanding of how to recognize and address CPU pressure in your SQL Server environment.

Full Transcript

Erik Darling here from Darling Data, with Darling Data. It came from the data. He’s just emailing me. Never mind. Nothing important. And we’re going to continue our lecture series on SP underscore pressure detector by first talking about what CPU pressure can look like in SQL Server. That’s a pretty good one to start with. We’ll also look at what memory pressure might look like in SQL Server. And we’ll also, well, well, not in this video. That’s the next video. If I put everything in one video, it would be an hour long and you would die watching it. Who knows? Oh, and then in another video, we’ll talk about how SP pressure detector can help you identify which queries are having which issues. Don’t look so amazed. Anyway, one thing, like, I think the way that it most commonly manifests itself is that it’s not going to be a problem.

when servers are having CPU pressure. It’s sort of either thread pool, a lot of thread pool weights, or a lot of a lot of SOS scheduler yield weights, right? Just not enough CPUs to service all the CPU stuff that’s going on. It’s not going to be CX packet, CX, or any of the CX. It’s going to be SOS scheduler yield and thread pool for the most part. Now, there are various ways to figure out how many worker threads. you have. And this is generally the calculation that you’ll want to use. And this is the calculation to use for 64-bit server. If you’re using a 32-bit server, you… I can’t conceive of you at this point in time. It’s windy out there today. If you can hear wind chimes in the background, it’s because it’s a very windy day here. So this is the calculation that you would use for, say, modern-ish versions of SQL Server that have fewer than 64 cores in them. Now, Microsoft actually did change the calculation recently for servers with more, well, semi-recently, with servers with more than 64 cores.

Before I talk about that, I did get a user question. They said, why don’t you wear one of your t-shirts when you record these videos? Why are you wearing an Adidas shirt when you record these videos? Because I don’t have any of these shirts left. I did not save one for myself. I gave all of these away at various pre-cons, SQL bits and past data community summit. Sorry, community summit. And should I ever get invited back to one of those lovely venues again, you can be sure that there will be more available.

I think I have a pretty good grip on which sizes I need to bring now, so there won’t be a scramble to give away leftovers of lots of small and extra small t-shirts at the end. But let’s look at this impressive piece of documentation. What we’re going to do is skip this part, because if you are using a version older than this, I just feel bad for you.

So we’re going to focus on this section down here. So starting with SQL Server 2016 SP2 and SQL Server 2017 and up. This also applies to 2019 and 2022.

Of course, it doesn’t stop at 2017. That’d be absurd. The calculations that you want to use would look like this. So if you have fewer than or equal to 4, you get 512 worker threads.

If you have greater than 4 and less than or equal to 64, you get 512 plus the number of logical CPUs minus 4 times 16. And if you have greater than 64 cores, the only thing that changes is it goes times 32. Now, it’s a little confusing, because it looks like it’s 512 squared, but it’s really just a footnote down here.

I don’t know. It’s an odd design choice, that whole thing. But whatever. Anyway, that’s where I got that calculation from.

And that’s how I know that based on the number of CPUs I have, minus 4 times 16 is the number of worker threads for my server currently. So you should be glad to know all that. Now, the reason why this is pertinent to the conversation is going to be partially because of the thread pool thing that we talked about.

Now, on just about every server, you are going to see some amount of thread pool weights. My wonderful friend, Josh Darnell, a.k.a. Josh the Coder, a.k.a. the guy who keeps my website alive, did some digging. And he had an interesting blog post that I’ll link to in the show notes about how SQL Server will grow and trim the worker thread set, the pool of workers that you have, as the workload needs more threads and as the workload needs fewer threads.

And so you’re always going to see some thread pool weights as SQL Server grows and trims the pool of workers. Where you have to worry about it is where you have, you know, many, well, I guess you want to compare the total duration of thread pool weights to server uptime. And if it’s a rather significant portion, if it’s like 5%, 10% or higher, you might have a pretty consistent thread pool issue, especially if there are long average weights per weight on thread pool.

Right? You want to, queries are waiting many seconds to get threads to run. That’s not a good sign.

Now, where things become rather interesting, of course, is with parallel queries. Because parallel queries can reserve a lot of worker threads. And they can work towards exhausting your available threads a lot faster than a bunch of single-threaded queries.

So execution plans are turned on, so I don’t have to press any extra buttons. If we run this query and we get a count from the comments table, we will have a parallel query plan. All this stuff here is parallel.

And if we look at the properties of the select, and again, you know, the properties section is a really important thing to look at. I’m going to blow this up over here so I don’t have to do too much tinkering with moving around and stuff. We will see that this particular query, which ran at DOP of 8, well, let me show you guys to open up this, reserved and used 8 threads.

A parallel query can reserve far more threads than it actually uses. So that’s a good thing to look at if you’re troubleshooting a parallel plan issue. Now, let’s get rid of that for a moment.

Now, that’s a parallel query that has just one single liner, one single branch. If we run this query, which will join a couple other tables together, and we look at the query plan, eventually it will show up. I promise.

This one has some additional branches and has some additional parallel exchanges. We have some parallel exchange here. We have a parallel exchange here.

We have a parallel exchange here. And typically, we want to think about those exchanges as being stopping points to a particular branch of the query. Now, sometimes SQL Server can share threads prior to a parallelism exchange.

So if we look at the thread stat here, it looks a little bit different. Now we have two branches, and we have reserved and used 16 threads there. Now, what’s important to note is that there will always be a plus 1.

So if you have DOPS set to 8, it’ll be 8 plus 1. And you can see that if you look at a parallel operator. It doesn’t have to be a parallel exchange operator.

But if you look under here, you’ll see that there are nine threads involved. Thread 0 is this coordinator thread, and then the other eight threads are ones that are doing actual work. All right, so it’s 8 plus 1.

So it was technically 9, but don’t shoot. And if we run this third query, which is a little bit different, the join between… Well, the join that I do to vote is a little bit different.

And none of these joins make a… Well, I mean, this join makes sense. This join makes no sense. I just did this to get a different join operator between votes and users. But if we change this query a little bit to join comments to votes, and we join on post ID column, which makes far more sense, we will get what looks like a quite similarly parallel plan.

And it’ll finish eventually, I promise. I swear to you. I wouldn’t lie about that.

God, I write good demos, don’t I? Who writes demos better than me? Look at that thing. 19 seconds on the nose, just like I predicted.

But if we look at this query, and again, we go to the… Oh, hey, thanks, tooltip. Show up whenever you want. If we go to the properties here, and we look at the thread stat, now we have a third branch, and we have 24 reserved and used threads.

All right, so typically the bigger and the more branches you have in a parallel query. If you notice the difference between this plan and the plan before, I don’t want to run it again and make you wait for another 20 seconds, but the last plan had a nested loops join in it between users and votes, and that’s why we didn’t have an additional parallel exchange.

This one, we have parallel exchange here, we have a parallel exchange here, we have a parallel exchange here, and we have parallel exchanges here and here.

So these are all sort of potential separate branches to things. But since we had DOP8, and we had three branches in this plan, we had 24 reserved threads, even though we only have eight threads really working.

But again, it’s going to be plus this coordinator thread. All right. So with all that out of the way, we’re going to use SQL query stress again, and we’re going to run this query in it, which you may recognize from the remote DAC video.

And I’ve already got this window up here set to show me, or set to show remote DAC stuff. And we’re going to use SP pressure detector to check on CPU issues.

So right now, I don’t really have much going on here. I have, you know, this, some, some SOS scheduling weights, but I just cleared out weight stats.

We don’t expect to see much of anything. We have some CPU details in here that are kind of nice. You know, we’ll tell you if any CPUs are offline. This is usually what I look at, like look at first, but there’s some other stuff in here that’s, you know, kind of useful to know, get kind of sort of the lay of the land for the CPUs.

And then if there are any, if there are any points in the, since the server has been up that you have hit greater than 50% CPU, we’ll also log those over here, right?

You can see that. But we’re going to pay, we’re going to, we are going to be paying attention to this section over here for kind of the remainder of the demos.

So let’s do this and let’s get this and let’s hit go here first and let’s hit go here. And since we’re already, we’ve already have the DAC, what we’re going to see is which queries are running.

There’s a section here, which will tell you if there are any requests currently hitting thread pool weights. Now this is important because sessions waiting on thread pool do not have a session ID yet.

They don’t have that context set for them. So you can sit there waiting for a very long time on, you know, waiting to get a worker thread.

Weight stats are going to start to pile up as the workload kind of chugs along. But if we look at this section right now, we’re going to see that we have, you know, again, 576 total threads, but we have 618 in use, which means we have negative 42 threads on this server.

Now at the moment, we have seven requests waiting for threads. All right, you can see that there. And if we look down in this thread pool section, there’s one, two, three, four, five, six, seven in here that have been waiting on thread pool for a bit.

Now let’s run this again since the workload’s been going for a little bit. Now we have four that are waiting on thread pool. And if we look at the weights, thread pool has slowly crept up.

And what we don’t, again, you know, just sort of my contrived workload, we haven’t had a ton of queries waiting on thread pool, but we have had on average queries waiting like five and a half seconds to get thread pool weights.

That’s if my cursor will show up in the right place. All right, server, I’m really beating the tar out of you there. We have, these queries are waiting about five and a half seconds to get a thread to execute, and that’s where things can look no good at all.

So, you know, this is holding pretty steady. We have some additional requests. The query that I’m running runs kind of slow, so this isn’t going to like, you know, really give you like an awesome view of like that creeping up and like getting a lot of requests that are waiting on it.

But I think you can get the idea pretty well from that. So let’s cancel this stuff, and let’s just take a look at weights again. Now, remember the two things that I told you are going to really creep up when we are waiting on, or when we have CPU pressure on the server.

One of them is going to be ThreadPool, which is cleverly documented for you using SP Pressure Detector as worker thread exhaustion. And again, that held pretty steady around the five and a half second mark, which is not good.

We don’t want this to be a weight that queries are experiencing this kind of weight on. But where things are, I think, really interesting is the other weight that I told you can get out of control when your server is under CPU pressure, and that’s SOS Scheduler Yield.

So if we look at that, right, they have that weight here, SOS Scheduler Yield, which again is query scheduling. And if we come over here a little bit, we have had 151,215 waiting tasks.

And on average, they waited 157 milliseconds for CPU attention, right? That’s queries saying, I’m ready to go.

Can the CPU pick me up, please? Waiting for my CPU driver. So that’s not a good situation. And now this is a very exacerbated situation, but I think what we should keep in mind with SOS Scheduler Yield Weights is queries when they get a CPU, get on a CPU to do some executing.

You have this thing called the CPU quantum, I guess we can be close enough for that. And I apologize for keeping scratching my eyes.

Again, it’s very windy out today, and I was outside earlier, and I have like grit in my eyeballs that is making noise. I blink.

It’s fun. But so you have this quantum, which is a set amount of time that a query has to use a CPU before it jumps off and allows another query to have CPU attention if there’s another query waiting, or your query will just immediately jump right back on if there’s nothing else in line.

In this case, everything was screwed up, and that quantum, that set amount of time, once you factor everything in, ends up around four milliseconds.

I’m going to say exactly four milliseconds, because imagine measuring four milliseconds exactly every single time. Bonkers. It’s insane. Now, granted, there are situations where you could have a non-yielding scheduler where something is just dominating a scheduler and not letting it go.

I see that happen sometimes with weird XP command shell stuff, but, or like system stuff too, like buffer latch timeouts, you might see non-yielding schedulers arise from that.

But queries were waiting 157.4, bunch of other numbers, milliseconds, to get back on a CPU to get four milliseconds of CPU attention.

And that’s a really bad situation. So, we have learned the prior video, why you want the DAC, the dedicated admin connection, that’s so you can run diagnostic queries when your SQL Server is under a fair amount of strain, which is what we’re doing here.

We’re using the dedicated admin connection. And we learned that when we have a set amount of worker threads for our SQL Server, and when we start exhausting those threads, things can really start to pile up and cause trouble.

Now, you don’t have to hit thread pool to have CPU pressure, but I showed you two signs of it. The thread pool weights with long average weights, and the thread, and the SOS schedule yield weights really piling up to the top.

And especially if it has long average weights there, because that means queries are waiting potentially longer to get on a CPU than they’re spending on a CPU.

And that’s a really, really bad sign. That just means either, usually you, either, I mean, it can mean a lot of things, right? Your workload is just god-awful. You have too few CPUs to service the workload.

You’re, I don’t know, you have really old CPUs that have like one or two gigahertz, and they’re spinning too slowly. Maybe you have balanced power mode on your server.

A lot of things can happen in there. But anyway, I’m going to call this one here, and I’m going to start putting myself together to talk about what servers can look like when they are under a lot of memory pressure.

So that’ll be, that’ll be fun for you too, right? Anyway, thank you for watching. I hope you enjoyed yourselves. You’re listening to the grit in my eye crunch.

If you, if you enjoy this sort of content, you know, gritty, gritty eye crunches or not, you should like and subscribe to the channel.

So every time I publish something, YouTube will yell at you about it. You get 10 million notifications. It should be a lot of fun for you. And I think that’s about it.

Yeah. 22 minutes of me babbling here. That’s, that’s quite enough. All right. Thanks. Goodbye.

Going Further

sp_PressureDetector: Why You Need The Remote DAC Enabled

Posted on June 12, 2023January 22, 2026 by Erik Darling

sp_PressureDetector: Why You Need The Remote DAC Enabled

Video Summary

In this video, I delve into the importance of enabling the remote DAC (Dedicated Admin Connection) in SQL Server when troubleshooting a server that’s experiencing issues. I demonstrate how to turn on the remote DAC and use it effectively by showing its impact during a simulated stress test with SQL Query Stress. This tool allows you to connect as a sysadmin using SSMS, ensuring you can access your server even under heavy load conditions where normal connections might fail. By enabling this feature, you’re prepared to quickly diagnose and resolve critical issues without the need for additional setup when problems arise.

Full Transcript

Erik Darling here with Darling Data. And here’s an unnatural Clark Kent curl going on in front. Can’t have that. Don’t want anyone getting me confused with the Man of Steel. But, uh, today, or rather, I don’t know, maybe we’ll talk about more stuff today, so it might be a little premature to just say the only thing we’re going to talk about today. because we’ll talk about some other stuff today, is, uh, we’re going to get back to talking about how I use various, uh, community tools related to SQL Server troubleshooting. And, uh, I believe so far, we’ve talked about SP Human Events and SP Quickie Store. Sorry, SP underscore Human Events and SP underscore Quickie Store. And, uh, this one I’m going to continue to talk about, uh, my stuff. Because my stuff is the most important. And, uh, we’re going to talk about the importance of turning on the remote DAC, the dedicated admin connection, when we might need to troubleshoot a SQL Server that’s, uh, having some issues. And, uh, the store procedure that we’re going to be talking about for the next couple of few videos is going to be SP underscore pressure detector, which is one of my favorites. It is nice, well, used to be short and sweet, and slowly has accumulated some, uh, some, some length to it. Not so much girth, just, just kind of length.

And, uh, but it’s all useful, useful length, and useful girth. So that’s good. I don’t want useless length or girth. I’ve got some useless girth. SP underscore. Pressure detector does not, though.

Uh, one thing I discovered recently is that if this whole SQL Server thing doesn’t work out, I’m going to get super into shadow puppets on the green screen. I’ve got to figure out the lighting a little bit better, but you get the idea.

You’re in for a real treat. Oh, Microsoft goes bankrupt. Shadow puppets it is. So the first, uh, thing, so, SP pressure detector has code similar or exactly like this baked into it.

Uh, so that it will tell you if the remote DAC is enabled or not. So it’ll, it’ll do a check to see if this is set correctly. When this is set correctly, we will have, uh, remote admin connections, but the value in use will be one instead of zero.

Right now it’s zero. We don’t have that turned on, and I don’t have that turned on, uh, because I want to show you how to turn it on. And then maybe even how to use it should be a very useful thing, wouldn’t it?

So right now, uh, this is not turned on. And one thing that is, is good to know about this, and I’m going to figure out the right way to move here, is that this is not an advanced option.

If this were an advanced option, you might on your server need to, uh, run SP configure to show advanced options before, uh, turning this on. So, uh, actually one thing that I do want to, that I’ve always found kind of interesting is if you execute SP configure with no parameters sent into it, it will just list out all of the configuration options.

Uh, it will not tell you all of the stuff that, uh, selecting from the sys.configurations dynamic management view returns, like if an option is advanced and stuff like that.

So that’s kind of weird, I think. Maybe, maybe SP configure could just do a select star from sys.configuration instead of messing around with this other stuff, but, uh, I digress.

Um, as I usually do. Uh, so let’s, uh, let’s run this, and let’s reconfigure our server, and say, see up here, uh, we have changed our configuration option.

Viva. Uh, it doesn’t tell you that you ran reconfigure. It says run the reconfigure statement to install, so, you know, done.

But when you run reconfigure, or it doesn’t say, like, reconfigure complete, or anything like that. It’s just a, just a silent yes. Um, I know that there’s also a reconfigure with override, uh, that you could do there, but that is, um, that is if you want to set settings that SQL Server maybe doesn’t agree with.

So, I would, uh, I would avoid using that, uh, as a practice. Just, just reconfigure like a normal person. Now, if we go back and look at this, the results here, we are all set.

Our value and value in use for our lovely, talented, remote admin connections is set to one. All right. All right.

Good stuff. Now, another thing that, um, SP underscore, pressure detector, will tell you, is, it’ll look to see if someone else out there connected to your SQL Server may have possibly, potentially, uh, taken the dedicated admin connection.

And the reason that’s important is because only one person at a time can use the dedicated admin connection. All right.

One at a time. Now, uh, some people, or I used to get some guff back when I used to, like, care about what people thought. Uh, they used to say things like, oh, it seems like a security risk.

Well, it’s not. Uh, you can only use this connection if your login has sysadmin. And, uh, quite frankly, uh, there’s just not a whole lot more interesting you can do with the DAC than you could do if you already have sysadmin.

Uh, I guess there’s some stuff with, like, the, uh, the resource database you could do, but that’s really not, uh, all that interesting compared to what you can do if you’re already a sysadmin.

All right. Groove is in the heart, so they say. So, uh, I’m gonna get GitHub open. So, I was doing some work on SP underscore blitzlock today.

Making some improvements. Always improving. That’s me. You can tell them. Nothing, just always getting better at things.

Uh, so what I’m gonna do is, uh, I’m going to, uh, I’m gonna really beat the crap out of my server. I’ve got two instances of SQL query stress open.

And the reason I’m using two instances of SQL query stress instead of one instance of O stress is, uh, I’m getting some weird ODBC errors from O stress that I just don’t feel like figuring out right now.

Uh, I’d rather record a video. Uh, I haven’t recorded in a week because I was on vacation. I was in Paris, France. And, uh, you know, you just don’t come back from France wanting to, like, dig into ODBC errors.

So, again, groove is in the heart. So what I’m gonna do is kind of show you why you want the DAC when the server is having issues.

And I’ve got this, you know, kind of crazy, quirky query going on in here. And I’m just gonna fire both of these off. And it’s gonna be, you know, kind of crappy. Like, I know this, these videos are supposed to be about SP underscore pressure detector, but, you know, even running SP underscore who is active has a tough time.

And even just trying to open a new query window is having a really tough time generating a connection, which is no bueno, right? We’re just gonna sit, this is gonna just, this will eventually time out.

But I’m not gonna sit there and make you watch it time out. That’s just kind of cruel and unusual. But if I kill this, and, uh, I’m gonna try to cancel these. Sometimes this doesn’t work as well as I’d like it to.

But lo and behold, uh, you know, that, that worked out pretty well. And, uh, one thing that I should show you here is that, uh, so SQL query stress has a default time of, I think, 15 seconds. So, uh, right here you can see that there are, like, you know, we, we, we, we completed some, but there, you know, we had some exceptions as well.

Rather, I think we just had all exceptions there. I’m not sure. I’m actually not sure how that’s getting measured, but we had exceptions on both of these, uh, equivalent to the number of iterations. So I think, I think we had a lot of stuff timing out, trying to, trying to connect.

We only have 200 threads, so if we completed 178 and had 178 exceptions, that’s twice as many as we could possibly have, almost twice as many as, uh, the threads that we have allocated here.

So, um, I don’t know. Math is hard. My friends, math is hard. So, now let’s, uh, let’s, let’s change things up a little bit. So, uh, SP who was active barely got any of the way through.

We ran for 23 seconds. Um, you’re just gonna have to take my word for it on that one. I guess I could move this way. There we go. And I could do a really careful zoom and hopefully I don’t catch any, any weird nether regions over there.

And there we go. there’s our 23 seconds of waiting for who is active to return results when it didn’t. Of course I killed it because, you know, the server is boned. And I don’t expect who, I don’t expect anything to be able to run when a server is really under a lot of CPU pressure like that because we need CPU threads to do things and we had like saturated the CPU threads on this one.

So, what I’m gonna do is show you a tiny itty bitty little shortcut. Very useful shortcut. Very useful engine. This is our top of hat would say.

And I’m gonna right click and I’m gonna choose connection and then I’m gonna choose change connection. And what I’m gonna do is I’m gonna use the SA account because that seems to be sensible.

And then I’m gonna come up to this line here where it says server name and I’m gonna add the word admin and I’m gonna make me do it again.

Thanks SSMS. I’m gonna add the word admin in here admin colon SQL2022 and I’m gonna put in my password and no one look.

Alright. If you saw what I typed please do your best to forget it. And I’m gonna hit connect.

Now there used to be a funny bug in management studio where you would do the admin connection thing and you would get an error message saying like it didn’t work.

But it did work. And we can validate that it worked. Again I’m gonna have to move to the side a little bit and again hopefully you know what I’m just gonna do something. I’m gonna do some clever navigating here.

I hope. Or try to. Let’s see. I gotta go this way and this thing has to go this way and then we will eventually see lo and behold that we do have the admin connection here.

So that’s good. We have the admin and spwhoisactive will work of course instantly because nothing’s going on. That’s needless to say.

But now let’s come back over to SQL query stress and let’s fire these two bad dogs off again. And now with the remote admin connection we get results back instantly.

Alright. We can run this a bunch of times. We’ll get all the stuff back. We’ll see that we’re supposed to have like I mean like under normal circumstances because we have 200 threads from each of these going in.

Like we would like expect to see like you know 400 rows in there because queries are running they’re running for a long time but under these circumstances there are a whole bunch of queries that can’t even get to the server to execute because we have the saturated worker threads all over the place.

So I’m going to cancel these again before I set my nice laptop on fire. And we’re going to close this one out without ever actually having looked at SP pressure detector.

This is going to be the importance of enabling the remote dedicated admin connection. So when your server is having problems you don’t have to worry about doing it then.

you’ll be all set. You’ll have your special SSMS login with the admin colon server name. You’ll have everything ready to go and you can fire up whatever tools, scripts you care about to figure out what the heck is going on with your server and why CPU is at 10 million percent or whatever.

Again, math is hard. Anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. If you enjoyed, if you did either, you should like and subscribe.

If you did neither, I don’t know, go get into my SQL. Who needs you? That’s how I feel about it. Go watch some green screen hand puppetry, I guess.

Anyway, thank you for watching. I’m going to record some other SP pressure detector videos today and hopefully if you are not enlightened in the least by this one, you will find yourselves greatly enlightened by future videos or at least enthralled, amazed.

My wife is texting me so I’m going to go pay attention to that for a minute. Upload this video. So, yeah, goodbye.

Going Further

A Little About Indexed View Maintenance In SQL Server

Posted on June 9, 2023January 22, 2026 by Erik Darling

A Little About Indexed View Maintenance In SQL Server

Video Summary

In this video, I dive into the topic of index view maintenance in SQL Server, sharing insights from my experience as Erik Darling with Darling Data. We explore a common misconception about the overhead associated with maintaining indexed views and demonstrate how proper indexing can significantly impact performance. By walking through an example where we create and maintain an indexed view, I illustrate the importance of having supporting indexes to minimize the time and resources required for updates. This video aims to demystify index view maintenance and provide practical advice on when and how to effectively use them in your database design.

Full Transcript

Erik Darling here with Darling Data. In this video we’re going to talk a little bit about index view maintenance. We’re going to talk about index view maintenance and create this lovely shadow right here. It’s because a lot of people have a pretty big misconception about how expensive it is to maintain indexed views. Now, I fully admit that adding an index view is like adding another index to a table. There is overhead to it. You’ve got to make sure that everything stays up to date in there. And I do concede that when you start doing things like creating index views, filtered indexes, computed columns, you know, columnstore indexes, that you are probably dealing with a particular index view. pretty large volume of data and that you probably have a pretty good reason to be a little terrified about how you’re going to keep all these different things up to date and in sync. So let’s dive right in. And this is not going to be a, you know, very technical video about things. This is, if you want deeply technical, I would suggest checking out, you know, index view maintenance is probably three or four blog posts about that at this point, maybe some stuff about bugs as well.

That have, but I said, but anyway, twice in a row, it’s a good lunch today. So if we run this query to update the post table, we get a query that takes about three seconds total to run. This takes about three seconds total to run because we have to update an index, a nonclustered index on the table to update the clustered index on the table. And right now we just don’t have a very good supporting index to find the data that we care about in the table. Okay. So, uh, bench line there, right? Two points. Now, if we, this view, uh, I say, I assure you, it has a very good reason for being created.

And we create this index on that view. Let’s create this unique clustered index. Uh, this index is going to take a little bit of time to create, which might be our very first sign that we’re going to have trouble doing index view maintenance, probably because we don’t have a very good index currently on the votes table to support creating the index view. When you create an index view, SQL Server has to run the query that creates the index view. When you want to modify the index view, you have to run the query that creates the index view to modify it.

If creating this unique clustered index on the index view, you can probably imagine that modifying the index view might take a decent amount of time. So let’s try that. Let’s run this and we’re going to do that same update that we did before. And we’re going to roll it back. And I’ll just keep in mind that I do have accelerated database recovery enabled for this database.

So I do not have to worry about the rollback time on this. The only things that I’m concerned about in this query are how long it takes to do that update. We went from like like 2.8 or so seconds to from read. And I think 12.984.

It’s probably close enough to 13 most people. I don’t know if he ran this a thousand times and took the average and probably probably probably pretty sure that we would hit about 13 seconds there. But if we follow the yellow brick costs or rather the yellow brick operator times in this query plan, we’ll find that most of the time starts We have to sort of assemble that index view in the We have this clustered index scan on votes takes about seven seconds.

We have, you know, some hashing things going on here. This one spills a little bit. Coming over a little bit more in the query plan. This takes about 10, 11 seconds in here.

And then finally, when we add in the 11.454 at the end of the branch that assembles the index view to the 1.5 seconds we get that it takes to do the actual update. We get about 13 seconds total. So doing a little bit of query plan analysis, we can probably guess that if we had a better index to support the index view being created, then we would have better performance creating or assembling the index view when we need to do that for modifications.

So let’s create index on the post ID column of the votes table since that’s really just about the only thing that we care about in here. From the votes table, you know, we’re joining on that column here. Everything else is pretty post centric except the ID column from the votes table.

But, you know, that is unique column anyway. And since it’s the primary clustered primary key of the table, it will be implicitly a key column in the index we create on the votes table. So now with an index on votes, let’s try this update.

Cool. Back to about three seconds. Remember that first iteration, that first modification that we did took about 2.8 seconds.

This doesn’t really add a significant amount of time on when we do this. So I’m pretty happy with that. If we look down here where the plan used to accumulate, used to be about seven, eight seconds in here.

We no longer have that. It’s about seven, 700 milliseconds. So a proper and properly supported indexed view in SQL Server. Too bird brained behind the scenes with it.

But, you know, I guess being bird brained is sort of a relative metric, isn’t it? Kind of is. Anyway.

After the clustered index view, we don’t really… …watching. I hope you enjoyed yourselves wherever you are.

Hope you learned something about indexed view maintenance. … … Final video of the day before I go to the airport.

Thank you. That was a really important journey after I come back. And bethe as well, the fact is for David and Tom and and use him to them to And to recognize our gender or his humanity has a more constant age. Thank you.

Going Further

A Little About Why IMPLICIT TRANSACTIONS Are Horrible In SQL Server

Posted on June 8, 2023January 21, 2026 by Erik Darling

A Little About Why IMPLICIT TRANSACTIONS Are Horrible In SQL Server

Video Summary

In this video, I delve into why implicit transactions are considered terrible, horrible, no good, very bad things in SQL Server management and development. Exploring the default settings of Microsoft’s JDBC driver and Python driver for SQL Server, which enable auto-commit by default, I highlight how this can lead to significant issues. The video examines the implications of using read committed isolation level as the default setting in SQL Server databases, except Azure SQL DB, where it’s a rare positive exception. Through practical examples, I demonstrate how implicit transactions can exacerbate blocking and deadlocking problems, making debugging and performance tuning much more challenging. By emphasizing the importance of switching to read committed snapshot isolation or using optimistic isolation levels, I aim to help viewers avoid common pitfalls and improve their SQL Server environments.

Full Transcript

Erik Darling here once again with Darling Data. And in this video, we’re going to talk about why implicit transactions are terrible, horrible, no good, very bad things. Now, part of why they are no good, horrible, very bad things is because they are the default auto commit option for the Microsoft JDBC driver and the Python driver for SQL Server. Whenever I talk to a client who is using either one of those, I know that I’m in for a long day of explaining the auto commit feature, the auto commit, I don’t know, connection string, element, whatever you call it. I’m not a programmer. I don’t know the names of these things. Flag. I don’t know. I don’t care that much. Call it whatever you want. But part of why that is a poorly chosen default is because of Microsoft’s poorly chosen default of read committed, the garbage isolation level, is the default isolation level for SQL Server databases, except Azure SQL DB, which is the one good thing I’ll say about Azure SQL DB. The rest of it is a waste of time and money.

Yeah. So because Microsoft made a big, big oopsie daisy back in like 2005, we have lived with the consequences and the consequences of billions and billions of NOLOCK ends ever since. So happy birthday, NOLOCK. So what I’m going to do is in this window, I am going to step through this script one thing at a time, and then we’re going to go look at a different window. I’m going to set implicit transactions on. We are going to run this update, and then we are going to run this select, and all that’s going to go fine.

And then in this window, we are going to go fine. And then in this window, we are going to set implicit transactions on. We are going to run this select, and we are going to wait for all eternity. Now, more recent versions of SP who is active, and I didn’t actually mean to close that, have added details about implicit transactions to the output. If you use the at get transaction info parameter, and set that to one. There we go. So you set that to one, and you will get a column a little bit further over in the results, called implicit tram. And we will see that implicit transactions are open for both of these queries.

And if we come back a little bit, we will have what appears to be a select query against the users table. I’m going to try to get my head right between these two. We have a select query against the users table, block what looks like blocking a select query against the post table. But that’s because we made a connection, we ran one query, and then we ran another query.

And because of the implicit transactions, SQL Server ain’t telling us the whole story. Now, the real crappy thing is, I mean, the deadlock XML and the block process report can already be misleading enough on their own, like capturing whatever query is in the buffer at the time that a deadlock or the blocking, blocking block process monitor loop goes through and find some blocking.

But this makes it worse because whatever query in the batch that was part of the implicit transaction was open, will show up in the block process report deadlock XML. And if you’re using a tool that, you know, like SP who is active that shows you what’s currently running, this will look very, very strange to you.

So implicit transactions cause a lot of additional blocking problems, at least, you know, from everything that I’ve ever seen with people using them. And they also make, well, actually blocking and deadlocking problems.

Part of that is because Microsoft made a bad choice with the default isolation level for SQL Server. And part of that is because implicit transactions are just a terrible idea. So if you are using the Microsoft JDBC driver, if you are using the Python driver to connect to SQL Server, I would strongly advise you to use read committed snapshot isolation as your isolation level, because you will avoid a lot of the really crappy blocking, deadlocking, and very confusing reports for analyzing locking and blocking.

So if I had to rate things, I would say user and optimistic isolation level, flat out, period, do it. Don’t use implicit transactions, flat out, period, don’t do it. If you’re going to use implicit transactions, because you’re too lazy to change your code, then you absolutely need to use an optimistic isolation level with SQL Server.

Otherwise, you will be creamed by the locking and blocking that goes on. Or you can do what everyone else does, slather your code in no lock hints, and just hope and pray for the best.

Hope and pray that you return correct data to your clients and nothing is ever wrong, and you never catch a weird update in the middle of anything. Because, Lord knows, that never happens.

Anyway, thank you for watching. I appreciate your time. I hope you enjoyed yourselves. I hope you learned something. I hope that you will choose to like and subscribe to this channel full of SQL Server wit and wisdom. And, again, I don’t know, I’ll see you in the next video.

And, again, thank you for watching. Thank You How Six Years man Shadow

Going Further

A Little About Why ISNUMERIC Sucks In SQL Server

Posted on June 7, 2023January 21, 2026 by Erik Darling

A Little About Why ISNUMERIC Sucks In SQL Server

Video Summary

In this video, I delve into why the `ISNUMERIC` function in SQL Server is problematic and why it’s best to avoid using it. I illustrate how `ISNUMERIC` can incorrectly identify non-numeric characters as numeric, leading to potential data integrity issues. Additionally, I discuss my frustration with the `TRY_CAST` and `TRY_CONVERT` functions, particularly their inconsistent behavior when dealing with certain data types like unique identifiers. To address these challenges, I provide a regex-based approach using `NOT LIKE` to filter out values that contain non-numeric characters, ensuring more reliable results in your queries.

Full Transcript

Erik Darling here with Erik Darling Data, Data, Data, Data, or just Darling Data, Data, or just Erik Darling. We’re going to talk about why isNumeric sucks and why you shouldn’t use it and alternatives you have to using it. And I’m going to sneak in also why I get annoyed with the TRICAST function in some circumstances.

So, let’s start. Let us begin. Gather around, children, by putting all of these things into a table. A temp table, because I am staunchly anti-table variable. All right. So, we have a dollar sign, a comma, a period, a one with some commas, a dash, a plus sign, whatever car 9, 10, 11, 12, and 13 is, I sort of forget.

And let’s look at the output of this. Now, the isNumeric function is dead certain that all of these things are numeric. Not numeric, darling. Just numeric.

So, a dollar sign is numeric. A comma is numeric. A period is numeric. A one with two commas around it is numeric. A minus sign is numeric. A plus sign is numeric. And whatever the heck these blank ASCII characters are, are also numeric.

Now, where TRICAST falls a little bit short on us, and… Well, I mean not short. It’s not wrong. It’s just… So, like, I get it. Like, if it returns a null if it can’t convert something.

But then it returns a zero when it can. And that can lead to some confusing logic for some folks out there who are somewhat dimwitted, like yours truly. I don’t know. That’s about it.

At least right now. But, uh, if you use a sort of regex-ish expression, you will get what I believe correctly are zeros all down this line here. And I always have to look this up whenever I need to do it.

Except now I get to look it up on my blog because I have a post about it and I have this video about it. But if you say where T dot N, in this case, because my table is aliased correctly as T, and the column’s name is N. If you say where that is not like this expression right here.

Alright, this string. Percent, open bracket, caret, zero through nine, close bracket, percent. This will tell you if there are any characters in a row, well, in a particular, uh, you know, column row, that, uh, that are not the numbers zero through nine.

And I know this logic is a little bit obtuse and feels a little bit backwards, but that’s, that’s what we’re looking for here. So, that’s good. Alright, we can return consistent zeros for things that are not the numbers zero through nine, which are probably the things that we care about the most.

Um, if you wanted to make allowances for, uh, dots and commas, you could do that, uh, in here as well. But that is totally up to you. Now, where things get annoying for me with TriCast, and TriConvert, it’ll do the same thing for both of these.

What we’re looking for is, is the whole point of, can you try to do this for me? Is that you expect to get a null back if you can’t do it. If you try and fail, you should get back a null.

That doesn’t always happen though. Now, let’s take for instance, we do this. Explicit conversion from data type unique identifier to int is not allowed.

Then return a null. Give me a null. Give me a null. I know it can’t be done. I want a null back.

Now, this is a not terribly common occurrence. I agree. But, um, there are, uh, at least various points in SQL Server’s DMVs where, uh, there are SQL variant columns.

And those SQL variant columns, of course, are, uh, you use that data type, that horrible mistake of a data type. Because, they have all sorts of different things in them. Some of those things are GUIDs.

Some of those things are numbers. Some of those things are strings. Uh, there’s just all sorts of crap jammed into them. And if you want to try to cast that column as something else, or if you want to try to filter on where, uh, you know, try cast to something sends back, uh, a not null value.

Like, where try convert, try cast to try convert something is not null. Which, you know, again, isn’t the greatest idea query-wise in the world. But if you’re querying system DMVs, who cares really?

You can throw all the crap at those you want. And it’ll probably turn out just about the same. Because the code behind them is absolutely wackadoodles. So anyway. Don’t use this numeric.

Uh, try convert, try cast can be tricky. Uh, they can lead to some weird. Uh, logic hoop jumping that you may want to do.

And, uh, what I find to be the most reliable thing to do is use not like, and, uh, again, this expression right here to, uh, filter out rows that can, uh, values that contain things that are not the number 0 through 9.

If you’re aware of any numbers that, uh, are not, that are outside the bounds of 0 through 9 that could be used in here. I don’t mean 11, cause 11 is two ones and all that stuff.

Uh, then please let me know, cause, uh, I would like to patent them as soon as possible. Cool. Alright.

That’s good. Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I hope that you have been enlightened. I hope that you have had quite the epiphany watching this video. Uh, if you, if you feel enlightened and epiphanized, uh, please like and subscribe.

Uh, I’ll give you a dollar if you do it in the next 30 seconds, starting now. Uh, 30 seconds is up. Sorry about that.

I move fast around here. Alright, cool. Uh, thank you again for watching. You are beautiful.

Going Further

A Little About The OUTPUT Clause In SQL Server

Posted on June 6, 2023January 21, 2026 by Erik Darling

A Little About The OUTPUT Clause In SQL Server

Video Summary

In this video, I delve into the intricacies of SQL Server’s output clause and its impact on query performance. Specifically, I explore how choosing an inappropriate target for the output data can force a query to run in a single-threaded manner, which is something I strongly dislike—parallelism is usually preferable unless there’s a compelling reason not to use it. By using the Stack Overflow database as an example, I demonstrate this issue with a table called `high question scores`, showing how returning output directly to the client or using a table variable can lead to a single-threaded execution plan. To avoid such performance pitfalls, I recommend using a temporary table (prefixed with a # symbol) instead, which allows for parallel inserts and maintains better query performance.

Full Transcript

Erik Darling here with Darling Data. And today we’re going to talk about the output clause in SQL Server in one respect. And that is how if you choose incorrectly or unwisely what you are outputting data to when you use the output clause, you can end up with a query plan that is forced to run single-threaded. If you know anything about me, there is nothing that I hate more than a query that runs single-threaded when it doesn’t have to. It’s not a good thing. Like, I’m okay with parallel plans, I’m okay with serial plans, but I’m not okay with a plan that is artificially forced to run single-threaded for no good reason. So, what I have in my stack overflow database is a table called high question scores. And all it is is a worker table that I use for dumb demos that I can modify all over the place and nothing matters with it. So the whole point of this table, right, is to populate it with the display name and highest score.

For a user for a question that they have asked. So the select query that does that work looks like this. And I’m just going to find for one user. All I care about right now is finding for Jonathan Skeet. And if I run this query, now I have accelerated database recovery turned on. So all my rollbacks are instantaneous. None of the query foibles and follies for this query are going to be due to the rollback. If you’re, well, I’m sort of standing in front of it, but I promise you this thing only ran for one second. And I just want to actually take some time to talk about how amazing it is that I nailed that to the second. All right. One second flat. All right. Look at that. It was just like 999. Is it going to make it? Can I do it? I did it. All right. And that’s cool. Most of the work in this plan is done over here. We did a clustered index thing on this side. All right. Had some parallelism in the query plan. Things went fairly well. All right.

We also rolled all that back. We don’t have data in the table. All right. This table has been written into and rolled back from a million times. So it doesn’t really matter. Now, what I’m going to show you is what happens to that query if we return output results directly to the client. So let’s run this. You’ll get the sense that this is not going to run for exactly, precisely one second.

All right. This runs for five seconds. And now we have a query that runs for 5.453 seconds single threaded. Now SQL Server is changing its tune about an index for that thing. But if we look at the properties of the plan, now this is if you’re on SQL Server 2022 or you are using some fancy Azure-ish version of SQL that Microsoft pays you a premium for the privilege of using, you will get this non-parallel plan reason. DML query returns output to client. Very descriptive. Thank you.

Thanks for letting us know about that. I appreciate you. It’s a very good reason, I guess. I’m not sure. A little… Quite frankly, my brain’s a little tarnished on that one.

So that’s not great. The other thing that will cause that is if we output to a table variable. So right now I’m going to declare a table called high question scores, which is a table variable because it starts with an at sign. Everything that starts with an at sign is a variable. I don’t care who you are, where you come from, what you do with your life.

If we run this… Oh, this also runs for five and a half seconds. Zoom it will focus.

And similar to the last query that we looked at. If we go to the properties of the root operator, we will get quite a verbose non-parallel plan reason. There we go. Table variable transactions do not support parallel nested transaction.

All right. So if you’re using the output clause and you are moving around large chunks of data, or if the query you are using to generate the output data does a significant amount of work, you may want to consider outputting into a temp table. That is the pound sign table, right? Not the at sign table, the pound sign table.

Because temp tables do not have the same restrictions on parallel inserts that table variables do. Table variables screw you up in many ways. That’s one of them. Temp tables do not have that restriction or limitation on them.

So if you are going to run some big, you know, query that, you know, grabs a bunch of data and cross a bunch of tables and aggregates it and, I don’t know, row numbers and who knows what else, and you want to return that data to a client, even if it’s only like five or ten rows that come back, if the work you do to get to those five or ten rows is significant, then you should dump it into a temp table first and just select out of the temp table at the end, because you will be able to do a parallel insert into the temp table where possible.

But at the very least, you’ll be able to have a parallel plan to get that data into the temp table. So, please do mind your output clause targets in SQL Server, because there are, as usual, landmines in everything you do. All right. Cool.

Thank you for watching. I hope you learned something. I hope you enjoyed yourselves at least a little bit. Please be kind. Like and subscribe.

I’m not even asking for a comment, just a like and a subscribe. Just a little piece of your life. Like I’ve given you a little piece of my brain and heart, liver, lungs, kidney, large intestine, small intestine, colon, pancreas, appendix. I don’t know. Whatever. I’m through with this.

I only, my copy of Operation when I was a kid was broken. It was missing most of the pieces. So human anatomy is largely a mystery to me.

Anyway, thank you for watching.

Going Further

Just A Weird Bug I Found In Some Client Code

Posted on June 5, 2023January 21, 2026 by Erik Darling

Just A Weird Bug I Found In Some Client Code

Video Summary

In this video, I delve into an intriguing bug that cropped up recently while working with a client’s codebase. The issue revolves around a peculiar update statement that initially baffled me due to its seemingly incorrect syntax. I explain how the query managed to compile and run despite updating the wrong table, thanks to an unintentional aliasing mistake. By correcting the aliases in the query, we observed a significant improvement in the generated query plan, highlighting the importance of proper alias usage, especially in modification queries. This experience serves as a reminder to always double-check our SQL syntax, particularly when dealing with updates and other modifications, to avoid unintended data changes or performance issues.

Full Transcript

Erik Darling here with Darling Data Enterprise for Business Platinum 365. And in this intentionally short video where I am totally screwing up my green screen by having extra windows open that are bright, look everything magically got better. I want to talk about a weird bug that I found in some client code recently that I have simplified down to this. Now I’ve got two tables created in my database. I’ve got a table called T, which you can see there is already an object named T in the database, and a table called TT. And there is already an object called TT named TT in the database. It’s a little funny that IntelliSense is freaking out about this one, but this one is like, nah, you’re cool. But anyway, I got an email from a client, and they were like, something weird is going on with this update statement. We can’t figure it out. And please take a look at it. And so they sent me the update statement. And of course, I was, you know, I was up late writing demos the night before. And I looked at the query and I was like, well, how does that even work? How does that do anything? I mean, your update, the update T, right? And you have an alias and you’re setting T dot ID equals one from a table called TT alias is TT. How is how is this even doing anything? What what is happening? Send me the query plan. And so they sent me the query plan. And I’m going to show you what this query plan looks like. Here and go over here and look. And I mean, the object, obviously, the object called T in my database is there with a columnstore index on it. That’s that was that was my little cheat code for various things and some some demos. But I looked at the query plan. I was like, Well, you’ve got two tables in there. And they’re like, Well, is it a foreign keys trigger? What’s going on? I was like, No, no, there’s a table named the thing that you’re updating. But you’re still selecting from the wrong table, you didn’t alias the second table correctly. I guess they had some an unnamed code completion and formatting tool that had alias the table automatically for them. And it was it was unintentional there. So I look at the query plan. So well, you’re, you’re, I mean, it’s weird that this even works, right? Like, it’s astounding to me that this compiles and SQL servers like cool, no problem.

Got it. All the reasonable things that I feel like I’ve written where SQL servers like, Whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, back it up. We can’t possibly do that. This thing compiles and runs. So I sent them back a copy and paste of the query done correctly, where we update tt and we set tt dot ID equals one. And miraculously, we got a much more sensible, a much more sensible query plan that didn’t involve any other tables in the database.

So anyway, if there’s a lesson here, please pay attention to your aliases, especially in modification queries. You may find at various points that if you alias things incorrectly, SQL Server will throw an error. You may find in other circumstances that if you alias things incorrectly, you will muck up a lot of data that you shouldn’t have actually touched. Because the way this thing used to be written, it would have actually updated the ttable and, you know, changed a row to something. Wouldn’t have changed the whole table. Because there is, there is a, what do you call it? There is a, let’s actually step back. There is some manual, oh, sorry, there’s some automatic phase separation in this query plan.

And we’ve talked about this in prior videos. I talked about it with a hash join. But here we have a sort that actually does the does the work that a spool would have done. And in another plan. So you wouldn’t update the entire table, you would just update one row out of there. So isn’t that nice? Anyway, like I said, intentionally short video, blog fodder, felt like recording it, didn’t feel like writing it. There are there are times when I love sitting down and writing blog posts, typing things. Right? Taking screenshots and uploading them to WordPress. And there are other times when, gosh darn it, it’s just so much better to do a video. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll be very cautious with your table aliasing, especially in modification queries.

And if you feel feeling generous today, you’re feeling like I really earned your time and attention. You can like and subscribe to my channel. Right? Thumbs up in the bell thing. Anyway, thank you again for watching.

Going Further

A Little About Bitmaps in SQL Server

Posted on June 2, 2023January 21, 2026 by Erik Darling

A Little About Bitmaps in SQL Server

Video Summary

In this video, I delve into the fascinating world of bitmaps in SQL Server query plans, explaining their utility and behavior through a series of examples. Starting off by introducing what bitmaps are and when they’re useful, I walk you through how these bitmap operators function within query execution plans. You’ll learn that while bitmaps get created early on in the plan, they’re actually used later during table scans, significantly reducing the number of rows processed. The video also explores the impact of degree of parallelism (DOP) on bitmap effectiveness and why sometimes a bitmap might not be applied to every operator as expected. By the end, you’ll have a better understanding of how bitmaps can optimize query performance and when they might not be as effective, especially at different DOP settings.

Full Transcript

Erik Darling here with Darling Data, casting big shadows today. Oh, look at me, I’m huge, I’m like the Hulk. Gigantic, huge person. Anyway, we’re going to talk about bitmaps because I find advanced science fascinating, even if I don’t fully understand absolutely all of it. I couldn’t really explain to you in great detail what a bloom filter is. But we can talk a little bit about bitmaps anyway. So let’s do that. Now, we’ve already got query plans turned on because I actually took the time to mentally rehearse this a little bit. And I’m glad that I did because I was sort of reminded of something weird that happens later on in the script that didn’t used to happen. So we’re going to we’re going to talk about all that stuff. So, first, let’s talk about what bitmaps are a little bit and when they are useful to us in SQL Server query plans. Now, the sort of confusing thing about bitmaps is that we see in the query plan, where they get created, where SQL Server decides, we need a bitmap here. So bitmaps get created here. But that’s not really where they get used.

Where they get used is down here. When we touch the table that the bitmap is applied to. Now, quite often, you’ll see something in your query plan that looks about like this. We’ll see a probe bitmap something in row. Batch mode on rowstore might look a little bit different. Sometimes this gets put into a filter operator, you know, sometimes we won’t see a bitmap operator in the plan. A hash operation will have a bitmap creator true. attribute to it if you look in the properties of the the the hash doodad. So if you pretend we pretend that this was in batch mode, and we get the order the properties of the hash join operator, we would see bitmap creator equals true somewhere. Oh, I don’t know about here, maybe somewhere painting with broad strokes there. Now, where when bitmaps are useful, you will see things like this. Now it’s gonna look like SQL Server just made an absolutely terrible estimate here. You’re gonna stomp your feet and you’re gonna say SQL Server, why did you not know that only 40,000 rows were gonna come out of here? Why are you so stupid? Who made you? Who is your creator? Who is the patron saint of your existence?

Who continues to deal with you? Put up with that?

Anyway, it is not SQL Server being wrong, wrong. The cardinality estimate is figured out well before the query optimizer decides that a is it’s going to the cost based optimizer, sorry, decides that a bitmap is going to be created and used some some leave the fancy term for that is post optimization rewrite or something like that. I love all these phrases.

Yeah, just just rewrite it after you’ve optimized it. Why not? Anyway, it’s that’s not what it is. That is the bitmap at work. Note that if we look at the properties of while doing this, the tool tip for this thing, we do read all the rows.

But as we read the rows, we apply that bitmap to filter them out as rows are coming out of the scan, like, it’s just like a sort of like a residual predicate in other in other in other senses, but we’re just ruling rows out way ahead of time. So that’s nice, right? The storage engine is like nope, nope, nope, nope, nope, nope, nope, nope, nope, nope.

And we get down to 40,000 rows out of the 2.4 million rows that we read. So that’s a pretty good reduction from the bitmap. So thank you, bitmap. You’re a great bitmap. The best bitmap that ever bitmapped.

Love you. Love you to death. So bitmaps are good. On the inner side of the scan, pay attention to estimated rows, number of rows read, the actual number of rows that come out. When you see a bitmap, you’re going to see that probe thingy in there, in the tooltip, and you will get some sense of if the bitmap was effective or not.

Now, this is really only useful information for actual execution plans and estimated plans. You just see all the usual dumb things that SQL Server thought would happen. You know, quite the optimist SQL Server is, except for the default database isolation level.

So where they’re not so useful. And this is kind of funny. So this is where, this was the weirdness that happened to me along the way.

Is I used to have a smaller VM that I did demos on. The demo VM was set to have a max stop of 4. To this many.

And if we run this query at max stop 4. And look at the execution plan. Every single row.

Well, I guess just about every single row. Right? Close enough to every single row. 99% of the rows pass the bitmap filter. Right? Because the bitmap filter kind of blooms things based on DOP.

You get that sort of like parallel page supplier type modulus thing of things. And because of that 99% of the rows pass the bitmap filter. This clustered index scan is going to look nearly identical to the last one that we looked at.

Except this one was far less effective here. Right? We still have that probe bitmap in row. But this time 99% of the rows come out rather than taking it from 24 million out of 34,000 or whatever.

So, you know, obviously that’s not very good. But if we run this demo and we don’t restrain the correct. Let’s just explicitly say max stop 8.

If we run this at DOP 8 and we get sort of a larger hash bucket for the bitmap filter. We do actually start reducing rows a little bit more efficiently down here. Granted, you know, 1, 8, 5, 1, 5, 6, 1.

Granted, you know, we’re still at 1.8 million out of 2.4 million. But at least we were able to get some more out of there. So sometimes bitmap effectiveness is dependent on a degree of parallelism.

Now, if we look at the properties of my server, I have eight cores in there. So I can’t really go higher and show you more effectiveness at higher DOPs. But there is at least some evidence that if we were to double this up to like DOP 16, we might even get to rule some more rows out down there in the scan.

Maybe. Maybe. Just maybe.

So, you know, if you suddenly find all of your bitmap operators become completely ineffective because of SQL Server 2000 2022’s degree of parallelism feedback feature, you know who to blame. You know who to send a letter to.

Actually, I don’t even know who to send a letter to. So whatever. So we’ve looked at a couple of cases where the bitmap was applied directly to when we read the index, when things were the storage engine was just like pow, pow, pow, pow, pow, pow, get out of here. Right.

The storage engine was kicking rows out left and right. Well, at least a decent DOP. And that first one was really good. The second one is still still a little iffy on that one. Let’s be honest with you. But not every bitmap gets pushed down that far. And I’ve never really been able to figure out why.

And I’ve always been too shy to ask. Just kidding. No, it’s just one of those things that I have not spent a whole lot of time trying to figure out because it’s never been a terribly big pain point for me. But if we look at this query plan, if we look at this tooltip here, we do not see.

Oh, wait, I should probably get that to a place where you can see it, too. Right. There we go.

So if we look at this tooltip, there is no bitmap at all mentioned here. If you look at this hash aggregate, there is also no bitmap listed here. We have a build residual, but no bitmapping.

And if we look at this repartitioned streams operator, we finally see the bitmap. Isn’t that strange? For some reason, the bitmap got stuck.

The parallel exchange said, oh, I don’t want to go any further than that. Things get weird down there. I would imagine that this is some sort of optimization that why apply a bitmap to all these rows when you can apply a bitmap to just the rows that come out of the aggregation at the repartitioned streams operator. And, you know, that’s actually sensible.

I actually don’t disagree with that. But if I had to put a best guess stamp on a reason why, SQL Server is like, well, you know, I’m going to aggregate all these rows anyway. We can just apply a bitmap to a smaller result set, you know, rather than, let’s see, that’s the comments table.

That’s 2, 4, 5, 3, 4, 7, 3. Oh, that’s an eight digit number. And after the aggregate, we have a 4, 4, 0, 0, 7, 5, 5.

That’s only a seven digit number. So applying that bitmap to far fewer rows seems like actually a pretty sensible optimization choice. So good job there, SQL Server.

I appreciate you. Appreciate you at DOP 8. I don’t appreciate you at any other DOP. DOP 8 or BUST. So, again, bitmaps, they get created in the plan, not where they get used in the plan.

You should pay attention to where the bitmap gets used. If you’re looking at an actual execution plan, you may want to look at how effective the bitmap is. If you’re able to run the query at higher DOPs, you may see bitmaps increase in effectiveness because you would have larger hash buckets to start filling up and filtering things.

So, I don’t know. It’s always worth a shot, right? Let’s throw some more DOP at it.

Don’t downgrade the DOP. Upgrade the DOP. Up with DOP. Down with downgrades. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

I hope you really dig my Hulk shadows behind me on the green screen. Sensitive sily, that thing. And if you feel so inclined, if you’re feeling generous, if you’re feeling kind today, you want to like and subscribe to my channel.

Like this video and subscribe to my channel. You can’t subscribe to this video. It’s asinine. But if you feel like doing that, I would appreciate it. If not, I still hope you have a great weekend. That’s about it.

Anyway, thank you for watching. I’m going to hit the record button one more time. Thank you for watching.

Going Further

A Little About Local Variables In SQL Server

Posted on June 1, 2023January 21, 2026 by Erik Darling

A Little About Local Variables In SQL Server

Video Summary

In this video, I delve into the nuances of local variables in SQL Server and how they differ from parameters and literal values. I explore why using a local variable to fix parameter sniffing is not only ineffective but also counterproductive, as it leads to poor cardinality estimates and suboptimal query plans. Through practical examples, including dynamic SQL and stored procedures, I demonstrate that relying on local variables can exacerbate the very problem they are meant to solve. I also discuss alternative strategies, such as using recompile hints or parameterized dynamic SQL, which can help mitigate issues with parameter sniffing while maintaining performance and accuracy.

Full Transcript

Erik Darling here with Darling Data. And in this video, we are going to talk about how local variables are different and how they work in SQL Server. What are they different from? Well, parameters for one, literal values for another, things like that. And if you want maximum detail, on this post, I would suggest you go here. This is a post on my site. You can follow the trustworthy bit.ly link here. That’s bit.ly slash uppercase L, local, uppercase V, variables. If you go to this URL, but you neglect to put the proper capitalization in, you will end up at a place that I cannot voucher verify for. I have no idea what’s going to show up on your screen. anything .rtf, disappearance of here. A sla as their lib ucont that you get is on the leading key column of the index.

There are no 3D or 4D chess statistics objects in SQL Server, for better or for worse. Maybe someday we’ll get vectorized statistics objects or something.

But when we create indexes and the statistics that get created alongside them show up magically in our databases, in our statistics views in our databases, this is roundabout the data that SQL Server stores about what is in our statistics.

You have some basic information up here. You have some density information down here. And then you have the histogram down here, which has 1, 2, 3, 4, 5 columns in it that helps SQL Server make cardinality estimates based on what you choose to filter on.

The one that I want to filter on here, or the one that I want to call attention to, is for range high key 2, 2, 6, 5, 6, which has 27,901 equality rows in it.

And SQL Server knows it. SQL Server can tell that from the statistics objects. So if we do an equality search on 2, 2, 6, 5, 6, we should be able to get a correct guess for the number of rows that exist for it.

But if we do something like this, let’s turn on query plans too. We might need those down the line. And we get the correct number of records back. We declare a local variable.

It’s an integer. We filter to that integer. And we look at the execution plan. SQL Server had no idea what the hell was going on. Guess 12. 12 rows.

That’s not a very good guess compared to 27,901. Might make for some interesting plan choices if we were joining this off to other tables and consider this to be part of some critical piece of code. Now, we get that guess.

If we look at the statistics, we get that guess from looking at the all density portion of the statistics object and multiplying that by the number of rows in the table.

So if we take those two numbers here and we look at what comes out, we get 11.9 some number of digits in SQL Server rounds that up to 12.

So it guesses 12 rows come out based on that density vector guess. Density is calculated by looking at the distinct number of rows assumed for that column in the table.

So this number here, I promise you, is equivalent to the all density number in the statistics object that we just looked at.

Now, I still hear every time when I look at client code that they used a local variable to fix parameter sniffing. However, I’m not sure in most cases how that can be a valid use case.

It’s a valid use case about once every four or five years. I’m not sure how that can be a generally valid use case considering the guess is so bad that you get that guess for everyone.

You get 12 for everybody no matter what. It’s a uniform guess. If you truly have a parameter sniffing problem because some rows in the table have, some rows are responsible for millions of entries in your table and some rows are responsible for thousands or hundreds of rows in your table and you’re getting bad plan choices because of that, it’s hard for me to imagine how you fixed parameter sniffing when everyone’s getting that crappy guess.

It’s just not sane nor rational to me that anyone still believes that when they say it out loud. I don’t know how anyone says that with a straight face.

I don’t know how anyone says that with any face, really. Anyway, if we look at, let’s take a store procedure where store procedures are usually what people are afraid of, getting whacked with that old parameter sniffing problem.

We’re going to run this query in three different ways. Now, inside our store procedure, and this is usually what, I’ll see something close to this, we’re passing in a parameter called ID, and inside the store procedure, we have a local variable called ID fix.

Now, let’s just pretend once upon a when someone passed in some bad data to the store procedure, it got a bad execution plan, and then things went all haywire.

So we create a local variable and we say if the ID is less than zero, then we set it to one, else we use the ID. And we’re going to use this in three different ways. We’re going to do this once using dynamic SQL, but we’re going to pass the ID fix local variable to dynamic SQL.

The trick here is that the dynamic SQL is parameterized so that we will magically, through alchemy, change our local variable into a parameter.

And I disagree with this capitalization here. I don’t know where that came from. I’m terribly embarrassed. I don’t know where that came from.

Aaron Bertrand’s going to, I don’t know, find some way to kill me. I don’t think he owns guns. But we’re also going to look at the results of this query, which is using the ID parameter rather than the local variable.

And then finally, we’re going to use this one where we use the local variable outside of dynamic SQL. And so we already have this created. Now, all we have to do is execute this store procedure.

We get the correct number of rows back for each and every one of these queries. But of these, only this one gets a correct guess of 27,901.

This one gets a correct guess of 27,901. And this one, of course, gets the crappy local variable guess of 12.

So again, using a local variable does not fix parameter sniffing. It merely changes the cardinality estimation model used when SQL Server goes and does all that fancy relational algebra of figuring out how tables get assembled using various letters in the Greek alphabet.

So as much as possible, I do urge you to avoid using local variables like this. If you’re dead set on using local variables for anything, I would suggest if the code is not frequently called, throwing a recompile hint on there.

If the code is frequently called, and you need to figure out some local variable values, please either pass those local variables to parameterized dynamic SQL or to a store procedure, which can, again, through the magic and wonder of alchemy, convert them over to parameter values.

And we don’t have to worry about getting crappy guesses over and over again. Now, this isn’t to trivialize issues with parameter sniffing. I do talk a lot about that and how to fix that in various ways we can deal with it.

Aside from Microsoft SQL Server 2022’s parameter-sensitive plan optimization, which is, I don’t know. You know when Goldilocks shows up to the three bears’ house and Mama Bear’s porridge is cold and Mama Bear’s bed is too squishy and I sort of feel like that’s what we got out of the box with that.

Hopefully some improvements get made there, but I do wonder. And speaking of the parameter-sensitive plan optimization, if we look at the text for these, we will see that that optimization did kick in for these first two queries, which used a parameter value, but not for this third query, which used a local variable.

So using local variables breaks the parameter-sensitive plan optimization, at least as of today’s recording. Anyway, that’s enough about this. I’ve said too much already.

I don’t know. Thank you for watching. Hope you enjoyed yourselves. Hope you learned something. I do hope you’ll choose to like and subscribe and stick around a little bit.

And, you know, maybe learn a thing or two from an old man about SQL Server. An old man yells at SQL Server. Anyway, thank you for watching.