Choosing a locking scheme based on contention statistics

If the locking scheme for the table is allpages, the lock statistics reported by sp_object_stats include both data page and index lock contention.

If lock contention totals 15% or more for all shared, update, and exclusive locks, sp_object_stats recommends changing to datapages locking. You should make the recommended change, and run sp_object_stats again.

If contention using datapages locking is more than 15%, sp_object_stats recommends changing to datarows locking. This two-phase approach is based on these characteristics:

When examining sp_object_stats output, look at tables that are used together in transactions in your applications. Locking on tables that are used together in queries and transactions can affect the locking contention of the other tables.

Reducing lock contention on one table could ease lock contention on other tables as well, or it could increase lock contention on another table that was masked by blocking on the first table in the application. For example:

If your application uses many tables, you may want to convert your set of tables to data-only locking gradually, by changing just those tables with the highest lock contention. Then test the results of these changes by rerunning sp_object_stats.

You should run your usual performance monitoring tests both before and after you make the changes.