The SQL Server Performance Tasting Menu

clinks glass

This post will have links to all the demo videos for my presentation, The SQL Server Performance Tasting menu. Since the demos change over time with whatever I currently find interesting, I want to have a place where people can find older ones.

To see the full YouTube playlist, head over here.

For individual posts with each video, here’s the list:

Happy learning!

Video Summary

In this video, I delve into the intricacies of multistatement table-valued functions by focusing on their primary driver: table variables. I explore how these functions inherit many of the performance issues associated with table variables, such as serial execution for modifications and inaccurate row estimates that can prevent parallelism. By walking through a practical example, I demonstrate how even simple operations within a function can lead to unexpected behaviors, like not utilizing parallelism when reading from a table variable inside a multistatement table-valued function. I also highlight the hidden costs of these functions, where significant time is spent in parts of the query plan that don’t contribute to row processing, making it crucial for developers to be aware of such nuances and test thoroughly before implementing them in critical code paths.

Full Transcript

I want to talk about multistatement table-valued functions because the primary driver of a multistatement table-valued function is a table variable. We’ve talked about table variables once or twice so far. And because they are the primary driver of a multistatement table-valued function, multistatement table-valued functions inherit genetically a lot of the defects that table variables carry with them, I guess. So let’s look at some of that. Alright? Look at some of that stuff. So what we’re gonna do is declare a simple table variable and insert some data into it. And then we will select data out of it. And the reason that I have this hint here will make a little bit more sense in the next demo. But for now, let’s just rock and roll with this. So this should run for just about four seconds altogether, inserting the rows in and then selecting them out. And that’s pretty simple there. Now, first thing that I want to make sure everyone is aware of is that modifications to table variables are forced to run serially. If we go hit F5, or sorry, if we either hit F4, not F5, we already hit F5 and ran the thing. If we hit F4 on the insert or on the root operator, or we hit F5 or on the root operator, or we hit F5, or we hit F5 and hit F5 or we hit F5, or hit F6 to get F5. And if we hit F5, we hit F5, we hit F5, run the root operator, or hit F5, or hit F5, or hit F5. And we hit F5, we hit F5. And we could not generate a valid parallel plan. This is true of any modification to a table variable, insert, update, or delete. And if you don’t have a particularly big, complicated query inserting into a table variable, this probably doesn’t matter much to you, but it is something that you should should be aware of. And the other thing is that even though we just stuck, let’s see, 1.3, 1.8, 4, 1.3, 1.3 million rows into this table variable, SQL Server only thought one was going to come out. So we get a very bad guess coming out of the table variable. However, at least we can read from that table variable in parallel.

We see you have a parallel, a little parallel icon there. If we go to properties and we look at the rows, we can see that across four threads we had some pretty decent row distribution there. So it is possible to read from a table variable in parallel. The problem is that quite often because you get this very bad estimate here, right, you get this one row estimate, oftentimes the cost of the plan will be so low that you will not get a parallel plan. Here I hinted the plan, I used a query hint to make this go parallel. Under normal circumstances, a query that costs 0.035 query bucks is not going to go parallel. Now, you do need to make one distinction here and that that’s the cost of the parallel plan. We can look at the cost of the serial plan and see if it would be expensive enough to go parallel. Rerun this. I don’t know how long this is going to take. It could be days or hours or minutes, seconds, but ooh la la, I was right.

Because of this very low one row estimate, we get a very cheap serial plan and SQL Server at no point even considers parallel emphasis. This query would have to cost at least 50 query bucks. And if we had an honest guess here, I would be willing to bet that SQL Server would give us a parallel plan.

And we can test that out by trying a recompile hint and see what happens. We’re using a recompile hint, we’ll get table cardinality from the query, but we will not get any column level statistics. And lo and behold, when we use a recompile hint and SQL Server gives us an accurate estimate, then we get a parallel plan. Now, I know that number looks weird to you, but if you recall back to the other video in this series about bitmaps, then you’ll recall why this estimate looks off.

We have an in-row probe here, which means that we have the bitmap filter ruling rows out, which means that we have a reduced number of rows coming out of that scan. So, cool stuff there, right? We got to see that the insert to the table variable was serial. We got to see that we could read from the table variable here in parallel. And we kind of got to see how the estimates that you get from table variables are bad and often influence the optimizer away from a parallel plan because the cost of a one-row query is often so cheap that SQL Server just doesn’t, you know, you don’t break cost threshold for parallelism. There’s SQL Server doesn’t get an expensive enough cost estimate in order to start thinking about parallel plans.

So now let’s take everything that we just did here. And let’s take this query and let’s stick it inside of a multi-statement table valued function because that is sort of the point of this whole thing. So we’re going to take all of that same logic and we’re going to put it inside a function. And now we’re going to call a query or call that function in a query.

And so sometimes I don’t know where some of this comes from. Some of this formatting just just pops in and it looks weird. And I spend a lot of time fixing it. I have SQL prompt too. I don’t know. I don’t know what happens. It’s bizarre. Absolutely bizarre. So we’ll run this. You can see I have SQL prompt. It’s right here.

Before anyone says, this is your SQL prompt, I have SQL prompt. Stuff gets weird. So this query ran for four seconds, which is about what we would expect because that’s what happened when we ran that same query without the function. But I want to show you a couple interesting things. One is that despite having this hint here to use a parallel plan, we do not read from the table variable in the multi-statement table value function in parallel.

There is no parallelism icon here. And the operator immediately following the scan is a distribute streams, meaning SQL Server needs to take one thread and split it up into multiple threads with rows across. So if we look at this, we now have four threads and we have all that there. If we look at this, we have all rows on a single thread. So we took single thread and split it up into four threads and distributed rows pretty evenly across them.

This is another limitation of multi-statement table value functions. You cannot read from the table variable inside of a multi-statement table value function in parallel. If you don’t have a lot of rows in here, that’s probably not a big deal. If you do have a lot of rows in here, which some people will undoubtedly have because I’ve seen people putting tens of millions of rows in table variables and not being sure why things are slow. But, you know, that’s just me. That’s just the kind of stuff that I get called for.

The other thing that’s really interesting about this is that the time disappears. We know this runs for four seconds. If we get the properties of this select operator and we go to query time stats, we can see that we spent 4.6 seconds of wall clock time running this query. But the operators in the plan don’t come anywhere near adding up to that. By the time we get to this final sequence operator, we’re at 1.147 milliseconds.

One might wager that the majority of the time was spent in here and that it is just hiding from us. The time was not spent returning rows. If it was just time spent returning rows, then we wouldn’t have the, it wouldn’t be in the elapsed time here. But we can, we can probably guess that that, remember before when we ran this and it took about three and a half seconds and the whole thing took about four seconds total.

And about three and a half seconds was inserting into the table variable. Well, I’m pretty sure that between you and I, we can make a reasonable guess that that’s what took three and a half seconds in our, in our plan there. Doing all that.

Good times, right? Good times. Good times with functions. That’s all I had to say about multistatement table valued functions.

be careful with them because they do use table variables. Table variables have a lot of performance or potential performance hiccups in them. So please be judicious and test carefully if you’re going to use a multistatement table value function.

Maybe try to separate it out from, you know, big important parts of code as much as possible, something like that. I don’t know, whatever.

Anyway, thank you for watching, and I will see you in yet another video. Goodbye.

Video Summary

In this video, I introduce the SQL Server Performance Tasting Menu series, a collection of presentations designed to share insights into various aspects of SQL Server performance in an engaging yet straightforward manner. The format is intentionally simple and direct because, while there are many talented storytellers in the community who excel at weaving compelling narratives around their topics, my goal here is more about showcasing what I find interesting or important about SQL Server without all the embellishments. Whether you’re a seasoned pro or just starting out, this series offers bite-sized lessons that can be picked up and applied as you see fit—no need to follow every video if you don’t want to; each one stands on its own merit. So, grab your favorite beverage, settle in, and let’s dive into the world of SQL Server together!

Full Transcript

Erik Darling here with Darling Data and a copy of Windows that is nearly as inactive as I am these days. So this is the intro video to the SQL Server Performance Tasting Menu series of videos that I’m going to be recording. And I do this presentation the way I do it because there are there are many people in the community who are fantastic storytellers. They can weave narratives and they have all sorts of fun anecdotes and flowery language for things. And I just sort of have a very specific thing that I want to tell you, don’t want to teach you about something and that’s it. I don’t have that particular gene. So this is set up in a way where I can show you what I think is cool or fun or interesting or depressing about SQL Server. SQL Server and you can watch it or you can watch it. There’s no pressure either way. You don’t have to stick with me from start to finish to learn something. You can learn lots of little individual things as you go. And so that’s that. That is the whole thing. I’m going to start recording the rest of the videos now. Hopefully you enjoy them and learn stuff and all that. So.

See you soon.

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.

*clinks glass*

Video Summary

Full Transcript

Video Summary

Full Transcript

Going Further

Related Posts

clinks glass