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

[Boris Knop]

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!

[#2]

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

[#3]

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