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;
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
We are at 130 compatibility level and LCE ON for both, and have tried with various combination with these settings in 2019, but its all the same, no improvements
Can any one share any info on this as to how to investigate it or get to the root cause?
Has someone come across a similar issue with SQL 2019, I am also not able to get much information compile reason associated with 'Temp Table Changed' either.
Let me know if I need to check something else as well, that I might have missed.