Update 6 for Navision 4.0 SP3: BE CAREFULL

Marije_Brummel · 2007-09-14

This week Microsoft released update 6 for Navision 4.0 SP3. Mainly a lot of SQL fixes, or so they say.

At first I thought that it was a combination of update 1, 4 & 5 but that seems not to be the case.

What they seem to have done is enable indexhints out of the box. This was already possible in older versions but it was by default disabled.

Be aware that installing this update without profesional help can lead to very unexpected behaviour.

It is indeed true that you can work around the SQL2005 bug with an index hint. But in the cases I have seen it usualy takes 4 or 5 to make the system work. The Microsoft solution can be a "little" overkill.

The update comes without documentation but there is some. Please contact your local Microsoft hub or SQL Perform for more information.

Still we need to put as much preasure as possible on Microsoft to keep working on better SQL support.

ajhvdb · 2007-09-14

Thx, for this warning. For every update/hotfix there should be a separate topic or forum with this kind of info.

DenSter · 2007-09-14

So instead of actually addressing the issue, it's another thing to make it behave like C/SIDE...

Is the SIFT issue at least solved?

Waldo · 2007-09-14

BlackTiger wrote:

Link to PartnerSource?

There is no partnersource link (yet) and no kb article (yet).
See my blog to get a link to the page where you can ask for the update... .

Marije_Brummel · 2007-09-14

https://mbs.microsoft.com/knowledgebase ... YYWSZOSTVP

Here is the link.

pdj · 2007-09-18

The link seems broken? I'm unable to find anything using this link nor the knowledge base article no. from your blog (940718)

Waldo · 2007-09-18

Seems to be broken as well for me ... yesterday it went fine :-k .

Meanwhile, you can check my blog for summarized info.

pdj · 2007-09-18

Waldo wrote:

Meanwhile, you can check my blog for summarized info.

Been there, done that... 8)
And I have also requested the file, but would just like to get the full specs.

Waldo · 2007-09-18

pdj wrote:

Waldo wrote:

Meanwhile, you can check my blog for summarized info.

Been there, done that... 8)
And I have also requested the file, but would just like to get the full specs.

Is very strange indeed. I got my info from those full specs ... and now I can't reach it anymore. Let's wait and see

.

pdj · 2007-09-18

I guess they found out they needed bigger warning signs for the default index hinting "feature"...

Waldo · 2007-09-18

pdj wrote:

I guess they found out they needed bigger warning signs for the default index hinting "feature"...

Well ... if that is the case .. I'm looking forward to hear their statements

.

pdj · 2007-09-18

It is back on-line and here is the "description" of the index hint change:

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.

:-k

DenSter · 2007-09-18

Yes, so now, with update 6, they turned it ON by default, which will make an even bigger mess.

Waldo · 2007-09-19

I took an hour to test it, and put my findings into a blog post here.

Here is the text:

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!

pdj · 2007-09-19

Great post, just a few comments or suggestions.

Waldo wrote:

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.

Well, since the code has to work the same for Native and SQL it has to assume you want to have it sorted by primary key in this example. That makes it ok, or atleast consistant...

Waldo wrote:

Why not just create an extra property on key level (like there are a bunch since 4.0SP1) called "Hint Index" (boolean).

I don't think it would be a good idea to make it a boolean. That assumes the index is maintained on SQL. I have several keys just for form and report sorting that isn't maintained by SQL. For these keys I would like to decide which other key it should use. I also have keys that are quite different on the SQL and there I might want a completely different key. So in short I would just like to decide which index (if any) it should index hint.

But all of this shouldn't be nessesary - SQL2005 should be clever enough to find the right indexes to use. SQL2000 was pretty good at it...

Waldo · 2007-09-19

pdj wrote:

So in short I would just like to decide which index (if any) it should index hint.

Well, I absolutely agree. It is not as easy, but it is definitally more flexible... .

Waldo · 2007-09-19

I was just informed by a much better blog about the IndexHint property. =D>

I have a feeling that the author is part of the design team, so I hope I deserve a break

.

cnicola · 2007-09-19

OK I am a little lost now.
The title of this thread is related to Update 6.
But reading the MS blog that waldo linked, it does not mention just update 6.
So :

1. This index hinting thing is only active by default for Update 6?
2. Since now we have 5.0 already, is index hinting on by default in 5.0 as well? I don't think so since I don't recall seeing the index hint in Profiler.
But then why add this rather distinct functionality to only one minor version of Navision? ](*,) (getting a headache already

)

