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 variab...
Sql Enterprise
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