I am seeing a lot of blocking with wait resource as OBJECT: n:zzz:0 [COMPILE] and TAB: n:zzz:0 [COMPILE] with a lot of RESOURCE_SEMAPHORE_QUERY_COMPILE waitstypes.
This is causing a lot of CPU overhead and it is almost reaching close to 100% most of the time.
We are running with
Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64)
Apr 11 2022 16:24:07Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
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
The same combination test when run on similar SQL 2016 server with identical configuration is running without any issue.
Can anyone share anything on this as what could be the probable cause?