question

knsk_bsk avatar image
knsk_bsk asked

Blocking due to recompile locks

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

MAXDOP 1

CTP 5
Compatibility 130
LCE ON
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?

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