Tag: how to create temp table in sql

How String Columns Can Mess Up Query Performance In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into how string data in your tables can significantly impact query performance, particularly focusing on memory grants. I explore why SQL Server estimates that half of a string column’s byte length is full, leading to potential issues when you select multiple text columns or have non-ideal indexes for sorting operations. By running through two example queries—one including the text column and one not—I demonstrate how this can cause substantial differences in execution time and memory usage, with spills to tempDB being a common issue. I also highlight more extreme cases where selecting large string data can lead to severe performance degradation, even causing SQL Server to run out of temporary disk space and fail queries.

Full Transcript

Erik Darling here with Darling Data. And, you know, I’m having a nice day. I returned from my family vacation. I say family trip because when you travel with kids, it’s not a vacation, it’s a trip. Just careful distinction there. There is not a lot of rest and relaxation. There is just playing referee in a different place. So, you know, here I am. And in this video, we’re going to talk about, well, basically how string data in your tables can mess up query performance. Now, there are a couple other ways that we’re going to discuss today. But today, this one, we’re going to focus on how string, columns can mess up query performance in the area of memory grants. Now, if you’ve watched other videos of mine, you may recall me talking about how when SQL Server estimates memory for a query, it starts with a serial execution plan. And it figures out how much memory would take for a single thread to execute your execution plan, right? Whatever the executable plan for the query is.

The things that primarily impact memory grants are sorts and hashes. Okay? It’s because of the way SQL Server estimates string column fullness, where whatever the byte length of your string column is, SQL Server assumes that about half of it will be full. So, for a VARCAR 100, SQL Server assumes that every row will have 50 bytes full. The more rows you select and the more text columns you select, or the more string columns, I’m going to say text because text is an actual data type. The more string columns you select, depending on their unicode-ness and other things like that, you can really blow up memory grants.

Now, I have a pretty non-ideal index for the query I’m going to run, but that’s because I want to show you what happens when we need to sort data, right? So, I have two different, well, I mean, basically, I have the same query twice. The only difference is up here in the select list. I am including the text column from the comments table, and the text column isn’t in VARCAR 700. And the second query, even though I’m doing a select C dot star here, since I’m not pulling the text column out in the final select, SQL Server is free to ignore it. So, and I have recompile hints on these queries, just because I don’t want any sort of automation or, you know, behind the scenes, you know, intelligent query stuff messing with my demos, right? I just want to show you, like, out of the box, you run this thing, what stinks about it, right?

So, the first query runs, and we select the text column, and the second query runs, we don’t select the text column, and let’s look at some differences between these two execution plans. Well, right off the bat, you might notice that this query runs for about twice as long as this query, right? Let’s just call that close enough to twice as long, right? 4.7 and 9.1 something, good enough. A couple hundred milliseconds off, who’s counting?

The databases, do we care about milliseconds? Yes, we do. We care about everything. And the primary source of the slowness is in the sort operator, where we spill here, right? And that takes about 8 seconds, and this takes about 3.8 seconds. And of course, it’s going to be because the spills are different, right? This one, oops, I didn’t go down far enough. Story of my life.

And this one, we have the warning. We spilled to level 2, and all 8 threads went kaboom. And that’s a big number of pages. If we put some commas in there, because SSMS, I don’t know, comma allergy. Let’s just put a little dot here. Oh, wait, that didn’t dot. That is close enough to a dot. And then 3 more, and that’s 411 million pages or something like that. I can’t even count that high. I’ve run out of fingers very early on. So that’s a pretty big spill.

And if we compare that to the spill down here, even though this is still spill level 2 and 8 spilled threads, that’s a lot fewer pages. And the reason we have more pages spilling is, of course, because we have that text column. And even though, well, let’s look before we say even though, right? Let’s make sure that we understand.

This one asks for a query of 4,296 KB. And this one asks for, well, the exact same memory grant. SQL Server asked for the exact same memory grant for both. And this one spilled a lot more, didn’t it? Interesting stuff there, right?

Even though we don’t have the text column in this one, right? Creation date, post ID score. And this one, we have our text column. Even though SQL Server knew that there was that text column in there, it asked for the same memory grant. That memory grant just didn’t do well for either of these.

Now, if we redo the index a little bit, and the point of redoing the index is to help out our windowing function on post ID and score. And just keep in mind, of course, that the order of the sorting for the windowing function makes a huge difference. Now, granted, I don’t, I mean, at least I’m pretty sure.

In all of the documentation I’ve read, you can’t partition by something descending. You can only partition by something ascending, probably. You can order by things descending.

So if we really do need the score descending here in order to make this index effective for the query that we’re running. And what sucks about both of them, too, is that if you don’t include all the columns that you’re selecting out of the table, you really, really knock down the chance of SQL Server choosing a narrow, nonclustered index to help with this stuff.

Because it just straight up doesn’t want to do lookups for row number a lot of the time, right? Just forget about it, right? Unless it’s a very, very small number of rows.

So looking at these two queries again, right? And this time, we’re going to completely get rid of the sort and the need for, well, the need to sort data to apply that dense rank windowing function in here. There we go.

With that in place, both of these queries take just about the same amount of time, right? We don’t have any big difference here because we don’t have that sort that asks for the same amount of memory in spills and all hell breaks loose, where one spill took twice as long as the other spill.

So we completely got rid of the portion of the sort and the query plan that used to live in this section, right? And now we just have the seek and the building of the windowing function stuff in there. So a lot of the times, you can fix problems with these sorts of things just by having the better indexing in place, right?

So not all that, like, you know, you can’t always just go tweaking indexes on the fly. But if you spot these things, then, you know, indexing can be one way to help out with the pains of string columns, especially when sorting data. The other thing that we want to look at is a more egregious example.

So I have two different queries here against the post table. And now the text column in the comments table is an envarkar 700, which is pretty small beans in the grand scheme of things, right? 700, you know, I guess 1,400 bytes, seven gizzard unicodes, and blah, blah, blah.

But the body table in the post table is an envarkar maximum. It’s a big one. Big boy.

Big boy. And if we run these two queries, we also might be able to spot some differences here. Some significant differences. So that first query finished.

And now we’re waiting on this second query to finish, aren’t we? We’re going to wait a little bit longer on this. Not going so well.

Second query. But that first query finished, I don’t know, it seemed like about eight, nine seconds maybe. I don’t know. This one’s really dragging on with that body column. I’m starting to think that strings are a mistake.

Especially, you know, maybe, you know, storing entire potential two-point-something gig strings in a database was maybe not an awesome idea. Maybe we shouldn’t be doing this. Maybe we should, you know, put them somewhere else.

Let’s put them in a text file on disk, right? And just have a pointer to the text file on disk. Or I don’t know.

Perhaps there is some other storage engine that stores that sort of large text more effectively. But holy cow, we have some differences here, don’t we? We have some big differences.

We have another very big spill difference. Let’s edge these two things a little bit closer together. There we go. We have one sort that took 5.5 seconds and another sort that took almost 49 seconds. Well, I mean, not technically 49 seconds.

Because we did some stuff over here. We did some stuff over here. And we’re going to ignore the time in the repartition streams because the timing code for parallel exchanges is absolute garbage. It just looks dumb and stupid and weird everywhere.

So just ignore that. Just forget that part for this example. Now, let’s compare memory grants. All right.

1214 megs for the one where we just select ID. And for the one where we also select the body column, we end up with a 17 gig memory grant. 17 gigs of our precious memory.

All dedicated to a sort. All right. Just one sort operator was like, hey, 17 gigs, pretty please. I could use that for sorting things.

You have an Envarcar Max. What do I know? Right? That’s pretty foul. And the differences in the spills are, of course, you know, spill level 1, 8 spilled threads, 40,000 pages. And here we have spill level 1, 8 spilled threads, but 3881976.

So 3.8 million pages. But this has had a significantly worse time because we have that body column involved. And we have to write that body column, this sort of stuff out to disk and reread it back in.

And gosh, what a pain that is. Now, at the far more extreme end of that, I’m going to show you two queries that ran over here. And if I lift up my left armpit and we focus in on the time down here, we have three minutes.

Let me move over. Three minutes and 30 seconds of execution time. Now, you may also notice that there is a little message down here that says query completed with errors.

What errors, pray tell? What happened? Hmm.

I don’t know. Let’s find out. Let’s go to the messages tab. And let’s see. Wow, that’s a lot of red text. I’m going to have to duck a little bit. But so basically what happened is that first query spilled so much to tempDB that I blew up tempDB.

I exploded it. I ran out of tempDB. I ran out of drive space on my VM.

I didn’t just run out of like, I didn’t just like fill up tempDB files because I have like an unreasonable max size on them. No, no, no. If you look at, I have a load disk space message on my VM.

And if we look at Windows Explorer and let’s look at, oh, let’s go to this PC where we see it. You know, I’ve got a 400 gig drive on this thing. And I have filled up tempDB nearly to the size of the drive.

Right. So I blew up a lot of tempDB space with this query. Now, of course, because of that, I don’t have an execution plan for this query. But I can show you, of course, that in the second query, if I quote out the body column, right, just I green text that body column.

Again, sometimes green text is very, very good in SQL Server, right? Sometimes we have wonderful uses of green text by getting rid of envarchar max columns from our select list. Well, go to the execution plan over here.

This query still ran for a minute and 14 seconds. All right. It’s a pretty long running query. So if we subtract the minute 14 from the 330 over here, that other query ran for like, you know, I guess a couple minutes and then failed.

Right. Filled up tempDB and failed. All because we were, you know, selecting that body column.

And that body column had to pass through a couple different sort operators. Well, actually, just one sort operator. The votes table down here. I mean, you know, I think there was another video that I recorded where I talked about a very, very offensive execution plan.

Where SQL Server chose a merge join with the votes table and chose to sort the entire votes table. All right. But it’s not this sort’s fault.

This just sucks in general. Right. This just makes all sorts of awful things happen. But the big problem is in the final sort. Because this final sort is where the body column from the post table would be involved.

The body column from the post table has nothing to do with sorting the votes table down here. It’s all this sort where, you know, even without the body column, we still have a spill. And we wrote, let’s see, 1904184.1.9 million pages out to disk.

And that’s just without the body. So, like, SQL Server just, you know, kind of chose a, I don’t know, just not good enough memory grant for this thing. Let’s see what it chose.

17 gigs again. Again, so, why 17 gigs? Well, on my server, which I believe, I forget what I have max server memory set to. I think it’s like 90 something gigs.

SQL Server is willing to give out 25% of your max server memory setting to any query, any single query running. So, 17 gigs is about the max memory grant that I can hand out to anyone. And 17 gigs is not big enough for some of these queries.

All right, just 17 gigs, we still spill out to disk. Whether that’s a function of, you know, the way memory is shared between operators and query plans, which, you know, it totally can be, or something else, just, you know, the amount of data we’re selecting.

You know, that’s a fairly large number of rows and a fairly big number of columns. I mean, from the post table, I mean, realistically, we do have a couple other text columns in here, like tags and title and last editor display name.

And if we quoted those out, you know, maybe we would reduce the spill. It might still spill. I don’t know.

It’s too lazy to check on that right now. Got other things to do. But, yeah, so when you run into these things, a lot of the times the worst spills that you will find are because you, either you did something that required SQL Server to sort data.

You have a presentation order by. You have top with an order by or offset fetch. Or you have a windowing function without an index to support the partition by order by part of the windowing function in order to get you this data in the order that you need it to, you know, apply whatever windowing you need to your data.

You know, those are things that will add, you know, sort problems in. Now, I think what’s really interesting with this one is that, and I sort of wish I had saved it, but it wouldn’t really have done you any good.

This query will still error out if I put a hash join hint on here. Because, again, the only part of this query that, you know, had like a dumb injected sort is when SQL Server chose a merge join here, right?

So SQL Server costed a merge join as being the more effective join type here, which requires sorted data. So SQL Server sorts the votes table, and so this does sort and spill, and this does require memory, but the problem sort is the one up here because we don’t have an index to support the windowing function that we are applying to the post table.

So this is the real big one because this is an absolutely phenomenal number of rows, and a lot of this stuff spills out. So even with just a couple text columns in there, we can still have some pretty significantly bad query performance.

So, in summation, as much as you can, as much as possible, you know, if you’re going to put strings in your database, avoid max data types wherever you can, right?

I understand some queries require them. So rather, some data structures require them. You might be storing XML or JSON or, like, you know, notes, like, you know, whatever. Some, like, open text entry field.

You know, there are reasons why you might need in VARCAR or VARCAR max columns in your data or in your tables, but, you know, just don’t use them unnecessarily. Please try to right-size your string columns as much as possible.

And when you’re doing things like if you really need sorted data for some reason, the two things that you can try to do are pre-sort your data and then fetch text columns later. I do have videos about that, informational versus relational columns.

It’s a very important concept. You know, like, you know, usually in order to provide a result set for someone, your joins, your where clause, your sorting can be done in a different stage of the query processing than retrieving all the columns you need to show someone.

So, like, not doing everything in one big swoop like this is, you know, usually a pretty good idea for query performance. You know, sometimes temp tables are a good use.

Sometimes you can use derived queries in some manner, whether it’s a CTE or, you know, it’s a, you know, sort of a derived table expression. There are different ways to separate those things out so that you don’t have to, you don’t have to select every single column and apply sorting to all of those columns.

You can do the initial set of data joining, filtering, sorting, and then go get the columns that you’re interested in displaying, right, the informational columns after you process the relational columns.

I have videos about that somewhere on my channel. I’ll go try to find them when I publish this. But no promises because, I don’t know, searching is hard, isn’t it? A little difficult.

So, anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something about how awful strings are in databases. I hope that you have now started a project to move all your strings to text files on the file system and just have pointers to the text files.

Kidding. I mean, if they’re PDFs or, like, Word docs, fine, like, file stream type stuff, fine. But, you know, if it’s just something for the table, I guess, I guess you could deal with that. Yeah.

Anyway, I have things to do, so I’m going to go do them. Again, well, I guess, I was going to think of one another. Oh, yeah. If you like this video, you can subscribe to my channel. And you can join the nearly 4,000 other data darlings.

Actually, maybe by the time this thing actually gets published, there will be 4,000 data darlings out there in the known universe. If you like this video, give it a thumbs up.

If you have anything to tell me about this video, you can comment on it. Good, bad, or ugly? Just don’t call me ugly. That’s personal.

Then we have to fight. But, yeah, you know, strings and databases are a very perilous thing. The number of problems that they can cause. So I’m going to be recording another video soon about other problems that max data types and string filtering can cause in queries, especially query performance issues.

We’re going to have one of those coming up soon. It’s maybe even in the text file right over here called don’t ignore filters. Might even be right in there.

Yeah, but, you know, strings and databases are a perilous thing. The bigger they are, the harder your queries fall. And just like we saw in this query where I was able to completely blow up 10DB with a sort, you know, it’s probably not why you want your queries failing.

You probably don’t want queries failing at all. Right? So, yeah, watch out for those string columns, George. Boom.

All right. Goodbye. Thank you.

Going Further

---

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

What Clients See

---

One of the chief things my clients complain about when opening support cases with Microsoft is that the people working support tickets on the Microsoft side… Aren’t actually Microsoft employees.

They’re third party vendors.

When they end up working with a third party vendor, the lack of expertise and poor communication is obvious and painful.

Many support cases are handled by third party vendors, and my clients often complain that their staff has totally inadequate SQL Server knowledge to do the job.

Even when my clients do get someone from Microsoft, the person they get is juggling an enormous caseload. The truly knowledgeable folks are worked to the bone and scattered across hundreds of cases.

The amount of turnover in support roles at Microsoft is also pretty astounding. I used to know a lot of people who worked in them, and… they’ve all moved on to other roles.

At this point, you’re lucky to get someone with 18 months of SQL Server experience handling your support case. That’s a far cry from when 18 months was about the minimum experience you’d find.

The worst part? I have clients who have paid Microsoft upwards of $75k USD for support contracts, and they can’t get anyone to answer basic questions or help with basic troubleshooting.

If you want to read a Great Post™️ about how bad things have gotten, head over here: How to Survive Opening A Microsoft Support Ticket for SQL Server or Azure SQL

You Deserve Better

---

But you won’t get it any time soon from Microsoft.

They’ve cratered their once excellent support infrastructure and replaced it with a frustrating, pass-the-buck, often indifferent group of third-party vendors, and overworked internal staff.

