NAV 2016 - Index Hinting?

bbrown

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?

bbrown

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.

kriki

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.

Slawek_Guzek

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

bbrown

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.

Slawek_Guzek

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

bbrown

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.

Slawek_Guzek

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...

Marije_Brummel

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. ;-)

bbrown

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.

Filip_Crombez

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"))....