NAV 2016 - Index Hinting?

bbrownbbrown Member Posts: 3,268
Is Index Hinting available in NAV 2016? I don't find anything in the MSDN documentation on it. If not, is there a recommended alternate solution to controlling index selection in queries?

I have a situation where certain filter values are resulting in non-optimal query plans. The particular table (Item) has about 35,000 records. The filter is applied to a field (Item Category Code) that has a secondary index. If value "A" is applied (which returns about 50 records) the plan uses the expected index. However if value "B" is used (which returns about 5000 records) the plan does a clustered index scan. This is clearly an issue with the "B" filter not being selective enough.

I have tested the query in SQL using an index hint. It will use the expected index regardless of the filter value.

Any ideas or suggestions?
There are no bugs - only undocumented features.

Comments

  • bbrownbbrown Member Posts: 3,268
    Index hinting does seem to work in NAV 2016. But would this be the recommended method? Where it's not mentioned in the documentation I'm wondering if something else is now recommended.

    There are no bugs - only undocumented features.
  • krikikriki Member, Moderator Posts: 9,094
    Before starting with index hinting, check first if your indexes are rebuild regularly (best daily or at least weekly) or when defragged (if no daily rebuilding, then defragging), you also need to update statistics on them.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Rebuilding indexes also updates statistics, no need to do both. Actually update statistics is enough in most cases, index fragmentation is not as critical as when SQL is not picking the index at all due to out of date statistics.

    In most cases rebuilding indexes improves performace because statistics also got updated during index rebuild operation
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • bbrownbbrown Member Posts: 3,268
    This is a testing/troubleshooting database in preparation for a NAV 2016 upgrade.

    Rebuilding the index was the first thing I tried. This is a testing environment, so the indexes are not being rebuilt regularly. But also the data is not changing frequently enough to require it. I will periodically run a reorg/rebuild/stats script.

    My thinking is it's those updated statistics that are causing SQL to not use this index. If you filter on a value that returns a low percentage of records, it will use this index just fine. Only when you filter on a value returning a higher percentage of records does the problem show.

    The index hint results in a very noticeable performance improvement.
    There are no bugs - only undocumented features.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2016-08-18
    From what you've explained it looks like SQL behaved 'correctly' ie decided that for low selectivity filter it is cheaper to go for clustered index scan rather than for index seek/scan + bookmark lookup.

    Unfortunately I have not seen anyting about index hinting in 2016. But.. Perhaps instead of forcing index hint (which only works in 2009) you could fool the SQL server and fake the statistics to make clustered index scan much less 'attractive' option ?

    Here is very interesting post from Brent Ozar Blog about messing with statistics - namely fooling the SQL Server into thinking that the table (or index) have much more rows than it actually have. The use it in query tuning to force SQL server to generate query plans just as if the table was massive - without actually creating a massive table.

    My though is that you could try to manually update statistics on the table and force increase of record count and page count on clustered index statistic, so in theory query optimiser should select the desired index + clustered index lookup as less costly operation..

    It's just an idea - never tried this myself.

    Another, simpler way of dealing with it could be to cluster the table along different, non PK index.

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • bbrownbbrown Member Posts: 3,268
    Thanks for the blog posting. I'll have to review it. Not sure I'll use it here, but may come in handy another time.

    It appears that Index Hinting does work with NAV 2016. I found the DBConfig table does exist, so I gave it a try. It does give me the results I was looking for.

    My feeling is changing the clustered index would just move the problem elsewhere.

    I think I'm going to stick with the index hint. It provides the results needed, is easy to implement, and is somewhat supported by Microsoft.

    Thanks for the ideas.
    There are no bugs - only undocumented features.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    You mean $ndo$dbconfig? Did you find it or did you create it? Where did you find it? I can't see it in freshly restored Cronus

    Either way - nice to know it works. Some other guy here blogged that index hinting was removed in 2013...
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Here is the "some other guy" who blogged it was removed. I am glad it was fixed. I have to test it. Thanks. Good to see you still around Brian. Long time no see. ;-)
  • bbrownbbrown Member Posts: 3,268
    This is an upgraded database from NAV 2009. So it's possible that table is from the original database. Key is it appears to work, even though not documented.

    There are no bugs - only undocumented features.
  • Filip_CrombezFilip_Crombez Member Posts: 13
    I refreshed my mind about the index hinting in 'older' navision releases by reading the document from Mark. (https://mibuso.com/downloads/index-hinting-whitepaper)

    However when I try with NAV 2016 and 2017, I don't see the results in the SQL application profiler or SQL extended events.

    Brian, did you check the sql commands were containing the 'index hint' ? :
    ...WITH (READUNCOMMITTED, INDEX("$3"))....
Sign In or Register to comment.