Sample Header Ad - 728x90

Rolling blocking on Compiling a SP

1 vote
0 answers
32 views
We are experiencing rolling blocking when a specific stored procedure runs. This SP runs multiple times a minute, and does heavily use temp tables. It doesn't block all the time, but during peak times its prevalent. blocking lasts for 30 seconds, the SP times out, then the next in line blocks. When it happens the blocked process shows as this: waitresource="OBJECT: 5:1371949573:0 [COMPILE]" The object is the SP itself. we don't see any specific statement inside the SP blocking, its just this compile wait. Looking at the plan cache, this SP has about 2500+ different, single use plans in cache. We have not been able to determine why this SP is not reusing a plan. Has anyone ran into this situation before, and more importantly, how can I find out the reason that it is not getting plan reuse? Is there an extended events session that can track executions of this SP, and specifically the reasons why its compiling so frequently? the only thing i can think of is that maybe since a temp table in this stored procedure gets multiple updates, inserts, and deletes would cause this? the SP does make use of multiple instances of Option(keepfixed plan) as well.
Asked by DBA Greg14 (265 rep)
Apr 3, 2025, 02:39 PM