CODEWITHSUNDEEP
sql-serverstored-proceduresparallel-processingrecompile
TheLegendaryCopyCoder
TheLegendaryCopyCoder

Reputation: 1832

SqlServer - OPTION RECOMPILE parallel execution behavior

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?

Upvotes: 2

Views: 434

Answers (1)

Martin Smith
Martin Smith

Reputation: 452947

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.

Upvotes: 3

Related Questions