Multiple cached plans for the same stored proc

  • Hi,

    When querying sys.dm_exec_procedure_stats, I noticed that some procedures appear multiple times in the result set because they have different cached plans.

    When joining with sys.dm_exec_plan_attributes, I could see that the difference between cache keys is sometimes the value in the column optional_spid.

    However, I could not find precise information on that column. Would anyone know how to write a simple stored proc and execute it in such a way that it appears more than once in sys.dm_exec_procedure_stats with different values in the sys.dm_exec_plan_attributes.optional_spid column?

    Thank you

  • calling the sproc with different settings on you connection will be enough for sqlserver to generate another plan. ( ansi settings )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thank you for your response, Johan, but I'm really looking at how to generate a duplicate plan because of a difference in the optional_spid column, not the SET options.

  • I only see that column being non-zero for sproc

    msdbsp_delete_job_references

    At this moment I cannot tell you what that actually means or why it defers from the others.

    did you have a look at http://ask.sqlservercentral.com/questions/41100/schema-qualifying-temporary-objects.html

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi Johan,

    yes, I had a look at that and now I understand.

    Here's a way to reproduce:

    On connection 1, execute the following:

    [font="Courier New"]CREATE PROCEDURE dbo.TestProc

    AS

    INSERT #t1

    SELECT 1 AS N;

    GO

    CREATE TABLE #t1(N INT NOT NULL);

    EXEC TestProc;[/font]

    On connection 2, execute this:

    [font="Courier New"]CREATE TABLE #t1(N INT NOT NULL);

    EXEC TestProc;[/font]

    Then run this to see the cached plans for the stored proc:

    [font="Courier New"]SELECT OBJECT_NAME(object_id, database_id) AS Name, *

    FROM sys.dm_exec_procedure_stats

    WHERE OBJECT_NAME(object_id, database_id) = N'TestProc'

    ORDER BY OBJECT_NAME(object_id, database_id)[/font]

    You should see 2 cached plans. To see what differs in the cache key, run this:

    [font="Courier New"]SELECT OBJECT_NAME(object_id, database_id) AS Name, a.*

    FROM sys.dm_exec_procedure_stats s

    cross apply sys.dm_exec_query_plan(s.plan_handle) p

    cross apply sys.dm_exec_plan_attributes(s.plan_handle) a

    WHERE OBJECT_NAME(object_id, database_id) = N'TestProc'

    AND a.is_cache_key = 1

    ORDER BY a.attribute, s.plan_handle[/font]

    This reveals that the optional_spid column is used because the temporary table in the proc refers to an existing outer temporary table, therefore the plan cannot be reused.

    Thanks for your help.

    Laurent

  • Thank you for the feedback.

    Now I get it too :w00t:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 6 posts - 1 through 5 (of 5 total)

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