High Compiles and Multi-Statement Table Valued Functions

Last Updated 7 years ago

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.

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

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.

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

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.

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

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

Andrew
January 26, 2017 5:08 pm

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
- Erik Darling
  January 27, 2017 7:24 am
  
  Thanks! Yeah, it just gets confusing because I have to name all my stored procedures “BadIdea”.
  
  Reply