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

%3CLINGO-SUB%20id%3D%22lingo-sub-183272%22%20slang%3D%22en-US%22%3ESQL%202016%20CU-8%20Enterprise%2C%20Multiple%20query%20plans%20for%20same%20sql%20statment%2C%20Bug%3F!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183272%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Efor%20some%20reason%20the%20optimizer%26nbsp%3Bgenerates%26nbsp%3Bdifferent%20plan%20handles%20(more%20than%2040K%2C%20same%20plan%20hash)%20for%20the%20same%20SQL%20statement%2C%20simple%20parameterized%20insert%20statement%20.%3C%2FP%3E%3CP%3Ewe%20have%20checked%26nbsp%3B%20passed%20variables%2C%20plan%20attribute%20(set_options)%20and%20could%20not%20figure%20it%20out%20why%20the%20optimizer%20recompiles%20the%20plan%20each%20run.%3C%2FP%3E%3CP%3Eno%20option%20recompile%20hint%20(%20or%20any%20hints).%3C%2FP%3E%3CP%3Eexamples%20(both%20statements%20runs%20with%20same%20user%20options%20and%20connection%20string)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%20Query%20Plan%2C%201%26nbsp%3BPlan%26nbsp%3BHash.%20%3D%3D%26gt%3B%26nbsp%3Bas%20expected%3C%2FP%3E%3CP%3EINSERT%20%5Bdbo%5D.%5BTableA%5D(%5BCreateDate%5D%2C%20%5BA%5D%2C%20%5BB%5D%2C%20%5BC%5D%2C%20%5BD%5D%2C%20%5BE%5D%2C%20%5BF%5D)%3C%2FP%3E%3CP%3EVALUES%20(%400%2C%20%401%2C%20%402%2C%20%403%2C%20%404%2C%20%405%2C%20%406)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3E40K%26nbsp%3B%20Query%20Plans%2C%201%26nbsp%3BPlan%20Hash%20%3D%3D%26gt%3B%20bug%3F%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3EINSERT%20%5Bdbo%5D.%5BTableA%5D(%5BCreateDate%5D%2C%20%5BA%5D%2C%20%5BB%5D%2C%20%5BC%5D%2C%20%5BD%5D%2C%20%5BE%5D%2C%20%5BF%5D)%3C%2FP%3E%3CP%3EVALUES%20(%400%2C%20%401%2C%20%402%2C%20%403%2C%20%404%2C%20%3CEM%3E%3CSTRONG%3ENULL%3C%2FSTRONG%3E%3C%2FEM%3E%2C%20%405)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20anything%20else%20i%20should%20check%20or%20its%20a%20known%2Fnew%20bug%3F%3C%2FP%3E%3CP%3Eif%20i%20should%20report%20it%2C%20can%20anyone%20please%20elaborate%20how%20we%20report%20bugs%20with%20the%20new%20interface%20introduced%20early%202018%20(%20got%20full%20MSDN)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-183272%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESql%20Enterprise%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-331118%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%202016%20CU-8%20Enterprise%2C%20Multiple%20query%20plans%20for%20same%20sql%20statment%2C%20Bug%3F!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-331118%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewe%20are%20running%20on%20simple%20parametrization%20and%26nbsp%3B'optimize%20for%20ad%20hoc%20workloads'%20is%20enabled.%3C%2FP%3E%3CP%3EI%20don't%20have%20the%20plans%20which%20were%20generated%2C%20that%20was%20more%20than%206%20months%20ago%2C%20we%20have%20changed%20the%20query%20so%20it%20will%20be%20suit%20our%20needs.%20I%20believe%20it%20has%20something%20to%20do%20with%20passing%20NULLs%20in%20the%20values%20clause.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20the%20plan%20is%20pretty%20simple%20and%20straightforward%20(2%20indexes%20(including%20clustered)%2C%20no%20triggers%2C%20no%20refe.%20constraints).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoris%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-330725%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%202016%20CU-8%20Enterprise%2C%20Multiple%20query%20plans%20for%20same%20sql%20statment%2C%20Bug%3F!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330725%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Edo%20you%20have%20%22forced%20parameterization%22%20and%20%2F%20or%20%22optimize%20for%20adhoc%20workloads%22%20set%20%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAre%20the%20different%20plans%20full%20plans%20or%20only%20a%20stub%20(5%20to%2010%20lines%20for%20a%20simple%20statement)%20plan%20%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegards%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClas%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies

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

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