SQL 2019 Std Edition high recompilation and CPU spike

Question

I am having an issue in SQL 2019 Std edition where I am seeing high compilation across multiple stored procedure. The reason for the recompilation as per the extended event trace is 'temp table changed’. However, when the same code is run across SQL 2016 Std edition the system is behaving normally. This behavior is similar for the Developer edition as well.

The database and the tests that are being run are identical across to servers. The CPU and memory are too identical for both the servers and before running the tests index are rebuilt too.

Below are some of the information;

SQL edition.
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Common settings for both the servers
Min Server Memory 51200 MB
Max Server Memory 112860 MB
Processor Intel(R) Xeon(R) Platinum 8259CL CPU @ 2.50GHz, 2500 Mhz, 8 Core(s), 16 Logical Processor(s)
RAM 128 GB
MAXDOP 1
CTP 5

Another point is any insert into #table is going for a recompile, but the same when converted into persistent table of table variable is working fine.
If any further info is needed will be glad to provide.
Can any one share any info on this as to how to investigate it or get to the root cause? or has someone come across a similar issue with SQL 2019.

Answer 1

Consider this:

   CREATE PROCEDURE innermost_sp AS  
      SELECT a FROM #temp  
   go  
   CREATE PROCEDURE middle_sp AS  
      CREATE TABLE #temp(a int NOT NULL)  
      EXEC innermost_sp  
   go  
   CREATE PROCEDURE outermost_sp AS  
      DECLARE @i int = 5  
      WHILE @i > 0  
      BEGIN  
         EXEC middle_sp  
         SET @i -= 1  
      END  
   go  
   EXEC outermost_sp  
   go  
   DROP PROCEDURE innermost_sp, middle_sp, outermost_sp  

If you run this on SQL 2017 or earlier and you have a trace with SP:Recompile enabled, you will see four recompilation events with EventSubClass = "1 Schema changed". This is not strange at all. Logically, innermost_sp sees a new temp table every time, and there is no guarantee that the schema is the same.

Nevertheless, they added logic to SQL 2019 to check if new temp table has the same schema as the old temp table, and if you run the above on SQL 2019, you will not see any SP:Recompile events.

The fix in CU5 referred to appears to apply to MARS (Multiple Active Result sets). Are you using this feature?

I don't know how your code look like. It would have been interesting to study the case, but I see that you have opened a support case, and that's probably a good move. Particularly, that is more or less necessary to get a fix.

If you have the time, please let us know how the case goes!