The decline in Microsoft SQL Server support quality can have serious consequences for your business. Inefficient support can lead to extended downtime, data loss, and decreased productivity.

Your business deserves better, and that’s where I come in.

With years of experience in SQL Server management and support, I understand the complexities and the importance of prompt, effective support.

Here’s what I offer:

1. Expert Knowledge: As a recognized SQL Server expert, I have a deep understanding of SQL Server and can provide the expert support that outsourced teams often lack.
2. Clear Communication: I prioritize clear, effective communication to ensure that I fully understand your issues and provide the best possible solutions.
3. Tailored Solutions: I take the time to understand your specific environment and needs, providing solutions that are tailored to your business.
4. Consistent Support: You’ll receive consistent, high-quality support that you can rely on to keep your systems running smoothly.
5. Help Dealing with Microsoft Support: You need someone on your side who can work through support cases, provide necessary technical details, and call support staff on their BS when they give you lousy reports.

Don’t let declining support quality affect your business. Hire me to handle your support tickets and ensure that your SQL Server environment receives the expert care it deserves.

With my help, you can focus on what you do best — running your business — while I take care of the rest.

Reach out today to discuss how I can support your SQL Server needs and provide the expertise and reliability that your business deserves.

Together, we can ensure that your technology works for you, not against you.

Thanks for reading!

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.

Spring Training

---

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Register for Data Saturday, on September 7th here!

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.

A Performance Mystery With Parameterized TOP In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into an intriguing and somewhat obscure performance issue in SQL Server related to parameterizing the `TOP` clause within stored procedures. You’ll see how using a literal value for `TOP` can lead to more efficient query plans compared to parameterizing it, especially when dealing with small numbers under 101. This behavior is due to exchange packet sending rules that allow earlier data transmission in certain scenarios. I explore this issue through various examples and demonstrate the impact on query performance, showing why this problem might go unnoticed until it causes significant delays. By the end of the video, you’ll understand how to mitigate this issue with better indexing strategies, using `OPTIMIZE FOR` hints, or by rewriting your queries for optimal performance.

Full Transcript

Erik Darling here with Darling Data, cleaning up a few silly little things in my way. And in this video, we’re going to talk about the dangers of parameterizing top in SQL Server. And this is maybe the weirdest query performance problem. that I have ever run across that wasn’t like a bug. It was actually sort of known behavior by like three people in the world. So that was fun. So what I have here is a store procedure. And this store procedure really only takes one parameter because that’s all it has to. And that one parameter is used here. where we select a top number of rows from the users table. And then we cross apply to the badges table to find the top one. Well, actually just mean to put this into English, to find the most recent badge that a user has gotten. And for this, the purpose of this query, we only care about users who have a reputation over, I think that’s 10,000. And we were going to order by reputation descending. All right, cool. So that’s the store procedure version. We also have the just the plainly written version with a literal value here for 38. And then we have the sniff top version here where we’re saying at top equals 38. The query plans for these, well, I had to run these ahead of time, because I don’t want to stand here for a minute and 13 seconds waiting for this query to finish. Much like we’re probably going to stand here for a minute and 13 seconds while I try to grab the right part of the query plan to mush things together. So looking at these two queries, and I I think, I mean, you know, probably the funniest part about all this is if like, if you saw this query, like in the plan cache or something, or in query store, monitoring tool or something like that, it would be really hard to decipher, maybe why one time it ran for seven seconds, and the other time it ran for a minute and 13 seconds. You know, cost costing being what it is and all that.

Nothing, nothing is very clear about this. And I know that there’s some green text up there. And the green text isn’t totally wrong. The green text is right. This, this, both of these would be faster if I had an index on the badges table. But I need to show you the behavior without the index, where things are really terrible and crappy, so that you understand what’s happening and what you need to fix. All right. So yes, an index would be helpful here. But that’s not that’s what I’m trying to explain the behavior to you. Not teach you another, not teach you another, this is not just, this is not another stupid video about adding an index. Any, any half-witted consultant in the world can make another post about just adding an index.

Me, I try to give you a little bit more, because you’re nice people and you deserve more than half-witted consultants just telling you to add indexes. So I need to move over a little bit to make sure all the green stays on the screen. Unofficially, when a top uses a constant, and the constant is a small number under 101, the exchange packets are allowed to send earlier than normal, as long as the exchange is below the top operator. The exchange packets are allowed to send as soon as they have a row, rather than waiting for those exchange packets to fill up completely.

This can only happen with constants or another thing that I’m going to show you in a minute. And this behavior is true going back to SQL Server 2005. I’m of course doing my testing on SQL Server 2016. Sorry, 2022 version 16, which you can see if you look right there under my armpit.

It may change in the future. So if you’re reading this at some far-off date and Microsoft has decided to fix this stupid issue, then I’m wrong all of a sudden. Well, you know, sorry. I was right for a long time, though. So I had that going for me.

When you parameterize top, it’s not considered safe to send exchange buffers early anymore, because you could stick any number in there up through the big int max, because top takes up to the big int max for a number of rows.

In cases where you’ve got a big top, say the big int max, which is this nine, this is that number right there that starts with a nine and has one, two, three, four, five, six commas in it. A lot of commas.

If you were sending that many rows one row at a time, it would be significantly more awful than sending over a smaller number of very full exchange buffers. If you’re surprised to hear that parallel exchange buffers can send at different times, depending on different situations, you’re not alone.

I was also very surprised. Not in a good way. Not in a, I get a golden ticket. It was more like, like when the doctor calls you on a weekend.

You’re like, what happened? So in the second query, where the exchange buffers are only sent when full, we spend a lot longer waiting for those exchange buffers to fill up so they can send.

This isn’t exposed anywhere in the query plan. You’re not going to be able to see this anywhere else in the world, unless you have a good, unless you’re handy with a debugger or you pay attention to things I say.

So obviously that’s not a good situation. And there are a couple ways to fix it. Of course, there actually, there are a few ways to fix it.

One way, you know, in my case, better indexing would mask a lot of the problem, but not completely solve the problem. One way of doing it is to add an optimize for hint so that your query, so that you tell SQL Server to optimize the query plan for top being equal to one.

And if you do that, this isn’t going to make the query faster than the original version with the literals. It’s going to make it on par with the original version with the literals.

That’s all. This, this thing is still going to take seven or eight seconds because I don’t have any good indexes, but this at least gets us a plan that doesn’t take like almost a minute and a half. Right.

And then another way of doing it is to rewrite the query to. And so, you know, like one thing that I try to stress whenever I’m teaching people about SQL Server stuff is there are a number of different ways to write a query.

And if you’re not getting very good performance with one of them, you should try another one. That’s query tuning. Top one is, you know, a sort of a shortcut, you know, top one with an order by.

You know, it’s sort of a shortcut for like getting a max or something or using row number. In this case, if we use a row number and we pump up the optimizer cardinality compatibility level, it’s not, not cardinality compatibility level to 150.

or 160. So that we get batch mode on rowstore. Oh, this, this formatting murder, murder on me.

Did I fix this one? I didn’t. Oh boy. SQL prompt is really letting me down. I’m going to mess everything up here. Another. So if we rewrite the query to get the top one using a slightly different method, and we allow SQL Server to use batch mode, this is, this will completely negate the need for an index because this finishes just about as quickly as one could hope for.

Sans and index, right? Cause this, this gets batch mode. You can tell by the window aggregate there. That’s a good thing. All sorts of good things happen in this query plan using batch mode that weren’t happening in the other plans.

So, uh, you don’t always need an index, do you? You don’t always need an index, do you?

You know what else you don’t always need? Half-witted consultants. You need full-witted consultants. Full of wits. Lots of wits. So, if you’re out there in the crazy borderlands of SQL Server, and you run into a query with a top that’s parameterized, and the execution is real weird, the execution time is real weird, well, you might be running into this.

And if you’re running into this, things you can do, one, um, explore, uh, better indexing scenarios so that, uh, you can at least mask the problem a little bit.

Uh, two, uh, stick an optimize for, uh, on the query, uh, optimize for the top being equal to one. That will at least help you understand if you’re hitting this problem.

Uh, that actually, that would really help you understand if you’re hitting the problem, and it might actually, uh, improve performance. And of course, number three, you can rewrite the query in a way that gets you, uh, you know, things differently, right?

