Tag: how to create temp table in sql

Query Hashes and Parameter Names in SQL Server

Posted on by Erik Darling

Query Hashes and Parameter Names in SQL Server

Chapters

Full Transcript

Erik Darling here, with Darling Data. Removing my monitoring tool mogul superhero outfit costume, play suit, for a moment. Talk about, I don’t know, something interesting that I ran into while working with some stuff in Query Store, and I thought that I would share with you. And it is the behavior of how queries get hashed when parameters get used. And I think if you’re the type of person who goes looking through Query Store for specific things because you use store procedures like SPQuickieStore and not the Query Store GUI, which, unfortunately, as of this late date and its inception, still does not allow any real, does not really have any search capabilities. Down in the video description, you will see all sorts of links that will help you get closer to me.
You can hire me for consulting, which is probably the ultimate in closeness. You can buy my training. You can become a supporting member of the channel. You can ask me office hours questions. And, of course, as always, if there is someone in your life who you feel needs this brand of enlightenment or harassment or whatever I happen to be up to, please do share, like, subscribe, tell a friend, all that good stuff.
Anyway, I have a new, well, I guess, I mean, I don’t know, like a little over a month old now, SQL Server Monitoring Tool. Totally free, totally open source, no intrusive stuff in your life. Just a really great way to keep an eye on SQL Server performance for servers you care about, but may not be able to get budget for a paid monitoring tool that would do this job.
Collects all the stuff that you would want to know about, all the stuff that I would care about and get into during my consulting engagements. And then for those of you who have embraced the robots, there are opt-in MCP servers that can talk directly to your collected performance data so that you can, you know, have the robots to reduce some analysis on just what got collected. It’s all nice and sliced up over time.
So they’ll be able to do a much, much better job of looking at those things than they would if you just set them free to a running SQL Server and you said, go run some DMV queries, I trust you. I wouldn’t do that. As far as me getting out and about in the world, apparently there is still some hunger for in-person connections.
So I will be traveling around. I will be at SQL Day in Poland, May 11th through 13th. I will be at Data Saturday, Croatia, June 12th and 13th.
Those are my fabulous upcoming international events. I’m very excited. You know, you can get away for a little bit. I’ll also be at Pass On Tour, Chicago, the Illinois, May 7th and 8th.
And I will be at Pass Summit in Seattle, Washington, November 9th through 11th. So I believe tickets are on sale for every single one of these things. So there’s very little excuse for you to not come see me in person as long as, you know, I guess, as long as you can get there.
Can’t we all, can’t we all just get there? Anyway, it is time at long last to look at this interesting thing. So let’s go over to SQL Server Management Studio.
We haven’t seen one of these in a while. And the first thing I want to say here is that this is not a political statement. I was born the night of this election and I often use this as a sort of jokey reference point to things in my life. But we have two queries here that are fundamentally identical, right?
We’re saying select c equals count big from dbo.users as u, where u.id equals some parameter. Right? And the reason why this is interesting is because for a very, very long time, I was under the, I guess, mistaken impression that parameter names were taken into account when generating a query hash. But after asking my dear friend at Microsoft, who I guess in turn asked their internal AI chat bot about this, which I thought was funny.
They said, the chat bot came back and said no. At this point in the code, when SQL Server is trying to figure out what a query’s hash should be, it sort of substitutes the parameter with some just constant node in there. And it’s just like, it doesn’t matter what you’re named.
Right? You can be anything. You’re just a parameter. What good are you? Right? You can be anything. Right? It doesn’t matter. So I thought that was funny because it turns out I was wrong. And the reason why this came up is because I was working with a client and we came across a troublesome query.
And I was like, well, let’s, let’s, let’s look this up by query hash and query store. Let’s see what we can’t get out of this thing. Let’s see what this thing is fully up to out in the world.
And so we put the query hash in the SP quickie store and I said, go find it, SP quickie store. And as fast as lightning, SP quickie store was out there finding query hashes for me. And it came back with two rows, one for the query we were looking for and one for another query that looked rather similar, but had a different query, but had a different parameter assigned to it.
And I said, oh, well, that is fundamentally distressing. Anyway, enough about me. How about you? Let’s run these two queries.
And, you know, we’re going to get execution plans for them. Not that the, not because the execution plans are of any interest to us whatsoever here, but because when we go and look at the query plans, we are going to see some interesting things.
So if we get the properties of this one, right, we have all of the usual attendant, lovely information that Microsoft has provided to us in the properties tab. And if we zoom in over here, we will see this thing. We will see the query hash and query plan hash for this.
These are somewhat long and difficult to remember. But if you sort of just get a general sense for them, I like to remember like sort of the last four digits, the last four bytes of the hash.
So we have like 9E02 and 7383. They start with 452 and 134. All right.
And if we just sort of keep this in mind and we go over here, we notice that when I switch back and forth between them, the parameter names change, but the query hash and query plan hash don’t budge, right?
None of that changes, right? We get a different SQL handle for it down here, right? We can see the SQL handle change because I suppose that does take the parameter name into account, but the query hash and query plan hash stay absolutely identical, right?
Down here on Jimmy Carter, we have the same thing. Starts with 452, starts with 134, ends with 9E02 and ends with 7383. So if you’re ever looking through a query store in this, you know, unlikely scenario, you’re using SP Quickie Store available at code.erikdarling.com.
You’re looking through a query store and you say, I want to find something by this query hash. So you use my very thoughtful include query hashes parameter to do that.
And you find multiple queries, the same hash. Well, if they have different parameters, that’s why. Parameters don’t factor into the query hash.
You learn something new every decade at least. Alright, that’s about it for me. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
I’ll see you in tomorrow’s video. Have a good one.

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.

T-SQL That Doesn’t Suck: I’m Running a Pre-Con at PASS Summit East

Posted on by Erik Darling

T-SQL That Doesn’t Suck: I’m Running a Pre-Con at PASS Summit East

On Thursday, May 7th, I’ll be in Chicago at PASS Data Community Summit East, running a full-day pre-con called T-SQL That Doesn’t Suck: Solving Performance and Concurrency Problems.

The pitch is simple: you already know how to write T-SQL that runs. It compiles, it returns rows, nobody’s filed an incident yet. The problem is “runs” and “runs well at scale” are different conversations, and production tends to be the one asking the hard questions.

All attendees get free access to Learn T-SQL With Erik.

What we’re covering

The day splits roughly in half.

First half is the performance problems that don’t show up until you actually have data and traffic behind them:

– Implicit conversions that quietly kill your seeks
– Non-sargable predicates hiding behind innocent-looking WHERE clauses
– Parameter sniffing traps — when it helps, when it hurts, what to do about it
– Joins that look fine in the plan right up until they aren’t
– Temp tables vs. table variables, and when each one actually wins
– CTEs that help vs. CTEs that just make the query feel organized
– Window functions that don’t spill to tempdb

Second half is concurrency — the stuff that turns a Tuesday afternoon into a war room:

– Blocking chains, and how to actually read them
– Isolation level surprises
– DML that holds locks like it’s paying rent
– Patterns that let readers and writers coexist without fist-fighting

We’ll also put AI-generated T-SQL on the table. Not to pile on — it’s showing up in pull requests whether you like it or not — but to talk honestly about where it falls apart and where it actually saves you time.

Details

– When:** Thursday, May 7, 2026, 9:00 AM – 5:00 PM
– Where:** Hyatt Regency McCormick Place, Chicago — Jackson Park B
– Level:** 300 (if you’re past “what is a clustered index,” you’re in the right room)
– Register:** Here

The hotel discount at the Hyatt cuts off **April 22**, so if you need a room at the conference venue, book this week.

Chicago in May. T-SQL all day. Come write queries you’d be proud to put your name on.

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.

Performance Studio 1.2.5: Query Store Time Slicers and Automatic Updates

Posted on by Erik Darling

Performance Studio 1.2.5: Query Store Time Slicers and Automatic Updates

Chapters

Full Transcript

Erik, plan, analysis, that doesn’t have the same, Erik monitoring tool mogul darling here. In this short video, just to talk about a couple very, very cool changes to the performance studio application that I’ve been working on. But very little of this is about anything that I’ve done lately. This is more about a community contribution Repo Romaine Ferriton. And I’m sure that I am not fully pronouncing something in there correctly. The first name I think I got, but the last name, there’s probably a much more melodic French way of saying that one. But very cool stuff. So what he added is when you go into the query store view and you connect to a server that has query store in it, there are a couple like, you know, sort of fundamental changes. Like one of them was there’s an automatic fetch. Now, when you get there for total CPU, and there’s an automatic fetch when you change metrics before you had to hit the fetch button again, which I guess was a bit clunky, not up to, you know, not up to standard. So that was the first thing. But then he added in these cool graphs. So like, as you’re, you can just get a much more easily sort of just get a visual look at like the like how big those numbers actually are. So like, you know, like, you know, you might be able to, you know, like, like sort of vaguely judge some of that when you’re looking over things. But now there are some visual indicators to be like, hey, no, this is really like, you know, like, like, maybe like you’re sorted by this metric. But you know, this metric also sticks out or something, right? It’s like good, good visual cues to like things you might want to look at. That was cool. I dug that. But the real cool thing, and I am just positively giddy about this one is that up at the top, there’s a slicer, right?
So like, you can see along that sort of top graph up there, when you had spikes in from the metric that you’re sorted by. So you can choose like where in here to go and look at things. So as far as time ranges go, if we click over here, and we say I care about these two spikes, the results filter to those two spikes, still as you as you change metrics to widen this back out. So it’s a little bit easier to see. As you change metrics, the graph changes as well. So this will tell you it’s sorted by average CPU time now. And we could do something just to make it a little bit more obvious where the changes, let’s do total memory. So here we have a bunch of new spikes to look at. And I think average memory has some other interesting ones where it changes. But as you change the as you change the what you’re fetching by the graph responds, and zoom in on whatever, wherever there were interesting spikes in there that you might care about digging into, which I think is absolutely just a fantastic thing to have in there. It’s so good that I am currently porting this over to the performance monitor query grids, because this is like, like game changer cool, I think, as far as like a feature goes.
Something else that you’ll notice in here, well, you probably might not notice immediately. But I’ve also added like an auto update feature, you do have to be on you have to do hit, you do have to be on 1.2.5 to see this. But if you go to about performance studio, you’ll see a new check for updates button. And when there’s a new version, you’ll be able to auto update right from that, you won’t have to like download a zip and do other stuff. Like I’m using this, this other GitHub repo, another open source one called Velo pack, that allows you to do full and delta updates to the application. I’m also getting this stuff into the performance monitor side of things. I’ve already done the full dashboard and the two installers. Light has a little bit more complexity to it because of the way the data in there is structured, but you’ll see it coming to those soon too. But now when you get to 1.2.5, you’ll no longer have to like scour social media or like, you know, like wait for some notification from GitHub that an update has been released. You’ll just you’ll get you’ll get a notification in the application and you’ll get you’ll be able to check right from here to see if there’s a new version.
So reducing friction, as they say in the PM world, I guess. So I don’t know, slowly becoming a product manager by trade, I guess. Anyway, that’s about it for this one. A couple of things that I’m very, very excited about in here, largely the the slicer in the new bar charts, but also, you know, making your life a little bit easier as far as, you know, getting to new versions of this and all the other stuff goes. But anyway, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. Hope you’re checking out the performance studio tool. It’s available over at code.erikdarling.com. That’s sort of a shortcut to my GitHub repos. That’s where you can see all the stuff I’m working on.
And if you have any feedback, let me know on GitHub. If you just enjoy it, I don’t know, maybe buy me a drink sometime. All right. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

