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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE PROC dbo.BadIdea @IDS VARCHAR(MAX) AS BEGIN DECLARE @IDTab TABLE ( ID INT ); DECLARE @Holder TABLE (DisplayName NVARCHAR(40)) INSERT @IDTab SELECT fsy.ID FROM dbo.fn_SplitYo(@IDS, ',') AS fsy; INSERT @Holder ( DisplayName ) SELECT u.DisplayName FROM dbo.Users AS u JOIN @IDTab AS it ON it.ID = u.Id; END; GO |
Then we’ll grab a CSV list of IDs and run our proc like this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SET NOCOUNT ON DECLARE @IDHolder VARCHAR(MAX) = '' DECLARE @i INT = 1 WHILE @i <= 100 BEGIN SELECT @IDHolder = STUFF( (SELECT TOP (10) ',' + CONVERT(VARCHAR(12), u.Id) FROM dbo.Users AS u ORDER BY NEWID() FOR XML PATH(''), TYPE).value(N'text()[1]',N'VARCHAR(8000)'),1,1,'' ) EXEC dbo.BadIdea @IDS = @IDHolder SET @i+=1 PRINT @i END GO |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE PROC dbo.BadIdea2 @IDS VARCHAR(MAX) AS BEGIN DECLARE @IDTab TABLE ( ID INT ); DECLARE @Holder TABLE (DisplayName NVARCHAR(40)) INSERT @IDTab SELECT ss.value FROM STRING_SPLIT(@IDS, ',') AS ss INSERT @Holder ( DisplayName ) SELECT u.DisplayName FROM dbo.Users AS u JOIN @IDTab AS it ON it.ID = u.Id; END; GO |
When when our loop runs, we don’t see the constant compilations of function code.
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!
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.
Thanks! Yeah, it just gets confusing because I have to name all my stored procedures “BadIdea”.