Microsoft Belgium has worked out an entire agenda on Dynamics NAV related topics.  These topics will be handled in a series of evenings (MSDN and Technet Evenings).  I already received the agenda of these evenings:

All topics will be presented by Steven Renders from Plataan.  Also active in the community with their Knowledge Blog.  For me, this means that it'll be worth the effort! .

Practical details for all sessions:

• Registration Time: 18:00
• Start Time: 18:30
• End Time: 20:30
• Language: English
• Location: Microsoft Belgium, Calliganlaan 1, Diegem 1831
  

I asked Microsoft to see if these evenings can be recorded in some way.  Then I can put it online as well.

To all Belgians: don't miss this opportunity !!

In 4.0 SP3 update 6, C/SIDE enables indexhinting by default.  This is quite a hot topic in the community at the moment:

• Mibuso
• Sustained engineering team Blog
• And also some things on my blog... .

Now, it is quite easy to go to the old situation, where indexhinting was not default. 

What do you need to do:

In Enterprise Manager (SQL2000) or SQL Server Management Console, create the a table with following statement:

CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)

GRANT SELECT ON [$ndo$dbconfig] TO public

With this next statement, you disable the indexhints:

INSERT INTO [$ndo$dbconfig]

VALUES ('IndexHint=No')

Now, the old situation is restored.  If you want, can add indexhinting one by one by statements like:

INSERT INTO [$ndo$dbconfig]

VALUES ('IndexHint=Yes;Company="DEFAULT COMPANY";Table="Vendor";Key="Search Name";Search Method="-+$";Index=1')

For this, just read the guidelines of the blog mentioned above.

You see that it's quite simple. 

I Hope this article is useful to you.

Review:

One of the discussed new features in update 6 is the fact that the new client (build 24143), is creating indexhints by default.  The official hotfix article (which you can find here) doesn't explain much:

"The SQL Server Option for Microsoft Dynamics NAV 4.0 Service Pack 3 does not provide index hints for all queries.

This problem occurs because you have to enable the "index hint" function. By default, it is disabled."

I'll try to explain with a very simple peace of code:

recCustomer.SETCURRENTKEY("Search Name");

recCustomer.FINDFIRST;

Normally, this would produce this SQL statement:

SELECT TOP 1 *,DATALENGTH("Picture") FROM "400SP3_NAV_TEST"."dbo"."DEFAULT COMPANY$Customer" WITH (READUNCOMMITTED)   ORDER BY "Search Name","No_"

Sometimes, when NAV won't take the right key, you have some options to change this.  One of these options (some kind of last resort) was index hinting.  I explained this in a previous blogpost.  You can find it here.

Now, in 4.0 SP3 update 6, index hinting is enable by default.  What does this mean?  Let's take the same query:

recCustomer.SETCURRENTKEY("Search Name");

recCustomer.FINDFIRST;

The result is now:

SELECT TOP 1 *,DATALENGTH("Picture") FROM "400SP3_NAV_TEST"."dbo"."DEFAULT COMPANY$Customer" WITH (READUNCOMMITTED, INDEX("$1"))   ORDER BY "Search Name","No_"

It adds the indexhint in the SQL query by default.  What does this mean?  Well, I'm not a SQL junky like some others in the community, but this is what I think it means:  Sometimes, it's better to take another index than the one you're ordering your data.  In SQL , you just don't create indexes for sorting.  Just for retrieving data.  Now, SQL will not have a choice … it IS going to take that same index, whether there is a better one for that specific query or not.

By the way … this is naughty.  Suppose even a more simple peace of code:

recCustomer.SETRANGE(City, 'Beerse');

recCustomer.FINDFIRST;

C/SIDE will generate an indexhint anyway:

SELECT TOP 1 *,DATALENGTH("Picture") FROM "400SP3_NAV_TEST"."dbo"."DEFAULT COMPANY$Customer" WITH (READUNCOMMITTED, INDEX("DEFAULT COMPANY$Customer$0"))   WHERE (("City"=@P1)) ORDER BY "No_"  

So, even though no sorting is specified … it IS going to create an indexhint.  Based on the clustered key?  NO!!  Based on the primary key.  Suppose you change the clustered key to another one (I took "Search Name"), it will still hint the same key.

I have been looking for a property on SQL Level and on C/SIDE level to be able to find a switch to turn it off, but nothing to find:

• In the "Alter Database" window
• In table "$ndo$dbproperty"
• In table "$ndo$dbconfig" (I couldn't even find this table, so it's working in a whole other way then before (again, see my previous post here))
• In properties on table level
• In properties on field level
• In properties on key level

Suggestion:

I have a suggestion.

Why not KISS ?  C/SIDE, Navision, … it has always been about keeping it simple.

Why not just create an extra property on key level (like there are a bunch since 4.0SP1) called "Hint Index" (boolean).  Then, you would have these SQL properties on key level:

• Clustered
• MaintainSQLIndex
• MaintainSIFTIndex
• SIFTLevelsToMaintain
• Clustered
• SQLIndex
• Hint SQLIndex

Only when the boolean is true … generate the indexhint.  Very simple, very straight forward, very NAV.  In the next phase, the default NAV database could be set up that was that most scenario's work (like the way the other properties have been set up since version 5.0 ).

Only a suggestion.  Probably others will have better suggestions.  Please post'm all!

 *** UPDATE!  ***

MartinN linked this page, where all info is displayed.  If I had seen this before, I wouldn't have written this post… .  Anyway … Important to know that there is a way to turn IndexHints off.  For further details, I'll just forward you to the "Microsoft Dynamics NAV Sustained Engineering Team Blog".

The KB article has been released.  You can find it here.

Nice thing to know is that this hotfix accumulates all previous hotfixes.

Here is a list of most important problems that are corrected:

• NAV crashes when emailing from contact card (office2007)
• Printing a large report with image in the header: "There is not enough memory to execute this function"
• When you deleteSIFT records, tot totals are calculated incorrectly
• Design problems on request form
• 4.0SP3 was incompatible with latest version of dw20.exe .
• Budget: an amount with more than 14 digits
• NODBC problems when upgrade from Sp2
• Database test, saving to a file or event log results in error message.
• RecordID variable as parameter restulted into an error.
• When using a dataport on a nonexisting option string: NAV didn't respond anymore
• Clicking a value in a flowfilter: nothing happened.
• Did not provide index hints for all queries (this is one that has to be investigated)
• No special characters in company name

It is recommended that you should only apply this hotfix to systems that are experiencing one of these specific problems!  You should contact Microsoft to get the hotfix.

Source: Microsoft

There is still no official KB article available, but Mark Brummel and Hynek found out that indexhinting is enabled out-of-the-box.  I want to refer to Mark's post on Mibuso: http://www.mibuso.com/forum/viewtopic.php?t=20831 .

This would mean that you don't use the power of SQL Server .. it's just simulating the behaviour of the C/SIDE database.  Be careful with this.