Since Dynamics NAV 4.0 SP1 a new database option, for Dynamics NAV on Sql Server, became available: Always Rowlock:

By default it is not enabled and by enabling it Dynamics NAV will send 'WITH ROWLOCK' hints towards Sql Server. Without it, we let SQL Server decide what kind of locks to use.

Rowlocks require memory to maintain, so some times, SQL Server will convert many rowlocks into one table lock when it thinks that it can use its memory better in other places. The ROWLOCK hint will prevent this.

If the ROWLOCK is not issued then SQL Server has the freedom to choose at what level it will lock: row, page or table.

• The up side of this is that it is a big reduction in lock maintenance and memory required for many rowlocks, which instead will be replaced with page locks è improved performance.
• The down-side is that page locking is not as fine-grained and therefore a user can be locking 'too much' data that can impact other users è reduced concurrency.

Forcing ROW Locking 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...

If you have a high transaction volume, dealing with large result-sets, "Always Rowlock" could cause an overall decrease of performance if the master-db reacts too slowly 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 resources).

So, if you activate "Always Rowlock", you may get fewer table locks, but SQL Server may need more memory. It's impossible to say in general, how it will affect each individual system. If you think you have quite a lot of memory, then the only way to see the effect is to turn it on, and see if the system works better. I'm afraid that I don't know of any better general rules, when to advice to turn "Always Rowlock" on or off.

Maybe you could share with us your own experiences with the Always Rowlock option?

Comments

[Online Pharmacy]

The up side of this is that it is a big reduction in lock maintenance and memory required for many rowlocks, which instead will be replaced with page locks è improved performance.

[kamagra]

Hello, i think that this post is very good, i would like to read more about it

[Nuno]

What a very good explanation. Hats off!