Query Tuning Archives | Page 43 of 147

A Little About Automatic Tuning In SQL Server

Posted on December 2, 2024January 22, 2026 by Erik Darling

A Little About Automatic Tuning In SQL Server

Thanks for watching!

Video Summary

In this video, I revisit the topic of automatic tuning in SQL Server, addressing some of the feedback from a previous discussion. After Brent commented that he felt I wasn’t being fair in my critique, I decided to delve deeper into his example and provide a balanced perspective. The video walks through setting up the environment with Stack Overflow 2013 data, creating necessary functions and indexes, and altering database compatibility levels to observe automatic tuning in action. By using SQL Query Stress for multiple executions, we were able to generate enough data to trigger automatic plan recommendations, highlighting that five executions are indeed insufficient for meaningful results. This exploration demonstrates the importance of thorough testing when relying on automatic tuning features, ensuring that your queries receive optimal performance over time.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we are going to revisit automatic tuning again. Because, obviously, I talked about automatic tuning in, well, now yesterday’s video. And, you know, Brent was having problems with automatic tuning, Jessica. I’m just making fun of the transcript. The way that things get transcribed in YouTube, I’m sure things look dumb in mine. But then, Brent commented on YouTube, that I’m not being fair. And also on LinkedIn, that I’m not being fair. And so I had a little chat with Bats Maru. And Bats Maru said, be fair. So in today’s video, we are going to be fair. Because if there’s one thing we care about here at Darling Data, it is fairness. So let’s walk through Brent’s example, which involves, I’ve just I’ve just reformatted things a little bit because it’s not specific to Brent. Everyone else’s query formatting gives me a headache. So I just reformat, move things around a little bit. But this is, you know, we’re using Stack Overflow 2013, not the full Stack Overflow database. Apparently everyone who says disks are cheap has never bought disks from Lenovo. So we’re using a slightly smaller version of the database here. But I’ve created the function and I’ve added the isValid to the table.

And that’s not my typo. Don’t yell at me. I’ve created an index on reputation and I’ve created the getTopUsersStore procedure. Well, actually, maybe I cut that off when I was moving stuff around. Anyway, that thing’s in there. I promise. Otherwise this thing would just throw errors, right?

And so what I’m going to do is follow along from here where we alter the database compat level to 110. We change the database scope configuration and we mess a little bit with QueryStore. And in Brent’s example, he had only executed the store procedure five times, which is an inadequate amount of sampling for the automatic tuning feature. Now, automatic, no, I went hunting through the Query, not QueryStore, the extended events GUI because I wanted to figure out if there were any events that would fire around automatic tuning. And there are a whole bunch of them. So I just created a session with all the ones that I thought looked interesting in there. And that’s what, that’s the live data that we’re watching over here. All right, cool. So we’ve got this thing watching our query, watching our server very carefully. And I’ve also got the script from yesterday fired up, ready to go.

So the first thing I’m going to do is rather than just execute the procedure five times, we’re going to go a little bit, we’re going to go a little bit harder than that. And we’re going to use SQL query stress if it’ll actually show up on the screen. Where are you, SQL query stress? There you are. No, that’s a new window. Oh, apparently that other window was frozen. Okay, good. Let’s load settings. Thanks. Thanks. Thanks for making me look like an amateur SQL query stress. It’s real cool.

Let’s put that in there. And then I’m going to just for this one, I’m going to do 100 and 100 to make it an even a lot of executions. And that all does, I don’t know, pretty quick, right? And if we chop off a zero there and a zero there, we’re going to get ready to do the next one.

Now, right now, we don’t have any data in here, right? Nothing is showing up here, and nothing will be showing up in here. But what’s really funny is that in Compat Level 110, we can’t even run this query to check on things. Even if I add an optimized, like a Compat Level hint to this query for 150 or 160, we can’t run this query with JSON in it because it’s not available under Compat Level 110. Would that I had a big enough hand to smack everyone at Microsoft who makes these decisions? I would gladly do it. It would just be an endless slap for, I don’t know, probably 10 years.

Anyway, you’re just going to have to take my word for it that there’s nothing in here and there’s not, well, obviously nothing in the extended event. So that’s great. So now let’s flip the Compat Level to 160. And we’re going to just do this for 10 by 10. And this is going to be significantly slower. But if we come over here and watch this, eventually we’ll get some data in here. It takes a little bit though.

This thing takes a lot longer to run under Compat Level 160. And of course, you know, the actual feedback for the event takes a somewhat significant number of executions before it starts thinking about regressions and making guesses and figuring out if things need to be changing. But there we go.

Miraculously, around 20 executions now, we have a regression check. And now since we are in Compat Level 160, we can run our JSON query. And we have some advice in here. All right. Average query time changed from 7.56 milliseconds to one, well, 15.2 seconds. And we have some stuff over here where just like with my example query, there was, you know, some information about which query we should force and which query IDs were involved. So let’s take that and let’s put that in there and let’s stop SQL query stress so that we don’t have another weird crash thing going on in there. I’m not sure why SQL query stress had a problem. But if we look in query store, we will see query ID one had two plans. And since this is sorted by average CPU descending, this will be the slow one. And this will be the fast one. We got 50 executions out of that and 10,000 executions out of that. So yeah, there’s actually stuff in there. Now, sort of interesting, maybe, I don’t know, vaguely interesting is if we flip compat the compat level back to 110 and we add some zeros back in here.

I don’t know exactly how interesting this is. And we run this a whole bunch of times. The live data view from here will actually show this automatic tuning check abandoned thing for our query. Like, see, there’s query ID one. That’s the one we are looking at. So at some point, this thing does, see, SQL Server does sort of give up on this one, because there are a lot of errors in there. Now, that finished. And now you can see that I use a Lenovo and Lenovo did a software check. It was very interesting stuff in my life. And now if we flip the compat level back to 160, so I can run the JSON query again. Sometimes this will say that the query is too error prone. It’s not happening here, but at least, I don’t know, I ran through this a few times. And like, there was one time where it said for the under reason, it was like, this query is too error prone, we can’t we can’t handle doing this. It only happened to know, like I said, every once in a while, over here, it says it is not error prone. But at least one time, maybe probably actually at least two times, it did say it was error prone.

I’m not sure why SQL Server changed its mind. Maybe I just ran this thing enough that it’s changed its mind about that. I’m not sure I couldn’t tell you. But anyway, the moral of the story here is that five times is not enough, not enough executions to get the automatic tuning stuff to kick in.

But if you run stuff a lot using I don’t know, you could use O stress if you’re feeling command liney. But SQL query stress does a pretty good job of executing stuff enough to trigger the automatic tuning, at least recommendations. And if you have the automatic plan forcing stuff on, it’ll force the plan for you.

Anyway, I think that’s about that. So we can probably stop this here. Me and Bats Maru are achieved peak fairness for the day. So we’re going to go celebrate now. I don’t know what we’re going to do to celebrate. Bats has some crazy ideas.

Bats has some crazy stuff to say. But anyway, that’s that. Execute the stored procedure more. Something will happen eventually. Anyway, thanks for watching. Hope you enjoyed yourselves. I hope everyone learned something.

And as always, my rates are reasonable.

Video Summary

In this video, I dive into the world of automatic tuning in SQL Server, addressing some concerns that arose when my friend Brent struggled to get it working properly. I walk through a detailed demonstration using SSMS and a sample query provided by Microsoft, showcasing how to set up and test the feature while emphasizing the importance of having sufficient data in Query Store for the system to make meaningful recommendations. By exploring real-world scenarios and potential pitfalls like parameter sniffing, I aim to provide clarity on when and how automatic tuning can be beneficial, as well as offer practical advice for those looking to implement it effectively.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about automatic tuning in SQL Server. And the reason why is because I’ve had a couple people tell me that my friend Brent ran into some trouble trying to get this feature to work. And I want to make sure that we can alleviate Brent of his worries and sorrows. So Brent released a video about not being able to get this thing to work. And it says, I struggle with this. I struggle with automatic tuning Jessica. Well, that’s, that’s worrisome enough isn’t it? It’s terrifying enough on its own. But then I, I posted a video about, uh, cardinality estimation feedback and Flagstar seven days ago said, this reminds me of Brent’s recent video. With him being unable to get automatic tuning to work. I have no idea what he was doing wrong. Okay. Fair enough. And then I opened a, a, an issue on my, my GitHub repo for SP Quickie Store. And then I opened a, a, an issue on my, my GitHub repo for SPQuickie store and then I opened a, an issue on my, my GitHub repo for SP Quickie store and it says, I don’t know what he was doing wrong. But then I opened an issue on my GitHub repo for SP Quickie store and it’s not And I’ve thought that maybe, you know, I don’t really do anything with this dynamic management view, but, you know, I thought maybe it might make a good addition to Quickie Store.

Maybe we get some additional query feedback stuff on it. And Reese Goading said, does Sys.dmdb tuning recommendations even work? Brent recently tried his best to get anything out of it.

As I recall, he failed. Oh, gosh. This sounds like real trouble. This sounds like a job for Darling Date. So let’s come over to SSMS.

And I’ve cleared out Query Store here because I don’t want anything interfering. And I’ve set CompatLevel to 150 because the parameter-sensitive plan optimization makes stuff in getting this demo to work really confusing.

You don’t want to be in CompatLevel 160 for this. And I’ve got this query over here that Microsoft provided. As you can see, it returns no rows currently, which is apparently Brent’s experience as well. But don’t worry.

We will get at least one row out of this. I promise. Now, I’ve already got this index created. So everything is good here. We’ve got that index.

