Forum Discussion
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?
- Most of this is still totally relevant to these issues: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn148262(v=msdn.10)?redirectedfrom=MSDN
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-spike/m-p/3290396#M1563
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.
- I'm assuming query causes blocking problem, can you share what quries are running on server?
Is it dynamic queries?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-spike/m-p/3290396#M1563
pls go over it and see if you can provide any insights.- you are using temp tables and every time procedure runs, datas in temp tables probably changes by CRUD and that will cause recompile issue(you can create xevent session that contains recompile event to track down RCA but i'm guessing that queries you mentioned above will probably get captured.)
please read this article -
https://www.sqlshack.com/sql-server-stored-procedure-recompilation-factors/
so i am suggesting you to change your procedure not to use temp table.
Just in case even using permanent table with truncate and modifing datas every time procedure runs also cause recompile. So please reconsider process of your work .
