performance issue in NAV 3.7 & SQL SERVER 2000

bdurga

We are facing a perfomance problem for our client.

please find the following information for more detail:

Current System:

Application Version: Microsoft Dynamics Nav 3.7 (HF(IN) – 5IN, HF(WW) -19 )
Database Version: SQL Server 2000 (No service Pack)
No. of licensed user: 85
Online User: 45 to 50


We found through client monitor that there are frequent table lock in the following objects:

Structure Order details
Tax Detail Buffer
Structure Order line details
Document Dimension
Value Entry
GL Entry
Reservation entry
Gn. Jnl Line
Gn. Jnl Allocation
Item jnl Line


Due to the table lock entire application gets locked for all users and comes to a standstill situation and we have been forced to restart the server to resume the operation. This locking and restarting situtaion happens frequently and detrates the performance of the system. The frequency in which the locking occurs cannot be traced. sometimes even if more number of users(like 55 users) are accessing the database with different transaction then the system works fine but if more users are accessing the same transaction objects then the deadlock situation araises.
so please help us in resolving the case.
Average transaction per day for additional information starting 1st feb till 19th Feb:

Sales - 765
Purchase - 217
Journal - 193

During the problem, we didn’t find any difference in the CPU and PF usage of Servers and also no big difference in the size of SQL log file as well.


thanks in advance

regards,
durga

matttrax

Processing more than 100 orders an hour is bound to have some collisions somewhere. What is the average number of lines on your orders? That is more important than the number of orders total. If they only have a few lines it's not a big deal, but if they all have 1000 lines tables are going to be locked for longer periods of time.

Have you considered doing automated batch posting at night? You could still post your journal entries and orders that had to go out right at that moment and it would alleviate some of your problems.

kapamarou

Search this forum on performance. This topic is quite big and there are a lot of things involved to consider...

For example: Do you optimize your tables?

bdurga

thanks for your reply. To solve this issue we have decided to reindex the existing table indexes. Can any one suggest on the availability of any tool for reindexing, or the methodology to adopt reindexing in NAV.

Regards,
durga

bdurga

Dear friends,

For the performance problem we are facing in 3.7 Nav application. we are trying to improve the performance by altering some coding methods. In all our customization we have locked the table using the following coding pattern,

<record variable>.LOCKTABLE;
<set the filter if required using SETRANGE/SETFILTER>
<performed the insertion, or deletion using INSERT/DELETE,DELETEALL>
COMMIT;

can any one suggest if our application performance will improve if we change our lock table pattern as below using the SLEEP command.
<record variable>.LOCKTABLE;
SLEEP(2000); //Allowing the table or record variable to SLEEP for about 2000 milli seconds
<set the filter if required using SETRANGE/SETFILTER>
<performed the insertion, or deletion using INSERT/DELETE,DELETEALL>
COMMIT;

can any one suggest us will the usage of SLEEP command while locking the table will release the lock of a particular table in the defined time and relieve the application from dead lock situation.

Thanks in advance,

Regards,
B.Durgalakshmi

kapamarou

I think it will ... just delay your deadlock for 2 seconds...


Performance and deadlocks are to different but related issues.

For deadlocks you need to follow the same table locking order to avoid it.
Improving your performance means less time for a transaction, thus less deadlocks, but not zero deadlocks...

If you are on SQL the lock will be placed not with the locktable command but with the following
FIND / INSERT / MODIFY / DELETE command...

If you search this forum you'll find a lot of info on deadlocks and performance...