Item Ledger Entry Locking & server proformance

aniruddha_majumdar

Hi All,

I am facing a problem of ILE locking and looking for a solution from experts.


The scenario is:
The deployment here is of LS retail NAV where lots of serial no. based items are involved.

In Production Server while posting the Item Journal with single serialized Item, quantity 1,00,000, the system is taking more than 20 hours to complete the posting.

But similar transaction in Test Server it's taking 5 hours to post the Item Journal.

Configuration of Production Server as follows:
1. Xeon Processor 3.16 GHz (2 processors) 32 bit
2. RAM : 16 GB
3. System Type : 32 bit OS
4. OS is Windows Server 2008 R2 Enterprise

Configuration of Test Server as follows:
1. Xeon Processor 2.6 GHz (2 processors) 64 bit
2. RAM : 12 GB
3. System Type : 64 bit OS
4. OS is Windows Server 2008 R2 Enterprise

Database : SQL Server 2005 Standard Edition

Please let the possible solution to reduce the time of processing , also is it due to difference of 32bit machine and 64 bit.

bbrown

There are lots of things that could be contributing to this problem. Among them, but not limited to, is the 32 vs. 64 bit. Also what is the disk configuration of each system?

bbrown

Is the SQL also 64 bit on the 64 bit OS system?

kriki

Test server 32 bit and production server 64 bit=?????????????????
If you don't use AWE, you have thrown away all the ram above 4GB!

Best put all 64 bit on that server!

you also need to check the configuration of the drives (64KB offset, 64KB cluster size, 64KB stripe size). use RAID10 (or RAID1 for TL-file) and NOT RAID5. Separate DB-files from TL file. The only files on those disks should be the DB-files (or the TL-file).
The only thing the server should do is SQL server. Nothing else should be done by that server.

While posting several KB of ILE, when you notice it is slowing down, run the statistics on the posting tables. If it doesn't help, run the rebuild index on those tables.
While posting these records (and doing nothing else), you can disable the indexes/SIFTS that don't serve and re-enable them after the posting.

aniruddha_majumdar

Hi Kirki,

Thanks Kirki for suggesting few performance enhancing areas.

I changed the MaintainSQLIndex & MaintainSIFTIndex property for few keys in ILE to NO and the system now able to post 45000 serialized item using Item Journal in 9 mins. But, system takes sudden change in its performance when the record is increased to 50000 serial items, the system now takes approx 2 hours to post.

Is the problem with NAV or SQL? Is NAV is having limitations on the no. of records it process?

Also, can you tell me how to run the statistics on Posting tables & rebuild indexes. Is these more from the SQL server front?

Thanks & Regards,
Ani

kriki

Probably it is because the indexes/Tables get too fragmented or the statistics are outdated.

To update the statistics on a table (you need to change the company part of the table and also update some other tables and you should also do that for the indexed views on the tables):

UPDATE STATISTICS MIBUSO2009SP1.dbo.[Your Company$Item Ledger Entry] WITH FULLSCAN;

If this doesn't help, try to rebuild the table (instead of updating the statistics, you can do this).
The easiest way is through NAV: File=>Database=>Info=>Tables=> find your table and optimize it. It is not perfect but it is good enough.

For a customer we had to post about 2MB of item ledger entries. Doing some tuning on the indexes, I could post around 50 item journal entries per second (depends on the server and a lot of other things).
I created a form that updated every 3 seconds and checked how many records there were. So I could calculate the no. of records posted per second. When I saw it was going down (around 40), I did a rebuild index through NAV on the fly.
PS: I had put the locktimeout in NAV at a few minutes for doing this.
PS2: I haven't posted all the records in one go, but posted in batches of about 20 records to be able to run the rebuildindex in between.

aniruddha_majumdar

Hi Kriki,

My previous data analysis based on 45000 and it's timing is actually not constant and can vary abruptly. The posting of same volume of Item Journal data of 45000 serial no. items at the peak hour when lot of transaction happening for several quarters and database taking now 25 mins to process even 10,000 serialized item.

To inform you here that customer is having ERP(MSD AX) & LS Retail NAV SQL database server in the same box.

Please suggest other areas of performance tuning.

Ani

kriki

What is the diskconfiguration?

Best would be: 1 big RAID10 for all databases and a RAID1 for each transactionlog.
And enough memory for all! 32 bit is NOT good!

In short, I am afraid that tuning will not be possible if you don't rebuild your server using 64bit software, correct diskconfigurations.

aniruddha_majumdar

Hi Kriki,

Client is now upgrading the system to a 64bit machine and they have SAN for data storage. Do you any statistical data of posting 100,000 or more serial item.

The difference here is we have 2 to 3 extra tables of LS Retail NAV getting impcated.

Regards,
Ani

kriki

Sorry for the late answer, but I have been in vacation.

I don't have any statistical data for posting 100.000 items. But that can vary a lot depending on hardware and settings in NAV.