1

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.
  • 2
    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 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
3

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.