Always Rowlock

Angelo

Hi Master,

What is the effect if I checked mark "always rowlock" option? The explanation about this is too short. May I know positive and negative side if checked mark this option? if this option will improve performance also?

Please advice... [-o<

rgds,
Angelo

Marije_Brummel

This will force a rowlock hint and try to override the lockescalation principle in SQL.

Since you are on SQL2000 it can be tricky since there is only 1.7GB of ram available for bot the lock memory and plan cache.

Can you run profiler to see if you have many lock escalations?

stryk

With "Always Rowlock" enabled the Optimizer Hint ROWLOCK is set which prevents (delays) lock-escalation (as Mark already said).

Forcing ROWLOCKing keeps the lock-granularity small, the probability of getting blocks is smaller. The disadvantage is, that by forcing the small granularity, this could cause a high "pressure" on the master database: administering many Row-Locks is more "costly" than administering few e.g. Range-Locks etc..
Hence, if you have a high transaction volume, dealing with large result-sets, "Always Rowlock" could cause a overall decrease of performance if the master-db reacts too slow due to the high number of lock-administrations.

Finally, "Always Rowlock" reduces blocking-conflicts (actually it is just disguising them) but could be at cost of the overall performance (which could be compensated by sufficient hardware resource).

I recommend to disable this feature and investigate the occurring blocks thoroughly, to solve/prevent them by optimizing the C/AL code, Index- or SIFT-Structures.
Just if anything else fails, then "Always Rowlock" should be the "solution".

Angelo

Thank you for your explanation =D>

So, Always Rowlock has Positive and Minus. Reduces blocking but decrease performance. Now, my main problem is Locking. user always get error " The xxx table cannot be changed because it is locked by another user. Wait until the user is finished and then try again.". this many times happened in Outbound transaction. How to investigate the occuring blocks?

regards,
Angelo

Bgestel

A good place to start is the

Navision SQL-Technical Kit

It contains al the tools/manuals/KB articles you need to start solving the problem.

an other resource is the

Navision W14.00 Tools CD update 1\Implementation\Performance Troubleshooting Guide

there you can find tools to specificly investigate locking.



If its not in the download section , try partnersouce.