Execution plan question
-
Is it possible that the execution plan of a stored procedure with the option "recompile" can be different to the execution plan of the T-SQL on the stored procedure? If so, can anyone point me to some whitepapers or any documentation on it? Thanks in advance!
-
Yes.
Recompile allows, among other things, variables to be 'parameter sniffed'. That can be enough to start the optimiser searching in a different area of the plan space, resulting in a different plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail. I forgot to mention that the stored procedure is running with the same parameters all the time as the ones used by the T-SQL.
-
But she's talking about variables within the query, not parameters. Parameters are sampled by default, variables can only be sampled during a recompile situation. The other issue is, if your local T-SQL is using variables and the stored procedure is using parameters, you can could get different plans then too. Again, back to parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Plus recompile relaxes the rules about the plan being safe for reuse, plus there's the additional visibility into table variables, probably other stuff too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you for the answers.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply