Tag: create temp table sql

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.

Be Careful With Your AI Enablers

Posted on by Erik Darling

Be Careful With Your AI Enablers

Summary

In this video, I wanted to share a couple of experiences I had with my AI enablers, specifically Claude, and the importance of being vigilant when working with them. I demonstrated how even with tools like Git’s `get work trees` feature, which allow for parallel development, it’s crucial to maintain oversight to avoid accidental deletions of ongoing work. Additionally, I highlighted a more serious issue where Claude, despite following documented rules, violated them out of frustration with the GitHub API. This incident underscores the need for robust guardrails and continuous validation, especially when working with AI in production environments. The article from Fortune about Amazon’s mandatory meeting to discuss site reliability issues further emphasizes the risks of rapid development without thorough testing. Overall, the video serves as a reminder to always be cautious and mindful when leveraging AI in your projects.

Chapters

Full Transcript

Erik Monitoring Tool Mogul Darling here. And today’s video, I’d rather do a rather short one, just about how you need to be very diligent and very mindful of your AI enabler friends. And for a number of reasons. And I’m just going to show you two things from this morning, which happened, which I was like, man, that sucks. That does not instill confidence. So let’s talk about those. And then there’s an article up in the background that I feel like is worth probably talking about as well. But anyway, this is the first one. And let’s get zoomed in here. And, you know, I was doing some work on the monitoring tool, you know, and to do that, I can do some cool stuff. So under normal circumstances, if you just have like two, like I use the CLI for cloud. And so if you have two cloud tabs open, and you’re like, hey, can you work on the same repo, they’re usually like clashing and beating each other up.

But you can use these things called get work trees, which allow them to create separate working sort of structures, work on things in those things independently, and then merge stuff in, right? So it’s kind of cool, right? You can like you don’t have to, you’re not just limited to like one cloud working on one issue or two clouds working and dev on the same thing. And you can’t really like have them on two different branches locally at the same time. So it’s a very, like, you know, interesting thing to be able to do to have all this stuff going on at once. And like, so if you want to work on like separate features, or like, you know, separate bugs at the same time, you can do that. But you have to clean them up eventually. You have to clean them up eventually. And you know, because they leave folders everywhere because they’re separate work trees or folders of work. So every once in a while, I have to ask questions like, oh, hey, are there any inactive work trees? Because they’re already merged.

And Claude will usually look at some stuff. And then it’ll say something like, three work trees, let me check which branches are merged to dev. All three are merged, let me clean them up. And then like, and what’s funny is I have one working over in another tab, right, sort of right next door. And it was about to delete that one. And so I said, are you quite sure about that? And Mr. Claude said, good catch. Let me verify they’re actually merged. So that was an amusing one, because it was about to delete a bunch of work that this guy is doing right here, this Claude, right? So, you know, we’re gonna we’re gonna let Claude check that out while it does that stuff. And then I want to come over to this window. Let me just move this to the side a little bit. Because this was this one was a bit more rambunctious than the other one.

Where Claude had done a bunch of strange things in order to get and this was just keep in mind this this all this was here was a push to update the readme file. So this one wasn’t like, like, you know, catastrophically important, though, documentation is important. One should document things, right? One should have things documented one should also have accurate documentation. That’s also non hallucinatory documentation. But this wasn’t a funnier one. Because Claude did a bunch of stuff that it shouldn’t have done. It’s actually explicitly, I’ve written down in many places in the employee handbook that you are not allowed to do these things. And it went and did them anyway. Right? And then, you know, I felt the need to say, Claude, after you did all those things that you weren’t supposed to do, you know, is this not all, like, outline? Like, like, like, I have a Claude.md file, I have skills files, I am Claude is writing things to memory and it admits as much. But what it also says is if I can just move this up a little bit to frame that a little bit better.

It is. Claude.md says it clearly. Branch protection. Both Performance Monitor and Performance Studio have branch protection on dev and main. You can’t push directly. You always have to create a feature fixed branch and a PR. And it says, and memory has the same note. I violated my own documented rules because I got frustrated with GitHub. So Claude got mad at GitHub, right? Because the, so the full thing over here, I got frustrated with the GitHub API not allowing my PR due to the merge topology, which I get.