DenSter · 2007-09-20

Very valid questions.
1: yes, it was put in place in update 6 as a "fix" for a little bug (with big consequences) that SQL Server was using a cached index for wrong purposes. We've been able to address the problem by turning on the index hint for just the ones that had the issue, and now MS has turned it on by default for all queries. The problem is that it simulates NAV behavior on SQL Server, and that is not really the right way to approach this in my opinion. It forces SQL Server to use certain indexes for certain queries, and it takes away some of SQL Server's very efficient query optimizer.

It's not really a big problem though, because we have the option to turn it back off, as shown in the blog. It's also really too early to tell if my skepticism is founded, but I've already heard a customer say that their database slowed down after installing update 6 on a test box.

2: no, this has not been "fixed" for 5.0 yet, because the problems came to light after 5.0 was already released. I have not seen or heard anything official from Microsoft, but I would assume that they are working on an update for the same issues in 5.0

By the way, Update 6 also includes a much more urgent issue that fixes a problem where SIFT did not always total correctly.

Waldo · 2007-09-20

cnicola wrote:

OK I am a little lost now.
The title of this thread is related to Update 6.
But reading the MS blog that waldo linked, it does not mention just update 6.
So :

1. This index hinting thing is only active by default for Update 6?
2. Since now we have 5.0 already, is index hinting on by default in 5.0 as well? I don't think so since I don't recall seeing the index hint in Profiler.
But then why add this rather distinct functionality to only one minor version of Navision? ](*,) (getting a headache already
)

You can read on the blog:

Index Hinting in Platform Update for Microsoft Dynamics NAV 4.0 SP3 KB940718

That platform update is "Update 6".

By the way, Update 6 also includes a much more urgent issue that fixes a problem where SIFT did not always total correctly.

This is actually the reason why you "should" install it ... and that's why I was looking for a way to get rid of the indexhinting.

cnicola · 2007-09-20

I know about the SIFT fix and that is why I was waiting for that damn thing also.
However after 2 months of SQL tuning I am a little reluctant to install something that may require me to revisit all that work.
I know, I know I can just disable index tuning but frankly all this SQL tuning fun gets old after a while ](*,) (especially when you are a consultant and not their internal resource. Try having 3 clients on SQL all with huge transactions volumes and keep all of them straight :sick: ).
Anyway enough ranting. Another dumb question: does 5.00 as it stands now have the SP3 (prior to update 6) issue with SIFT? (I am sure you see where I am going with this :oops:

).

kriki · 2007-09-20

Waldo wrote:

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!

I think better would be a new command for indexhinting. I can think of times that you wouldn't want indexhinting using a certain SETCURRENTKEY.
So I would say something like this:

RESET;
SETCURRENTKEY(....);
SETINDEXHINTING(...); // it would be nice to have this one!
SETRANGE(...)
....

And of course, the same thing as property on forms, dataports,reports.

mrQQ · 2007-09-21

So correct me if i'm wrong: if we update to this version, to preserve exact same behaviour as previous versions, we have to

INSERT INTO [$ndo$dbconfig] VALUES

('IndexHint=No’)

on each installation. Correct?

Waldo · 2007-09-21

Well, that should indeed be the case, but I haven't tested it out (yet).

Waldo · 2007-09-21

Indeed .. tested and approved.

mrQQ · 2007-09-21

Good. Or actually, not really, because i wan't to update our development clients, because they crash far too often, and this means we have to update a whole lot of dbs aswell

kriki · 2007-09-21

mrQQ wrote:

So correct me if i'm wrong: if we update to this version, to preserve exact same behaviour as previous versions, we have to

INSERT INTO [$ndo$dbconfig] VALUES

('IndexHint=No’)

on each installation. Correct?

And what do we need to do if we want indexhinting for a certain query?

mrQQ · 2007-09-21

as this blog entry states:

As mentioned earlier, you can disable index hinting for the entire system and then enable it where appropriate for your application.

but then it fails to give an example of how to do it..:(

oh, and it seems this update wants to convert sql db to new version, so it won't be opened with pre-update client, correct?

DenSter · 2007-09-21

Just keep reading all the way down, you will see as many as FIVE examples of how to turn it back on.

mrQQ · 2007-09-21

so does that mean that i have to enter one line IndexHint=No, and then further lines to enable them for special cases..?

DenSter · 2007-09-21

That's correct

Update 6 for Navision 4.0 SP3: BE CAREFULL

Comments

Categories