Like one thing, like I said, I always try to teach people. There are many ways to write queries. Uh, if the first way isn’t fast enough, try the second way. The second way for me here was using row number rather than, uh, top one, because the row number, uh, gives you different sort of set of, uh, optimizer stuff that the top one thing, usually won’t get you, like the batch mode on rowstore for the row number thing there.

So, a few things you can try if you’re running into this problem. I hope you never run into this problem. It is a terrible, awful, no good, very bad problem.

Uh, uh, and when I ran into it, I almost cried. But, then I remembered, Robert Smith told me, boys don’t cry.

And so, well, didn’t, didn’t cry. Never, never had, never will. It was, I don’t, I don’t want to let Robert Smith down. He seems, he seems very sad.

if he, if he, if he knew I was out there crying, I don’t know, I don’t know that he could take it. I don’t know how long he would be for the world. So anyway, hope you enjoyed yourselves.

Hope you learned something. I, I actually, I truly hope that all of you have learned this from this video. Cause if you knew about this before, I don’t know.

I, I would, I would feel terrible. It’s, God, this sucks. Uh, if you like this video, which you might be, if you, if you like watching videos of car accidents and, um, like early, early road runner videos, uh, you, you can, you can give me a thumbs up.

Uh, you can, you can leave a, uh, motivationally, uh, I don’t know. Nice. Just a nice comment. Something, something sweet roses and stuff.

Uh, if you like this sort of SQL Server performance tuning content, uh, you can subscribe to the whole channel and you can get what can watch all the videos and you can get notifications every time I post a new one, which will be pretty frequently because I have nothing else to do except post videos for you.

Cause I love you and I miss you. And it’s been, it’s been too long. We haven’t hung out. Anyway, I’m going, I’m thinking I’m going to find something to do. It’s not standing in front of my computer for a little bit because I think the lights are beginning to affect me.

So thank you for watching. the industry is. Thank you.

Going Further

---

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

A T-SQL Query To Get The Text Between Two Delimiters In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of using the `SUBSTRING` function in SQL Server to extract text between two delimiters. Erik Darling from Darling Data shares his insights on a common pitfall: misunderstanding how the arguments work. He explains that while many assume `SUBSTRING` works with start and end positions, it actually requires specifying the length after the starting point. To illustrate this, he uses examples from the `sys.messages` view, highlighting the importance of being precise when defining these parameters to avoid errors like “invalid length passed.” The video is a must-watch for anyone who frequently deals with string manipulation in SQL Server, as it demystifies a function that, despite its simplicity, often trips up even experienced users.

Full Transcript

Erik Darling here with Darling Data. Of course, who else would have me at this point? I can’t imagine. I can’t imagine at all. In today’s video, we’re going to talk about how to get the text between two delimiters in a SQL Server query. The reason why we’re going to do that, and I know this may sound dull and pedestrian to many of you, the reason why I’m going to do this is because every month I get this email from Google, and it’s all about my website, search results, performance, and stuff like that. And every month, the blog post that I wrote about this is the number one post. All the stuff that I write and record about, all the things that I do, how to get the text between two delimiters is the big winner. Alright, fine. Okay, you get a YouTube video too. That’s your prize. For being the number one blog post on erikdarling.com, formerly ericdarlingdata.com, you get a YouTube video. Memorialized, canonized, itemized, you get a YouTube video.

So the first thing that everyone gets wrong about substring is what each of the arguments does. And this isn’t purely in the context of SQL Server. There may be other implementations of substring that do things differently. But in SQL Server, the substring function has three arguments, and they are the input expression, in other words, the thing that we are going to get a substring of, the start position of that string, and then the length of the string. It is not start and end, right? This third one does not meant to find the end position of a string.

This is where you tell substring how long the string is after the starting point. Alright? So the way this works, and I’m going to be using the sys.messages built-in view for this.

And I’m going to specifically be searching for entries in the text field that have at least two semicolons in them so that this works. If you don’t do that, or if you don’t have something, if you don’t, like, if you’re not really confident about the substring stuff of the character presence or position in the string, you’re going to write a lot of really defensive, like, code around this.

Otherwise, you’re just going to get a bunch of, like, you know, invalid length passed to whatever errors. And those suck, and I hate debugging them, and it’s miserable, and I don’t want to do it. So I try to be as defensive as I can when I write these things.

So anyway, this is sort of a basic way of doing stuff. The fully parsed string portion is down here. This is the only section…

Zoom it, you are on my last nerve. This is the only section that’s going to actually do what I’m trying to teach you how to do. There are a few other columns in here to enumerate what the actual first, second… Come on, zoom it.

…and third arguments are as they’re passed in. All right? So we’re going to run this. And we’re going to look at the results. And what we’re going to see is…

Well, I mean, exactly what I was telling you about here. The first argument is the text of the error messages from sys.messages. The second argument is, like I said, the starting point of the string.

And the third argument… Notice that this is not always a higher number than this, because all this is is the length of the string after this argument, right?

So for this one, we go from the 71st byte and then we go 6 bytes over. This one, we start at byte 59 and we go 441 bytes over. This one, we start at byte 31 and we go to a 13, right?

So again, first argument, the input string. Second argument, starting position. Third argument, length of the string.

That’s why when you’re doing this stuff, what you have to do to make sure that you get all that positioning right is a lot of jumping around.

And it’s really annoying and I hate it. And I’ve had to do this for my entire SQL Server career. And I almost never remember how to do it correctly the first time around. And if it’s one of those situations where we need to do like a reverse and find like the last thing in a string and then jump, forget it.

I have to look for like 15 different blog posts before I can get that one right. So for the parse string one, though, the thing that we’re actually learning how to do today, the first argument, pretty self-explanatory, just m.text, right?

That’s all we need there. The second one, we want the car index in the text column of colon plus the length of whatever you’re searching for. So if we were searching for something that were two characters here, if we were searching for like double colon, then the len would have to change to two, all right?

Or we’d have to change plus two or plus double colon here. Otherwise, we would have an inaccurate starting position. The third one for the length of the string, this is where things get annoying and complicated, right?

The first two things, fairly self-explanatory, right? The thing that we want to split, the thing that we want to substring rather, and then the car index of the first thing that we care about plus the length of that first thing that we care about so that we start on the thing, like right next to it, right?

We like colon right next to the colon. You get what I mean. The third one, this is the tough part.

So one of the nice things about car index as a function is that it, where that, so pad index does not have this, car index does have this, is car index supports a third argument optionally, where you can tell it the starting position that you want to start looking at.

So for this is going to look exactly like it did up above. We’re looking for the first colon in the string, but the third argument is going to tell our query to look for, is going to start looking after it finds this.

So basically, after it finds this thing plus the length of this thing, right? This line up here is basically just a copy and paste of this. We want to find the very next colon in text after we find a colon in the text plus the length of the colon right there, right?

So because remember, we got to bump up one over so we don’t capture like anything stupid. Then, and this is where, this is the part that always murders my brain. After that, we have to subtract, right?

We have a little subtracty thing here. That means subtract. The subtracty thing means subtract. We have to subtract the length of the thing that we’re looking for. And we also have to subtract the car index of the first position that we find, right?

So essentially, we have to subtract this from this so that we get that, we get the correct string length. Because remember, we’re not, substring in SQL Server is not expression start end, it’s expression start length.

So in order to figure out how many characters or how many bytes over we need to go from the thing we care about, we need to subtract the first position that we find and the length of the thing from the second position that we found, right?

Because that’s what gives us the text between delimiters and not just text from delimiter to the end of the string. I know.

This stuff isn’t fun. Most sensible people, if they have a job and they work for a place, they’ll probably write an inline table valued function to do this because remembering this is awful.

But it is the most popular post on my blog, a popular blog on my entire website, so it gets a YouTube video.

And that’s about it. So apparently a lot of people always forget how to do this. Myself included. Anyway, thank you for watching.

I hope you finally learned how to use substring in SQL Server. I hope you enjoyed yourselves. I hope you learned something, like maybe what substring actually does in SQL Server.

If you like this video, lots of thumbs, lots of comments. I’ll take anything.

Send me a letter. Just kidding. I don’t want, I can’t read, I can’t read. And if you like this sort of SQL Server content, maybe not exactly this SQL Server content.

Apparently a lot of people really like this. Maybe this is what I’m going to blow up on. If you like this sort of SQL Server content, you can subscribe to the channel, like 4,000 almost other people have.

