Identifying tables where concurrency is a problem

sp_object_stats prints table-level information about lock contention. 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 10 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 3-1 shows the meaning of the values.

Table 3-1: sp_object_stats output

Output row

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-time

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: