mibuso.com

Microsoft Business Solutions online community
It is currently Mon Sep 01, 2014 9:32 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Always Rowlock
PostPosted: Thu Jan 31, 2008 12:08 pm 
Offline

Joined: Fri Sep 10, 2004 5:05 am
Posts: 165
Location: Angel's Home
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


Top
 Profile E-mail  
 
 Post subject:
PostPosted: Thu Jan 31, 2008 12:15 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 4179
Location: Olst
Country: Netherlands (nl)
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?

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Specialist

Author of the book : Microsoft Dynamics NAV Application Design

MY BLOG : http://markbrummel.wordpress.com


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Fri Feb 01, 2008 9:08 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Mon Feb 04, 2008 4:08 am 
Offline

Joined: Fri Sep 10, 2004 5:05 am
Posts: 165
Location: Angel's Home
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


Top
 Profile E-mail  
 
 Post subject:
PostPosted: Fri Feb 08, 2008 12:41 pm 
Offline

Joined: Wed Jan 17, 2007 4:54 pm
Posts: 108
Location: Etten leur
Country: Netherlands (nl)
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: