Apr 16 2018 09:52 AM
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!
Feb 05 2019 02:11 AM
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
Feb 05 2019 08:37 AM
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