And we’ve got a store procedure here that will get the total sum of scores based on a parent ID and a post type ID out of the post table. No, it’s not much to look at. But it’ll get us where we’re going.

It’s just enough to make this whole thing work. Now, let me show you why this feature will kick in for this procedure. If we run these two executions of the procedure back to back, the first one returns a result almost instantly, which is great.

Everyone wants instant results. Isn’t that nice? All right.

And the second one takes a little bit longer. All right. So here’s the first one. It takes exactly one millisecond there. That’s pretty good. And this one down here, this takes eight seconds. That’s not so good.

That sounds like parameter sniffing to me. Could be a big problem, couldn’t it? All right. So let’s make sure that we have a reasonable baseline sitting around in our database here for SQL Server to work with. The big idea here is to have enough of a baseline in Query Store for it to be able to do something.

Now, I’m going to kick this one off to execute. And this is going to take around about 45 seconds to a minute. And while this thing executes and does its thing, let’s come over here and let’s look at this wonderful free script that I got from Microsoft’s page on the Sys.dmdb tuning recommendations dynamic management view, where, you know, we got all this stuff.

We got a JSON value and we’ve got some ifing in here. That’s quite nice. And then down in here, we select from the, that didn’t frame up too nicely.

So we’ve got to select from the DMV and we’ve got to cross apply to some open JSON stuff. Why? I don’t know.

Some real big wrinkly brain over there thought, I have an idea. JSON’s new. Let’s cram a bunch of crap into JSON. Let’s make it, let’s make it as hard as possible for people to get reasonable information out of this.

All right. Yeah. Great idea. Why don’t, why don’t you just use some more XML? Why don’t you just follow query plans and, uh, and extended events and, uh, the block process report and the deadlock XML report. So I could at least reuse some of my XML knowledge here.

No, just jam it all in JSON. I’m sure it was three nanoseconds faster to do that. So anyway, of course, this thing runs and it finishes. Look at that.

44.8 seconds. I was almost right about 45 seconds. Now, with just those two things having run, we still don’t have a row in here. We don’t have a row in here because we still only have one plan. We’ve got nothing for SQL Server to compare it to.

So we’re going to come back over here and we’re going to hit SP recompile here. And then we’re going to run these two store procedures back to back. All right.

And this time, do they both run faster? Yeah, sort of. I mean, the one that was really slow before runs a lot faster. We went from eight seconds down to 650 milliseconds about. But the one that ran really quick before in like one millisecond now takes 600 milliseconds.

Okay. Fair enough. Let’s hit, let’s, to not interfere with, with query store or the plan cache or anything. Let’s hit SP recompile there.

And now I’m just going to run this one 30 times for the one that was kind of slow before. This is going to do some more. This is going to do more work a little bit more quickly than the last time around. It’s not going to take 45 seconds this time.

Excuse me. Because we’re using that clustered index scan plan, this should finish up in around about 20 or so seconds. Oh, 13 seconds. Oh, you know what? I’d rather under promise and over deliver than anything else.

But now with 30 executions of that around, when we run this, magically we have a row. Don’t worry, Brenty. I gotcha.

So what is this telling us? Well, average CPU time changed from 56 milliseconds to 7.689 seconds, right? 7,600 milliseconds.

And if we scroll all the way over here, we’ll have some advice from this DMV. And it will be that, oops, I, I messed that whole thing up. Ah, there we go.

Let’s slide that back. That we should force for query ID 10 plan ID 9. Okay. Well, here’s query ID 10. The regressed plan ID is 11.

The recommended plan ID is 9. Okay. Well, let’s see. Let’s take query ID 10 right here. Let’s copy that. And let’s use my free, amazing, immaculate store procedure. Oh, that didn’t, what the hell?

Oh, it must have copied the, must have copied the column name. I forgot that that’s a default thing you can set. So let’s look for a query ID 10. That is the right one, isn’t it? Query ID number 10.

Let’s just double check and make sure. Query ID 10. Wonderful. We’re going to run that. And now let’s, let’s, let’s, let’s refresh our mind of what SQL Server was telling us. The regressed plan ID is 11.

So let’s go look at the regressed plan ID, right? That’s going to be plan ID number 11 right here. And this is the clustered index scan. Okay. Now the, the good plan ID it says is nine, right?

That’s this one, the one that says we should force right here. If we come over here and we look for plan ID nine, that is the clustered index seek with the key lookup. Now, would you want to force this plan?

It’s a good question because for one set of parameters, that’s a pretty good, that’s a pretty good plan to force. For another set of parameters, that’s a pretty bad plan to force. So should you follow this advice?

Probably not. Probably not a great idea. Now I will, I will, I will grant SQL Server a little bit of grace here that in the query plan that it says was regressed, there is a missing index request that would probably help things out here, but it doesn’t, it doesn’t tell you to create the index.

It tells you to force the plan. And I don’t really know that I love that so much. So what should you do here? Well, what I would recommend is not, maybe not listening to the advice of the tuning recommendations query. Because if you force that plan for that query, you will have some that are very quick and some that are very slow.

What I would probably recommend you doing is tuning that query. Maybe that missing index request would have been the thing to do it. But I think what’s generally valuable about this view is not necessarily the advice that it gives you about which plan to force, but really that queries are ending up in there with some discrepance, with some, with some obvious signs of regression that you might want to address.

Right? So this is sort of a typical parameter sniffing scenario where something about the query plans that it generates for one set of parameters and another just don’t get along so well. So what I would recommend you looking at the two different query plans, figuring out what’s different about them, figuring out what you can fix in order to make that plan as shareable, as simpatico as possible across as many different parameter variations as you can think of, and then going from there.

So as usual, Microsoft wanted to do something like AI, ML, really, you know, a self-tuning database that we’ve been reading about since 2000. Nonsense.

Didn’t, doesn’t quite deliver. But we got kind of a cool new DMV that can help us find queries that could use our help. You, it’s, we still need to do the helping. And I’m sure that there actually might even be cases where forcing a query plan would actually do you some good.

It might happen. It just didn’t happen for me here. So, uh, it does work. And I hope that, uh, Brent can sleep well now.

I hope that we can all just move on from this, this, this tragic, tragic chain of events in the SQL Server community. I hope that we can begin to heal and process the devastation that has befallen us. Brent couldn’t figure it out.

Well, anyway. Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you’ll watch other videos of mine. Because who knows what else I’ll figure out.

Alright. Goodbye. Goodbye. Bye.

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 Pains With NOT IN And NULLable Columns In SQL Server

Posted on November 29, 2024November 29, 2024 by Erik Darling

Performance Pains With NOT IN And NULLable Columns In SQL Server

Hey, I’ve got a coupon that offers an even steeper discount on my training than the one at the bottom of the post.

It brings the total cost on 24 hours of SQL Server performance tuning content down to just about $100 USD even.

Good luck finding that price on those other Black Friday sales.

It’s good until I wake up and remember to turn it off, so hurry along now.

Thanks for reading!

Going Further

Happy Thanksgiving From Darling Data (And Something For You To Be Thankful For)

Posted on November 28, 2024November 13, 2024 by Erik Darling

Calenderish

It’s always tricky to time these holiday posts. Time zones and traditions make these things difficult.

Being of the American persuasion and tradition, I tend to be that-centric. If I could invite all of you over for a holiday celebration, I would.

But since that just wouldn’t be practical (and we are practical practitioners of these here database thingies), the best I can offer is my sincere hope that you Have A Great Day.

Since I promised a little something for you here, I’ve got a coupon that offers an even steeper discount on my training than the one at the bottom of the post.

It brings the total cost on 24 hours of SQL Server performance tuning content down to just about $100 USD even. Good luck finding that price on those other Black Friday sales.

It’s good until I wake up and remember to turn it off, so hurry along now.

Thanks for reading!

Going Further

When Profiling SQL Server Stored Procedures Goes Wrong

Posted on November 27, 2024January 22, 2026 by Erik Darling

When Profiling SQL Server Stored Procedures Goes Wrong

Thanks for reading!

Video Summary

In this video, I delve into the intricacies of using extended events to profile stored procedures, specifically focusing on a common issue where multiple quick queries add up to significant overall duration. I introduce SP_HumanEvents, a tool I developed to simplify working with human events in SQL Server, and demonstrate how it can be used to identify performance bottlenecks in scenarios where individual queries are fast but collectively take longer than expected. By sharing this practical example, I aim to provide insights for query tuners faced with complex stored procedures that require fine-grained optimization at the micro-level.

Full Transcript

Erik Darling here. Does any of this surprise you at this point? Darling Data, alive and well, thriving, writhing, grinding away. In today’s video, we’re going to talk about extended events profiling ics. There’s actually really only one ics, but it’s happened to me so many times I’m going to pluralize it. So, I’ve got a stored procedure. You may have heard of it. You may have used it. You may have it installed on your server, but it sits there collecting dust while you don’t update anything, called SP underscore human events, events, events, events. And of course, the point of it is to make working with human events a lot easier than Microsoft has made it, because as usual, Erik Darling cares about you where Microsoft does not. Erik Darling wants you to succeed. Erik Darling wants you to figure out your problems. Erik Darling wants you to succeed. Erik Darling wants you to live a long, happy life. Microsoft cares not, except for anything other than maximizing shareholder happiness. So, we’re going to look at that today. Not shareholder happiness, but that thing up there. Anyway, before we do that, let’s talk about my happiness. If you really like this channel content and you find me worth $4 a month, there’s a link in the video description where it says become a member, which is sort of a strange way of putting it, but I guess that’s what you become when you get a membership. You become a member. You can do that for $4 a month. If $4 a month is just far beyond your financial grasp, I get it. Not everyone can make these lucrative YouTube videos for money.

