Post reply

Very strange behaviour - Multiple Exec plans - Attached is the complete code

  • April 11, 2013 at 9:50 am

    #1605671

    Lynn Pettis (4/11/2013)

    And the reason you get a separate plan for the execution of the same procedure from different sessions is that the procedure is actually accessing a completely different table in those sessions. The table #test in session 1 is not the same #test in session 2.

    "•If a stored procedure refers to a temporary table not created statically in the procedure, the spid (process ID) gets added to the cache key. This means that the plan for the stored procedure would only be reused when executed again by the same session. Temporary tables created statically within the stored procedure do not cause this behavior."

    http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • April 11, 2013 at 10:09 am

    #1605677

    Lynn Pettis (4/11/2013)

    And the reason you get a separate plan for the execution of the same procedure from different sessions is that the procedure is actually accessing a completely different table in those sessions. The table #test in session 1 is not the same #test in session 2.

    Thanks,makes total sense.

Viewing 2 posts - 16 through 16 (of 16 total)

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