question

knsk_bsk avatar image
knsk_bsk asked

SQL 2019 Std Edition high recompilation and CPU spike

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

sql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.