I am up to nearly 20 members at this point. So, if you do the math on that per month, I can almost pay one month of a cable bill. Almost. You can, liking, commenting, subscribing, all free. I’ll open things that you can do to make me a happy man. If you need help with your SQL Server and you are looking around the internet saying, gosh, all these consultants look stupid and goofy and they say, they write schlocky things about leadership or whatever.

I’m very good at all these things. I’m much better than the rest of them. So, if you need help with that at a reasonable rate, you know how to reach me. I’m very reachable. Reach anywhere and find me. Up, down, around. Pretty much any preposition or proposition. Anyway, if you want some SQL Server training of the low cost, high quality variety, if you go to that link up there and you put in that coupon code, you can get all of mine for the rest of your life for about 150 US dollars. It’s a pretty good deal. It’ll make you happy.

It really, really tickles the dopamine. If you want to catch me live and in person, I will be alongside Kendra Little for two days of performance, tuning, wonderfulness, this November 4th and 5th in Seattle, Washington.

If there’s an event near you that you think Erik Darling would make a good component of, a good member of, you can let me know what that event is and I can talk to the organizers and maybe get a pre-con there so that, you know, I can pay my hotel bill or something, which is always nice when that happens.

So, anyway, with that out of the way, let’s talk about extended event ics, or rather an ick that I have with extended events. Now, I’ve got a store procedure here called eventually. And the whole point of this store procedure is that there is a one second delay and then a very fast query and then a one second delay and a very fast query.

And what I’m going to do is I’m going to use spHumanEvents. Well, I’ve already used it. We’ve already got this thing over here, which is we’re watching live data. If you know, but I’ve set up, I’ve run spHumanEvents with this set of parameters to set up this session to look at query performance data.

And then I came over here and I right clicked on this and I hit watch live data. Now, when I do that and I click run here, this is going to run for about six seconds because there are about one, two, three, four, five, six, wait for delays.

Now, the way that I set up spHumanEvents to run is I put a query duration filter on here of five seconds, which means anything that runs over five seconds, we’re going to capture information about. Usually a pretty good starting place when you’re profiling an entire store procedure because, you know, something that runs over five seconds, you can usually do something about that.

If you have stuff that runs for way longer than five seconds, like if the whole thing runs for an hour and like there’s three queries, you might want to set that a little higher so that you figure out, you know, we don’t know, probably maybe one of the queries takes a second and the other one takes like three seconds and the other one takes like many minutes.

Well, you don’t really need to see query plans and stuff for the other ones. That’s kind of boring. But sometimes in unprofiling store procedures, there’s a lot of stuff going on. There’s lots of tiny little queries.

There might even be dynamic SQL. There might even be other store procedures getting executed. There might be all sorts of stuff that happens. I can’t possibly predict all of the insane things you people will put into your store procedures. So I usually start off by doing something like this so I can just capture the sort of higher value stuff and figure out what of that stuff I need to tune.

The thing is, all of the queries in here ran pretty quick, right? So we had the wait for delays that bumped up the total duration, the wall clock time, but nothing in here took a whole lot of CPU time or took a lot of wall clock time individually.

It happened as a group. Now, so when we come over here and we look at the output in extended events, we’re only going to see a couple lines.

We’re going to see module end and statement completed. So we see where the store procedure finished. That’s module end. And we see SQL statement completed. That was the query that I ran in SSMS saying we’re all done, right?

And they each had a duration of just about six seconds of wall clock time. There’s a little bit of difference in microseconds there, I guess, for whatever reason. Not really terribly important.

Not something we have to worry about. Maybe it was just probably just the difference between like the store procedure ending and SQL Server being signaling back to SSMS, hey, this is done. This can happen when you have code where sort of like what I was talking about before, you have a whole bunch of queries that run very quickly individually, but they add up to a lot of time in the aggregate, a high duration in the aggregate.

You might have loops. You might have, you know, cursors. You might do all sorts of weird stuff in a store procedure that makes the store procedure run for a long time.

But you don’t have an actual single individual query that you can go in tune very easily. It’s not a very easy situation when you’re dealing with that because now you have to figure out, you know, like at a very, very small scale, very small improvements you can make so that, you know, each individual step finishes faster than it did before so that in the aggregate, things are faster.

You know, like with a big query that takes a long time to run, you might be able to make, you know, let’s just say it takes 30 seconds to run, you might be able to make a whole bunch of changes to that one query to bring that one query down to like, I don’t know, two, three seconds or something.

But when you have a query that runs, say, you know, a million times and you need to get that query to produce results in the aggregate faster, you’re looking at really micromanaging a lot of different individual things.

That’s when you have to start getting query plans for much smaller bits of SQL and say, taking something that takes 300 milliseconds and getting that down to even fewer milliseconds, like three or two or five milliseconds or something crazy.

And going from 300 down to that smaller number is where you start, you know, seeing the bigger results again in the aggregate. That’s a little bit beyond what I can do in this short video, but it is something that it is an eventuality that a lot of query tuners need to prepare for where, you know, you can even compare this to like, if you have a big, gigantic, massive query, and let’s say it finishes in 500 milliseconds, but there are 500 operators in there and they all take very few milliseconds.

You know, sometimes a query like that is just the sum of its parts, right? Like you, like the only way for you to make something faster is either to take parts out of that or really start breaking down where like any amount of time is spent and trying to improve that.

Not always the easiest thing to do for, especially for big queries like that, you might even have significant compile time on the query itself. But anyway, just something that you should be aware of when you’re profiling. I mean, if you’re not using SP human events, you’re, you’re, you’re screwing up, but something to be aware of when you’re profiling stuff is that you might have a store procedure that takes five or six seconds to run, but none of the individual parts really contribute heavily to that.

So that’s when you would have to start taking the query duration filter and putting this down to a much smaller number, like, you know, like either one second or 500 milliseconds or something else in order to find query tuning opportunities. So that for each individual execution of that thing, you can speed that individual execution up and bring down the entire wall clock time of the whole shebang. So anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope that you will continue to view this channel, comment, subscribe, like, maybe even become a member. Anyway, that’s good for me here.

Thank you for watching.

Going Further

Tricky Scalar UDF Rewrites In SQL Server

Posted on November 26, 2024January 22, 2026 by Erik Darling

Tricky Scalar UDF Rewrites In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the intricacies of rewriting scalar UDFs in SQL Server, a task that can quickly turn from routine to headache-inducing. Specifically, I explore an interesting edge case where a scalar UDF that was guaranteed to return a result ended up as an inline table valued function (TVF), which altered its behavior unexpectedly. This change not only affected the results but also introduced new challenges in how the function needed to be called and handled. I walk you through the process of rewriting this function, highlighting the pitfalls and solutions encountered along the way, such as dealing with null values and ensuring that the rewritten function returns the expected results. By sharing these insights, my goal is to help you navigate similar scenarios smoothly and avoid the frustration that comes with unexpected changes in SQL Server functions.

Full Transcript

Erik Darling here, looking, trying to look tough, trying to not look crooked, trying to hopefully work out that scoliosis with Darling Data. And in today’s video, because I feel like I’ve had to write a lot, write, I feel like I’ve had to record a lot of these videos lately because as I go back through my notes and things, I realize just how annoying rewriting scalar UDFs can get. Today we’re going to talk about a really interesting edge case where a scalar UDF that was always guaranteed to return a result ended up as an inline table valued function, which even though it would always return a result, would not always return exactly what the scalar UDF did. And if you want to be the person who looks heroic doing this sort of thing, you need to make sure that you return the correct or the expected results when these things run.

If you like my material and you have four extra dollars a month, you can subscribe to my channel with a membership using the link down in the video description. I’m trying not to, it’s probably over that, somewhere in there. If four dollars a month is too rich for your blood, if you’ve, you know, got better things to do with your money, then I understand.

Liking, commenting, subscribing, all perfectly wonderful free things you can do to keep me motivated to keep creating these videos. Because we’re hanging on for dear life here. If you need help with SQL Server, these are all things that I am better than everyone else at.

So if you need help with any of this stuff, you can hire me for a reasonable rate and get better help than you can get from anyone. Anywhere. On the planet.

Beer Gut Magazine said so. If you would like some high quality, low cost content for about 150 US dollars for the rest of your life, you can get that from me. By going to that link and using that coupon code.

And guess what? There’s a link for that too. Sorry. Link for that too. Down over that way, somewhere. Not in my area, in that area.

Of course, for as long as humanly possible, I will be in Seattle for Pass Data Summit. November 4th and 5th with Kendra Little tag teaming two days of performance tuning, titillation, and top notch shoe things. Anyway, let’s get on with the show.

So, let’s look at function rewrite annoyances. And what I’m going to do is show you the initial function or a close enough approximation of the function that I was dealing with. And, you know, just because I have a newer SQL Server version, and I don’t want scalar UDF inlining to kick in because that will break the demo.

I’m just declaring this thing in here so we have a date time. Right? So, it’s a scalar UDF.

What this thing does is it declares B, which is a bit, which is false, which if you ever run this, it will be zero. Excuse me. And what we do is we select B equals case when all this stuff, blah, blah, blah, blah, blah.

And then we return B. The thing is, if we select this here and no row gets returned, B doesn’t change from false to null. It stays false.

So, when we run this query, what we’re going to get back is a whole bunch of zeros from our scalar UDF. All right? So, you look at this query here. Thing zero equals the scalar UDF that I just showed you.

