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

Copper Contributor


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 Replies



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 ?







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).