Post reply

Duplicate Plans in Plan Cache

  • September 20, 2017 at 3:50 am

    #328409

    Hi,

    If I invoke a stored proc from my application, capturing in extended events, it will cache the plan (this is in cache cleared using dbcc freeproccache).
    If I then invoke the stored proc from SSMS, using the captured sql, it will create a duplicate plan in the cache.  

    I am deeming them "duplicate" here because they have the same SQL Handle and both are using the same cached sql statements (going by QueryHash), but they have a different Plan Handle.

    Does anyone know why this is? 

    Thanks

  • September 20, 2017 at 7:15 am

    #1960157

    Shalaboosh - Wednesday, September 20, 2017 3:50 AM

    Hi,

    If I invoke a stored proc from my application, capturing in extended events, it will cache the plan (this is in cache cleared using dbcc freeproccache).
    If I then invoke the stored proc from SSMS, using the captured sql, it will create a duplicate plan in the cache.  

    I am deeming them "duplicate" here because they have the same SQL Handle and both are using the same cached sql statements (going by QueryHash), but they have a different Plan Handle.

    Does anyone know why this is? 

    Thanks

    There are often differences when executing through your application compared to executing through SSMS.
    You can use dm_exec_plan_attributes, pass in the plan handle and check for differences. Here is the documentation for that view:
    sys.dm_exec_plan_attributes (Transact-SQL)

    Sue

  • September 20, 2017 at 10:55 am

    #1960208

    Cool, thanks Sue

Viewing 3 posts - 1 through 2 (of 2 total)

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