Okay? Simple enough. This is also a simple enough UDF to rewrite. Or so it seems. Bah, bah, bah, bah. It’s terrifying.

Now, if we run this, which rewrite, or rather writes a new function. It doesn’t rewrite that function. This is the rewrite, not rewrite.

See? It’s different. This will return a table, which is the type of function that does not cause performance to throw up. And if you look at what this does, it is nearly the same thing, but without declaring a variable or anything else like that.

So, this is where things change, right? Because now we’re not setting, we don’t have a variable called B. We can’t declare a variable called B in an inline table valued function.

So, now we’re just setting B to this. And if B doesn’t turn out, B is null in here. Then, B is null in the results.

All right? So, I think I already did this, but, you know, just to make sure. We do this. And then, since this is an inline table valued function, we have to call the results a little bit differently. All right?

So, this is, we’re going to select the B column from our rewrite function. And when we do this, the results will be null. And this is not what users expect. And this is what will make users freak out. Now, I know what you’re thinking.

You could just put an is null on that. And then, if it returns null, it’ll get replaced with zero. But you’d be wrong.

That does not work out. Is is null broken? Hmm. No. No.

We just have to put wrap is null one step further out. But this is, don’t worry. This isn’t the final fixed result. This is just me showing you how annoying this can get when you’re trying to figure out what’s wrong. You could put an is null around the entire function column.

And this would finally get you a zero where you expected a zero. Right? So, just wrapping the return column from the function in is null doesn’t get you anything.

This does. But that’s not good. Right? That’s not, that’s not what we’re after. We don’t want people to have to remember to wrap an entire.

They already have to remember that this isn’t a scalar UDF. And they have to call it in like the select list like this if they want it to get used. Painful enough.

Right? Microsoft couldn’t budge on that. Right? It couldn’t, couldn’t do any better. This is what we get. Thanks. Real pal. Can’t just rewrite a function and have it get called the same way.

You got to do all this crap. So, anyway. Annoying. What I found the easiest way to get this to work correctly is, is to wrap this inside the function. We have our normal function call.

And then we have a union all to just selecting B equals zero. Right? So, this is sort of the equivalent of setting that local variable in the scalar UDF to zero. And then we get the max out here from that internal, that B column in our derived select.

Right? So, if the max is zero from the union all. Because zero is going to be bigger.

The max, zero is the max of null. Right? You have a zero and a null. Zero is the max. If we have a one because it returns a true. Or if we actually get a false back. Then we’ll get a zero back here.

But if we use this version of the function, we can just call this normally. And we will get back our expected zeros in this column. So, when you’re rewriting functions, you know, apart from the fact that, you know, making sure that performance is better.

Right? With an inline table valued function versus scalar UDF. Or even a multi-statement table valued function.

That’s really important. Right? Performance needs to get better. But you also need to make sure that the results you return match what users were getting back before. So that they don’t have this, like, new surprise result back.

Because you might have QA or unit testers or unit testing or, I don’t know, people who care about this sort of thing. And they might look at your results and say, that’s all null now. It’s not zero.

And it used to be zero or one. Now it’s null. Now it messes up this other thing. Like, maybe they were inserting this into a table. And that table doesn’t allow nulls in this column. What’s going on?

Why is it broken now? And they’re going to look at you and they’re going to think you’re an idiot. But since you’re smart, you stick with darling data, that won’t happen to you. Because now you know how to rewrite scalar UDFs into inline table valued functions and not break everything.

So good for you. It’s amazing. Isn’t it? Aren’t you glad you spent this time with me?

Aren’t you glad you invested this time in your SQL Server learning journey? I sure am. Anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I hope to see you again in the next video, which I will be recording, I don’t know, any minute now. I suppose we’ll find out, won’t we? Maybe God will finally strike me dead.

I never can tell what’s going to happen while I’m uploading these things. Anyway. All right. Let’s end on a cheery note. I think you’re pretty. 3, 2, 1.

3, 1.

Going Further

An Advanced SQL Server Query Profiling Technique

Posted on November 25, 2024January 22, 2026 by Erik Darling

An Advanced SQL Server Query Profiling Technique

Thanks for watching!

Video Summary

In this video, I delve into advanced query profiling techniques using SQL Server’s built-in dynamic management views and actual execution plans to gain insights into query performance in real-time. By examining two different versions of a query—one serial and one parallel—I demonstrate how to use these tools to identify potential issues such as skewed parallelism and inefficient resource usage. This approach allows you to pinpoint where queries might be getting stuck or experiencing delays, providing a deeper understanding of query execution that can lead to more effective troubleshooting and optimization strategies.

Full Transcript

Erik Darling here. Yeah, we are going to do a Darling Data today, buddy, you and me. So in today’s video, we’re going to talk about advanced query profiling. Now, I realize that this might bring up painful memories for people of a profiler or trying to get words, extended events to work, or maybe even just running SP Who is active. All very valid ways to profile queries. But this one’s a little bit different. So with newer versions of SQL Server, have these built in views or dynamic management thingabobs that actually like when you look at query like actual execution plans. Now, you see all the per operator stuff like times and threads and all this other cool stuff. And there are dynamic management views in the background that back that stuff up. So you can actually look at a query while it’s running. This is part of why in my other video about what to do if a query executes for too long to get a query plan. Part of what I said was, if you like click to turn on actual execution plans, and you run SP Who is active with get plans equals one, you can actually see like the in flight actual execution plan is like rows and time accumulating that you can start to figure out where things get stuck. We’re going to use some of the dynamic management views that fuel that stuff to look at a couple different queries while they execute. So with that, out of the way, let’s talk about money, like four bucks a month worth, if you want to become a member, there’s a link in the video description that says join. And if you do that, you can give me $4 a month for producing all of the SQL Server content. You can also give me more if you’re feeling like Daddy Warbucks over there. If money is no object, well, you can always like comment or subscribe. And then money definitely won’t be an object. If you need consulting help with SQL Server, I am very good at all of these things. And my rates are very reasonable. You can hire me. If you would like some training on SQL Server, I offer a lot of it for a very low price, about $150 USD with the 75% off coupon code, you could spend your money on far worse things like like other people’s Black Friday sales. If you would like to see me live and in person, I will be well, you can see like all of me and some of Kendra Little to or half me half Kendra, we will be at past data summit in Seattle for two days doing pre cons about TQL Server performance tuning, November 4th and 5th. I think I already said in Seattle, but there you go. Anyway, let’s talk about this advanced query profiling stuff. Woohoo, it’s fun, right? That’s what everyone everyone always wished for. So I’ve got a couple of queries here. And let’s just make sure we are everything free in the of the database. I recorded a video where I had to do some other stuff. So you know, things might get weird. But anyway, let’s get an estimated plan for this. And this is a demo query that I’ve shown in other videos. And this video, or rather this, this query in this video is going to run just about as crappily as it did in the other video. But we’re going to look at two different versions of it. One of them is a serial version. And the other version is a parallel version or a non serial version. So we have a non parallel and a non serial version. Or a parallel and a serial version. However you want to however you want to phrase that however you feel comfortable with that.

I’m cool with that. But this is the way the the parallel plan the non serial plan looks. So what I’m going to do is talk about this query a little bit. So this query hits some different DMVs, like sys.dm os tasks and workers and worker threads. And then the one that that shows us the sort of in flight query progress stuff is this sys.dm exec query profiles thing. This is the one where we’re going to see the different operators and like progress and stuff. And then we’re going to look at sys.dm os waiting tasks. And we are also going to look at sys.dm exec session wait stats to get the top weights for the query while it runs. This is going to be if there were any weights going on here, this is going to be if the top weights for the session. So if I run this, there’s nothing going on. And if I come back up, I should have highlighted this query before I did anything else. And we start running this. This will start returning results. Now since this is a serial plan, we only get one result back for every operator. Remember, if we think about the plan that we just saw, all these operators were the ones that we saw in the plan.

What we’re going to see is this this thing is sort of finished the rows on this don’t change. But the rows on this this clustered index scan, those are those keep going up. And these are going to go up to about 17 million. And so right now things actually look a little weird in this column because I’m reusing this window. So maybe this this demo, you know, could you could use a little bit of a fresh window start in here. But that’s really the sort of unimportant thing. Just being able to get this stuff while a query is running is pretty awesome. So that query finished and we got to see the clustered index scan make progress here. So now let’s take a look at this query while it runs because this is where the cool CPU query thing gets very interesting. So now we have a whole bunch of we have a whole bunch of results for each query operator. If we scroll over here and look, we’re going to have this parallel resource description thing. And we’re going to have a whole bunch of things for each operator, right? We have a whole bunch of entries for each operator because we have parallel threads working on them.

Now, I’m going to scroll down here and look a little bit because it’s going to what I want to figure out is when this thing gets close to the end, which is about 17 million. So I’m going to give this one more run. And that should get us close to 16 million, right? So that query is about to finish. But what I want to show you here is where something like this can be useful. Now, I’ve talked before about parallel thread skew and how when you build an eager index spool, even in a parallel plan, only one CPU thread gets used to build that spool. That sort of situation with skewed parallelism can happen just about anywhere.

Now, if we look up, I should probably frame this a little bit better. If we look over here, we’re going to have this clustered index scan. This clustered index scan where we have, we can see the different durations and stuff. But look at the row count here, right? If we come over and we look at this query plan, we will see from SQL Server that we spent 59 milliseconds scanning the clustered index of the user’s table, right?

