Always Rowlock

Angelo
Angelo Member Posts: 180
edited 2008-02-08 in SQL Performance
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

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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?
    Marije Brummel | Freelance Business Central Specialist
  • stryk
    stryk Member Posts: 645
    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".
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Angelo
    Angelo Member Posts: 180
    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
    Bgestel Member Posts: 136
    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.
    **********************
    ** SI ** Bert Van Gestel **
    **********************

Categories