DiskANN Vector Index Improvements

Posted on by Erik Darling

 DiskANN Vector Index Improvements

Chapters

Full Transcript

Erik Darling here, Darling Data. And I finally have something to be excited about in the vector area. It would figure that I just finished, you know, wrapping up my sort of YouTube expose into vector search in SQL Server 2025 and saying, man, like, Microsoft doesn’t make some fixes here. Like, I don’t know where this story’s going. But lo and behold, at, well, I guess, SQL Server, SQL Con, uh, this, uh, last week in Atlanta, uh, it was announced that a lot of the problems that I had with disk and indexes, uh, are, are gone now. So congratulations, a round of applause to, uh, everyone who worked on that. This is wonderful news because now Microsoft actually has a pretty good story around, uh, vector search in SQL Server that it just didn’t have before. So the, the, the two main things were, uh, one, uh, the, when you added a vector index, uh, to a table, the whole table became read only. That has been, that has been fixed now. That has been worked out. So you’re, you can write to your table. So people can both write to, to your, to your tables and like do normal stuff. And, and, and, and the vector index doesn’t stop that. Uh, so that, that is wonderful. That is fantastic news. Uh, this, this feature finally has a strong pair of legs under it. Uh, they’ve also done some other stuff, um, where, uh, along the way. Um, I think the other main thing in here, I haven’t had a chance to test any of this out. It’s rolling out pretty slowly to some of the, um, some of the Azure, uh, regions, but I have, I’m using my robot friends to probe them. I haven’t found one where, uh, this is available yet. So maybe it’s still a little too soon, but I just haven’t found it yet. Maybe, maybe I just missed it. I don’t know. You can never trust those robots. They are, they’re kind of lazy sometimes. They’re like, yeah, I checked all that. Sorry, nothing there. And you’re like, but I see it. And they’re like, oh, ah, sorry. I missed that one. But, uh, anyway, uh, some of the other cool stuff that they did.
Um, was speed up, uh, the, the creation of vector indexes. Uh, if you remember some of my videos where I showed you, uh, how slow it was and the insane amount of code that ran behind the scenes on that. Uh, apparently that’s all gone. I have, again, I have not yet tested it. So I don’t know what the improvement is or if that weird code still happens, but just runs faster now. We’re going to wait and see. But it seems like the way, um, it seems like fundamentally the way that, um, like the vector indexes get created now is just, totally, uh, different in storage engine and behind the scenes. And there’s not like 3000 lines of strange code with bizarre use hints running. So this is, this is a very, this is very good news for us here in vector land. Um, I guess there’s an important note about migrating existing indexes, but if you were crazy enough to use a preview feature and create indexes, Oh, I mean, I guess read the, read the warning there. Um, of course, as soon as I start recording this, it becomes the noisiest day in the world. I had a plane fly by, there’s ambulances going. I can’t win sometimes.
Uh, but the other thing that they did that I think was really cool is, um, let me get, scroll down to this part. Um, the query syntax and, uh, filtering bits. If zoom, it will cooperate. I’m going to give me Mark Vassinovich’s number. Uh, can file a complaint about zoom, about zoom it here. Uh, but it used to be that you use, like when you wrote a query, like, uh, the one on, well, I guess further, right. Uh, you, you, you had to ask for a much higher top end number, uh, sometimes because you didn’t know like how many things it would find. So if you wanted like the top 20, uh, from like the outer query, but you asked for the top end in the inner query, uh, you might not get as many back as you asked for in the inner query.
And so your outer top 20 would not be 20. So you had to sometime ask for like the top end 100 or 200 in order to make sure that you got 20 back. But all that has apparently been improved. Uh, the top syntax has apparently been extended. So top with approximate, that’s going to be fun to mess with. Uh, I can’t wait to get my hands on that one. See what, see what I can see again. I wonder if it’s only applicable with vector searches or if top with approximate is, uh, is, is usable in other, uh, non-vector index, uh, non-vector, non-vector searches, but we’ll, we’ll see.
Um, maybe, maybe that’s said in the post. I don’t know. I haven’t read all of it too closely. I just got so excited. But anyway, uh, if zoom, it will unzoom now, now that I’m done with you. Thank you. Uh, apparently there’s also some cool optimizer stuff in here, um, where the optimizer will choose depending on, hello, zoom it. Uh, the optimizer will choose between when to do a vector search, when to do an exact search, uh, based on, I guess, some various factors here. So, uh, again, very good job, um, everyone who worked on this. This is very exciting stuff for those of us who, um, have an interest in vector search and SQL Server 20, well, I guess not just 2025.
So I suppose it’s all in Azure as well. Uh, not, not, not being a huge disappointing stink bomb. So, uh, this, this all looks great to me. It all sounds great to me. As soon as I get my hands on it and I get to start messing with it, I will, I will of course report back. And, um, what do you call it? What was the other thing? Uh, I don’t know. So, uh, I, I tried to ask about when this might make its way to us, uh, earthly denizens who you, who still use on-prem SQL, uh, what cumulative update it might land in, but not sure on that yet. Um, so, anyway, uh, exciting news. Very happy about this. Again, good job to all involved and, uh, I cannot wait to get my hands on it.
Alright. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

SQL Server Performance Office Hours Episode 57

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 57


 