I don’t know what I’m going to start saying when over 4,000 people have subscribed. I’m optimistic that I’ll survive, I’ll live to see that.

But anyway, thank you for watching. Happy, happy strings, happy substringing. Oh, yeah.

Okay, goodbye.

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.

How To Get Only Letters Or Numbers From A String For Free In SQL Server

---

Thanks for reading!

Video Summary

In this video, I delve into three underappreciated functions from my GitHub repository that can significantly enhance your SQL Server string manipulation tasks. Often, when working with clients, I encounter subpar versions of these functions—clunky and inefficient code that could be easily replaced by the well-optimized solutions I present here. The focus is on `get letters`, `get numbers`, and `strip characters`—functions designed to streamline common string operations like extracting specific character sets or removing unwanted characters from strings. These inline table-valued functions are efficient, avoiding the pitfalls of scalar UDFs and multi-statement table valued functions, making them a valuable addition to your SQL toolkit. Whether you’re dealing with phone numbers, usernames, or any other text data that requires cleaning, these functions can save you time and effort. So, give them a try and see how they can improve your workflow!

Full Transcript

Erik Darling here. Still alive. Amazingly. Still talking about SQL Server. Probably a little less amazingly. In this video I want to talk a little bit about three functions that are in my GitHub repo that don’t get enough, if any, attention whatsoever. Look, you know, look, look, I got a GitHub repo. Ooh, everyone has a GitHub repo. It’s not anything crazy to have a GitHub repo. Most of what gets attention in my GitHub repo are the stored procedures I write to help with SQL Server troubleshooting. Things like the stuff over here that starts with the SPs like SP Health Parser and SP Human Events and SP Log Hunter and SP Pressure Detector and SP Quickie Store. There are a lot of SPs in there. These string functions get very, very, very, very much, attention. And it’s sort of a shame because when I’m working with clients, I often see like really crappy versions of these functions like copy and pasted from some website from the year 2000. Inevitably scale our UDFs, every single one of them. Some of them multi statement table valued functions. But like always like while loops and other crap that make your query easier.

Keep those queries awful and hate you slow and make you need to hire me. Maybe I shouldn’t be talking about it. Keep those queries slow. Call me. I’ll do something. I’ll wave my hands at them. But the three basic things that I see a lot of people trying to do in queries a lot has to do with like string manipulation. Microsoft has given us some help with string manipulation lately, sort of. We got a string split and a string ag and I don’t know. I guess that’s it. You get what you pay for. It’s only $7,000 a core. Why develop anything useful? Apparently we’re getting regex. Azure SQL DB has regex in preview, which can only mean SQL Server vNext is getting regex in preview. So, um, can’t wait to start seeing regex in a where clause. To be honest with you, gonna make the, revive the entire SQL Server community having regex in where clause. Everyone’s coming back. Everyone’s coming back.

All right. Not a sink and ship at all. So, uh, these are the, what the three functions do. Uh, they are called get letters, get numbers, and strip characters. Uh, they all do nearly the same thing. I’m going to talk about what’s in these files a little bit before I show you the, the stuff. So in every file, there’s two versions of these. Uh, there’s one version where you can use a numbers table and the numbers table does help a little bit with performance. There’s also another version that uses a CTE to build up a sort of, uh, internal numbers table, uh, that looks like this. Right? So there’s a little bit more work in there. Uh, the numbers table is a bit faster. It’s not, you know, depending on what you’re doing, the, this, the speed difference is, uh, somewhere between negligible and, um, uh, profound.

So, uh, you know, make sure that whatever version of these you’re using suits your use case appropriately. Uh, so what these things both do is, uh, this one is get letters. So what this does is it basically, uh, uses that string splitting thing to do some XML-ing. And what we do is look for, uh, basically any single character in a string that matches the AZ-AZ, uh, uh, uh, pattern, right? So that’s all the, you know, characters between A and Z. Um, so that, that, that, that’s what that does. Uh, if you need out weird characters, I don’t know, write your own function. Uh, and then the, the get numbers function does just about the same thing, except it only looks for where, uh, the single character is like zero to nine.

The strip characters one is a little bit different because what this does is it seeks to remove, uh, some, some matched expression from, uh, from your strings. All right. So they, they all function slightly differently. Um, I think maybe, I mean, sort of theoretically, technically, strip characters could replace both get letters and get numbers. But, um, I, I sometimes find that it is, it is, it is helpful to write code that is geared towards a specific task.

Uh, the, the more, um, the more that your code might have to do or, like, the more generalized your code is, sometimes the less efficient it is. Uh, overly generalized code, big, bulky, slow, lots of thinking, decision-making, uh, things to check on. Uh, more focused, narrow-casted code generally tends to be faster and work better.

So, uh, with that out of the way, let’s go over to Management Studio and let’s just see a couple examples of these things working. So, uh, this is the get numbers, uh, function. And if I run this, uh, we’ll see, uh, a bunch of nulls where some usernames don’t have numbers.

And then for lines where the usernames do have numbers, we will only get the numbers from them. Things like 4614. Without the user, right?

So, that’s, that’s, that’s get numbers, which is pretty handy. Uh, get letters does the exact opposite of get numbers, where, uh, when we get down to this batch of users, where there, there, there were numbers there at some point, uh, the numbers have been completely stripped out.

All right? So, fun stuff there. Uh, the strip characters one, well, like I said, there’s a, there’s a solid case for strip characters replacing both. So, both of those, uh, in different ways.

But, um, well, so like there are some weird, there are some weird characters in some of these. It’s a screwy Unicode thing. Uh, the results look a little weird sometimes with this.

But if you look at, uh, the, the, the display name column in the Stack Overflow database has some, has some very weird things in it. It’s an Envarcar and strange things happen. So, but we, when we want to, uh, strip out characters, um, well, this is, this is the one where we, where we were removing numbers.

And we can see the numbers get removed in here. Like that. And for the one where we were looking to, uh, do the opposite, which is one where we were trying to remove all that stuff.

Uh, so there’s, like I said, there are some strange things in here. But the, the important thing is that for these, for these rows, uh, we only get the numbers back from those. Uh, for, for these, uh, I would have to, like, go through and, like, do, like, an ASCII or Unicode check to figure out exactly what is odd in here.

But Tomek Melissa, I guarantee you, has some weird Unicode character somewhere in that name. Uh, we just, I, I just didn’t really do all that much digging on it. So, if you, if you have a need in your database to either, um, a lot of, I see a lot of this stuff with, like, phone numbers or, you know, um, something along, something along those lines.

Uh, and it’s usually pretty good for, these are pretty good for that stuff. Uh, they are inline table valued functions, so they don’t have the same problems that scalar UDFs or multi-statement table valued functions would have. So, if you want to give these a shot and see if they fit your use case, you know, I got get letters.

I got get numbers. And I got strip characters. And they all do, you know, just exactly what they sound like. So, you should try them.

And if you, I don’t know, like them or you find bugs or you find things that can be improved from a performance perspective, well, this is what GitHub is for. We collaborate. We’re a community.

We all high-five each other. No one gets paid. No one gets paid. All right. Cool. Uh, thank you for watching.

I hope you enjoyed yourselves. I hope you’ll try my handy little functions out. Uh, I hope you learned something. Uh, what else? If you like this video, I like thumbs.

I love thumbs. Thumbs are the best. Comments are nice, too. Uh, and I also like subscriptions. Subscribers.

I like when people say, I want to hear from you more often, Erik Darling. Uh, because, you know, that’s how I make friends. I yell at my camera on YouTube.

So, anyway. Like. Subscribe. Hang out. Spend some time with me. Mm. Try some functions out. They’re free.

The first function’s free, kid. All right. Uh, thank you for watching.

Going Further

---

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

Check Constraints And Query Performance In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the world of database constraints and their impact on query performance, particularly focusing on how tweaking these constraints can lead to more efficient execution plans. While most viewers might not have constraints in their databases, I explore a scenario using Stack Overflow data to illustrate why constraints are important for maintaining data integrity and optimizing queries. By adding and modifying constraints, we see firsthand how SQL Server processes queries differently, leading to significant improvements in plan shape and performance. This video is part of my ongoing series on database optimization and query tuning, aimed at helping those who want to improve their skills in these areas. If you found this content amusing or informative, I’d love to hear your thoughts—thumbs up and helpful comments are always welcome!

Full Transcript

Erik Darling here with Darling Data. In today’s video, we’re going to talk about how you can tweak constraints in your database to improve query performance. Now, this video is admittedly a pretty good bit of narrowcasting. I know most of you out there, you do not have constraints in your database. If it’s a data warehouse, I’m with you, though. Like, check constraints, unique constraints, foreign keys, and data warehouses. The domain of morons. Do not mess with your database load times. Your data warehouse load times. And your OLTP databases, OLTT, not a thing. Oh, your OLTP databases, where, you know, referential integrity and things like that are pretty important, you should have constraints like uniqueness and check and foreign key because they’re good for your data. They’re good little data vitamins. You should have your data on those vitamins. They can get big and strong like Fred Flintstone. So, I mean, this is just kind of a funny thing. It amuses me. And if it amuses me, I record it. And hopefully it amuses you, too. That’s why at the end of every video, say, I hope you enjoyed yourselves. Because, oh, crap, I hope it’s funny to someone.

If it’s just me laughing at my own jokes, boy, that would be so depressing. Oh, dear. Oh, dear. I’m spiraling. Spiraling real hard. So, I’ve got my Stack Overflow database, and I’ve already created this index. And this index is on ZoomIt. I’m having real ZoomIt problems. I might have to, like, restart my computer or something because ZoomIt is just being acting a wild. Wild fool on me. This index is keyed on reputation and upvotes, and it includes display name. Now, what I want to point out about this index before we go any further is that this index does, in fact, have a standard, let’s see, is that, well, almost 200 row history. I’m 194 rows. And you can see all of the histogram stuff in here. This is our 194th row. Maybe if we, oh, I mean, we created the index, so that is a full scan.

There is our 194th row. This is our highest value, 1047863. 1047863. Oh, that’s seven digits. That’s John Skeet. And then we have various and assorted reputations up here. And the very first reputation in the table has a range high key of one, right? So SQL Server knows that the lowest range high key that it got from this data is the number one. The thing that this doesn’t guarantee in any way, shape, or form is that the lowest number that will always be in here will always be the number one, right?

SQL Server has no logical guarantees about that because we don’t have any constraints that enforce that. So what we’re going to do next is look at a query plan. And when we’re going to look at this query plan, we’re all going to gasp. We’re going to be shocked and awed and horrified at how disgusting this query plan is. I mean, it runs pretty quickly because let’s, like, look. They don’t all have to be awful. Some of them can just get the point across.

Some of them, you really just… See the pictures up there? There’s a seek and a sort and a parallel gather streams and a top. Sometimes you just need the pictures to tell the story. In a way, you do kind of miss the old days when there were no operator times in query plans because then you could just, like, take a picture of something and be like, trust me, it was awful. But this, you know, keeps you a little bit more honest. So me being honest with you, I just want you to pay attention to the operators in the plan and the plan shape.

Namely, we have a parallel plan. Our plan has a sort in it. Those are the two things that are important here. Now, if you’ve watched my other videos about indexes and indexing and how indexes put SQL Server data in order, you would think that, you know, having a less than or equal to predicate on reputation would make it fairly easy to… For SQL Server to know that, like, it doesn’t have to sort upvotes, right? We order by upvotes here, but we have, like, an equality predicate.

And one is the lowest in the table. So, SQL Server, you should know. But it doesn’t know because we don’t tell it that. We have to over-communicate to databases. You know, they’re very neat. Databases are like big neat balls.

Like, you’ve got to really spend a lot of time coaching and coaxing. It’s a whole thing. So, if we add this constraint, where we say reputation greater than zero, SQL Server knows right now that every single reputation in the users table is greater than zero.

And so, you would think that SQL Server would logically be able to say, when this query runs, well, everything’s greater than zero. So, that really means that the only number that could show up in here is the number one. I mean, yes, theoretically, technically, scientifically, there are numbers between zero and one.

But this is an integer column. But we don’t have that level of precision. But SQL Server, still not very confident. And we end up with the exact same execution plan with the seek and the sort, and it’s all parallel.

It’s very interesting, isn’t it? If we want to fix that, if we want to change that for good, we need to get rid of the constraint as it exists here. And we need to change the constraint. Constraint? Constraint? I don’t know what a constraint is.

I don’t think that’s a word. We need to change our constraint to be reputation is greater than or equal to one. And when we do that, now SQL Server changes its whole mind.

We have a single-threaded index seek with no sort and no need for a parallelism gather stream because there’s no parallelism. So, what’s kind of funny here is if you look at this index seek, this just says seek predicate reputation equals one. So, we have that equality predicate now on reputation.

And we have the SQL Server with that knows that upvotes is all in the order that we want it to be in. So, we don’t have to actually physically sort anything. Upvotes is all in the right order.

If we go back a step, oh, dear, what happened? That was a bit scary. That was like one of those end-of-the-world movies. If we go back a bit and we look at this constraint or look at the query plan with the greater than zero constraints in place, the index seek looks a little bit different.

Right? We still have the seek predicate, but it’s less than or equal to one. So, just having that zero in there, just having greater than zero doesn’t really help us. Having the greater than or equal to one gives us a much tidier query.

So, let’s be honest here. Very few of you have constraints. Very few of you pay this much attention to query plan details and maybe to the way constraints are written.

And how that can be changed to improve query performance. Very few of you would even probably pay attention to that query plan that we just saw. Why?

It runs in 100 milliseconds. Are you going to tune that? I don’t know. Do you need to? Maybe. Could you do it by changing a constraint that you probably don’t have on your table anyway? Yes.

That’s the magic of databases. These are the kind of little things that matter. I’ve said it before in other videos. You know, the further along you get in your database.

I mean, I gear myself towards the query tuners. The further you get along in your query tuning career, the more these small details and these little minutiaes make a difference. So if you want to improve, leave the query performance stuff aside for a little bit.

If you just want to improve yourself and you want to improve your query tuning abilities, these are the kind of things that you’re going to have to start paying attention to. Now, let’s say we’re starting from a place where you have no constraints on this table. That’s the first thing you’re going to want to go after, right?

Because if this is an OLTP environment, not a data warehouse. Data warehouse, constraints, no. Get out. You’re in an OLTP environment and there’s like specific domain rules and regulations around some of the columns in your database. Overcommunicate to SQL Server what can and should be in those columns.

It will help you in the long run. And then further on, when you start to have really insane problems in databases, pay attention to how those constraints are written. Because to SQL Server, which apparently does not trust integers, there’s a big difference between greater than zero and greater than equal to one.

So just stuff to keep in mind. Stuff like this I find all the time. Stuff like this I find and it surprises even me.

It just kind of takes a little bit of like the curiosity to tinker and, you know, toggle with stuff a little bit and try to figure out exactly what’s wrong with SQL Server today. Because it’s always something. Always something wrong with SQL Server.

It’s sort of like a Munchausen type thing. I’m not a doctor, but it sounds like a reasonable diagnosis. Anyway.

Yeah. One, get some constraints. Two, write your constraints right. Important lessons there.

Right? For you. Thank you for watching. I hope you enjoyed yourselves. I found this particular scenario funny. I hope you also found it funny.

If you didn’t, well, I don’t know. Maybe the next one will be better. I can’t make any promises, though.

If you like this video, thumbs ups and helpful, not hurtful comments are always appreciated. And, of course, if you like this sort of SQL Server content generally, and you would like to get notified whenever I talk about things that amuse me, well, you can subscribe to the channel, and you can get the notification along with, like, 4,000 other people.

And like we’ve said before, 4,000 people have never been wrong. Never once. All right.

Thank you for watching. I need to go do something else for a moment. What it is, you’ll never know. You can only guess. You can only dream. You can only speculate. You can only be conjecture.

But I’ll know. I’ll know.

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.

Spring Training

---

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Register for Data Saturday, on September 7th here!

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.

Tweaking SQL Server Queries To Induce Parallel Execution Plans

---

Thanks for watching!

Video Summary

In this video, I delve into the nuances of parallel execution plans in SQL Server and why sometimes a hint or trace flag isn’t enough. Erik Darling from Darling Data shares his insights on using an `ORDER BY` clause to trick SQL Server into generating a parallel plan when needed. He explains how adding unnecessary sorting can make SQL Server perceive the query as more costly, leading it to choose a parallel execution path. The video also covers other methods and considerations for inducing parallelism, emphasizing the importance of validating whether such plans are truly beneficial or just adding overhead.

