# Multi-statement Table Valued Function causing massive PAGELATCH_EX waits

We are getting insane PAGELATCH_EX waits because of a Multi-Statement TVF. I've found that the temp table created by the function is on tempdb file #1 and doesn’t ever change (always '#A465FC0D'). It appears to be permanently cached there. The function is very heavily used and all the processes line up waiting to access the same temp table.

The function simply does a string_split, but using string_split directly, or converting the function to an inline TVF is 7x slower. Strings to split are relatively small (max 200 ids, but many are just a few ids)

CREATE FUNCTION [dbo].[IntListToTable] (@list nvarchar(max))
RETURNS @intlist TABLE (val int)
AS
BEGIN
insert into @intlist
SELECT
CAST(value as int) val
FROM
string_split(@list, ',')
return
END


I understand that the page latches are caused by the use of this function, but I'm looking to understand the caching/re-use mechanism here better.

And why is this better than using an inline TVF or STRING_SPLIT() directly?

• SQL Server 2017 CU2
• We have 24 temp db data files
• This happens on a couple of dedicated read replicas that have a more narrow workload than our OLTP server. Both have buffer cache hit ratio of 100% and plan cache hit ratio of 88%.
• We restarted the instance and a new temp table was created and its been that same one ever since.
• If you can give us some examples of execution plans (pastetheplan.com) that use your custom function and the built in function, as well as the queries that use them and any relevant DDL it will help us greatly. – LowlyDBA - John M Dec 12 '18 at 19:05
• You should ask one question per question. The question about page latch waits is entirely separate from the one about better performance with the multistatement TVF. For that one can you ask a new Q with code demoing the performance difference? – Martin Smith Dec 12 '18 at 23:55