In general, choose a lock scheme for a new table based on the likelyhood that applications will experience lock contention on the table. Whether to change the locking scheme for an existing table can be based on contention measurements on the table, but should also take application performance into account.
Here are some typical situations and general guidelines for choosing the locking scheme:
Applications require clustered access to data rows due to range queries or order by clauses. Allpages locking provides more efficient clustered access than data-only-locking. Rows may not be returned in key order of the clustered index for queries.
A large number of applications access 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 tables that have 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.
