Is NAV using Dynamic and/or FastForward cursors?

pdj

Once again Microsoft has released a KB article which confuses me. Can anyone please enlighten me regarding this hotfix:
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;974798

All my NAV5SP1 customers are currently using build 27191 which uses Dynamic Cursors. We tried one of the first newer builds that went back to (partly?) using Fast Forward cursors, but we encountered so many errors we quickly gave up. (The WHERE clause sometimes didn’t match the filters…)
We would really like to switch back to Fast Forward cursors, as the Execution Plans with Dynamic cursors are often very poor. But does the above hotfix do that? I recall having heard something about NAV would still use Dynamic cursors “when a transaction is started”. Could someone please confirm this, and describe exactly when a “transaction is started”? I assume it refers to whether or not it is a READUNCOMMITTED or UPDLOCK statement, but would appreciate clarification.

Thanks in advance

pdj

Seems I'm missing something...
How can you optimize indexes without knowing which cursortype is used? :-k

David_Singleton

pdj wrote:

Seems I'm missing something...
How can you optimize indexes without knowing which cursortype is used? :-k

You need to either guess, or go back to an earlier version of Navision.

stryk

Hi!
Yeah, the many changes of cursor types really suck ](*,)
@MS: Please - give us back the good old "Fast Forward Cursors" [-o<

You could find out which cursor type is used by the NAV by running the SQL Profiler to trace the cursor-creation. This should look somewhat like that:

declare @p1 int
set @p1=1073741864
declare @p5 int

-- either:
set @p5=12304    -- Fast Forward (= 16 + 4096 + 8192)

-- or:
set @p5=12290    -- Dynamic      (=  2 + 4096 + 8192)

declare @p6 int
set @p6=8193
exec sp_cursoropen @p1 output,NULL,N'SELECT  * FROM "Navision"."dbo"."CRONUS 403$Item Ledger Entry" WITH (READUNCOMMITTED)   ORDER BY "Entry No_" ',1,@p5 output,@p6 output
select @p1, @p5, @p6

There should be some @Px=12304 or @Px=12290. 12304 means "Fast Forward" is used; 12290 shows "Dynamic" cursor is declared.
See also http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2009/02/20/cursor-types.aspx

pdj

stryk wrote:

Yeah, the many changes of cursor types really suck ](*,)
@MS: Please - give us back the good old "Fast Forward Cursors" [-o<

Couldn't agree more ](*,)

stryk wrote:

set @p5=12304 -- Fast Forward (= 16 + 4096 + 8192)
set @p5=12290 -- Dynamic (= 2 + 4096 + 8192)

Great, thanks a lot. =D>
However, I mainly need it when looking at result from this query:
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/01/04/simple-query-to-check-the-recent-performance-history.aspx
But it turns out Lars already updated it to include the cursor-type: :oops:
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/10/14/simple-query-to-check-the-recent-performance-history-ii-now-including-query-plan-information.aspx

stryk wrote:

See also http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2009/02/20/cursor-types.aspx

I had, but I didn't realise it could be used to reverse engineer the cursor type used by NAV.

David_Singleton

The thing that gets me, is that Microsoft keep saying how smart SQL is, that we should trust SQL and let IT decide the best plan to get data from the server. Yet the NAV team seem to want to go back to the Native days where the programmer decides.

I am completely lost as to why we are making such a huge step backwards.

Also can someone explain the "MaintainSQLIndex" and "SQL Index" fields in the Table designer. Since we can no longer use these, wouldn't it be safer to just remove them.

Belias

Also can someone explain the "MaintainSQLIndex" and "SQL Index" fields in the Table designer. Since we can no longer use these, wouldn't it be safer to just remove them.

they keep talking about these...
http://blogs.msdn.com/nav/archive/2009/ ... -uses.aspx
maybe they've in mind to make SQLIndex work... :-k

DenSter

Belias wrote:

maybe they've in mind to make SQLIndex work

What it looks like to me is that each time one of these "improvements" are implemented, NAV on SQL Server acts more like native NAV database server. Index tuning that used to be very effective has been rendered useless now, and in many cases have to be undone because NAV is forcing (sometimes unselective) NAV keys down SQL Server's throat.

Overall, in general, I guess performance improves, but specific cases can become much more difficult to troubleshoot without modifying code. It used to be that you could tune an index on SQL Server and performance would improve. Now you almost have to make a wholesale change in everywhere in C/AL code that a key is used. The issue that I have is that the choice is taken away, it would be nice to have the option to play around with it the way we used to.

kriki

DenSter wrote:

it would be nice to have the option to play around with it the way we used to.

Definitely!

garak

kriki wrote:

DenSter wrote:

it would be nice to have the option to play around with it the way we used to.

Definitely!

I'm absolutly the same opinion. I had here some big performance problems after changing a new customer from 4.03 to 5 SP1.
OK, there was also some stupid C/AL from the old NSC, but i could only optimized some performance problems (Standard C/AL) by tuning the indizes directly on the SQL Server enginge self.

pdj

But isn't the KB article about a switch back to Fast Forward cursors, and thereby back to taking advantage of a more selective SQLIndex?

David_Singleton

Belias wrote:

Also can someone explain the "MaintainSQLIndex" and "SQL Index" fields in the Table designer. Since we can no longer use these, wouldn't it be safer to just remove them.

they keep talking about these...
http://blogs.msdn.com/nav/archive/2009/ ... -uses.aspx
maybe they've in mind to make SQLIndex work... :-k

I think you missed the point.

Both these functions worked very well in earlier versions of Navision, but they have effectively been removed. Using "MaintainSQLIndex" and "SQL Index" it was possible to substantially reduce the size of a table, and thus dramatically increase the insert speed and thus reduce blocking.

This is no longer possible, since a SQL index must now cover 100% of a Navision key to work correctly.

In small and medium sized databases, this can be ok, but we need to have the option to let SQL decide its own execution plans for larger databases.

The main issue is that Navision has no mechanism for sorting, so it uses one "key" for both sorting and indexing. SQ on the other hand is able to separate these functions, and we should let it do what it thinks is best. The golden rule in SQL is keep indexes as simple as possible, but we can't do that.

I have a very large customer that has been waiting for NAV2009 SP1 to upgrade their old 4.00sp3 system. But now because of Dynamic Cursors we now need to rethink that plan. Since the performance on 2009 is just not acceptable.

Anyway in the end the biggest issue is stability. We go out and tune a database for a customer, we get them happy with a smooth system that has minimal blocking and the users are happy. The customer then reads that they should be on the latest version because its faster, but then they upgrade and the system is slower, so we need to throw away all the tuning work and start again. This is not fair to clients.

So if they are going to try something new, then at least give us a flag that we can use to tell Navision to use the old method when we need it. That way the client's system will not be slower when they upgrade.

pdj

David Singleton wrote:

I think you missed the point.

Hi David,
Have I done something to offend you, as you apparently don't read my posts.

David_Singleton

pdj wrote:

David Singleton wrote:

I think you missed the point.

Hi David,
Have I done something to offend you, as you apparently don't read my posts.

:-k I think I read you posts. :oops: Not sure how I offended you ?

My remark was to Belias where he says "maybe they've in mind to make SQLIndex work... ". But my point (which I think he missed) was that those features worked in older versions of Navision, so no need for MS to make the them work, just give us back what we had.

To me its just bizarre, there are cases where Dynamic cursors are best and there are cases where Fast Forward cursors are best, and SQL is smart enough to know when to use which, so lets leave it up to SQL to decide.

David_Singleton

pdj wrote:

But isn't the KB article about a switch back to Fast Forward cursors, and thereby back to taking advantage of a more selective SQLIndex?

As to the KB article, I would not trust it, since it seems to mix up two issues. One minute they are talking about Cursors and the next saying that the error is related to resizing on a sales budget form. Also it doesn't say under what circumstances it uses Fast Forward. I would assume that really this is just the fix to allow Fast Forward in forms to allow for users filtering without the correct key.

But the article is very unclear and pretty much unusable since it mentions the error, but not what they do to solve it.


So to answer this question. I really don't have a clue, which is why I originally did not comment on the KB (not because I was ignoring you).

But I do not see any indication what so ever that MS are moving back to letting SQL decide its own execution plans. I think we need to contact every one we can, and get the word out that this is a wrong move and needs to be changed.

pdj

David Singleton wrote:

Not sure how I offended you ?

Well, I was just getting frustrated that nobody was discussing the KB article the post was supposed to address, but you were apparently just as confused as I was after reading it. :-)

