Forum Discussion

KanishkaB's avatar
KanishkaB
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's avatar
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's avatar
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 .
  • KanishkaB's avatar
    KanishkaB
    Copper Contributor
    May 10, 2022
    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.

Resources