To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here, Darling Data. My contractual obligation to you, my adoring audience. It is Monday and so we are going to do office hours and I am going to answer five of your questions that are burdening you and I seek to unburden you from these questions. Sort of like a sin eater for databases, I guess. Down in the video description are all sorts of helpful links. Ways that you can interact with me and ways that you can’t interact with a robot, if you get my drift. You can hire me for consulting, you can buy my training, you can become a supporting member of the channel, or you can continue what you’ve been doing so graciously and asking me office hours questions for free. And if you find this content to be at all enthralling, lightening, maybe just seeing me on the screen burdens the load you feel in your life day to day, subscribe, subscribe, and tell a friend because I have all sorts of people who I would like to unburden. All right, let’s get right out of here. If you want free SQL Server monitoring, you should check out my GitHub repo. There’s a link to it down in the video description as well. It’s a very useful link section. I think probably the most useful link section on the internet if we were going to start measuring things. But it is totally free, totally open source. You don’t even have to put in an email address. It’s not phoning home telling me what it’s doing. It’s just a bunch of T-SQL collectors, things that I would normally run if I were doing a consulting engagement, except it spits it out into all these pretty charts and graphs. It also powers a bunch of, you know, dashboards. And, you know, if you’re the type of person who does enjoy chatting with robots about stuff, there’s an opt-in MCP server that you can use that can take advantage of well-defined and articulated rules.
for talking to your performance data and just your performance data. It works a whole lot better than just saying, hey, MCP, go look at my whole SQL Server and tell me what’s wrong. Because it’s focused, it’s over time, it’s nice and broken out. I’m telling you, my war on big monitoring continues and they have earned this comeuppance. So, help me bring down the big monitoring industrial complex and go download your free copy today. I will be out in the world even more. As soon as two dates disappear, two dates reappear. It’s amazing, right? How does it happen? How does Eric do it?
A lot of Bloody Marys is the answer. I will be at SQL Day Poland, May 11th through 13th. That spans, the 12th is included in there, right? The 12th is not optional, right? The 12th is happening. I will also be at Data Saturday, Croatia, June 12th and 13th. I’ve never been to Croatia before, but that should be fun. Pass on tour. I’ll be back in Chicago. I just left Chicago. Boy is my liver tired. I’ll be there May 7th and 8th and then past Summit in Seattle, Washington, November 9th through 11th.
I guess the 10th is not optional in there either. So, we’ll do that whole thing. Anyway, thank you for watching. I’m just kidding. It is. It is finally getting… You know what? It got nice and then it got crappy again and I just feel like God is out there toying with my emotions. I am not hip. I’m not hip to this winter situation we’ve got going on. You know, it’s ugly.
Anyway, let’s answer some questions because apparently it is what the people desire is questions being answered. Up first, what do we have here? Have you ever… I’m not clicking on that link. Have you ever seen a situation where users are forced to use nested join logic for performance gain? Is there a better way to achieve the performance gain without the somehow valid but fugly syntax of nested joins?
See Reddit posts. See your own Reddit post. I only see my Reddit posts where I tell people to download my free things and sometimes it works. So, yeah, of course I’ve seen those situations. I’ve seen a lot over the years. I’m a grizzled veteran and I’ve seen all sorts of terrible things. You’re asking about alternatives that aren’t ugly? Well, I mean, think about other ways that you would reshape a query.
You might, you know, join two tables together and put the results into a temp table and then use that materialized set of data to join off and do other things. Right? There are other ways to reshape a query, of course, but that’s just a very convenient one for some circumstances where you have to do everything kind of all in one go. Another convenient way of doing that would be using intra-query row goals, you know, using a top operator or offset fetch in order to sort of put a logical fence around certain operations to sort of make them happen in a certain time, space, place and way.
Those are, you know, just think of it as any other query tuning exercise, right? Yeah. SQL Server is doing something dumb. What do I do? Okay. I mean, granted, it’s not going to be like, you know, maybe it’s not going to be an indexing problem in these cases, but it is certainly ugly, but legitimate.
Sort of like some, some people I know. I messed that one up. Nevermind. Anyway, when looking at an actual execution plan, how do you know that it is time to try the legacy cardinality estimator? Because it’s a cheap gamble. Man, who do you think you are?
Legacy cardinality estimator has been fantastic at many things for many years. It is not a cheap gamble. If you are using the default, as Microsoft calls it, the so-called default cardinality estimator, you know, it might do okay. But nine times out of 10, if I am seeing particularly lousy join cardinality estimates, then that’s, that’s, I know, I know it’s legacy time.
But no, I don’t think it’s a cheap gamble. It’s not always an effective gamble, right? It’s not always a winning gamble. But it is certainly not a cheap gamble if you are using the so-called default cardinality estimator to give the legacy cardinality estimator a spin and see how it turns out. Because you just might find that a lot of those queries, I don’t know, maybe, maybe they’re just legacy queries that do better.
You know, I just, I find that it’s, it really does come, I think, for me mostly, yeah, it probably does come down to seeing really bad join estimates and sometimes really bad join choices in, with the default cardinality estimator where I’m like, alright, legacy, get your leggy butt out here. Let’s, let’s see what, let’s see how you do, right? Alright, moving on here.
Have you ever seen SQL Server audit kill a server? If it did, who would catch it? I only want to monitor some sysadmins, but I worry that my big OLTP workload might be impacted.
I don’t do a lot with SQL audit. I’ve, you know, of course seen it on a lot of servers. Most people who I say, hey, what’s that audit auditing, are like, I don’t know, I didn’t even know we had that.
And then, like, you look in the error log and it’s full of, like, audit errors and you’re like, well, apparently, apparently you care a lot. I’ve never seen it kill a server. Not to say that, I don’t know, maybe it could happen, but I think, I think it is okay at stuff.
Um, I don’t know anything about your workload or your sysadmins or what you want, what, what, what you want to monitor them doing. But, um, you know, um, maybe, maybe try it out in dev first. Maybe, maybe see how that goes.
I don’t, I don’t, cause I, I don’t know all what you want to do anyway. I hate audit. I mean, not, not cause it’s like bad, but because like, there’s no query plans. So like, who cares?
It’s not, not for me. All right. TempDB is always top weights, even on weekends. Does that automatically mean TempDB is our bottleneck or is that misleading? Well, it certainly is a pretty good indicator that, that TempDB is a bottleneck.
Uh, but it depends on how you’re measuring weight stats. There are a lot of weight stat scripts out there in the world that, uh, just, just compare weights to the general population of weights and tell you what percentage of, of the, the total population that weight accounts for.
And, uh, those, those scripts don’t really do a whole lot to, uh, put things in perspective, like how long the server has been up, uh, how much those weight, how those weights compare to server uptime, uh, things like that. So, uh, and I think it, it depends on how you’re measuring a bit, but you know, certainly TempDB could be, could be a bottleneck for you.
Uh, you know, all, all the usual, uh, you know, sort of advice applies to investigating your workload further. Uh, perhaps you should try sampling your weight stats over a period of time. Or you could, you could even download a completely free monitoring tool from code.erikdarling.com and you could, you could actually find out if TempDB really is your bottleneck.
Or if you’re being misled by whatever script you’re on. Alright. Ah, we run full scan stats every night.
Wow, you must have a lot of time. Uh, still see horrible estimates on some queries. What could cause that? Well, my friend, uh, maybe you should meet our other friend who thinks the legacy cardinality estimator is a cheap gamble. Uh, so, why, why could you have that?
Um, I mean, a million bajillion reasons. Uh, let’s see, let’s, let’s count them off. Um, perhaps, uh, you are using local variables. Perhaps you are using table variables.
Perhaps you are writing non-sargable predicates in some way. Uh, perhaps you are using the default cardinality estimator, right? Um, you know, uh, maybe your queries are suitably complex that any cardinality estimator may have a difficult time, uh, figuring out, uh, just how many rows are going to emanate from various elements in your query plan. Uh, you know, but mostly, uh, you know, if, if you run full scan stats and you still see horrible estimates, uh, you know, you know, switching cardinality estimation models, it’s certainly one option.
Um, but you know, I think again, what, what’s, what’s important to understand about cardinality estimation is that it’s basically only valid, um, to any real degree. Uh, at like, uh, like, uh, it’s not, I guess it wouldn’t be a root operator, I guess it’d be like a base operator. So it’s usually like cardinality estimation is usually pretty okay.
Like when you’re reading from a table, right? So like, just think about like the far right or bottom of your query plan, like that sort of crescent shape where, uh, index and table access tends to happen. That’s really the only place where cardinality estimation is like, you know, uh, valid valid.
Uh, once you get to joins and group by and other things that may reduce rows, cardinality estimation gets really hard. The other thing to keep in mind about your query plans is that they are complete Frankenstein monsters in some cases where, you know, SQL Server is exploring all sorts of alternative plan shapes. And while that’s happening, it may come, it may have like cardinality estimates up to this one point and then it might replace a whole subtree because of like, wait a minute, I was wrong about this.
We got to do something else here. Uh, and so like it might stitch plans together in a sort of weird way. It was, it used to be very, very common, uh, to see query plans where it was like, and I think 10,000 rows are going to go into this merge join.
And I think 40 billion rows are going to come out of this merge join. And you’re like, I, I don’t understand that math. Where did that, how did that math, math, where did that math come from?
What is the origin of this math? And then like, but you know, over time, you just kind of get used to seeing oddities like that because, you know, uh, your, your plans are again, this terrible island of Dr. Moreau monsters that had to be put together very quickly. Right.
To make you happy to make, make you say, what is it? Why is the optimizer taking so long? It’s been 15 minutes. Gosh, darn it. All right. All right. Anyway, I gotta go and do something else with my day now.
Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video. I think.
Anyway, as far as I know, some terrible fate befalls me, but I don’t know. I don’t know. I’m feeling pretty lucky these days. All right. Thank you for watching. All right.
Thank you for watching. All right. All right. All right.

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.