And that happened pretty evenly, right? About 300,000 or so rows ended up across all of the threads. If you look at the thread ID and the node ID, right? So for node 4, that’s that clustered index scan. We had our parallel threads, 0, 1, 2, 3, 4, 5, well, 6, 7, 8, sort of a little bit backwards in there.

But that’s okay. But we had all eight threads accounted for with rows on them. Now we’re going to scroll down to the other clustered index scan. And what we’re going to look at in here is the same thing, right?

If we scroll, like this is all the threads for that clustered index scan. But look what happened in here. This is way different, right? Up here, for this clustered index scan, when we look at the thread and, oh, sorry, that was up there.

We look at the thread and row count stuff. These were all spread evenly. Down here, right, if we look at the thread ID and then the node ID for node ID 7, right, that’s the clustered index scan over here. And we look at this. All of the rows are ending up on a single thread.

And that’s exactly what I was talking about with the eager index pool being built single threaded. So, like, really one thread was responsible for all of that. Now, if we go over and look at the final, like, actual execution plan, we can verify that when we look at the actual number of rows.

Where are you? There you are. We found you. So all 17 million rows ended up on thread 6 right there. All right. And if we come back and we look at the DMV query, that’s thread 6 for node ID 7.

And that’s where all of the threads were. So sometimes when I’m really troubleshooting a very difficult query, you know, I’ve talked about other ways where I might, you know, get the in-flight execution plan and see where stuff goes. If I just want to do something, like, quick and dirty to kind of get a sense of where things are stuck, what we’re waiting on in different places, this is a very good way to do it.

Now, the weight types and stuff for this query can get a little weird because multiple operators will say they’re waiting on something when really we’re only waiting on doing one thing. For example, in this query, waiting on building the eager index pool, that’s the exec sync weight. In a parallel, like, in a parallel execution plan, building an eager index pool like this will result in that exec sync weight.

That’ll really pile up. We ended up with almost 40 seconds of it. 30, 30, oh, I should zoom in on that a little bit. Almost 38 seconds, well, 38 seconds minus 5 seconds for the clustered index scan.

But you can kind of start to get a sense of where things are stuck, what you’re waiting on. You can see if, you know, you’re having problems with lopsided parallelism. You can see if threads are doing more work than others.

You know, you can see kind of the spread of work in there. And it’s just sort of a different way of looking at a query that’s actively executing to figure out where things are stuck, where things are, you know, how long you’ve been waiting. You know, you have the full wait duration for all of this stuff in here.

And so you can just kind of start to get a good sense of, like, where a query might be stuck at various points in the plan. So I will, of course, put this script on GitHub because I don’t expect you to remember it or to, you know, follow me scrolling around in the video and copy word for word and everything because that can get dangerous. So I’ll put this one on GitHub.

But anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. And I hope you got a tiny little insight into just how awful it is to sometimes be troubleshooting SQL Server problems. So, hmm.

And apparently I have a BIOS update from Lenovo. Hopefully this fixes some of my Intel issues. We’ll find out, won’t we? Maybe this will be my last video because my laptop will brick after this.

I don’t know. Knock on wood. It doesn’t. All right. Cool. Thank you.

Going Further

The Broken fn_xe_file_target_read_file DMF In SQL Server

Posted on November 22, 2024January 22, 2026 by Erik Darling

The Broken fn_xe_file_target_read_file DMF In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into a frustrating issue with the `sys.fn_xe_file_target_read_file` dynamic management function in SQL Server, which has been broken since 2017. Despite Microsoft’s addition of an event date column to make filtering easier, something went terribly wrong due to uncoordinated development efforts. I demonstrate how this defect impacts query results and execution plans, showing you a workaround using explicit date conversion. Additionally, I share my experience with the `SP_Human_Events` stored procedure, which I’ve developed to handle similar issues efficiently. The video concludes with a thought-provoking question about the quality of production code in Microsoft’s software products, encouraging viewers to consider the thoroughness of testing and the potential for hidden bugs.

Full Transcript

Erik Goshdarnit Darling here with Darling Damnit Data. And in today’s video, I’m a little fuzzy. Let’s fix that fuzziness. Let’s take the fuzz off. I don’t need help looking any fuzzier, do I? Things are fuzzy wuzzy enough in this face. Today we’re going to talk about a rather disappointingly broken dynamic dynamic dynamic dynamic management function related to extended events. Now this is one of those things that for as long as, so this function used to be fine, but then in 2017 Microsoft added an event date column to it so that you could filter out date stuff from the DMF rather than going all the way into the XML to do it, which, sounded great. Except the summer interns were up to their old hijinks and apparently they did not talk to the Windows file system folks because something went terribly wrong. I’m going to show you what went terribly wrong and how you can fix it. Now this is now, you know, extended events have been the replacement for Profiler since 2012-ish, right? I mean, I know that they dropped in 2008 and that’s when they were supposed to launch it.

like really get awesome, but they just never did. Microsoft adds a lot of extended events every version of SQL Server, but the whole process of collecting and, you know, like analyzing extended events has always been awful. That’s why I’ve spent thousands of lines of code and hundreds of hours of my life writing stored procedures like SP human events to overcome some of the large gaps that I’ve found working with extended events generally. But before we do that, I’m going to blow your minds on this beautiful Friday and I’m going to tell you that you can support this channel for $4 a month by signing up for a membership at the link in the video description. If $4 a month is just too much for you, you would rather stuff that money in your mattress and hope that it doesn’t get moldy and rotten, get mouse eaten or rat eaten or something.

You can like and comment and subscribe. You can fill my heart with joy by your mere presence on the internet. If you have a SQL Server that you need consulting help with, that’s what I do for a living. Also shocking, I know, I am not just a YouTube celebrity. I also get my hands dirty and do actual work with SQL Server that other people either don’t want to do or don’t have time to do or can’t figure out what to do. So that’s me. And as always, my rates are reasonable.

If you would like some very high quality, very low cost training and you want to spend money before all those Black Friday sales kick in and people want to charge you way more money for SQL Server training content, you can get all of mine. About 24 hours worth for the rest of your God-given life for 75% off, which brings you to about $150 USD. It’s a hard deal to beat. Black Friday or no Black Friday.

If you are still on the fence about Past Data Summit and you’ve never seen one of my videos before, you can rest assured that I will be there November 4th and 5th co-hosting two days of wonderful performance tuning pre-cons with Kendra Little. It will be more fun than you’ve ever had in your life. Probably more fun than you thought could possibly be had with SQL Server.

So you could do that too. But with that out of the way, let’s have fun. All right. So this is a very quick Friday video.

Now, this is the DMF, the Dammit Management function that I’m talking about. Sys.fn.exe file target read file. Rolls right off the summer intern’s tongue, doesn’t it?

Now, this issue has already been logged here and I’ll put the link to this in the video description. But this is a known defect and Microsoft has just always been like, yeah, maybe we’ll do something about it eventually. I don’t know.

I’m on SQL Server 2022. It’s been broken since SQL Server 2017. Will they ever fix it? I don’t know. But we got ledger tables. We got dot feedback.

Priorities. Priorities. Maybe, maybe does no one uses extended event. Maybe that few people use extended events that they just don’t care.

That could be the case. But here’s the problem. If you were to query this DMF and you were to think that this timestamp UTC column would be something that you could very easily filter on with some date math, say, I want to find the last seven days of data, you would be horribly wrong. You would be mistaken.

Something in the file format of the system health extent, the XEL files. This is not limited to the system health extended event. This is all of them.

It uses like, I think it’s called like Windows epic time or something like that. And it’s just a weird number because it’s an epic, right? It’s just a big, long integer and it doesn’t get filtered correctly when you do this. Right?

It’ll get displayed correctly. It’ll get converted to a timestamp in UTC when you query it. But when you actually touch the file, things go awfully wrong. The only way to get around that is to write your query like this and actually convert that column to the correct date time to with a specificity of seven and compare that to whatever date math you want.

Now, I’ve already run these queries because, you know, I’m pretty sure this is going to be a Friday for you and I don’t want to, you know, plug up all your time running queries, even though they only take about 500 milliseconds. But here’s the first query, right? And if you look here, there is no filter predicate there.

If you look here, we’ll just read that there. If you look at this filter, it’s just saying where expression 1000 is not null. And if you look at the nested loops join, we don’t have anything there either.

It’s almost like that predicate on that column gets completely lost in the shuffle. But even worse is we don’t get any results back for the seven days that we wanted the results for. You might compare and contrast that with the query below where there is a convert on there.

And we do get a number of results back. In fact, in all, we get 566 rows back. That seems significant to me, a 566 row difference.

Actually, the difference between 566 and none. One might run that first query rather naively and assume that the SQL Server System Health Extended event just doesn’t have anything of value in it that we could use to troubleshoot our SQL Server. But that wouldn’t be true, would it?

That would be wrong. That would be incorrect. Now, the execution plan for the other one, you might notice if you’re quite eagle-eyed, has an additional filter in it right here. And this filter is where that predicate actually does get applied and applied correctly so that we can get data out of the System Health Extended event for the range of time we care about.

Now, my free store procedure, SP Health Parser, you can find it at my GitHub repo, does use very similar queries to this to pull data out of the System Health Extended event. And this was something that I struggled with a bit when I first started writing the query. And I was thinking to myself, well, you know what?

If someone is on SQL Server 2017 or better, we should not go digging into the XML to look for the timestamp of things. We should just use what’s in this dammit management function. And you know what?

This was a really annoying thing to deal with. Because, like, you’re running this query that should work and it doesn’t work. And, like, you start sanity checking yourself and then, like, you actually feel quite nuts. You feel quite preposterously insane.

