Forum Discussion

Boris Knop's avatar
Boris Knop
Copper Contributor
Apr 16, 2018

SQL 2016 CU-8 Enterprise, Multiple query plans for same sql statment, Bug?!

Hi,

for some reason the optimizer generates different plan handles (more than 40K, same plan hash) for the same SQL statement, simple parameterized insert statement .

we have checked  passed variables, plan attribute (set_options) and could not figure it out why the optimizer recompiles the plan each run.

no option recompile hint ( or any hints).

examples (both statements runs with same user options and connection string):

 

1 Query Plan, 1 Plan Hash. ==> as expected

INSERT [dbo].[TableA]([CreateDate], [A], [B], [C], [D], [E], [F])

VALUES (@0, @1, @2, @3, @4, @5, @6) 

 

40K  Query Plans, 1 Plan Hash ==> bug?

INSERT [dbo].[TableA]([CreateDate], [A], [B], [C], [D], [E], [F])

VALUES (@0, @1, @2, @3, @4, NULL, @5)

 

 

 

is there anything else i should check or its a known/new bug?

if i should report it, can anyone please elaborate how we report bugs with the new interface introduced early 2018 ( got full MSDN)?

 

Thanks in advance!

 

 

 

Sql Enterprise

2 Replies

Sort By
  • msclash's avatar
    msclash
    Icon for Microsoft rankMicrosoft
    Feb 05, 2019

    Hi,

     

    do you have "forced parameterization" and / or "optimize for adhoc workloads" set ? 

    Are the different plans full plans or only a stub (5 to 10 lines for a simple statement) plan ?

     

    Regards

     

    Clas

    • Boris Knop's avatar
      Boris Knop
      Copper Contributor
      Feb 05, 2019

      Hi,

       

      we are running on simple parametrization and 'optimize for ad hoc workloads' is enabled.

      I don't have the plans which were generated, that was more than 6 months ago, we have changed the query so it will be suit our needs. I believe it has something to do with passing NULLs in the values clause.

       

      I believe the plan is pretty simple and straightforward (2 indexes (including clustered), no triggers, no refe. constraints).

       

      Boris

       

       

Resources