You (and others) keep talking as if the SQLIndex and MaintainSQLIndex properties suddenly don’t work. I hope we agree that they both “work”, it is just the consequences of changing the SQL Index that is different due to the Dynamic Cursors. I am still customizing the SQL Index to optimize, so I disagree that we never should do it. We should just use more caution.

David Singleton wrote:

To me its just bizarre, there are cases where Dynamic cursors are best and there are cases where Fast Forward cursors are best, and SQL is smart enough to know when to use which, so lets leave it up to SQL to decide.

Really? Are you saying that specifying a cursor type, is like specifying an Index Hint to overrule SQL’s own logic? I have looked at MSDN and found this article http://msdn.microsoft.com/en-us/library/ms712466(VS.85).aspx This is new to me but seems like a good idea. I just fear it will require further specification before each FINDSET or similar cursor operation.

David Singleton wrote:

As to the KB article, I would not trust it, since it seems to mix up two issues. … But the article is very unclear and pretty much unusable since it mentions the error, but not what they do to solve it.

This is the case with almost all the KB articles about the run-time changes in NAV. Extremely frustrating. :evil:

David Singleton wrote:

But I do not see any indication what so ever that MS are moving back to letting SQL decide its own execution plans. I think we need to contact every one we can, and get the word out that this is a wrong move and needs to be changed.