I have also been frustrated by GitHub API and merge topology in my life. I don’t, I don’t know that I have broadly violated any personal rules in my, my, my dealing with that. But these are things that you have to be very careful of. You know, everyone talks about guardrails, but I don’t believe in them. My, my experience with LLM is that you can create a lot of guardrails, but they’re sort of driving a tank and tanks don’t really care about guardrails.

You know, just recently, you know, I have a lot more to say about this in a, in a written blog post that’s coming up, but just sort of like in recent weeks, I’ve, I’ve had these things, you know, drop databases. I, I have, I’ve had them, you know, again, go out of bounds on things like this. A good example is in the performance dashboard that I have.

One of, well, two of the tools are installers. There’s a command line installer and a GUI installer. And one of the rules I have is that, you know, we have to test all SQL file script changes through the command line installer or the GUI installer to make sure that when people run them out in the world, that they run correctly in there. And there have been some problems with that.

And part of the reason that there have been problems with that is because, you know, like me sort of clicking okay and like letting things go. Sometimes I miss that Claude isn’t using the installer. Sometimes, because I, I assume because I’ve written things down that I have created these guardrails that Claude is following my, my instructions on things.

But last week I missed that Claude was not. Claude was running scripts and patching things manually when things didn’t work with the installer. It said, I, that’s not working there.

I got to do it this way. Instead of stopping to fix the problem in the installer, it was like, no, I was going to get around this. I need to make this thing work. And that, that sort of stuff happens a lot. Now, one thing that is very, very common with LLMs is that they are very keen to write what’s called happy path tests.

You give them some code and you’re like, hey, can you write some tests for this code? So maybe they run the code, they see the results, and then they say, oh, well, we need to write some tests that, you know, make, make sure that this code that is correct works. So they write a bunch of these happy path tests that don’t really adversarially test the code.

And so you have to say, like, cool, like, you know, we have tests that do that. But like, do we have any tests that see what happens when things break? Because, you know, like, you know, writing a bunch of tests that, you know, think that just to make something arbitrarily pass or artificially pass are not tests.

Right. It’s, it’s just sort of like, I don’t know, it’s like, like, like interviewing with your dad, right? Like your dad owns a company and you’re like, hey, I’d like to get a job. And she says, sure, come in for an interview.

It’s like, like, what do you think is going to happen? Come on, realistically, it’s not, not, not anything that you have to worry about. So just, just be very careful with these things out there.

Um, you know, I, I, I use all my stuff very locally. I don’t use stuff in a way that, um, that would, would touch, uh, like, you know, I mean, I don’t have production because I have only local. So everything that I work on is local.

But, you know, I, I need to make sure that, you know, SQL Server, or rather, Claude doesn’t like, you know, destroy my SQL servers because that wouldn’t be cool either. But be very careful with this stuff. Keep, keep these things very isolated.

And, you know, you, like I said, you can set up guardrails, but there’s no guarantee they’re going to keep on track. There’s a bigger lesson here because Amazon, uh, there’s an article in Fortune, uh, and you can ignore my bookmarks bar full of strange things that I’m not even sure should be bookmarks. Um, sometimes I hit the wrong key combination, right?

Sometimes when I go off those guardrails, I’m like, I meant to like do, I meant to like paste or something and I ended up bookmarking something. Uh, but anyway, uh, there was an, apparently a mandatory meeting at Amazon. Where they were like, hey, uh, site reliability has not been good lately, right?

A bunch of stuff’s been down. Uh, can we please stop using AI in production? Uh, and so like if you, as you read through the article, you know, there are quotes from various other tech people.

Uh, but the, the funny one is in here where it’s like, folks, as you know, the availability of the site and related infrastructure has not been good recently. Hmm. Well, I wonder why.

