Index Hinting - Default Setting

bbrown
bbrown Member Posts: 3,268
edited 2008-10-14 in SQL Performance
I'm having a bit of a brain cramp this morning.

In version 5.0 SP1 is IndexHinting ON or OFF by default? I'm chasing a performance issue in a 4.0 to 5.0 upgrade and the process keeps running back to the clustered index.
There are no bugs - only undocumented features.

Answers

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    OFF.

    But I've seen this happen with SP1 on rare occasions...
    Marije Brummel | Freelance Business Central Specialist
  • bbrown
    bbrown Member Posts: 3,268
    Thanks for the reply.

    That's what I just figured out, because my performance issue goes away if I turn it on. Now I just need to figure out the specific index hint that I need. I don't want to leave it on globally as I have already seen the issues that causes.

    My issue was with the new tabe 5823 "G/L - Item Ledger Relation". I am filtering and sorting on the "Value Entry No." bu the system kept reverting back to the clustered index to retreive the records. The table has 40 million records so this didn't help the performance. Turning on index hinting is forcing it to the correct index and greatly impriving performance.
    There are no bugs - only undocumented features.

Categories