For better performance [Always Row Lock]="yes" or "no"

kamalbaaklini

HI
it is not clear what setup is better for performance [Always Row Lock]="yes" or "no"
(this is found in the path = Files -> Database -> Alter -> Advanced)

in a 150 concurrent sessions environment, and big database (around 1.2 terra), where lot of purchase order and transfer orders created daily. (a lot means more than 1000 PO and 1000 Transfer per day)

your comments are appreciated

:-k

ROLA

If you have a SQL Server with more than 16GB of RAM then "Always Row Lock = Yes"
Please note that this option was lost in versions released since NAV2013.

The setting disable Lock Escalation on the SQL server.
Instead of escalation of recordlocks into page- or tablelocks, It will add more recordlocks (req. more memory).
This will reduce lockings, but increase memory load.

ROLA

In version 2013 and later you should consider using the SQL-server trace flag 1224.
Please note that this is a global setting for the entire SQL Server..

bbrown

ROLA wrote:

In version 2013 and later you should consider using the SQL-server trace flag 1224.
Please note that this is a global setting for the entire SQL Server..

This only prevents lock escalation due to excessive number of locks. Other factors, such as memory pressure, can still induce lock escalations. Thus you want to have plenty of RAM before using this setting. This also does not SQL from acquiring less granular locks to begin with.

kamalbaaklini

ROLA wrote:

If you have a SQL Server with more than 16GB of RAM then "Always Row Lock = Yes"
Please note that this option was lost in versions released since NAV2013.

The setting disable Lock Escalation on the SQL server.
Instead of escalation of recordlocks into page- or tablelocks, It will add more recordlocks (req. more memory).
This will reduce lockings, but increase memory load.

we have SQL 2008R3 64bits with RAM=196GB
and NAV 2009 R2
so what do you suggest?

bbrown

kamalbaaklini wrote:

ROLA wrote:

If you have a SQL Server with more than 16GB of RAM then "Always Row Lock = Yes"
Please note that this option was lost in versions released since NAV2013.

The setting disable Lock Escalation on the SQL server.
Instead of escalation of recordlocks into page- or tablelocks, It will add more recordlocks (req. more memory).
This will reduce lockings, but increase memory load.

we have SQL 2008R3 64bits with RAM=196GB
and NAV 2009 R2
so what do you suggest?

What edition of SQL?