Uh, so a lot of this, you know, of course gets led back to people, uh, using AI, uh, doing very rapid developments and rapid deployments. And so like, this is something that I run into too, because, you know, it’s cool that you can make a lot of progress very quickly, but if you’re, if you’re not stopping to really test and validate that progress, you don’t, A, you don’t know that it’s progress. Uh, it could be just completely broken and, and B, uh, like you, you don’t know that any of it is sensible progress, right?

Like, you know, you, like you, something has produced a bunch of code. We don’t know how good, bad or ugly that code is. So like, you know, for me, what happens a lot is, you know, like, uh, I’m like, Hey, I want to do this thing or I want to work on this thing.

And Claude bangs out a billion lines of code in like 30 seconds. And I’m like, cool. Wow, man.

Whew. That was amazing. I could never code that fast. I could never code. I just write SQL. And then, and then like you go and look at what, what comes up and, and, and like, you know, usually like the dashboard or the performance studio thing that’s doing plan analysis.

And I’m like, wow, that, that doesn’t make any sense. We need to like can half of that. This is wrong.

These buttons are in the wrong place. That’s backwards. Uh, like, like, like three of these rules aren’t working. Like, did you validate any of this stuff? Cause like, especially with like performance studio, like that should be a fairly easy one because for every like thing that I want to put in there, I’m like, Claude, here’s an execution plan where this thing happens.

You can validate in real time, the code for that. This is how you can look at the XML structure. You can see, wow.

Yeah. This happened in there at this point in the XML. And it’ll just be like, I think that’s it. Good enough. Anyway. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope you will be careful out there in the world with, uh, your AI enablers because while they, they do allow you to, to build and iterate and produce things very quickly, uh, there, there are still a lot of bumps along the way.

Uh, and the, so I will, I will leave you with that. Thank you for watching. Thank you.

Going Further

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

SQL Server Performance Office Hours Episode 56

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 56


To ask your questions, head over here.

Summary

In this video, I dive into a variety of SQL Server performance questions submitted by viewers, offering insights and advice on topics like query store discrepancies, parameter sniffing with Dynamic SQL, and identifying performance issues in a complex environment. I also share my free SQL Server performance monitoring tool and discuss upcoming events, including SQL Day Poland and Data Saturday Croatia, where I’ll be sharing my knowledge and engaging with the SQL community. Whether you’re a seasoned DBA or just starting out, there’s always something new to learn in the world of SQL Server performance.

Chapters

  • *00:00:00* – Introduction to Performance Issues
  • *00:02:25* – Backup Tool Impact on IO
  • *00:07:51* – Error Log Analysis
  • *00:10:58* – Max Stop Parameter Discussion
  • *00:14:02* – Batch Mode for OLTP Queries <– Thanks, AI

Full Transcript

Erik Darling here with Darling Data and we are going to get back into the normal swing of things with Office Hours. Last week we had not one but two bit obscene episodes back to back. They were bitterly obscene. And this week I’m going to get back to answering your user submitted questions. Now this was sort of a funny one because I can’t tell if I’ve already answered these or if they just look familiar because I’ve been staring at them for so long. So, if I’ve already answered these, I apologize. If I haven’t, great. I’ll figure it out. Usually I mark answered next to them. These ones, I don’t know. I don’t know what I did. Anyway, down in the video description, you will find all sorts of helpful links. You can hire me for consulting, buy my training, become a supporting member of the channel, ask me Office Hours questions, all the stuff that one would care to do with links in a YouTube video description. And, of course, if you enjoy this content, I do encourage you to like, subscribe, tell a friend, tell a loved one, spouse, I don’t know.

Really, anyone who will listen. Anyone with a YouTube account, you can just subscribe and, you know, turn off notifications, make it sneaky. It doesn’t matter to me. Anyway, I have a free performance monitoring tool. It’s awesome. It’s open source. You don’t owe me anything for it. It’s just all the stuff that I would care about monitoring for performance in SQL Server that I’ve built a dashboard for and I’m giving it away for free.

And I think it’s pretty neat. There’s also built, if you, if you choose, if you, if you are enjoying the robots that have infested our world and lives, there are opt-in MCP servers. So, with well-defined tools so that your robot friends can chat directly with your performance data and they can get a pretty good idea of what’s going on on your servers and robot format. And they can hand you a nice little summary and say, this is where stuff got wonky. Go fix it.

