0

On SQL-Server 2016 (using Management Studio 2016), I created two identical stored procedures: one is compiled with SET QUOTED_IDENTIFIER OFF, the other one with ON.

Here is the query plan executed with ON, instant result, we can see the right index seek is used:

https://www.brentozar.com/pastetheplan/?id=S1ZNIpZ8z

And then this is where the problem lies, executed with OFF, it took more than 5 seconds with an index scan:

https://www.brentozar.com/pastetheplan/?id=SkCOUTWLf

Here are the DDL of the guilty indexes:

CREATE NONCLUSTERED INDEX [IX_CAB_NO_CAB_TYPE_CAB] ON [TraceTri].[CAB]
(
    [NO_CAB] ASC,
    [TYPE_CAB] ASC
)

CREATE NONCLUSTERED INDEX [IX_CAB_CHECKSUM] ON [TraceTri].[CAB]
(
    [CHECKSUM] ASC
)

I don't understand how the query plan can be totally affected by this SET option, as it seems to have no link with the way it should be executed?

Pierre Nicolas
  • 65
  • 2
  • 10
  • query planes are affected by many SET options. why does it surprise you? – Mitch Wheat Feb 02 '18 at 07:52
  • 1
    Whilst at an intellectual level, the "why does this particular setting change things?" would be good to have an answer to, the sheer number of things that are listed as breaking when `QUOTED_IDENTIFIER` is `OFF` is surely an indicator that you shouldn't really be contemplating any other setting than `ON`? – Damien_The_Unbeliever Feb 02 '18 at 07:54
  • Can you please update your question with links to both execution plans pasted here: https://www.brentozar.com/pastetheplan/ ? Did you compare **compiled** parameters for both plans? – sepupic Feb 02 '18 at 10:30
  • Mitch Wheat: because this option has nothing to do with index use Damien_The_Unbeliever: it took me hours to understand why this query was slow, I need to dive deep on the reason @sepupic: Done. And yep, both of them were compiled – Pierre Nicolas Feb 02 '18 at 11:12
  • Yes, thanks, now it's clear that even compiled values = runtime values and they are the same in both cases, but plans are still different. It's interesting. Can you please show the DDL of both IX_CAB_CHECKSUM and IX_CAB_NO_CAB_TYPE_CAB? – sepupic Feb 02 '18 at 11:30
  • @sepupic: Yep, here you go :-) – Pierre Nicolas Feb 02 '18 at 11:48
  • Parameter sniffing. It's considered a different query. Same goes if you used `ARITHABORT` or anything like that. – Nick.Mc Feb 02 '18 at 11:53
  • It's NOT parameter sniffing. Both plans are **compiled for the SAME parameter values**. Yes of cours, 2 different set options -> 2 plans, but why on the earth should ther be DIFFERENT for the same COMPILED values? – sepupic Feb 02 '18 at 13:03
  • For some reason, IX_CAB_CHECKSUM cannot be used when QUOTED_IDENTIFIER is OFF. In both cases cardinality estimation is the same and is correct, only total impossibility of using IX_CAB_CHECKSUM can cause the optimizer to choose IX_CAB_NO_CAB_TYPE_CAB – sepupic Feb 02 '18 at 13:17
  • The strange thing is that BOTH plans are compiled for QUOTED_IDENTIFIER="true" – sepupic Feb 02 '18 at 13:30
  • So have you tried to recompile this procedure one more time? Set options for both plans are IDENTICAL. The only explanation I can think about is that at the moment of compilation when you got a bad plan, IX_CAB_CHECKSUM index was not available. It was not created, it was offline rebuilding or it was disabled, smth like that – sepupic Feb 06 '18 at 07:19
  • @sepupic: Yep I can recompile and recompile again, the result stays the same... I doubt the index was disabled or rebuilding, I actually did this test on a local backup with no use of the tables nor the indexes – Pierre Nicolas Feb 06 '18 at 11:27
  • If you can recompile and still get 2 different plans, the index is available, otherwise the good plan could not use it at all. It's very strange case, really – sepupic Feb 06 '18 at 11:32
  • Can you please change your proc with QUOTED_IDENTIFIER OFF: please add a second query (same query) but with a hint WITH (INDEX(IX_CAB_CHECKSUM)), will it build a plan using this hint or will it ignore it? Then please share this plan, I want to see both queries (with hint and without hint) in one plan – sepupic Feb 06 '18 at 12:48

0 Answers0