T-SQL Inline UDF Plan Caching and Reuse in SQL Server
Since it’s also Christmas, enjoy my favorite Christmas song.
Video Summary
In this video, I delve into the nuances of inline table-valued functions (inline UDFs) in SQL Server, focusing on how they differ from scalar and multi-statement table-valued functions when it comes to parameter sniffing and plan caching. By demonstrating with an example, I show that inline UDFs are automatically inlined into the calling query, which means traditional methods of tracking execution plans using extended events become less useful. Instead, I highlight how running queries with `OPTION (RECOMPILE)` can yield multiple execution plans, providing a more dynamic and realistic view of performance under different conditions. Through this exploration, I aim to help you understand when and why certain query plans are chosen by SQL Server, offering insights that can lead to better optimization strategies for your database operations.
Full Transcript
Erik Darling here with Darling Data. In today’s video, we are going to finish up our amazing, thrilling coverage of various user-defined function parameter sniffing and plan caching scenarios in SQL Server. And we’re going to finish up by talking about the type of UDF that I generally prefer, which is the inline table-valued function. To put it briefly, it is the inline UDF, the actual inline UDF. If you like this type of stuff, well, I do this type of stuff all day. Occasionally, I do get paid for it, too. If you would occasionally like to pay me for it, you can hire me for consulting. You can also buy my training, become a supporting member of the channel, ask me office hours, questions, and if you enjoy this type of stuff, please do like, subscribe, and tell a friend.
Past Data Community Summit, geez, closer by the day, ain’t she? Of course, Seattle, Washington, November 17th to 21st, two days of T-SQL pre-cons, the likes of which you have never seen. It’s a great sell line presented by me and Kendra Little. I’m not sure if that’s a great sell line. You should do, what was one from Dune? We got T-SQL pre-cons the likes of which God has never seen. God has not seen? I forget, forget the exact quote. I’m a terrible nerd these days.
but anyway you you go you come you show up you learn things it’s good for everyone anyway let’s talk about inline udf’s here so i’ve got an inline table value function here denoted by the fact that the the returns portion of the function body just says returns table right we do not return a table variable we are not returning a data type we are returning the result of a select the select that we’re returning the result of is exactly what we’ve been doing in prior iterations where we’re just doing this whole thing right so we’ve got that going for us now the first thing that i want to say here is that inline table value function the reason why they’re so different is because the code from them is automatically inlined into whatever query you run so this extended event that we’ve been using to track other executions is no longer useful to us right so if we run this query and we look at the query plan right uh we will see that we got this query plan back right but we actually got the query plan back here right with with other types of udf’s the plan for the udf is hidden away from you and but like for for this we don’t get anything back because the filters on this are for where we want the query post execution plan for the object name of co that contains cohort score and even though the name of our udf does in fact contain the name cohort score uh we we it does not execute as a separate object the the the query inside of this is like inlined into the the query that calls it right so we don’t have to worry about this extended event anymore we can close that out and please god don’t crash ssms please god don’t crash thank you always touch and go with these things but let’s run all three of these with option recompile just because um you know i talked about how um the other other versions of this i needed to make specific versions of the functions with the option recompile hint to see different query plans and write big loops and everything but this one we just naturally get three different query plans with option recompile and the end of the query right this one up here uh it’s a parallel thing we scan an index we scan an index we do some hashing this one down here has a bit more going on in it uh you know we’ve got some compute scale hours and we’ve got a seek and then we’ve got this parallel zone in the plan and then this one down here uh sort of a weird mix between the two except this one uh the sorry the second query i wish this thing would like hold its framing a little bit better but uh this one here uh is just like uh index seek loop uh loop index seek you know we’re done or rather index seek loop loop index seek loop index seek this one down here is of course uh index seek loop uh hash join down to the post table so we got three different plans with the recompile hint right there and we could see the plans without having to use extended events to track what’s going on inside the function because with other ones we wouldn’t see the actual execution plan for the function without the extended event so we’re getting somewhere yay uh so if we uh just because we’ve done it every single time we run dbcc free proc cache and we select the top one here this is kind of annoyingly slow and we’re going to talk about this later but this takes like seven seconds and it takes seven seconds because sql server chooses i mean really like a non-parallel plan and it does some silly things right like uh like like this part is probably fine 152 milliseconds but then like we we did a you know single threaded scan of 17 million rows in the post table maybe not a great idea sql server maybe maybe not such a great idea but the the point here is that we get this version of the plan back and we have the whole query plan nestled in with our query now again we’re not tracking stuff in extended events like we were with the scalar or multi-statement uh valued functions but now when we run this with uh the the wider range of things really because this is where the reputation is over 800 000 people we get back results really quickly and sql server chooses a different execution plan from the one we just saw right like this one here we’re not doing that big ugly hash join single threaded scan of the entire post table we do a seek and a loop and a loop and a seek and a loop and a seek and all this returns relatively quickly right like we could do some performance tuning work on this but i intentionally had to make parts of this not perform great to show you like the differences and the different plans if everything had a perfect plan there wouldn’t be much of a demo here would there no be bored there would be no youtube video crap couldn’t have that what would you do what would you do with your life so now let’s try this for three right so now we’re going to do this and you know and in prior runs when when we did like the the scalar and the multi-statement one for just three it was usually really quick but for this one we get the same kind of crappy query plan back that we got back for reputation equals one right it takes like seven seconds and that’s that’s not so great either i’m like not thrilled about that but again when when we run this like with like in the context of like the full query we we get a we get a good planning or we get a better plan again and this all finishes relatively quickly for the four rows that we care about right again it’s kind of like a you know seek loop seek loop blah blah so on and so forth right you get you get the idea but this all works out a lot better now uh we are going to request performance assistance i’m going to say performance assistance please and we’re going to try to figure this thing out so um like you know you might start by thinking well sql server just you know it it didn’t do a loop join it should have done a loop join right needed a loop join like the the hash join plan is stupid sql server please just use loop joins but when we just hit the loop join at the end uh this ends up taking a little bit longer right that was the last time we ran this it was seven seconds and now that’s like 12 and a half seconds so that loop join hint was clearly not effective and sql server has made an even sillier mistake here now it has done all this right again all single threaded scanning the post table aggregating some stuff uh you know then a loop join down here and then another thing down here that takes like three seconds and so this all adds up to about 12 and a half seconds of time down in this portion of the plan now normally i would do this re i would do this i would do this hint inside of the function body but just for sort of like code brevity sake uh i’m going to just take the function body and inline it into the query and add the hint to that that i would usually put in the function body and that is to just add a force seek hint right here on the post table because really what this hinges on is sql server seeking or scanning the into the post table and with a lot of uh inline table valued function rewrites you do have to do this sort of hinting because like despite them being like the superior type of function in just about every single way imaginable you know they can return more than one row they can return multiple columns there’s no table variable involved they don’t force your query to run single threaded for any reason this is just the optimizer picking a bad single threaded plan for whatever reason the optimizer is doing that for um i haven’t gotten so far as to experiment with the parallel version of these plans because uh i i wanted to get this part done and recorded so uh you know not terribly interesting there uh yet anyway but um if we add a force seek hint to this query and and we run this well now now we’ll now we’ll get sort of the plan that we want and like i said normally i would take this and i would put this in the the body of the function and i would say sql server uh where every time this function runs i am going to force you to seek into the post table because we have a great index there for that now i like i i did try other stuff in this you know like i’ve been talking a lot about row goals and oh you can add top to this and that and top and speed things up top did nothing here top was ineffective uh across this entire thing fast one hints ineffective uh i tried all sorts of hinting around things and really the force seek hint here was the only one that kind of stuck and made things tolerable so so um the bottom line here is that uh because inline table valued functions even though they accept parameters they get inlined into the code and you don’t really see the same type of parameter sensitivity plan reuse thing that you would uh when you deal when you are dealing with scalar and multi-statement table valued functions so we can tell that this is the end because down here is where i was working on the loop code that i showed you in the first video we’re not going to look at that again because we just don’t need to see that but uh i i i think that’s the end so um thank you for watching i hope you enjoyed yourselves i hope you learned something and i will see you in tomorrow’s video i have a short video on sort of a funny take on sql injection that i hope you enjoy too um it’ll be it’ll be it’ll be it’ll be a friday short we’ll call it right because it’s not long so because it’s not long it’s a short all right i think i think i’ve i’ve taken enough of your time thank you for watching i’ll see you tomorrow all right that’s that’s good goodbye you
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.