Multiple Plan_Handles for Single sql_handle

• alex.palmer

  SSCrazy

  Points: 2292

  More actions

  July 5, 2017 at 3:38 am

  #338654

  Hi

  I have some SQL code that is producing multiple execution plans.

  SQL Handle                                                          -                                 Plan_Handle
  0x02000000C091301E7C7594690D04C37E3A111512E27D157A    -   0x06000500C091301E40A18B89000000000000000000000000
  0x02000000C091301E7C7594690D04C37E3A111512E27D157A    -  0x06000500C091301E40616F84000000000000000000000000
  0x02000000C091301E7C7594690D04C37E3A111512E27D157A    -   0x06000500C091301E4061947B030000000000000000000000
  0x02000000C091301E7C7594690D04C37E3A111512E27D157A     -  0x06000500C091301E400118EC000000000000000000000000
  0x02000000C091301E7C7594690D04C37E3A111512E27D157A    -   0x06000500C091301E40A1A55B020000000000000000000000

  Looking at the XML for the execution plans they all have the same set options and the fact that they all have the same SQL_Handles shows the text is exactly the same.

  Has anybody got any ideas why I have multiples plans?

  Thanks

  Alex

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  July 5, 2017 at 1:56 pm

  #1949569

  Maybe try checking the plans using dm_exec_plan_attributes - it can be easier to find differences using this DMV.

  Sue

• alex.palmer

  SSCrazy

  Points: 2292

  More actions

  July 6, 2017 at 1:28 am

  #1949621

  Hi Sue

  I've not seen that DMV before and it's solved (or at least I know the cause now) my problem

  Although the set_options were 251 for the queries, they all had different user_id's. This stops plan reuse for different executing users.

  On closer inspection the queries didn't use the schema name, everything is in dbo apart from the executing users who all have their own schemas

  Thanks

  Alex