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.
What about this function makes it forever re-usable?
And why is this better than using an inline TVF or STRING_SPLIT() directly?
Other helpful information:
- 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.