Advanced T-SQL Triage: I’m Running a Full-Day Pre-Con in Jacksonville

Posted on by Erik Darling

Advanced T-SQL Triage: I’m Running a Full-Day Pre-Con in Jacksonville

On Friday, May 1st, I’ll be in Jacksonville for Day of Data’s pre-con day, running an all-day session called Advanced T-SQL Triage: The Art of Fixing Terrible Code.

If you’ve ever inherited a stored procedure that looks like someone lost a bet with the optimizer — MERGE statements, RIGHT JOINs nobody can explain, CTEs stacked eight deep, scalar functions wrapping scalar functions — this one is for you.

All attendees get free access to Learn T-SQL With Erik.

What we’re doing all day

We’re not going to sit through slides about what a clustered index is. We’re going to look at queries that are actually broken and fix them. On the table:

– Paging logic that scans when it should seek
– Window functions that spool and spill because nobody gave them a sort to work with
– Indexed views that look clever on paper and lock everything in practice
– Data modifications that block like linebackers
– Dynamic SQL that’s parameterized, fast, and doesn’t make your DBA cry
– When CROSS APPLY is the right tool (and when you’re reaching for it because you don’t want to think)
– Views vs. inline TVFs vs. scalar UDFs, and why the optimizer treats them very differently
– Why RIGHT JOIN is not simply LEFT JOIN spelled backwards
– Rewriting scalar UDFs so they stop wrecking your execution plans

You’ll leave with a cheat sheet, a working mental model for diagnosing slow queries without guessing, and — if the day goes the way it usually does — a short list of queries at work you’re itching to go refactor on Monday morning.

Details

– When: Friday, May 1, 2026, all day
– Where: University of North Florida, Jacksonville, FL
– Register: Here

The free Day of Data event is the next day, Saturday May 2nd, same venue. Come for the pre-con, stay for the community event. The hotel discount at the Hilton Tru cuts off April 22, so if you need a room, don’t sit on it.

If your queries scare you, come fix them with me.

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.

Join me at SQLDay in Wroclaw, Poland! May 11-13 2026!

Posted on by Erik Darling

Join me at SQLDay in Wroclaw, Poland! May 11-13 2026!

Chapters