Full Transcript

Erik Darling here with Darling Data. And I have sort of a short video today because sometimes a short video is what I have time for and sometimes a short video is what you have time for. So this is going to be a good and happy time for when I only have time for a short video and for when you only have time for a short video. It’s just amazing how that’s going to work out. Well, one thing that comes up a lot for me in my consulting and in, well, I mean, I guess sort of like a general query tuning methodology is, you know, there are definitely times when a parallel execution plan is preferred. And faster. The problem is that Microsoft doesn’t give us a min-dop hint. There’s no way to tell SQL Server via a hint called min-dop that you want to set a minimum degree of parallelism for a query. It would be nice if you could. It would save everyone a lot of trouble. Unfortunately, Microsoft doesn’t care about you that much. Or apparently, it doesn’t care about you that much.

Or just wants to make you suffer. And that sucks. What we do have, we have a trace flag called 8649. The trouble with trace flags is that, you know, you do need admin permissions to use them. And, you know, you can’t put trace flags in some modules like views, things like that. Like, you know, you can’t put, like, you know, option query trace on whatever at the end of a view query. And we also have a use hint called enable parallel plan preference. That one has fewer drawbacks, but the problem with both of them is that Microsoft does not document nor support them.

So, you know, we as query tuners are left with this unfortunate sort of, you know, do we want to use undocumented, unsupported hints and trace flags in our queries? Yes. Yes, yes, we do. It’s fun. Or do we want to find other sort of more natural ways of getting SQL Server to think that a query will do so much work that we need a parallel plan for it? Now, there are a lot of circumstances where you just don’t need to encourage SQL Server all that much.

Most of the time, SQL Server will just be like, you get a parallel plan and you get a parallel plan and everyone gets a parallel plan. Go crazy. So, you know, that’s fun. But sometimes you got to play some tricks. There have been various attempts over the years that are more successful in some compatibility levels and in some contexts than others.

I often find that, you know, you can usually coerce SQL Server into using a parallel plan by making it do something extra that doesn’t logically change the query that it wasn’t doing before. I think probably the easiest one to talk about is like sorting, like making SQL Server order data. A lot of the times when you make SQL Server order data, it will freak out and be like, whoa, expensiveo.

Parallel plans for everyone. So just to give you like, I realize these queries, you know. Okay, first off, like if I was yelling at someone about this query, top without order by is complete garbage nonsense.

If you’re writing queries like this and you don’t have like the, like, if God did not hand you a rock that said this query can only ever return one row anyway, and you do this, I think, I kind of think you’re an idiot and you should stop. This is not a good practice.

But I just want to show you really quickly that using this top one query and then using a top one with an order by, how that, how SQL Server doing more work makes it be like, oh, we need a parallel plan now. So let’s look at these two things.

All right. Top one, same query, top one with an order by. And if you look at these query plans, you might be shocked. You might be horrified.

You might be mortified to see that the first query we ran with no order by runs for nearly 11 seconds. All right. So we spend about almost nine seconds in the votes table and then doing some hash flow distinct hippie stuff right here.

And this thing runs for quite a while. And what’s really amusing about both of these queries is neither one of them return any rows. I did that on purpose.

Kind of a funny, kind of a funny query. I know the stack overflow data apparently well enough to get queries to do dumb things like this. So the second query does have an order by.

But here’s what’s tricky about it. It has an order by on the ID column. And how did that end up over there? That is not where, that is not correct indenting.

One, two, three, four, five. Oh, we need one more. There we go. Ah, no, come back. There we go. That is proper indenting. Four spaces in for select list columns. I don’t know why SQL prompt sometimes screws me and aligns this thing with the top or distinct.

It’s awful looking. But this query, those were ordering by u.id. And the funny thing about u.id is that it is the clustered primary key of the users table, meaning this thing already has an index on it.

It is well indexed. It should be, SQL Server should be able to return this in index order very easily. But that’s not what SQL Server has a problem with.

What SQL Server has a problem with is the votes table. Look at our mighty votes table. Look at all these rows.

These rows coming out of the votes table. Look what we do to those rows. We sort them. We have to sort these rows.

Because we ask SQL Server to return this data in order. Ah, curse you. To return data from the users table in order. SQL Server thinks that from an I.O. perspective, it would be a whole lot smarter if we had data in order from the votes table.

All right. So to make the loop join a lot faster. All right.

So SQL Server does us a solid here. And sometimes this kind of stuff, this general approach works with like all sorts of queries. But especially, you know, when you have exists and not exist queries where you’re dealing with queries that have these sort of funky little row goal things in them.

You really need to, you know, make sure that the plan that you’re getting is not one that is susceptible to row goal problems. Row goal problems are painful problems. Because this query up here, quite honestly, has a row goal problem.

If you look at this stuff, that’s a sassy row goal problem. The SQL Server is like, ah, I think there’s only one. Mm-mm.

Mm-mm-mm, SQL Server. You’re wrong. We’re all wrong. Well, I think being wrong about row goals is the worst kind of wrong you can be. Because when you’re wrong about row goals as a query optimizer, you really barf all over everyone’s day.

It is a sick and sad thing to see in the world. But this query, these numbers look a lot smarter to me. And this parallel plan turns out a lot better, especially when we’re dealing with lots and lots of rows.

So, and if you’re out there trying to tune a query and, you know, let’s just say on the off chance that maybe you’re like, I think a parallel plan will be better here. And there might be all sorts of reasons you might think that. You might, you know, look at one of your operators and be like, holy cow, 50 million rows on one thread?

That seems like a bad idea to me. We should have those rows on dop threads because dop threads would be a lot faster. So, if you come across a situation where you’re pretty sure that a parallel plan would be a lot faster.

And, you know, like it’s okay if you want to test queries using trace flag 8649 or the enable parallel plan preference use hint. But if you need to find a more natural way of inducing parallelism, often, you know, adding things that don’t really change much about the query but do change the work SQL Server thinks it has to do are pretty valid ways of getting a parallel plan where you otherwise just wouldn’t be able to get one. So, order by is one of those funny things.

And there are all sorts of places where you can stick order by, especially with big tops, like top two bajillions, in order to get a parallel plan where it just, it doesn’t change logically or semantically what the query is doing, what the query is returning. But it does fool the optimizer into thinking that a query is going to be a lot more expensive from a costing perspective. Not necessarily like, oh, it’s going to make the query slow.

Remember, costing and slowness are not the same thing. But it’s just a pretty good way of getting SQL Server to think, to like operate under the impression that it has to do a lot more work than it actually has to do. Get yourself a parallel plan and just make sure that, you know, like you can validate pretty quickly if the parallel plan is faster and if the parallel plan is efficient.

Remember, one of the key things about parallel plans is that you should see a fairly good difference between CPU time and wall clock time. If you have a DOP 8 query, ideally, you would see CPU time be 8x whatever wall clock time is, but that’s not always going to be the case. But what I want you to keep an eye on is something like the closer CPU time and wall clock time get to each other in a parallel plan, the less efficient the parallelism was.

That’s where you’re going to see like a lot of rows ending up on like one or two threads or something within the parallel plan. And that’s going to just just going to show you that the parallelism was pretty inefficient. So I’ve covered that in many videos, many, many videos, thousands of videos at this point, maybe even billions.

Hard to keep track. Only. Only there was some sort of dashboard told me how many videos I had.

All right. Anyway. Thank you for watching. Hope you enjoyed yourselves. At least a little bit. I hope you learned something.

And if you like this video, if you are into playing tricks on the optimizer, like I am, throw this video a like or throw me a comment that says, Cool dude bro job.

I think you did a cool job, bro. Something like that maybe. Your English might be better than mine. Mine’s terrible.

And also if you like SQL Server content, messing with the optimizer, giving it noogies and wedgies and, you know, all that good stuff. Bullying it a little bit. It’s okay to bully the optimizer.

It’s a computer program. It doesn’t have feelings. So if you like that kind of content, subscribe to the channel. Like almost 4,000 other people have. I mean, 4,000 people can’t be wrong.

If I’ve learned anything from elections the world over, 4,000 people cannot be wrong. All right. Cool.

With that sort of crushing optimism, we’re going to sign off here. Thank you for watching.

Going Further

---

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

Why Operator Times Are Wrong For Some MSTVFs In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the peculiarities of query plan times for multi-statement table valued functions (MTVFs) and why they can appear so strange. While I generally advise against using MTVFs due to their frequent performance issues, I explore two specific examples in detail. By examining three different queries that call these functions, we uncover how disabling interleaved execution is crucial for getting accurate timing information. The video highlights the discrepancies between actual runtime and the misleading query plan times, offering insights into why MTVFs can be problematic and when they might not provide the performance benefits one hopes for.

Full Transcript

Erik Darling here with Darling Data. And if I look a little bit different in this video, it’s because I’m really happy. So, you and I, we’re gonna get happy together. We’re gonna get real psyched on all this. You and me. So, in this video, we’re gonna talk about why query plan times for some multi-statement table valued functions look really weird. Now, I don’t want you to think that I contain, that I condone, not contain, I contain, I contain multi-statement table valued functions. Multitudes of them. Multitudes of multi-statement table valued functions. No, I don’t want you to think that I condone the use of multi-statement table valued functions because in general, they’re, in my experience, they are either like a complete performance albatrosses tied to the next of your queries or they’re just whatever. I don’t think I’ve run into a situation in my, in my, in my, my history where switching to a multi-statement table valued function made things dramatically faster. I suppose there’s some, there’s some chance of that happening if you like, you know, were splitting up a larger, more complicated query and you decided to use one to like, to like, materialize some intermediate results but even, even that’s a bit of a, of a, of a, of a far-fetched scenario. That’s a pretty big gamble because of the many limitations that table variables have in SQL Server. And if you have questions about that, watch the rest of my channel videos because I talk about it a lot. All right? So, do that. Do, do, do a little bit of diligence. All right? A little bit of diligence is due from you.

You, you won’t, you won’t, you won’t, you won’t be one diligence. So, uh, we’re going to look at two different multi-statement table valued functions. Uh, and we’re going to look at three different queries calling two different multi-statement table valued functions. That sounds about right. So, this is the first one. It’s called score stats. Uh, it accepts a user ID integer. It inserts some stuff into a table variable. Well, actually, let’s, let’s zoom in here a little bit. This is what makes a multi-statement table valued function. A multi-statement table valued function and not an inline table valued function is that we are returning a table variable. Right? This is our return clause at out table stuff. Uh, and I make everything with schema binding because I hope that, and hope and pray that someday I find, uh, a, a situation where schema binding makes things better or faster.

In the meantime, I dislike making anyone trying to change tables or columns experience some pain. Be honest about that. A little poke, a little poke on you. So, that’s the first function. The second function does something rather similar, uh, also with schema binding. Uh, we return a table, uh, it does an insert into the table and, uh, then returns the table down at the bottom.

I think I neglected to show that in the first one, that this is the final return statement returns our table variable. I know, calm down. Calm down. Please. It’s, it’s, it’s all too much. It’s all too much. So, these are the three queries that we’re going to look at. Uh, we have the first query, which, uh, calls the first multi-statement table valued function.

We looked at, uh, score stats. We have another multi-statement. Well, we have another query that calls the other multi-statement table valued function that we looked at called vote stats. And then we have, um, we have a third iteration of that query, uh, where I have specifically disabled, uh, interleaved execution. And the reason I’ve done this is because it used to be that if I ran these two queries back to back, if I ran this one, then the re-ran the same query, uh, I would get accurate execution times shown in the query plan, uh, on the second run, when it was, uh, you working with a cache plan.

We’ll talk about why in a second, but I don’t get that anymore. So now I have to use the disable hint to show you what’s going on. So for the first query and the first query plan, uh, we get accurate and honest times. Uh, this runs for 6.38 seconds. If we come over here and we click on the properties, like good little property clickers, and we look at the query time stats, we get, uh, the right timing on those.

Uh, this function is not, at least, uh, I don’t think it was, uh, this function is not eligible for interleaved execution. Uh, there’s, we’ll see in the other functions, there’ll be a message up in here that’ll say, oh yeah, you’re cool with that. You can, you can get me some functiony goodness. You’re a nice hip happening person.

Uh, but this one, uh, we don’t, we don’t have that. Uh, but anyway, this multi-statement table value function does return correct and accurate, uh, query timings. Uh, the second one does not. Uh, so the second one, the query that doesn’t have the hint, uh, where this thing says it ran for one second, is an absolute lie. And if we scroll down to the second query where I’ve disabled, um, the interleaved execution, it’s, it’s run for 45 seconds.

This query did too, and we can, we can verify that this query ran for 45 seconds. Cause we, if we go to the properties here and we look at the query time stats, we can see, uh, a complete and utter nonsense lie for CPU time. And then 42 point 42 and a half seconds of elapsed time, right?

So the CPU time is way screwed on this and the, but the elapsed time is probably more accurate. Now, coming back to what I was talking about with the interleaved execution. If we go in here and we look, uh, but this part of the query plan, uh, we’ll see this contains interleaved execution candidates is true.

How nice we have, we have found a truth in the world. Uh, and that is, that is just excellent for us. And then if we look at the properties of, uh, the table valued function call up here, notice the, notice this query plan, this query plan are a little different.

Uh, this one is just like, you know, blah, blah, nested loops, table valued function. This one is like table valued function sequence. And then we have a table scan on the, the, from the, coming from the table variable from, from within the function here.

But if we look at, um, if we look at the properties of the table valued function call from the, this part of the plan, you will see, uh, this handy dandy little thing that says, is interleaved executed true? True. True. It is true. It is not false. Uh, I think when it’s false, that, that node just isn’t there, which is crappy.

It’s like, I don’t know. Just make things, makes things confusing for people. So I don’t want, what I don’t want you to think is that, um, uh, disabling interleaved execution is what made this, this call, this call slow.

These calls both run for just about the same amount of time. There is, there is a slight difference in the parallel versus non-parallel plan. What interleaved execution does is, and the reason why this thing doesn’t show accurate cardinality is because what interleaved execution does is it, it, during like query compilation, it populates the table variable to come up with a cardinality estimate.

So like, it sort of like happens in these like two phases where like, like that happens and then the rest of the query runs using that, uh, using that estimate. So like, this query gets a better estimate and actually uses a parallel plan. So like, this one should be, this, that’s why this one is like a few seconds faster.

But these things both run like we, like we’ve seen because, because when I click on this and I go and I look at the query time stats, uh, this runs for 42 and a half seconds and this runs for about 45 seconds. So the parallelism helps a little bit for this plan because we got a better act. We got a better cardinality estimate, uh, coming from the function.

Uh, but in this one, like, so for this one, you know, we got, uh, a 30 row guess and we got one Oh eight, six, five, eight, six of 30. Uh, and this one up here, uh, we, well, I don’t know. I don’t know if that’s really better.

One Oh eight, six, five, eight, six of four, three, seven, eight. So, so I don’t know. Uh, sure. The parallel plans a little bit better. Um, I don’t, I don’t know if three seconds is really that great of a difference here.

Uh, I, I, if I, if you, if you or your boss or whoever were paying me to tune this query, I wouldn’t be like, Oh, just make the multi-statement table value function parallel. Cause three seconds, that’s as good as we can do. Uh, we, we would, we would do much better than that.

You and I, you would look at these query plans. We would ponder the majesty of interleaved execution. And then we would click quickly throw the whole thing in the garbage and rewrite this in a different way. Cause, uh, there’s, there’s really like, I love when Microsoft does this.

It’s so cute where they’re like, Ooh, we have this thing. That’s going to fix this crappy feature we have. And then it, it’s just so mediocre. It just does so little.

And you’re like, okay, wow. You’re real. You didn’t really move the needle there. Uh, it didn’t, didn’t work. Didn’t really do anything. But anyway, I don’t know. Uh, that’s, that’s enough of that.

Thank you. I love you. You’re, you’re wonderful, beautiful people. Uh, I, I appreciate you watching my videos. Uh, I appreciate everyone who, who, who throws me likes and who throws me comments, whether, no matter what the comment is.

Uh, and I also appreciate, uh, the, the nearly 4,000 data darlings out there in YouTube land who, who, who have subscribed to this channel and, and, and, and find it necessary to be notified, uh, when I, when I do these things.

So that’s all there. Uh, I’m going to prepare a different demo and probably record something else. So, uh, uh, I, yeah, thanks. Thank you for watching and I’ll see you in, in, in another video next time.

Goodbye.

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.