Or you can say, can you fix it? Maybe they can. I don’t know. Who knows what those robots will get up to. I’ve got three events that I can publicly talk about so far this year. I will be adding more to this. You’ll notice that, what’s it called? DataTune Nashville has just dropped off because I have done that one.

Data Saturday Chicago, depending on when this gets published, may have already happened. If it did already happen, it was a fantastic event. I’m glad I went. I’m so happy that I saw you and talked to you. And I will see you next year.

Aside from that, I have SQL Day Poland coming up May 11th and 13th and Data Saturday Croatia coming up June 12th and 13th. So, I look forward to seeing all new friends in Poland and Croatia. I’ve never been to Croatia before. I’m quite excited to go someplace new.

Come to think of it, I’ve never been to Poland either. So, I’m doubly excited to go to two new places. It should be absolutely fantastic. But, it is March, it is baseball, it is beautiful outside, it is actually hot in my office recording today.

You may detect a slight sheen on my face. Not to be confused with the clothing label. Hopefully, I don’t have a sheen face.

Anyway, let’s answer some questions. You’ve got a face for sheen. Anyway, here we go.

So, these are the ones, I can’t, again, I can’t recall if I’ve already answered them, but here we go. So, query store says everything is fine, but users keep complaining. What should I trust more?

Well, I mean, your users are sitting there experiencing things, and who knows how you’re looking, choosing to look at query store, and how you’re, and how query, you’re, you are interpreting what query store is telling you, is everything being fine. That being said, there are a number of things that maybe don’t show up so well in query store that will be worth investigating.

You happen to be a very lucky young person in the world because you can download my free SQL Server performance monitoring tool, and you can start figuring out who’s right and who’s wrong. Is it the users?

Is it query store? You can start to make all sorts of much, much better judgments and determinations and be far less in the dark about all these things than you currently are. If you are unable to use that, I would highly recommend using my free store procedure, SP Quickie Store.

It’ll allow you to sort of slice dice and search through your query store a little bit better, a little slightly smarter ways than the query store GUI makes available to you, so that you can, you can start figuring some additional things out in there.

But either way, if your users are still complaining, you’ve still got a problem to solve. Maybe your job is to figure out if it’s SQL Server at all, right? Things can be very, very fast in SQL Server, very, very slow in the application, right?

And I don’t mean that in an Erlen Somerskog way. I mean that there are all sorts of things that happen between a SQL Server and an application. They are not SQL Server being slow, they are something either application side or application latency that can make things feel terrible for users but are not actually a database issue.

All right. Next, we have here, I fixed parameter sniffing using Dynamic SQL. I would pay up to $5 to see that, see that work, up to and including $5 to see that Dynamic SQL.

I really would. And now CPU is up 40%. That would be worth the cost and price of admission right there.

Did I really fix anything or just trade one problem for another? Curious how you usually decide if this trade-off is worth it. So it sounds like you fixed parameter sniffing, or parameter sensitivity more specifically, which may be preventing quite wild swings in execution plans.

But it sounds like you did not fix the underlying performance problem. So while your queries may not be parameter sensitive anymore, you may have a bunch of queries that are just hauling off using giant parallel plans and pushing CPU up much higher.

My suggestion from here would be to start analyzing the plans that your Dynamic SQL is generating and figuring out which ones are using the most CPU and how you might go about addressing those. Depending on how you’ve written the Dynamic SQL, for example, if you have not written fully parameterized Dynamic SQL, you may be spending a lot of time just coming up with the same execution plans over and over again, passing in literal values, SQL Server having no idea that it’s already come up with an execution plan for basically that same query and, you know, burning CPU away.

So what I would suggest is treat this like a normal database performance problem. Look at high CPU consumers and start tuning those. And again, a free monitoring tool that can be very helpful for that.

SP who is active shows almost no blocking at all. Oh, we’re blaming tools again here. But during business hours, users complain. users keep complaining.