Full Transcript

Erik Darling here with Darling Data, and this is, of course, a promotional video. I have to warn you before I do one of those on YouTube or else I don’t know what happens. I’ll get eaten by an algorithm, I guess. Anyway, much more exciting. This year, this May 11th through 13th, I will be in Wroclaw. That is tricky. That is not Wroclaw. Wroclaw or Wroclaw. I have been informed that that is Wroclaw, Poland. Poland’s a lot easier than the other one. But I’m gonna be at the SQL Day workshop, finally, in the year of Pope John Paul, 2026. I was originally supposed to be there in 2020, but due to unfortunate world events, I did not get to go to Poland. I got to spend a really weird, like, two in the morning talking to my webcam.
So, I’m getting my revenge, finally, though, and I’m getting to go to one of the coolest conferences in all of Europe. I have a pre-con there that’ll be on the 11th of May. I already bought my plane tickets, don’t worry. It’s gonna be all about advanced T-SQL. I’m not gonna teach you how to write a join or write a right join or write a wrong join or wrong or right join.
I’m gonna teach you all the, like, just weird stuff that I’ve learned about T-SQL databases and the way that those two things interact over the many beard-graying years that I’ve been dealing with our beloved SQL Server. So, I hope to see you there. I hope to see you, of course, at the pre-con because I have extra goodies for anyone who shows up. My course, Learn T-SQL with Eric, usually a $500 USD value. All pre-con attendees will get free access to all 60-plus hours of video for the rest of their lives.
The pre-con that I teach is just a small compartmentalization of some of my favorite parts of the course, but there’s a whole lot more out there. And if you want to just be able to relive your favorite moments with me in Poland, well, you’ll be able to do that for the rest of your life. So, I hope to see you there at the SQL Day conference in Wrocław, Poland.
God, I hope I got that right. And, man, I love you. Goodbye. Thank you.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Performance Studio 1.2 Release: Query Store Improvements

Posted on by Erik Darling

Performance Studio 1.2 Release: Query Store Improvements

Summary

In this video, I delve into the latest updates and enhancements to my standalone query plan analysis tool within Performance Studio. I’ve been working on this tool to provide a more personalized and helpful experience, especially after feeling let down by SolarWinds’ inaction on Plant Explorer. The latest release focuses on improving the query store functionality, adding filtering and graphing capabilities to make it easier to analyze and understand query performance over time. I walk through the new features, including how to filter and view query history, and demonstrate the execution graphs to help you visualize query performance trends. This update is part of version 1.2, and I encourage you to download it from code.erikdarling.com to explore these new features for yourself.

Chapters

Full Transcript

Erik monitoring tool mogul here. Well, I guess in this video I’m a query plan analysis mogul. We’re not talking about monitoring tool stuff here. We’re talking about my stand-alone query plan analysis tool that, analysis stool, analysis tool, pause, that I’ve been working on because, well, I’m sick of SolarWinds not doing anything with Plant Explorer, and I wanted something that I could put a little bit of myself into. Not in a weird way, in a helpful way. Anyway, I’ve got a few things that have changed since last time. There were a few bug fixes. You know, not a whole lot. But this release was, for me, mostly about making the query store stuff a little bit better. Because, you know, I punted a little bit better. Just to get something in there. And there was some stuff that I didn’t do that I wanted to do that I just got around to doing. So, let’s talk about what I did. Anyway, it’s all fun, right? So, let’s open up Performance Studio. And let’s click on the query store button. And we must test our connection here. And let’s connect into, let’s say, Stack Overflow 2013. All right. So, the stuff that I added so far is a little bit of filtering magic and a little bit of graphing magic. So, just, you know, normally you hit Fetch here, and you get all this stuff back. Now, if you hit Clear, it doesn’t clear out the results pane. It clears out the search stuff. So, don’t hit Clear and think, this isn’t working. This is a bug. I have to bother Eric. That’s not what works here. So, let’s look at some of the filtering stuff. So, I’m going to come over to Management Studio real quick. And let’s look at some plans that I have here.

So, I’m just going to grab the top recent most 10 just by whatever. And we have, let’s see, plan ID 8246. So, if you want to look at, you know, if you want to go searching for stuff, you just hit plan ID there, plug that in and hit Fetch, and you will get plan ID 80246. Isn’t it? Isn’t it our lucky day? You could also do that lookup by query ID or whatever. You could also look at things. You can also search by module name. I think the only one that we might find in here is dbo.dropindexes. This is what I get for typing on my own. I was looking by plan ID. There we go. There we are. All right. There’s our module, dropindexes. Anyway, there we have that. So, if you want to search through query store data now, just sort of like you could do with SP Quickie Store. I don’t really have like the full spate of things in there like comma separated lists and all the other stuff.

This one, I just wanted to get something simple into so you could see that. But then also, if you right click, you hit View History. Well, that’s not a lot of fireworks, is it? Let’s do this a little bit better. Let’s do this thing some justice. Let’s hit Clear. And let’s go to Executions. And now let’s hit Fetch. And let’s see, maybe we can find one that has a little bit of life to it. And hit View History. And this is what we get back. All right. So, sort of like, oh, I got to fix that. Look at that. Nah, that’s silly.

Didn’t show up. Didn’t show up when I opened it. It only showed up after I clicked on it. Hell yeah. This front-end stuff is hard. Man, I thought back-end work was difficult. Front-end stuff, very sensitive. Very sensitive. Anyway, I’ll fix that later. But what you have here is sort of a graph over time of how your query performed. You know, kind of just to sort of try to bring things on par with how, you know, like the query store things work.

You can do average duration. You can do average CPU. I guess those are about the same there. You can do total CPU. Oh, look, it changed a little bit. And, you know, all the rest of it. Executions. Wow, it did nothing for a long time. And then it executed a whole lot. Well, I guess those are all tiny little single executions. And then there was a big spike in executions. So, that was fun.

