I'm working on updated procedures that I inherited from someone who is no longer at the company.
I have found procedures that include WITH RECOMPILE option after the header. In the notes it says that it was added "to mitigate timeouts from blocking"
ALTER PROC ups_SomeProc (@pMyParameter INT) WITH RECOMPILE
AS
BEGIN
In all my experience I never heard of WITH RECOMPILE option helping with blocking or even be related to blocking in any way.
Am I missing some understanding about this option or the person who implemented it was the one who got confused on what it does? Have anyone heard of this before as a solution for blocking?
Note: This was done when server was still running SQL Sever 2008 edition.
OPTION WITH RECOMPILE forces Sql Server to Recompile an execution plan even if there is an existing plan cached in the memory.
If the underlying data changes dramaticly and very quickly the cached execution plan becomes less efficient. Therefore in such situation using WITH RECOMPILE option executes the procedure much faster then it would execute if it had used the already compiled execution plan.
My guess the developer/person experienced some long delays when he executed the stored procedure. and when he used the WITH RECOMPILE option it got executed faster. So maybe he thought executing this procedure without recompile option causes blocking. :) funny story but I think this is what happened.