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:
Changing from allpages locking to either data-only-locking scheme is time consuming and expensive, in terms of I/O cost, but changing between the two data-only-locking schemes is fast and does not require copying the table.
Datarows locking requires more locks, and consumes more locking overhead.
If your applications experience little contention after you convert high-contending tables to use datapages locking, you do not need to incur the locking overhead of datarows locking.
The number of locks available to all processes on the
server is limited by the number of locks configuration
parameter.
Changing to datapages locking reduces the number of locks required, since index pages are no longer locked.
Changing to datarows locking can increase the number of locks required, since a lock is needed for each row.
See “Estimating number of locks for data-only-locked tables” for more information.
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:
Lock contention is high for two tables that are updated in transactions involving several tables. Applications first lock TableA, then attempt to acquire locks on TableB, and block, holding locks on TableA.
Additional tasks running the same application block while trying to acquire locks on TableA. Both tables show high contention and high wait times.
Changing TableB to data-only locking may alleviate the contention on both tables.
Contention for TableT is high, so its locking scheme is changed to a data-only locking scheme.
Re-running sp_object_stats now shows contention on TableX, which had shown very little lock contention. The contention on TableX was masked by the blocking problem on TableT.
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.