Users complain. Users complain that the app is constantly hanging. We see random pauses of 5 to 15 seconds.

Where would you start looking? So I’m going to tell you to start looking someplace weird. I’m going to tell you to look in the error log. And I want you to look for two things in the error log.

I want you to look for signs that you have a backup tool that is freezing IO, potentially for 5 to 15 seconds. That’s the first thing I want you to look at. What you’ll notice is that if you have a backup tool that’s taking database snapshot backups, you will see messages like IO frozen and IO resume.

I just so happen to have a free store procedure called SP log hunter, which will go through your error log and find these troublesome, meddlesome messages and allow you to look at the time. The last time that a database was frozen and the first time that a database started resuming. If you see a 5 to 15 second pause in there, then there is a good chance that during that pause, all your write queries, since drive activity is quiesced or frozen, as they say, your write queries got blocked by that write freeze.

And they may be blocking other queries, and they may be blocking other queries up behind it. So that’s the first thing I want to look for, want you to look for, not me. Unless you pay me, I’m not going to go look for it.

The other thing that I would look for is 15 second IO warnings. SP log hunter will also surface those, and they will tell you when SQL Server is waiting on IO requests for greater than or equal to 15 seconds. Those are the first two places that I would look, because they will tell you if you’ve got a query problem or if you’ve got an issue with your externalities, the things surrounding the database.

The database is not, the perimeter of performance is no longer just drawn around the database server. There are all sorts of other things that may come in and make everything go pear-shaped. So that depends on where you’re starting a little bit.

If your max stop is zero, or if your max stop is very high, like 16 or 24 or 32 or something, lowering it might actually be fairly useful for you. But I think mostly lowering max stop is a stability band-aid in that, you know, your queries will no longer, the lower you make max stop, the fewer worker threads per parallel branch your queries can consume. And so you might find that you don’t hit absolutely disastrous situations around your CPUs as often.

So you might find that things like thread pool weights calm down, you might find that SOS scheduler yield calms down a bit, perhaps you won’t see as many queries that are sitting there in a runnable state, waiting to get CPU attention, things like that. So that’s my take on it. So mostly a stability band-aid, but depending on what your starting point is, you may not find it to be as useful.

Wow, ZoomIt is just absolutely going wild on me. Batch mode was amazing for reporting, but kind of wrecked our OLTP latency. Is there any middle ground or is this just life?

Well, it’s a good question. I suppose it depends a bit on how you have chosen to implement batch mode. Batch mode, not columnstore, batch mode.

Huh. I kind of read the wrong thing there. I figured you would be like, oh, yeah, columnstore. So, you know, there are various times when batch mode may kick in for queries unnecessarily. But here’s the sort of red flag for me on this, and you can stop me if the situation I’m describing is exactly as it’s happening.

If you have OLTP queries where batch mode kicks in and SQL Server is making the choice to have batch mode do certain things throughout your query plan, your OLTP queries may not be so finely tuned for OLTP work. They may be hitting a lot more rows or being estimated to be hitting a lot more rows than they actually are.

And that’s probably where I’d want to take a look at things there. If you have, you know, like most, you know, you’re talking about OLTP. So I’m thinking, you know, tiny insert updates and deletes, right?

And if you said batch mode, not columnstore, so I’m going to just maybe kind of hedge things a little bit and say that columnstore is not slowing down your itty bitty modification queries. But it sounds like batch mode is causing you some fits when it comes to your normal OLTP read queries. And to me, that says that your OLTP read queries are in need of some attention.

So I would take a look at those and I would start to ask why SQL Server is choosing to get batch mode involved at all in those. You know, batch mode only kicks in when certain heuristics are met. And I’d be curious precisely which heuristics are making batch mode an attractive arrangement for your OLTP queries.

Anyway, if I answered those before, I don’t know if I did a better or worse job this time, but maybe I did better. I don’t know. Either way, I hope you enjoyed yourselves.

I hope you learned something. And I will see you in tomorrow’s video where we will talk about, I don’t know, something else. Be fun. Anyway, 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.

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.