Anyway, just some small improvements that I’ve made to my Performance Studio app here. This is, again, something that you can open up query plans with, run queries, experiment with performance things, get a whole bunch of good information back about what’s going on in the query plans.

Today’s video is just going over the query store additions that I made in version 1.2. So, you have that now at your disposal to have fun with and look forward to. This is already released. So, if you go to code.erikdarling.com and you click on Performance Studio, you should see the 1.2 release with a bunch of zip files. This thing is available for Windows, Mac, cross-platform.

So, I would encourage you to read the readme file because there’s a lot of good things to read in the readme file about what this thing does. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll try my plan analysis tool here.

And I will see you in, well, actually, I don’t know if this is Thursday or Friday’s video. So, either I’ll see you tomorrow or I’ll see you Monday for office hours. All right. Have a good one.

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.

T-SQL That Doesn’t Suck: I’m Running a Pre-Con at PASS Summit East

Posted on by Erik Darling

T-SQL That Doesn’t Suck: I’m Running a Pre-Con at PASS Summit East

On Thursday, May 7th, I’ll be in Chicago at PASS Data Community Summit East, running a full-day pre-con called T-SQL That Doesn’t Suck: Solving Performance and Concurrency Problems.

The pitch is simple: you already know how to write T-SQL that runs. It compiles, it returns rows, nobody’s filed an incident yet. The problem is “runs” and “runs well at scale” are different conversations, and production tends to be the one asking the hard questions.

All attendees get free access to Learn T-SQL With Erik.

What we’re covering

The day splits roughly in half.

First half is the performance problems that don’t show up until you actually have data and traffic behind them:

– Implicit conversions that quietly kill your seeks
– Non-sargable predicates hiding behind innocent-looking WHERE clauses
– Parameter sniffing traps — when it helps, when it hurts, what to do about it
– Joins that look fine in the plan right up until they aren’t
– Temp tables vs. table variables, and when each one actually wins
– CTEs that help vs. CTEs that just make the query feel organized
– Window functions that don’t spill to tempdb

Second half is concurrency — the stuff that turns a Tuesday afternoon into a war room:

– Blocking chains, and how to actually read them
– Isolation level surprises
– DML that holds locks like it’s paying rent
– Patterns that let readers and writers coexist without fist-fighting

We’ll also put AI-generated T-SQL on the table. Not to pile on — it’s showing up in pull requests whether you like it or not — but to talk honestly about where it falls apart and where it actually saves you time.

Details

– When:** Thursday, May 7, 2026, 9:00 AM – 5:00 PM
– Where:** Hyatt Regency McCormick Place, Chicago — Jackson Park B
– Level:** 300 (if you’re past “what is a clustered index,” you’re in the right room)
– Register:** Here

The hotel discount at the Hyatt cuts off **April 22**, so if you need a room at the conference venue, book this week.

Chicago in May. T-SQL all day. Come write queries you’d be proud to put your name on.

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.

Performance Monitor 2.3 Release: ErikAI, More FinOps, and MCP Madness

Posted on by Erik Darling

Performance Monitor 2.3 Release: ErikAI, More FinOps, and MCP Madness

Summary

In this video, I provide an update on the upcoming release 2.3.0 of the monitoring tool, highlighting several exciting features and improvements. One of the most anticipated additions is the introduction of EricAI, a smart analysis engine designed to offer advice and insights similar to those I would provide manually. This tool will analyze various metrics, such as lock weights and page IO latch weights, to suggest potential optimizations and improvements. Additionally, I discuss enhancements to the FinOps functionality, aiming to make the tool more user-friendly and cost-effective, with features like a new monthly cost column and a light data importer for seamless data migration. The release also includes bug fixes and improved testing processes to ensure a smoother user experience.

Chapters

Full Transcript

Erik monitoring tool mogul, reasonable rates darling here. In this video I want to talk a little bit about the upcoming release 2.3.0 of the monitoring tools. These features will impact largely both of them, though some of them to different degrees. So just to talk about a few of the things that are going to be in there. Zoom. So one of the features that I’ve always wanted to put into a monitoring tool was like a smart analysis engine. I’m terrible at naming things, and so I didn’t name this thing. Claude just started calling it EricAI. I think it was trying to be nice to me after messing a bunch of stuff up. But what this is going to be is sort of like trying to give you the advice that I would give you. Look at the things that I would look at. It’s maybe not, you know, it’s not going to be a hundred percent there for a little bit, but I am starting to, you know, break ground and get some of the scenarios out there. What’s been fun about this is like, you know, like not having every scenario, like readily available to me while I’m building this has been building sort of like a test data mockup thing where, you know, we can play with different numbers and see how the thing, different inferences sort of follow the path. So like, you know, some like basic ones, right? Like you look at weight stats and you’re like, or like, you know, you sometimes you start with weight stats. Sometimes you look at weight stats and you’re like, that’s boring. But let’s just say you always start looking at weight stats and you see a bunch of lock weights. Then EricAI will go look at the block process and deadlock reports and we’ll figure out like what kind of queries are blocking, right? And it will go like look at different like query collectors to see if there are like performance issues with those queries. It might even look and tell you if, you know, if there’s a lot of reader on writer blocking and deadlocking, if an optimistic or row versioning isolation level would make sense. It’ll also do stuff like if you have like really high page IO latch weights and you have like 24 gigs of memory and four terabytes of data. EricAI will tell you some bold and reasonable things about your situation. So there’s a whole bunch of stuff like that in there. And, you know, like working through that and trying to get all the different scenarios covered. So that’s going to be a fun little project. The other thing that I have going on in this release is a bunch of updates to the FinOps stuff. Apparently FinOps is a thing that bosses love.

