In general, choice of lock scheme for a new table should be determined by the likelihood that applications will experience lock contention on the table. The decision about whether to change the locking scheme for an existing table can be based on contention measurements on the table, but also needs to take application performance into account.
Here are some typical situations and general guidelines for choosing the locking scheme:
Applications require clustered access to the data rows due to range queries or order by clauses
Allpages locking provides more efficient clustered access than data-only-locking.
A large number of applications access about 10 to 20% of the data rows, with many updates and selects on the same data.
Use datarows or datapages locking to reduce contention, especially on the tables with the highest contention.
The table is a heap table that will have a high rate of inserts.
Use datarows locking to avoid contention. If the number of rows inserted per batch is high, datapages locking is also acceptable. Allpages locking has more contention for the “last page” of heap tables.
Applications need to maintain an extremely high transaction rate; contention is likely to be low.
Use allpages locking; less locking and latching overhead yields improved performance.
