Quick one today:

Interesting topic today: Does recompiling procedure stop current executions of the procedure?

Of late, in our lab, we’ve been needing to run several performance tests (volume testing) to measure performance metrics under different load and traffic patterns. In one of those iterations, we ran into a peculiar situation, where we needed to recompile stored procedures every few hours. For some reason, the cached procedure was becoming less efficient, even though it was not flagged as expired to be recompiled automatically. One thing that became clear was, at any given time, several incidents of the same procedures are being executed by different application processes. So, when a procedure is recompiled, it does not impact any of the procedures that are already in execution. It only impacts any new executions.

When a procedure is recompiled:

• Sql Server gathers all the meta data needed to generate optimal procedure plan based on the state of the database at that given time period.
• Once recompilation is complete, it forces all new executions from then on to use new plan.
• The current running procedures (that started before recompilation) still use the same old plan.

Let’s look at an example:

While this procedure is running in the above connection, open a different session and run the following command that forces recompilation and new execution.

Result:
Object ‘dbo.testt’ was successfully marked for recompilation.