Apr 22 2022 10:43 AM
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?
May 09 2022 01:04 AM
May 10 2022 05:25 AM - edited May 10 2022 05:28 AM
The queries are a part of a stored procedure and they are not dynamic. The strange part is this behavior is not there when you run the same thing on SQL 2016. The tests are run across similar databases and configurations- one that I shared above. As for the query I am providing a replica of the actual below
SELECT *,
CASE WHEN @Var=1 AND t.Col1 = 0 AND t.Col2 = 1 AND t.Col3 = 0
THEN (SELECT CASE WHEN exists(SELECT 1 FROM xx WHERE xxid=t.xxid)
THEN (SELECT TOP 1 STATUS FROM xx WHERE xxid=t.xxid
ORDER BY colxx DESC
)
ELSE -5
END
)
ELSE -3
END
SomeName
FROM #tmp t
ORDER BY col4 ASC,
col6 asc, col7 asc
The issue is related to a problem I posted earlier- https://techcommunity.microsoft.com/t5/sql-server/sql-2019-std-edition-high-recompilation-and-cpu-sp...
pls go over it and see if you can provide any insights.
May 10 2022 07:14 AM
May 10 2022 07:22 AM
May 10 2022 07:27 AM
May 26 2022 11:31 PM
May 27 2022 12:45 AM
Will go over the document to understand the situation.
Can you also look at the below link https://techcommunity.microsoft.com/t5/sql-server/sql-2019-std-edition-high-recompilation-and-cpu-sp...
This is also one issue that we are facing and the one reported here- 'Blocking due to recompile lock' is a part of it.
Let me know if you could add anything to it.