Identifying tables where concurrency is a problem

sp_object_stats prints table-level information about lock contention. You can use it to:

Report on all tables that have the highest contention level
Report contention on tables in a single database
Report contention on individual tables

The syntax is:

sp_object_stats interval [, top_n        [, dbname [, objname [, rpt_option ]]]]

To measure lock contention on all tables in all databases, specify only the interval. This example monitors lock contention for 20 minutes, and reports statistics on the ten tables with the highest levels of contention:

sp_object_stats "00:20:00"

Additional arguments to sp_object_stats are as follows:

top_n – allows you to specify the number of tables to be included in the report. Remember, the default is 10. To report on the top 20 high-contention tables, for example, use:
```
sp_object_stats "00:20:00", 20
```
dbname – prints statistics for the specified database.
objname – measures contention for the specified table.
rpt_option – specifies the report type:
- rpt_locks reports grants, waits, deadlocks, and wait times for the tables with the highest contention. rpt_locks is the default.
- rpt_objlist reports only the names of the objects with the highest level of lock activity.

Here is sample output for titles, which uses datapages locking:

Object Name: pubtune..titles (dbid=7, objid=208003772,lockscheme=Datapages)


  Page Locks     SH_PAGE                UP_PAGE               EX_PAGE
  ----------    ----------             ----------            ----------
  Grants:            94488                   4052                  4828
  Waits:               532                    500                   776
  Deadlocks:             4                      0                    24
  Wait-time:      20603764 ms            14265708 ms            2831556 ms
  Contention:         0.56%                 10.98%                13.79%

 *** Consider altering pubtune..titles to Datarows locking.

Table 5-1 shows the meaning of the values.

**Table 5-1: sp_object_stats output**
Output dow	Value
Grants	The number of times the lock was granted immediately.
Waits	The number of times the task needing a lock had to wait.
Deadlocks	The number of deadlocks that occurred.
Wait-times	The total number of milliseconds that all tasks spent waiting for a lock.
Contention	The percentage of times that a task had to wait or encountered a deadlock.

sp_object_stats recommends changing the locking scheme when total contention on a table is more than 15 percent, as follows:

If the table uses allpages locking, it recommends changing to datapages locking.
If the table uses datapages locking, it recommends changing to datarows locking.