Forum Discussion

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

Microsoft

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

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