Acute table locking problem in navision 4.0 SP3 even with good hardware resources

Business Central/NAV Technical Forum
1

Hi,

We are facing problem wherein navision gets hanged or extremely slow during Posting of Sales Order and Transfer Orders, very often (around 95 % times) resulting in error of table locking on Document Dimension and Structure Order Line Details tables; wherein user has to start posting of document all over again. This problem is occuring almost on routine basis during peak hours when warehouse users try to post documents with lines ranging from 100 to 2000. If in case a transfer order with 1500 to 2000 lines is to be posted, than it is certain that all other users will get table locking error for 5 to 12 minutes. It is something unacceptable with such highend hardware and software resources as mentioned below: -

Processors: - HP Blade server with Intel Xeon E7420 @ 2.13GHZ (4 Processors)
RAM: - 32 GB With 25.5 GB Allocated to SQL Server
Operating System: - Windows Server 2008 Enterprise, Service PAck 2, 64 Bit
Nav Version: - Navision 4.0 SP3
DBMS: - SQL Server 2005 as DBMS
Number of Users: - Approx 125 Concurrent Users
Database Size: - 110 GB

Current Table Status
Table No. Table Name No. of Records Record Size Size (KB)
32 Item Ledger Entry 3975861 7,143 27734576
357 Document Dimension 948578 370 343016
13795 Structure Order Line Details 3836374 1,017 3811688
5741 Transfer Line 12672 31,326 387664
37 Sales Line 251328 11,435 2806544
110 Sales Shipment Header 30452 1,884 56032
111 Sales Shipment Line 829937 2,200 1783128
112 Sales Invoice Header 99831 2,031 197960
113 Sales Invoice Line 1140196 3,087 3437088
5744 Transfer Shipment Header 55563 635 34464
5745 Transfer Shipment Line 453366 1,677 742472
5746 Transfer Receipt Header 55539 700 37976
5747 Transfer Receipt Line 459295 1,671 749384

Thanks in advance for the Help…

2

Hi Shiv,

there are lots of things you need to look at.

1/ Hardware. Most Navision systems I see with perfromance issues have the server setup incorrectly. If you explain your hardware setup it would help to diagnose.

2/ From table 13795 I would assume you are running Indian version. This has some issues in the older version.

3/ You need to give some indication of what you are doing to cause the blocking. How is Navision setup and what is happening when you post.

4/ The really big thing. What customizations have you done or had done to the system.

Basically as a comparison imagine I asked a question like this:

“Hi, I have a very fast car (200kmh top speed), but it takes me a long time to drive to work in the morning. Can you tell me what is wrong”

How would you answer that?

3

Regarding the “Blocking Issues”: maybe this could help you to fnd more details about the blocks - and hopefully some inspiration to solve them:
http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

Regards,
Jörg

4

Hi David,

Thanks for your expert input… You have rightly pointed towards correct aspect in point 1. We are already in the process of studying the hardware resouces as database is kept at SAN drives so are checking out whether the drive is on RAID 01 or RAID 5 and there are two kind of drives, in which 1 is comparatively slower than the other one…so may be thats causing the delay…+ m going through the customizations part as well to find out if some bad code is creating so much of problem. Will update you soon with updated details…

5

Hi Jorg,

I have started studying your blog…will definately be giving it a try…will update you soon with the results… Thanks…