SQL 2005 and Navision 4.0 SP2 Update 3 Mysterium

Sorcerer

Hello out there,

right now i've got a new issue with our beloved navision....

first the facts: Navision DB 2.60, Navision Client 4.0 SP2 Update 3, SQL Server 2005 SP1 (Enterprise)

After converting the database from sql 200 and 3.70 to 2005 and 4.0 by recovering a navision backup into a new database we have a strange problem.

If the users try to set an Applied Entry into the lines (Journal or sales/purchase line) the entry form opens filtered by the account / item. now the form hangs and there is no chance to get back the client must be closed. so far so bad. if i try this from the same machine, client, computer with my account (same user rights / setup) the form opens fine the applied entry can be set without problems. the only difference between the other user is that i've created the database. even the db_owner role doesn't change anything. (now i've got to say that we have a pretty hughe database with round about 14 million g/l ledger entries and 7 million item ledger entries but that doesnt seem to be a problem for my account)

has anyone an idea how to avoid this problem? (sql jobs like update / create stats runs every night, rebuilding indexes every weekend)

thank you

ara3n

sounds like this problem


https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYKORSXPPNNWUNVVRWXPOUVYSWPSNUWVPUQUPSSVSPRMZRTNXK

Sorcerer

could you tell me what this kb article is about?
we are no partner so we can not view this.

thank you.

ovicash

Sorcerer wrote:

could you tell me what this kb article is about?
we are no partner so we can not view this.

thank you.

you could try this

Change the clustered index on Microsoft SQL Server
In Microsoft Dynamics NAV 4.0, use the key properties to determine which key is the clustered index on SQL Server. Microsoft Dynamics NAV 4.0 always adds the primary key to the end of a query. If you change the clustered index from the primary key to another key, SQL Server will be less likely to make query plans that include a clustered index scan.

For more information about how to use key properties, refer to the documentation in Performance Troubleshooting Guide for Navision.

bbrown

We have run into this issue with a technical upgrade (client only) of a large 3.60 database (~100 GB). We see the issue when scrolling thru the larger ledger tables.

Microsoft gave us the technote referred to above. None of the options provided a workable solution. Changing the clustered index just resulted in SQL doing a scan of the new index.

We found that removing the clusted index would help sometimes, but not always.

We are currently working with a MS Escalation Engineer on this issue.

ara3n

Hello bbrown
Could you keep us posted on this issue. One of my client is running into this issue. For them it's the customer table, just scrolling through it on the list form causes client to hang.

bbrown

Will do..

My original post is at http://www.mibuso.com/forum/viewtopic.php?t=13154&postdays=0&postorder=asc&start=0

The client is runing 3.60 native for their live system. They are running SQL in a test system. This performance issue has forced a delay in bringing SQL live. They have hit their busy season, so the cut-over probably will not happen until after the holidays.

Sorcerer

our database is about 100 GB so this issue seems to be a problem with big databases...

and we can say that it does not happen with client 3.70 and sql 2000.

but what i can not understand is that everything works fine if i use my account (database creator).....

bbrown

This issue is not limited to large databases. It is only more noticable. I have been able to duplicate this issue with a standard CRONUS database. The delay is only detectable with Client Monitor, but it is there. With a larger database the delay becomes noticable by the end-user.

ara3n

Yes this client has 500000 customers, so the wait just makes Navision client unresponsive.

bbrown

Our client has around 325,000 customers. They notice the issue more with the ledger entry forms (customer, inventory, G/L). They can scroll to the bottom quickly, but experience a delay when they reach the bottom and try to scroll back up.

A similar delay is seen when they reach the top and try to scroll back down. It can take over 30 seconds to move to the next record.

Sorcerer

ok. after our weekend jobs (optimizing keys out of navision => rebuild index, updating/creating statistics) it seems navision can be used now. the users can work again with the ledger forms.

i've also restarted the sql server because the tempdb has grown to 6gb!!

could anyone tell me if the problem would be solved if the database is set to compatibility level 80 (SQL 2000)?

bbrown

i've also restarted the sql server because the tempdb has grown to 6gb

Having a large tempdb is not a problem. SQL uses tempdb for its workspace (cursors, etc.). When a SQL is restarted tempdb is recreated at its orginal size and must expand again as needed. This expansion has the same impact as auto-expanding any other database file.

illug

Is the problem of slow scrolling when reaching the bottom and going back up solveg in SP3?

We are experimenting the same things here. For one table, it was solved by adding MaintainSIFTIndex for the key in question that had previosly been disabled.

Sorcerer

no. find more for this topic here:
http://www.mibuso.com/forum/viewtopic.php?t=13154