And then you find, you start digging around the internet about this particular function. And you realize that it’s just broken and you have to fix your query to get around the brokenness of the function. Now, I’m going to ask you, I’m going to end this video on a question.

Is this production quality code? And I’m not talking about my query. I’m talking about the type of code that you need to do this sort of thing in order to fix. When people talk about worrying about, you know, the quality of Microsoft software products, SQL Server, the cumulative updates, well, no longer the service packs, but the cumulative updates that Microsoft puts out, some of the features that it inserts into new versions of SQL Server, one might start wondering just how thorough the testing is on these things.

And one might start to have real questions about just how high quality the code being implemented in your enterprise Ferrari database system is when you start running into stuff like this. Because who knows what else those summer interns worked on? And who knows where else stuff might be weird and broken?

So, I’m just going to leave you with that question. Ponder that for a moment. If you were reviewing code and you came across a bug like this, would you let that code go out into your production workload? It’s a good question to ask, isn’t it?

Isn’t it? Anyway, thank you for watching.

Going Further

Of RECOMPILE Hints And Query Store: Where Are My Parameter Values?!

Posted on November 21, 2024January 22, 2026 by Erik Darling

Of RECOMPILE Hints And Query Store: Where Are My Parameter Values?!

Thanks for watching!

Video Summary

In this video, I dive into the world of query tuning with a focus on recompile hints and Query Store. Erik Darling from Darling Data shares his insights based on extensive experience in performance tuning, particularly when dealing with complex queries involving temp tables or table variables. He discusses the frustrations that come with trying to tune such queries using only the data available in Query Store, highlighting issues like missing parameter values and the difficulty of reproducing slow query scenarios. To illustrate these points, Erik demonstrates how recompile hints can be used effectively, contrasting them with the limitations of traditional methods. By walking through a practical example, he shows viewers how to leverage recompile hints for better performance tuning outcomes.

Full Transcript

Erik Darling here with Darling Data. And we’re very happy. I promise. We’re thrilled. We’re thrilled beyond compare. In today’s Lent Apology Free video, we’re going to be talking about why PowerPoint, not Excel, why PowerPoint decides to double advance when I click on it. Just kidding. We’re going to talk about Recompile Hints and Query Store. So I do the majority of my performance tuning work looking at Query Store data. Most people who I work with don’t have a good third-party monitoring tool that captures the type of stuff that, like, I really get into, like, you know, performance metrics, queries, running, resource usage, yada, yada, yada, yada. It really gives me a good sense of which queries I need to go of which queries I need to go after to fix stuff. So most of the time I end of the time I end of the time in query store.

I’m dealing with a lot of sort of weird situations. Query store is wonderful. Query store is wonderful. But I think just sort of in general query tuning when certain things are going on in SQL Server can be quite frustrating.

Recompile Hints are probably the least frustrating. What can become very frustrating is when the query you need to tune uses a temp table or table variable. And the process of populating that temp table or table variable is not quite straightforward.

So, like, you’re working on code in a stored procedure and you would have to execute a whole bunch of other stored procedure stuff that’s not enough to get the, you know, temp table or table variable populated in order to execute the query that you care about that’s taking a long time. That’s the first part.

So, like, when you look in query store, you just get the query that was slow. Even if it’s in a stored procedure, you don’t get the whole, like, stored procedure plan the way you do in the plan cache. You just get that one single query.

And if there are table value parameters, table variables or temp tables in use, those don’t show up. Where it gets also frustrating is if someone is using local variables or if someone is using option optimized for unknown, the compile time parameters, I mean, the runtime parameters are never in there unless you play some weird tricks, but the compile time parameter values won’t be in there for local variables or recompile.

So that’s also a bummer. That also makes troubleshooting stuff really difficult because you have no idea how to re-execute the query to reproduce what’s slow in order to figure out what to fix. Bah!

What’s the other one that I really hate? Oh! So, if you pass in a parameter, but the parameter isn’t used in, like, a where clause, like, let’s say you have four parameters, three of them get used in the where clause, and one of them gets used in, like, a case expression in the select list, the value for the one that gets used in the select list case expression won’t be in with the rest of the parameter values because that had nothing to do with cardinality estimation.

So, three really frustrating things that you can run into when you’re trying to tune queries, and I really wish that there was, like, you know, like an instant replay button or, you know, something cached along with a plan that would make queries like that runnable so that you could figure out, like, you know, get an actual execution plan, look at the, you know, like the operator time statistics and get, like, some better clue about where in the plan you need to focus to fix things.

Sometimes you can look at a query plan and figure it out. Other times, you know, that cached slash estimated plan just lies to you in too many places and too many ways to make that useful. So, what recompile hints do is almost the opposite.

It can be, in very large execution plans, it can be frustrating, and I’m going to show you why. In smaller plans, it’s fairly easy to, you know, figure stuff out, but we’ll talk about that in a minute. Before we do, though, you might be surprised to hear, this might come as a shock to you, that you can support this channel by signing up for a membership, and that there’s a link in the video description to do that.

You can do that for as little as $4. That’s cuatro dólares. Dollares?

I’m not good at it. I’m not multilingual. I only speak like English is my second language. But if you’re unable to fork over the four bucks a month, you can keep me company in other ways that are also heartfelt, like liking and commenting and subscribing.

I think we’re at about 20 members, and we’re at about over 4,700 subscribers. So I’m feeling, feeling quite loved and adored these days. Maybe, maybe at 5,000 I will just burst with joy.

If you need SQL Server consulting help from a young, handsome fellow with reasonable rates, I am very good at all of these things. If you need me to do something else, we can discuss what that something else is and figure out if it’s anything that I am also really good at. Just, you know, make sure it involves SQL Server.

It’s the only thing that I ask of you. If you would like some high-quality, low-cost SQL Server training that beats the pants off the competition’s Black Friday rates, you can get all 24 hours of mine for the rest of your God-given life for about 75% off.

That brings it down to about 150 US bucks for you, special folks out there. If you are a live and in-person type of person, and you want to see me and Kendra Little co-present two days of performance tuning madness, you can do that this November 4th and 5th at the old Pass Data Summit in Seattle, Washington.

It’ll be fun. That’s all I have to say about that. It’ll be fun, and if you miss it, you’re going to regret it, because it may never happen again.

Just don’t know where the future will take us. But with all that out of the way, let’s do what we normally do and party hard and talk about recompile hints and query store. So we’re going to use Stack Overflow, and we’re going to get rid of some indexes that I had for something else.

And I’m going to create this store procedure that has basically the same query in it twice. Really, what these queries actually do is completely unimportant. All I need to do is show you the difference in how things look in query store.

So I’m going to give this a few runs just to make sure. Actually, we’ll see how long this takes. All right, one second.

We can give this a few runs just to make sure that everything ends up in query store where we want it, because without that, we are sunk. And now let’s use my free store procedure, SP Quickie Store, to find plans for this store procedure. You might be looking at this and thinking to yourself, wow, Eric, that’s amazing.

I can’t even find stuff by procedure name in the query store GUI. And you’d be right. That’s why I’ve spent, like, thousands of lines of code and hundreds of hours of my life working on this store procedure, to make life easier for everybody, because Microsoft won’t do it.

Isn’t it nice? So we’re going to run this, and we’re going to have two entries in here for our two queries. We’ll see two individual query IDs here.

And we’ll see, well, I mean, I suppose two different plan IDs, too. That all makes sense. And this first one is going to not have a recompile hint in it, and this second one is going to have a recompile hint on it.

We’re not, like, comparing how if recompile helped or hurt these queries, because we really just ran the query with the one parameter. It’s not like there’s going to be parameter sniffing or the thing ran in one second, so there’s not really anything to really fix.

The only thing that I want to show you is that in the plan without the recompile hint, if we go to the properties and we look over here, if we go to the properties of the select operator and we look over here, we will have the parameter list and the compile time value for the parameter here.

This makes it really easy to, you know, pull that query, pull the query text out of QuickieStore’s results, plop it in a new window, and then do something like, you know, create a temporary store procedure to execute it with that parameter value so that you don’t end up having to do anything weird, like, you know, declare a local variable which screws things up or whatever else.

Great. That’s how that happens there. Now, let’s look at, what did I just close? I don’t know what I just closed.

I might have closed the wrong thing. We’re going to have to go back and figure that out. Did I close? Yeah. Oh, no, it’s over there. Things just moved around strangely, I guess.

Anyway. Yay! Let’s look at the query plan for the query with the recompile hint in it. Now, if we go to the properties of this one, we are not going to see, oh, I actually hit the shift key.

There we go. We’re not going to see the same parameter list over here. Why? Well, option recompile does something different from, obviously, what option optimized for unknown or using local variables is, where you just have no record of what those values are in the query plan XML.

What option recompile does is it treats your parameters and local variables like literal values, and those literal values get used when you touch, well, hopefully when you touch tables and indexes.

But if we hover over this thing, we’ll see, oh, baby, easy, zoom it. We’ll see the literal value that we passed in to the store procedure here as a predicate scanning the clustered index.

Now, again, this isn’t a query tuning competition. This is just to show you how this behavior might look a little bit different. Now, where this can get annoying in really big plans is if you have a lot of different predicates or parameters that you pass in, and you have a lot of different tables that get used, and some of them have parameters and some of them don’t.

You have like 20 joins and like a where clause with like eight different predicates in it, and you hit eight different tables. It’s kind of a hassle to go to each table, figure out which literal value predicates got used there, and then reconstruct things that way.