And my goal is to try to make this is helpful or rather I want this tool to be as easy for you to get and use in your environment as possible. And part of that is appealing to bosses. Bosses love FinOps because FinOps means saving money. So not only is this tool free, but this tool might also save you money too. So like, you know, not only is it trying to infer like, you know, like interest server optimizations and stuff for like, you know, things you can do things you can do in place that could maybe lead to like hardware downsizing, or it could lead to like maybe even like moving from like enterprise to standard edition. Long term also want to look at like server consolidation things. I also want to look at I mean, I have a very, very, very long stretch goal, incredibly long stretch goal, where I want to figure out I want to sort of like do like a like, like how compatible your SQL Server stuff code and everything else is with migrating to Postgres. It’s just going to be like a scorecard. I think I don’t I don’t know if I could actually go beyond that. But it’s going to be fun to work on.

Some other stuff that have gone in there. Oh, before before I move on, a couple things with the FinOps. So like, I tried to think of a bunch of reasonable ways for people to rather for me to infer how much your SQL Server costs you. Turns out, that’s like impossible to do, right? Like, I mean, like a standard enterprise have list prices, everything in the cloud has list prices. And then like, you know, is it reserved? Is it spot? Is it three years? Is there one year? I don’t know. And I don’t want to figure all that out. So I let you tell me how much your server costs you a month, because I don’t know, right? I’m not going to be able to look at everything and figure that out. So if you go into manage servers, now, you’re going to have this new column called monthly costs. And I just threw a number in there to get some other stuff showing. But if you don’t have numbers in there, after you upgrade, if you highlight a server, and you go to edit, you can put the monthly cost in there. And you can tell me how much this server costs you a month. And then we can start figuring out how much we can save you based on that. Right? So that’s pretty fun stuff. There’s been some other things added to this. This is not a very great example, because this is just looking at SQL Server 2022. And I haven’t been doing a lot of query work on it. But, you know, just sort of some cost savings recommendations. So some stuff in there, of course, you know, the utilization tab, I talked about this in another video.

But I did add a new tab to this. And the new tab is fun. Because if you if you watched another video of mine about SP Quickie Store, where I talked about sort of like looking at queries, but like using like the sort of 8020 rule, and figuring out like, you know, like, you know, like 20% of your queries are responsible for 80% of your misery or something. I started I put that in here too. It’s not exactly the same as the one in Quickie Store. But it does do, you know, pretty much identically the same thing. But this will start looking through your query stats data and trying to figure out which queries across like a variety of metrics are responsible for like, just you know, a lot of your misery. Like for this example, you know, we have this tp, tp, hammerdb tpcc query, that has had 376 million executions use 3 million milliseconds of CPU time, right, just like 35.9% almost 36% of the servers entire CPU time. So this is clearly something that we’d want to be like, hey, like, what are you doing? Like, maybe can we do anything about you, right? This is something that you’d want to address. So we have that stuff going on in there. So a couple neat things that that I’m working on in there.

Another thing, this is actually some user submitted issues that I thought were good. A light data import. So you know, I always sort of pictured the light thing is something that someone would just spin up and start, you know, plowing away the server with. I didn’t, I didn’t think people would use this as like a long term monitoring tool. Turns out, they love this thing. Turns out, it’s a great long term monitoring tool. I didn’t know that. It’s not at all how I pictured it. But you know what, I’m not complaining, use it however you want. But but there was no way to bring easily bring data from like a past version to a new version. So now I’ve got a light data importer in there. Basically, the way that works is, you open up the new version, you open up the new version and you hit an import button. And the import button will like you point it to the directory where your old thing is in, it’ll flush all the data out of your current DuckDB database to a parquet file. And then it’ll copy all the parquet files over to your new, your new directory. So you’ll have all your past data in there.

I’ve got an alert now for when servers go online and offline. In the light database, there is a per server utility database setting, because one of the FinOps tabs up there runs SP index cleanup to get you a list of indexes you can consolidate. And turns out not everyone uses like master or performance monitor or whatever. And also, if you’re using light, you probably don’t have a performance monitor database. So you can pick which database you want to use in there. All of the execution plan analysis stuff that I’ve been working on in Performance Studio has been getting ported over to the two dashboards. And so now I have MCP tools that can run those rules and do that stuff. Let’s see. Here, I don’t know, there was some MCP stuff that I worked on, blah, blah, blah. And let’s see. Well, I don’t know, there’s been a bunch of bug fixes since bug fixes since the last one. These are less, these are less fun to talk about, because you know, I hate bugs. And I hate knowing that I produce something that had a problem for someone. So I’m trying to have less of that happen. And the way that I’m trying to have less of that happen.

Is some of the things in here. Primarily, some more adversarial testing. Actually, that bottom one is stupid. That was a bad test that we got rid of. So more adversarial testing, especially on the installer. That’s, that’s, that’s gonna be a big one for helping me long term, because that installer process turns out to be the hardest part about this whole thing. Not only upgrades, but like the scripts and everything. That turns out that was the most that is the most difficult part of the whole project. And I’ve also added some automated code review in here. So like whenever someone submits a PR, I have cloud code code review it. And usually it’s good. And usually there’s some back and forth between the like, let’s face it, everyone else is who can everyone who contributes to this is using a coding agent.

So that gets set. It’s just like two things talking to each other. But I added this other layer in there. There’s a different coding agent to hopefully get a different point of view or perspective on, on things as they get is pull requests come in. And that’s code rabbit. code rabbit. It’s a hopeful, apparently free for open source repositories. So I don’t know if my credit card starts getting charged, I’m in trouble. But I added this thing in to hopefully get like, you know, because like something like when I when my cloud makes a pull request or merges stuff. It’s basically done its own code review. So maybe that’s not great sometimes. So I’ve added in another layer here to hopefully catch more stuff before problems arise. But anyway, just a quick update on stuff that’s coming in 2.3 that should be out a little bit later this week. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about well, I don’t know yet. Well, we’ll work it out on the way, won’t we? All right. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.