Home Forums SQL Server 2005 Administering Very strange behaviour - Multiple Exec plans - Attached is the complete code RE: Very strange behaviour - Multiple Exec plans - Attached is the complete code

  • April 11, 2013 at 9:15 am

    #1605641

    curious_sqldba (4/11/2013)

    ChrisM@Work (4/11/2013)

    Lynn Pettis (4/10/2013)

    I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

    Edit: Actually the temporary table #test is different when created from different sessions.

    A new plan for each new session, even. There's some useful info here.

    Still doesn't answer my question. My temp is executed in block of where the condition is never used, but still there are multiple exec plans. Please go to the original post for more details.

    It is never used when the procedure is executed, but SQL Server still generates a plan for the SQL statement in the IF block. Compilation does not evaluate the IF clause, that is done during execution, after the plan is generated.