I really wish that Microsoft would make this stuff easier, both for option recompile, option optimize for unknown, and all the other stuff. I realize that when you use option optimize for unknown, and you get this sort of density vector guesses, the message is that it shouldn’t matter what the compile time values are, because you get the same crappy guess no matter what.

But it really does help to figure out, you know, what the, like I feel like the compile time values should still be included in there, because if you’re troubleshooting a slow query, obviously the local variable guesses for the data distribution of what you searched for didn’t work out one bit, and you need to focus on reproducing that and figuring out how to fix it, like obviously aside from just nuking the optimize for unknown hint.

A great way to test that is to add an option recompile hint instead and see if you get a better plan. Anyway, before I drift too far along here, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope that I do not need to apologize for the length of this video to anybody out there and the entire internet, and I will see you in the next video. Goodbye, farewell, take care of yourselves.

Going Further

Why You Should Always Use Unicode For Dynamic SQL

Posted on November 20, 2024June 30, 2026 by Erik Darling

Why You Should Always Use Unicode For Dynamic SQL

Thanks for watching!

Video Summary

In this video, I delve into the importance of proper string typing when working with Dynamic SQL in SQL Server. I highlight common pitfalls, such as using `varchar` instead of `nvarchar` for Unicode characters, which can lead to data loss and incorrect results. By emphasizing the need to preserve Unicode-ness throughout your dynamic SQL strings, I aim to help you avoid these issues and ensure that your queries handle international characters correctly. Whether you’re crafting complex Dynamic SQL statements or designing robust database schemas, understanding how to properly manage string types is crucial for maintaining data integrity and preventing unexpected behavior.

Full Transcript

Erik Darling here with Darling Data. And I think the theme of this week’s videos is going to be length. Some people have taken to complaining about my length in the comments. And so rather than apologize for my length, as I apparently often have to do, I’m going to do some short ones this week so that everyone can be comfortable with my length. Now, this video is going to be about Dynamic SQL string typing. Since we’re going to continue with the theme of apologizing for lengths, using the right type and length and strings for Dynamic SQL is very important. You don’t want to apologize for having too short of a length when you’re crafting Dynamic SQL. So always be careful there. But the subject of this video is more along the lines of make sure that you know exactly what kind of data is going to end up in your Dynamic SQL strings.

Because if you have Unicode characters in there, they could disappear in a variety of different ways when you are drafting and crafting and concatenating all your Dynamic SQLs together. So, if you like this channel, you can join like 20 other people in getting a membership for like $4 a month. I know. Big money. Watch out. Erik Darling might get a new Adidas shirt soon. Lord knows I’ve been wearing this one for three years or something. If you are unable to fork over a Paltry $4 a month, you can do likes and comments and subscribes. Because, you know, you gotta have options, right?

If you need SQL Server help from a consulting perspective, these are all things that I am very good at. Just ask anyone. And you can, you’ll get an answer, I’m sure. If you need some low-cost, high-quality training, you can get about 24 hours of it for about $150 USD with that discount code at that URL. The link for all this stuff is down in the video description. So, you can hang out in there if you’re feeling clicky.

I will be live and in person at Past Data Summit, November 4th and 5th in Seattle, Washington. That week, I will most likely be, what do you call it, intoxicated? No, I’m gonna put up some weird, like, livestream-y videos from Past Data Summit. I might try to interview some smart people. I don’t know yet. I’m sort of getting that figured out because I am enjoying the YouTube video content thing.

I’m just not sure if I want to have, like, a bunch of pre-baked stuff going on during Past or if I want, you know, live, fun Past content. Who knows? Maybe I’ll give Steve Jones a wedgie. Anyway, let’s get on with… Oh, we faded to black. Ah, crazy. All right. So, let’s talk about Dynamic SQL. And this is all, you know, a lot of the videos that I record are based on experiences and interactions with clients of mine, the nice people who pay me to have, you know, the spare time in the day to record these videos.

So, we’re just gonna make sure there’s nothing going on. Even though indexes and database contexts have nothing to do with this demo, everything you see here could happen anywhere, even in Azure SQL DB, if you’re stupid enough to use Azure SQL DB. So, excuse me. Here’s some Dynamic SQL, right? And if we run this, what I want you to focus on is that we did not correctly type.

So, like, when you run Dynamic SQL, if you’re gonna use SP Execute SQL, or even if you’re gonna do exec, you probably should use Unicode strings because Unicode strings will do a better job of preserving Unicode data. But if you’re running, if you’re using SP Execute SQL, you have to have the input parameter typed as Unicode.

It will not accept a non-Unicode string or parameter or variable or anything when you’re, you know, executing it. And say, no, it has to be Unicode, dummy. So, what I see a lot of people do is, so that they don’t have to worry about n-prefixing every single string concatenation block, is they’ll declare a varchar max variable or parameter, and they’ll do all their Dynamic SQL concatenating into that thing.

And then, at the end of it, they’ll set their Unicode max string like this. Sorry, I circled the wrong one there. I squared the, rectangle the wrong one. They’ll set their Unicode string equal to the non-Unicode string, and then execute Dynamics SQL with that.

The problem is, like you might have seen because I hit execute on all that stuff, is that you lose the Unicode-ness of your stuff in there. So, these are some Japanese characters in this string, and you can see right off the bat that we just end up with a bunch of question marks for there.

And even converting the string back over to Unicode does not resuscitate them. So, when we select that string, we get a string of question marks. Not good. Not what we wanted there.

Now, pay a little bit more attention to this one, right? Because in this one, we’re actually doing almost everything right. Where we preserve the Unicode stuff here.

Oops. Oh no, it’s red. Ugh. Let’s do that again. Let’s fix that. Let’s make my pointer pink again. So, here we do have the Unicode characters, and here we do have the Unicode characters.

But when we actually go and select that string, this happened. And this happened because the string inside of the string was not correctly typed. This stuff can happen in all sorts of weird places.

So, this one down here is actually done correctly, right? So, if we look at this, and we run this, now we preserve our Unicode-ness all throughout, right? We get that good stuff there.

We returned everything that we should have. And, you know, just to sort of keep going on the same thing, it’s going to be fairly obvious since I’ve showed you all this stuff.

But, you know, making sure that all of your stuff is correctly typed is really important. Even if you do something like this, the fact that you do this is just VAR card does not, it doesn’t help that you end prefix this.

SQL Server is like, oh, that’s actually Unicode data. We have to change that. SQL doesn’t do anything to help you. It does absolutely nothing. You get question marks back from this. You really do need, if you’re going to be handling Unicode anything, to have everything be Unicode.

Now, this isn’t, you know, just dynamic SQL. Dynamic SQL is just an easy vehicle to show you what I mean. But, this is something that comes from a lot of different places, right? So, like, database design, you know, people used to really hem and haw and nitpick stuff like, oh, are we actually going to use Unicode?

Oh, will this table actually get that big? And, you know, they would make kind of dumb decisions and they would, you know, start the database off with non-Unicode strings and do things like make identity columns integers instead of big ints. And, I feel like that kind of stuff really comes back and bites a lot of application developers and a lot of, you know, architect type people because they made bad choices at the outset based on, you know, things that really shouldn’t have, you know, like application specifications that really should override like, oh, this is just the best practice.

So, you know, when I’m, you know, consulting with people and trying to help them with something that they’re making a first pass at, usually a couple of the things that I make sure we go over is that, or rather are that, you know, if you’re not sure, if you don’t have like a particular domain assigned to an integer column, like, you know, there’s only going to be like 16 or 20 like valid statuses for something, make that a big end because you don’t know how big that table is going to get.

You make an identity column or a sequence column or even if you’re generating IDs in the application to make sure that they are completely in order and monotonically increasing with no gaps, you got to be careful, right? But if your application is a runaway success and all of a sudden you hit 2 billion rows and go boom, what if, you know, you start expanding into foreign markets where people, you know, use Unicode characters for a lot of stuff, all of a sudden those name fields and those address fields and a lot of other things might go boink.

So, not just Dynamic SQL, but application design everywhere. I think, you know, if you’re going to do things safely for the long haul, for the long term, you should use Unicode as much as possible. There are just a few specific things about Unicode that for Dynamic SQL that you have to be real careful with.

Like, you know, you can’t execute SPExecute SQL, which is the only safe way to execute parameterized Dynamic SQL so you don’t get SQL injected. Everything has to be Unicode and you have to really mind all the concatenation stuff to make sure that all your strings are correctly prefixed with that uppercase N so that you don’t lose anything in the concatenation.

You’re probably not going to get like the whole string implicitly converted over to VARCAR because that would be, you know, kind of ludicrous. But, I don’t know. Some people find my pronunciation of VARCAR ludicrous. So, I don’t know. I don’t really know what to tell you there.

But anyway, yeah, Unicode. Be safe out there in the database world. Whether it’s Dynamic SQL or, you know, table design or anything like that, Unicode generally is the better choice to make sure that your application is safe and sound in the long run.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you will be out there with all your full Unicode self. So, thank you for watching.

Going Further

Advanced String Searching In SQL Server

Posted on November 19, 2024January 22, 2026 by Erik Darling

Advanced String Searching In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into advanced string splitting techniques in C-SQL and SQL Server, focusing on scenarios where strings are delimited by various characters. I start with a basic example of using the `SUBSTRING` function to extract text between two delimiters, explaining the nuances of its three arguments—especially how the third argument works differently from other substring functions. Then, I move on to more complex cases involving multiple delimiters and special characters like asterisks, exclamation points, question marks, and dollar signs. To tackle these scenarios, I employ `CROSS APPLY` twice to find the first and second occurrences of each delimiter, demonstrating a practical solution for extracting text between them. This approach helps in creating a minimal viable product (MVP) that can be easily adapted to various string patterns. If you’re curious about how to handle such intricate string manipulations or just enjoy solving complex SQL queries, this video is definitely worth your time.

Full Transcript

Erik Darling here. I hope no one heard that weird hand fart. With Darling Data, recently voted by BareGut Magazine to have the YouTube channel with the most accidental hand farts, which was really, I think the good folks at BareGut Magazine are psychic. How else could they know? Anyway, today’s video, we’re going to talk about advanced string splitting in C-SQL. SQL Server, or really just one aspect of it. I can’t possibly cover very advanced aspects of string splitting in SQL Server because that would be a long video. And I’ve gotten complaints about videos that crested the 20 minute mark. And boy, oh boy, the attention span on you kids. If you feel like you need to stim a bit and step away from the computer, YouTube was kind enough to provide you with a pause button somewhere over there. Oh, my fingers gone. Somewhere over in that corner. So you can always hit pause and come back after you’ve hand flapped and gargled and done your fidget spinner or whatever. But anyway, we’re going to talk about you and me and how you can buy me a fidget spinner. For the low cost of $4 a month, you can sign up for a channel membership, which will get you all of these videos.

If you don’t have the $4 a month, if you feel that I am unworthy of fidget spinning, you can like and comment and even subscribe to the channel and join 4,700 other dated darlings who get notified every time one of these gorgeous videos drops. If you need SQL Server consulting, I am of course available not 24-7, but sometimes seven days a week, usually during the day, but not before like 8 a.m. and definitely not after like 6 p.m. That’s when I do other stuff. I have a family and all that who also require my time, though the pay on that is significantly lower.

Anyway, if you would like to watch me do this whenever you feel like it, 24 hours a day, seven days a week, you can get all of my performance tuning content for life for about $150 with the discount code SPRINGCLEANING. What’s nice about that is that you don’t have to worry about anything after you get it. You know, there are no time commitments.

You can go off and stim and spin and whatever, flap your arms, whatever wackadoodle stuff you need to do, and then come back and watch more of it. If you think 20 minutes is a long time, 24 hours is even longer. If you would like to catch me live and in person, I guess this would be a total of 16 hours and not in 20-minute chunks.

You can catch me and Kendra Little November 4th and 5th at Past Data Summit in Seattle doing SQL Server stuff, performance tuning, getting wild all day long, between the hours of 8 p.m. and 6 p.m. most likely. If there is an event nearby you and you think, boy, this handsome visage that stands before me sure would be a great accidental hand fart addition to that lineup, let me know what that is. Who knows? Maybe I’ll show up.

And with that out of the way, let’s talk about this string splitting nonsense. Now, before we get into the advanced stuff, I need to show you the proper way to get, let’s just say, a string between two delimiters. In this case, we’re going to have the same delimiter twice, but in real life, actually in the example that we’re going to look at below, we will have a variety of delimiters in slightly different circumstances.

So, first, I need to show you the proper way to do this. If you just have two delimiters and you’re like, give me whatever’s between them. Now, it doesn’t have to be colons.

It could be any two delimiters, right? It might be a period and the next period. It might be a period and a comma or a space and then something. Whatever it is. There’s all sorts of uses for this sort of stuff.

So, we need the substring function. And we need to talk about the three arguments of the substring function. All right? The first argument, because this is something a lot of people mess up. The first argument of the substring function is the string that you want to sub for.

Ah. Minus one family-friendly point. All right? The second argument of the string split is the position that you want to start your substring at.

In this case, it is the position of the first semicolon in the string. Right? That one up there.

Plus the length of that character. This is something that’s pretty important. Like the length, you need to add that onto the position of that so that you don’t include that. If you want to include it, you can.

But in this case, we don’t want that colon to show up. We want the space between the colons. I’m losing more family-friendly points as the further this goes on. The third argument is not the end position.

A lot of people think, because in some places, substring does function differently. But the third argument in SQL Server is not the position of the end. It is the number of bytes after the first thing that you want to include in the string.

So that’s where things get more complicated. Because the first one, we just need to get the car index of the colon in the text plus the length of that. And the second one, we need to use advanced car indexing to get the position of the first occurrence in the string after.

Right? There’s a third argument for car index. And we’re going to start it at the position of the first colon in the string, of course, plus the length of the colon.

Damn. This is not going well. Then we have to do some other stuff.

We have to subtract the length of the delimiter and the length of the car index of the first occurrence. And that will give us the string between the two things. Now, I’m using the sys.messages table.

And I’m only looking for rows that have two colons occur in them. It’s medically improbable, but there it is. And this is just to make it a little bit easier.

Because if we didn’t have this, we would need all sorts of case expressions or other protections for the substring function to make sure that we don’t throw an error if we give an invalid length to the substring function. So that’s why that’s there.

But if we run this query, we’re going to get back the actual text of the message. And we’ll be able to verify in a few different places that this is correct. Right?

So let’s just take this one as an easy example. It’s from colon space percent d to colon. And that’s what we get right there in the parse string. There’s another good example a little bit further down that’s really easy to show in there.

I forget exactly where it is, but we’ll just look at this one. There’s two colons in that one. That one’s a little weird.

I don’t know. You get the point. It worked. Right? Actually, this is the… Actually, no. That one’s not so good. These ones are good. These ones are easy. So here we have is page percent d.

And that’s exactly what we get back in there. Some people would throw like a L trim, R trim, or a trim on this to get rid of spaces around that. I’m not that fancy.

So we’re just going to leave that in there. But anyway, the whole point is this all works. Right? This all works just fine. The situation I had to deal with was I needed to find the space between a variety of delimiters. And I needed to find the first occurrence of each one.

So what I’m going to do is show you a little bit about what the strings looked like for me. This is not, of course, exact. This is just sort of what…

This is enough to make a simple MVP. It’s minimal viable product or MVC, MCVE, complete example, minimal viable complete example or whatever they call it, where the strings were kind of weird. Some of them started with a number and then some of them started with a character of some sort or special character, not like a letter.

But they were all sort of set up like this where I needed to find the space between the first weird thing and the next weird thing. And I knew what all the weird things were. They were asterisks in this case, exclamation points, question marks, and dollar signs.

Right? So these are all the weird things that I had to find. And I had to find the first occurrence of whatever came first in the string and then the second occurrence of whatever came next in the string. And that required some serious brain time from me.

Now, just because I don’t remember what I actually did from this, I’m going to rerun all this. And I’m going to create these two tables and populate them. And then I’m going to show you that this table has one row for each instance of the special characters I had to find.

I didn’t specifically need this. It just made writing the query a little bit easier. So the substring is going to do exactly what we did up there.

It just looks a lot cleaner because all I have to do is put the columns in here and operate on the columns rather than have to generate all the expressions in the select and operate on all the expressions. Because that turns into a real confusing time with all the functions, sub-function stuff in there. T-SQL doesn’t make it easy to nest these things.

But what I did was I used cross-apply twice. The first cross-apply will go and find the top one. And it will get the top one from this query.

And what this query does is find the search position. So it finds the car index of the search string and the string that we care about. And then it looks for where search position is greater than zero because this actually helped me avoid a lot of errors.

And, you know, it was better this way. And then we order by the earliest search position. So search position ascending, so the earliest search position.

Then, in the second cross-apply, we’re actually going to reuse elements that project out of the first cross-apply. So note that this one is called x1, right? And if we come down here and we zoom in on this, in this one we’re going to do, this is like the second argument of substring.

We’re going to search from the search position of the search element that we care about and the string that we care about at the starting position of the x1 search position. Right? So, and then down here, rather than filter on greater than zero, we’re going to say where the position in this one is greater than the search position that we found for the first one.

Right? And then we’re going to order that by this. And now if we run all this, what we’ll get back is exactly what we should see.

Right? So just to highlight this string a little bit, the first weird character was a dollar sign. The second weird character was an asterisk.

So, and then the substring between those two was the number 23. And that holds up for these as well, where the first weird character was an asterisk. Sorry.

The first one, the first weird character was a dollar sign. The second one, the first weird character is an asterisk. That’s in the first position. The second one was an exclamation point. And the fourth position in the substring between asterisk and exclamation point was the number 12. So this works for all of these pretty well.

Granted, it might not be like the most explosively well-performing code in the world. If you have very, very large data sets, of course, indexing for this stuff does help a bit. But once you get into the realm of like searching through strings and stuff, a lot of performance stuff can happen that isn’t easily controlled by you or indexes or really anything else.

It’s all very fuzzy string stuff. As I’ve said before, strings were a mistake. Should not be in databases.

Everyone should have just learned binary and learned how to read binary representations of strings. I’m kidding. You shouldn’t actually ever have to do that. But anyway, I hope you enjoyed yourselves. I hope you learned something.

I hope that you find sort of weird query stuff like this as fun as I do to solve and write and figure out how to get it to work. I am not the sharpest knife in the drawer. So actually writing this code took me quite a bit of like, you know, head on keyboard moments.

But once it got there, boy, was I proud of me. I was like, I’m a big boy now. I don’t need a diaper anymore yet.

I mean, I might need one again. Who knows where the weekend will take me. But, you know, weird stuff happens between, in the delimited between Friday and Monday, weird stuff happens in there. Hopefully not with my colon, though.

Anyway, that’s probably enough there. Thank you for watching. Goodbye. Please don’t report this video.