Identifying tables where concurrency is a problem

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

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:

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: