High Compiles and Multi-Statement Table Valued Functions

Way back in 2016

I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions.

Leaving aside all the other performance problems with them, this is another way they can mess with your server. Let’s look at a typical setup where they get used, and how SQL Server 2016 can help you fix the problem.

You made a bad choice

Rather than use Table Valued Parameters, you decided to pass a CSV list to a stored procedure, and you wrote a crappy function with a WHILE loop in it to split the CSV list. Or you copied one from the first result you found on the internet, even though it warned you that it was a really just God-awful die in a fire idea.

To simulate workload, we’ll need a stored proc, and a loop to feed it a random list of IDs.

Then we’ll grab a CSV list of IDs and run our proc like this.

If I were on vNext, I’d be using STRING_AGG, but I’m trying to keep things clean. No big deal, though. I’ve been using XML this long, right?

We’re going to be using the Extended Events session and query from before. We’ll kick that off, run our loop, and then be on our merry way.

Shredding the data will give us a bunch of repeating patterns that look like this.

10… Top 10… Yeah.

So yeah, that function seems to get up to something once for every ID you pass in. Remember that in our STUFF… query, we grabbed the TOP 10 each time. In the XE session, each time we call the proc, the string splitting function compiles and executes code 10 times. Bummerino. That’s the life of a loop.

On SQL Server 2016 (and really, with any non-looping code), we can get around the constant compilations with a simple rewrite. In this case, I’m calling 2016’s STRING_SPLIT function instead of the MSTVF function.

When when our loop runs, we don’t see the constant compilations of function code.

You’re a joy.

Finisher

We didn’t cover a lot of the performance hits that MSTVFs, and table variables in general can cause. Much has been written about those topics, but I haven’t seen this come up. It’s one of the many reasons why you might be seeing high compilation rates in sp_BlitzFirst, or in your monitoring tools. Or because you’re really good at guessing compilation rates.

This was, oddly, the first demo I wrote on my new home cloud/server/whatever. Not exactly pushing the bounds of performance, here, but Extended Events data shredded pretty fast 🙂

Thanks for reading!

, , ,
Previous Post
What Do You Wanna Know About Storage?
Next Post
First Responder Kit Updates: So Many Updates, Brent Quit

2 Comments. Leave new

  • Erik, I really love your entertaining writing style. And smart to call the procedure “dbo.BadIdea” which is an excellent example of self-documenting code.

    Reply
    • Thanks! Yeah, it just gets confusing because I have to name all my stored procedures “BadIdea”.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}