Forum Discussion

Copper Contributor

Apr 22, 2022

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 overh...

SQL Server 2019

KanishkaB

Copper Contributor

May 10, 2022

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.

mcdasa

Brass Contributor

May 10, 2022

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 .