I have heard from an MS supporter that they are changing back to Fast Forward cursors, but only partly. I have e-mailed him for clarification, but I haven’t got any response. I therefore hoped someone in here knew something.

David_Singleton

pdj wrote:

I have heard from an MS supporter that they are changing back to Fast Forward cursors, but only partly. I have e-mailed him for clarification, but I haven’t got any response. I therefore hoped someone in here knew something.

I think you will find this is just for GUI requests as I mentioned in my post above.

pdj

David Singleton wrote:

I think you will find this is just for GUI requests as I mentioned in my post above.

Ok, I was told that is was "until a transaction had started". That didn't make any sense for me, but you might be right. I have only tried two builds after 27191, and they had so many errors I decided to stick with the rather old build. Now I'm just tring to decide if it is time to give it a go again...

Belias

@David:
That's why i supposed sqlindexes were not working 100% correctly...
:-k http://blogs.msdn.com/nav_developer/archive/2009/04/10/beware-the-sql-index-property-on-nav-5-0-sp1.aspx

David_Singleton

Belias wrote:

@David:
That's why i supposed sqlindexes were not working 100% correctly...
:-k http://blogs.msdn.com/nav_developer/archive/2009/04/10/beware-the-sql-index-property-on-nav-5-0-sp1.aspx

No this is the issue. These functions used to work, in earlier versions of Navision, but don't work with the newest versions.

Belias

David Singleton wrote:

Belias wrote:

@David:
That's why i supposed sqlindexes were not working 100% correctly...
:-k http://blogs.msdn.com/nav_developer/archive/2009/04/10/beware-the-sql-index-property-on-nav-5-0-sp1.aspx

No this is the issue. These functions used to work, in earlier versions of Navision, but don't work with the newest versions.

#-o !!!eheh, what a fool...maybe i have to read more carefully...then the article i posted before (http://blogs.msdn.com/nav/archive/2009/ ... -uses.aspx) doesn't say anything about the possibility to make SQLIndexes differents from the original nav index...ok, now i got it, thanks for the explanation

pdj

pdj wrote:

You (and others) keep talking as if the SQLIndex and MaintainSQLIndex properties suddenly don’t work. I hope we agree that they both “work”, it is just the consequences of changing the SQL Index that is different due to the Dynamic Cursors. I am still customizing the SQL Index to optimize, so I disagree that we never should do it. We should just use more caution.

David Singleton wrote:

These functions used to work, in earlier versions of Navision, but don't work with the newest versions.

Why do you keep "blaming" the properties? The problem is not the properties - the problem is the cursors...

David_Singleton

You misunderstand. I am saying these functions don't work, though maybe "are not usable anymore" or "do not serve the purpose they were originally intended for" or "USE WITH EXTREME CAUTION".

The reason they don't work is because Navision is forcing (or at least strongly suggesting) to SQL that it use Dynamic Cursors.



We are agreeing here trust me.

pdj

David Singleton wrote:

We are agreeing here trust me.

No worries then 8)

DenSter

pdj wrote:

Why do you keep "blaming" the properties? The problem is not the properties - the problem is the cursors...

Technically yes but if you look at it from a "cause and effect" point of view (Adding simple and selective SQLIndex causes faster queries) then you can't deny that this "doesn't work" anymore. The SQLIndex property is still there, and it does what it is supposed to do as far as setting the index on the SQL Server table object. However, it is now more likely to cause even further performance problems. To me that is a step backward.

We used to be able to significantly tune an entire database (which came down to tuning about 20-30 tables) in about 2-3 days. All we did was analyze the indexes, modify them (i.e. simplify them in most cases), and we had huge successes and gains in performance. Now you have to match indexes on SQL Server with the key on NAV, so if you have to make keys more selective, you have to add the key, find all the code that reference that key and modify that too. Anyone who'se ever done a field length increase knows how much time this takes.

It is not fair to the customers, especially to those who have invested in tuning their databases. We should have had the option, this should be a database setting, or even a table setting.

pdj

Yes, it seems we all agree.
If I should "defend" the usage of Dynamic Cursors, it would be someting like this:
"It improved the results in the Application Benchmark Toolkit in the Cronus DB!"
This is just useless for the partners in the real world, and is making me wonder if Microsoft finally is forcing customers with more than 80-100 users to AX by putting these handcuffs on NAV. :-k