Sql-server – Multi-statement Table Valued Function causing massive PAGELATCH_EX waits

set-returning-functionssql-server-2017waits

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.

Best Answer

Since you are on SQL Server 2017 CU2 - you might be hitting this bug - Heavy tempdb contention occurs in SQL Server 2016 or 2017 .

Update to atleast CU5 since CU5 has fixed above bug.

I would recommend CU12 (latest).

Related Question