SQL table locking (Nav 4.0)

seckpin

I know that the problem of table locking has been discussed before and it was mentioned that some performance tuning will minimize this problem.

My question is: is the table locking for Navison with SQL DB an inevitable problem? Is it possible to resolve it totally?

Our company (in retail) has high volume of transactions each day and the table locking problem is making the users extremely frustrated. The explaination that this problem is due to many users posting transactions at the same time isn't acceptable to them. Some even commented that the other better ERP system such as SAP doesn't have such problem, and what kind of ERP system doesn't support concurrent users.

Is this really an unsolveable problem until version 5.0, which was supposed to work better with SQL?

diptish.naskar

Its true that NAV 5.0 can communicate better compared to the other versions of NAV, but this fact is yet not fully established. With respect to the table locking in 4.0. Yes you can remove the error fully if you know how to tune the database properly. For this I think you should get in touch with an expert who can do the tuning properly.

kriki

If they are posting transactions (e.g. posting sales lines or purchase lines or item journal lines or G/L journal lines), they will always have the problem of tablelocking. This because before writing a new entry in the ledger entry-tables, Navision does a tablelock (in SQL=lock the records that are read AFTER the TABLELOCK-command) and reads the LAST record and then it adds 1 to the "Entry No." to create the next entry. This makes that it is not possible to parallel posting.
But SQL tuning will help a lot to decrease the time of the posting.
Another possibility is to write all the info of the users in another table which can be done in parallel and then a NAS that reads the table and posts it.

kriki

[Topic moved from Navision forum to SQL General forum]

stryk

Hi all!

Well, I know this is somewhat a kind of "nit-picking", but from a SQL Server point of view one nearly impossibly gets a real "Table Lock" = TABLOCK X with NAV; the real physical locking-extents are lower, basically we are talking about RANGE Locks ... anyway ...

There are several ways to reduce blocking issues, starting with SIFT Optimization (most problems here are not caused by the Ledgers but by the SIFT tables behind!), optimizing the Clustered Indexes, etc. and of course: optimizing the C/AL code.
Here PLENTY could be done, just to mention one crucial thing: avoid LOCKTABLE! Even though not the full table is locked, the transaction is SERIALIZED and UPDATELOCKS are set, finally resulting in blocking problems.
LOCKTABLE should only be set where it is really necessary, e.g. when performing postings ... anything else should be thoroughly investigated ...

Regards,
Jörg

seckpin

Thanks for everyone's input.

Yes, i've read from the forum and listened to the MS webcasts about disabling SIFT to improve the performance. But when we raised this up to our consultants, the reply was that we can't do that because we have a lot of reporting needs and all the indexes are needed for those reporting.

I do not know SQL enough to understand whether or not they are right, but our own DB Analyst has done a check on our Nav SQL DB usage and said that our indexing ratio is 30%, which is way too high for any DB design.

In addition, i thought that there's always a trade-off between operational needs and reporting needs, and the former should always be given priority especially we are in the retail business. If we can improve the system performance on the day-to-day operations by forgoing the reporting performance, then this is the way to go. Furthermore, for reporting needs, we can always make use of other tools such as Crystal or data mining tools. Is my such understanding incorrect?

We are having a tough time convicing our consultants to re-look at the indexing, while they are also having difficulties in fixing the performance issue. So what we have now is the never-ending table locking problem (especially during month-end closing period) that leaves us with lots of disgruntle users. And on top of that, though not on the same topic, our update analysis view process is also taking more than 7 hours to run, which somehow i think may also be caused by SQL tables not being optimized.

stryk

Hi!

Hmm, this sounds somewhat frustrated ... please have in mind: the problems CAN be fixed!

... the reply was that we can't do that because we have a lot of reporting needs and all the indexes are needed for those reporting...

Honestly, this does not sound like your consultants know what they are speaking about. A SIFT level is something quite different than an index.
Of course it is possible to find out which SIFTs and Indexes are needed, but I doubt your consulants have done this research ...

According to SIFT please read this thread: http://www.mibuso.com/forum/viewtopic.php?t=18933

An Index-Ratio of 30% is standard NAV!

Which SQL Server version are you using? With 2005 it is possible to determine precisely which indexes are used/required and which not.. Reducing the number of Indexes (and SIFT levels) will decrease blocking problems!

Another reason for "blocks" are bad execution plans - Index Scans instead of Index Seeks. To avoid this, the OPTIMAL indexes should be created, starting with optimized Clustered Indexes, which of course requires a thorough investigation.

So, there are lots of things to do to improve performance!

Greetings,
Jörg

David_Singleton

Hi Seckpin,

firstly all ERP systems have locking. Irrespective of what one poster said above, it is not possible to remove locking from Navision.

Your aim thus is not to eliminate locking, but to reduce it. You have said that your Consultant is not willing/able to reduce the locking, and on this I agree with Jorg that you need to start looking further. You don't need to do anything as drastic as changing your partner, but you should at least look at getting in a Dynamics Freelancer to come in and look at the system.

There are a large group of us freelancers out there, and we work together to help in situations like this.

The remarks you make in your post are all reasonable expectations, and it should be possible for you to have a smooth systme with vry minimal locking.

DenSter

First of all, every database management system performs locking, EVERY database management system does. MS Access, Oracle, SAP, Progress, they ALL perform locking. It is necessary to lock resources to manage database integrity.

It becomes a problem when the locking is perceived by the users as a problem. For one user a 2 second lock is not an issue, when for another a half second lock is an insurmountable problem. In other words, it depends on how you use the system.

There is HUGE room for improvement in NAV on SQL Server, as long as you know what you are doing. It sure does not sound like your consultants know what they are doing. That's not to say anything bad about them, because this is tricky stuff. But the reality is that there are many indexes and SIFT levels that are not used in your system, and the right people can help you find those and eliminate the ones that are redundant.