Tag: Parallelism

Better Domain Knowledge Makes For Better Queries

Posted on by Erik Darling

Better Domain Knowledge Makes For Better Queries

Chapters

Full Transcript

Erik Darling here with Darling Data, continuing my upward trajectory as a monitoring tool mogul in the SQL Server community, but today we’re going to take a little break from shoveling free software to the masses, and we’re going to talk a little bit about how you can use better domain knowledge to write better queries, and I’ll give you a little example of that using the Stack Overflow database, because that’s usually how these things work, isn’t it? Anyway, down in the video description, you will find all sorts of helpful links wherein you can hire me for consulting, buy my training, become a paid supporting member of this YouTube channel, and also where you can do other things, like ask me office hours questions, and as always, if you enjoy what I’m doing over here, please do like, subscribe, and tell a friend, because I like to see the number, I like to, bigger number is better for these things, so please, please spread the good word, my friends. Okay, we’re going to briefly pimp some free software here. I built a free SQL Server monitoring tool, free, open source, available on GitHub, the link to that is down in the video description as well.
If you are in need of that sort of thing, if you care about the performance of the SQL Server, but maybe don’t have the budget to get a paid monitoring tool, which charges you way too much for the pieces of crap that they are, then you can use mine, which is not a piece of crap, and well, you know, it’s pretty good at this point. So, you’ve got that. It’s an option for you, right now. I will be out in the world doing all sorts of fun things.
I have SQL Day coming up in Poland, May 11th through 13th. Data Saturday, Croatia, June 12th and 13th. Pass on tour in Chicago, Illinois, May 7th and 8th.
And, of course, Pass Data Summit, the full big deal out in Seattle, Washington, November 9th through 11th. So, if you are in the ticket buying mood and any one of those locations seem seemly to you, well, I suggest you go buy tickets. I’ll have pre-cons at all of them, teaching advanced T-SQL stuff, so, you know, you should show up, do that.
Anyway, it is March, but it doesn’t feel much like March today. Actually, I’m not sure what it feels like, because my fingers aren’t really feeling much. So, you know, hopefully it’s not a stroke, but I’m pretty sure it’s just cold, because I can still do a drumroll.
So, anyway, we are back into having two spawns of SSMS open territory in my life, so here we go. Now, a lot of times when I am helping clients write queries, you know, it takes, there’s like a learning curve with just about any data set. Because when you first sit down and you look at things, you might say, oh, well, they’ve got some tables.
And, you know, this is the way that they’re joining those tables together. These are the existing queries, so maybe I should follow that pattern. But then you might find that some of those patterns aren’t really correct and don’t really yield the results that anyone would expect or want.
You get kind of weird stuff back, and you’re like, I don’t think that’s exactly it, right? Like, you know, you’ll learn to get a sort of taste for these things the longer you’re consulting. And so, like, a lot of the time that I spend, aside from, like, making the queries faster and, you know, figuring out why the queries are slow, stuff like that, is just sort of figuring out the data that’s underneath them.
Because sometimes the more you look, the stranger things you find. So, if you were to sit down at the Stack Overflow, sit down and you became a developer at Stack Overflow. Not that I think they’re hiring at this point.
No, this is the same. If you were to become a developer at Stack Overflow, you might see, like, users and you might see posts and you might say, well, I’m going to assume that there is a pretty strong relationship between users posting things and posts existing and stuff like that. And, like, I’m pretty sure this seems like, you know, a pretty strong relationship, right?
Users post things. And so, you might get a request to write a query and you might say, no problem. I need to find the user with the most posts, so I’m going to go do that.
And you might run a query that looks like this. You might get no results back using the magical live zoom feature available in SQL Server Management Studio 2022 point something. I can zoom right in there and my green screen is having a tough day.
But I can zoom in and see that I got no results back and I am quite saddened by this. So, this is where sort of, like, examining the data and getting better domain knowledge about it can help you write faster queries better and better queries faster. Both ways, right? Effective and efficient.
People say you can’t have both. I say you can’t because I care about you. So, if we were to look at who has the most posts in the post table, we would find this owner user ID zero. Who is this mysterious owner user ID zero with 226,238 posts?
What a prolific, prolific thing that must be. What a user. Well, we don’t have that user ID in the post table.
That is not a person. That is not a thing that exists. And so, we become confused and sad. Maybe a bit listless, limp.
You know? Just a flab in the world. And then we start to lose faith in these relationships. You might say, well, gosh darn it, why don’t we have a foreign key?
You might, maybe it’s not enforced or something, right? Maybe someone untrusted the foreign key. There’s all sorts of things that are like, ah, I thought that made sense, but now it doesn’t make sense. So, like, again, the more domain knowledge you have about the environments you’re working in, the more effective you become in them.
So, if, you know, going through this exercise and saying, well, I no longer trust, like, that foreign key, SQL Server doesn’t trust it. I don’t trust it either now. You might say, well, what if I go and check first, right?
So, what if I say, now, instead of just getting whatever from the post table, I say, well, I need to add a qualifier here. I have to say that whatever user ID I find, that user ID has to exist in the users table because without that, we get no results. And that doesn’t make for a very good query experience.
And so, with that domain knowledge applied, now we can run this query and we can get back a very expected result, right? We get back top post to John Skeet. So, my advice here is don’t take relationships for granted in SQL or in life, I suppose.
When you’re, you know, first starting to work with a data set and learn about that data set, I do encourage you to explore it so that, you know, because you might be helping someone tune a query that’s wrong. You might be helping someone tune a query that doesn’t make sense, right? So, the more time you spend learning the data and exploring, the better off you are.
And I guarantee you, it will help you not only write better queries, but in some cases, it might even help make your queries faster because you’ll be avoiding touching a lot of data that you don’t have to. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you m-m-m-m-monday for office hours. All right. I hope I have some questions. We’ll have to go look. I’ll go check out the Google form today.
All right. Goodbye. Have a good weekend.

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 Pre-Con at SQLDay in Wroclaw

Posted on by Erik Darling

Advanced T-SQL Triage: I’m Running a Pre-Con at SQLDay in Wroclaw

On Monday, May 11th, I’ll be in Wrocław, Poland for SQLDay 2026, running my full-day workshop Advanced T-SQL Triage: The Art of Fixing Terrible Code.

Quick pronunciation note before we go any further: it’s *VROTS-wahf*. Not “Rock-law.” Not “Rack-claw.” Not whatever you just tried in your head. The “W” is a V, the “ł” is a W, and the “c” is making its own decisions.

I was wrong about it for years. You will be too. That’s fine. The Poles are patient people (I hope).

SQLDay is the largest data conference in Central and Eastern Europe, held at Centennial Hall — a UNESCO World Heritage building that genuinely makes the average US convention center look like a storage unit.

Workshops are Monday the 11th, the main conference runs Tuesday and Wednesday. If you’re anywhere in Europe and you’ve been meaning to catch one of these workshops in person, this is the one.

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

What the day looks like

A full day rescuing T-SQL from itself. We’ll look at queries that are actually broken and fix them:

– Paging logic that scans when it should seek
– Window functions that spool and spill because no one 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 safe
– 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 you haven’t been to Wrocław before — a strong case for staying an extra day.

Details

– Workshop: Monday, May 11, 2026 (all day)
– Conference: Tuesday–Wednesday, May 12–13, 2026
– Where: Centennial Hall, Wrocław, Poland (in person + online)
– Register: Here

If your queries scare you, come fix them with me. And if you’ve already got them under control, come anyway — there’s a three-day conference right after, and Wrocław’s old town is worth the flight on its own.

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.

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.