I need clarification on how SQLServer behaves when you execute a parameterised procedure with OPTION RECOMPILE in parallel.
Option Recompile: "You can force SQL Server to recompile the stored procedure each time it is run. The benefit here is that the best query plan will be created each time it is run."
My question is, when executing this procedure in parallel, will the best query plan be created for each concurrent procedure executed or will it use 1 plan?
The best query plan will be created for each concurrent procedure executed in parallel.
This plan is only used by the session and not added to the plan cache.
It would be a bug if the plans could be used between sessions as OPTION (RECOMPILE) can perform simplifications based on the parameter values that are not generally applicable to all possible parameter values.