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 recoverability

    We 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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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 recoverability

    We 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply