SQL 2019 Std Edition high recompilation and CPU spike

Other SQL Server Topics
1

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.

2

5 Questions to Ask When You Upgrade SQL Server - Brent Ozar Unlimited®

It's the first question :slight_smile:

3

Thank you :slight_smile: and I get it completely, but need some inputs around the problem I referred above :frowning:

4

Please have a try may be works for you.

  1. Open the windows start menu, search for SQL Server 2019 Configuration Manager and launch
    the application
  2. Select SQL Server Services.
  3. Right-click on “SQL Server (Studio Instance) and select Properties
  4. In the Specify a startup parameter” field, type the following: -T2430
  5. Click Add and then Ok
  6. Right-click on SQL Server (Instance) and click Restart.

------ Try first above then try below as optional

Customize SQL Server instance features (Optional)

  1. Connect to your database (localhost\Instance) using windows authentication.
    a. Max degree of parallelism
    i. Right-click the instance and select “Properties”
    ii. Select “Advanced”
    iii. Set the value for Max degree of parallelism based on the number of cores in
    your server with a maximum value of 8.
    b. Memory per query
    i. Select “Memory”
    ii. Set the Minimum memory per query value to 4 MB (4096 KB) ---select same or more
    c. Default fill factor
    i. Select “Database settings”
    ii. Select the Default index fill factor value to 20
    iii. Click “Ok
5

Hello Sameer,
The above did not help.