Blocking due to recompile locks

Copper Contributor

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?

7 Replies
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-sp...
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 .
I perhaps missed to provide certain info. I did capture the reason for recompile and in most cases its statistics changed and temp table changed. Now, the interesting part is this thing is very low on SQL 2016 and that is something where I am not able to get any clue. The CPU spike(almost 100%) due to excessive recompilation is being seen in SQL 2019 and not SQL 2016. While I agree we can change our process, but that would still left the puzzle unsolved- why is that not happening in such a volume in SQL 2016.
I think you can use track the issue using xevent i mentioned.
or you can track if statistics was changed with same datas but different version of sql server.

or you can check if auto statistics update is disabled or not.

https://blogs.lessthandot.com/index.php/datamgmt/dbadmin/when-do-statistics-update/

@GregLow 

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.