sp_object_stats

Description

Shows lock contention, lock wait-time, and deadlock statistics for tables and indexes.

Syntax

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

Parameters

Examples

Example 1

Reports lock statistics on the top 10 objects server-wide:

sp_object_stats "00:20:00"

Example 2

Reports only on tables in the pubtune database, and lists the five tables that experienced the highest contention:

sp_object_stats "00:20:00", 5, pubtune

Example 3

Shows only the names of the tables that had the highest locking activity, even if contention and deadlocking does not take place:

sp_object_stats "00:15:00", @rpt_option = "rpt_objlist"

Usage

• sp_object_stats reports on the shared, update, and exclusive locks acquired on tables during a specified sample period. The following reports shows the titles tables:

  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.
  

• The meaning of the values are:

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

• 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 that had the highest level of lock activity.

• sp_object_stats creates a table named tempdb..syslkstats. This table is not dropped when the stored procedure completes, so it can be queried by a system administrator using Transact-SQL.

• Only one user at a time should execute sp_object_stats. If more than one user tries to run sp_object_stats simultaneously, the second command may be blocked, or the results may be invalid.

• The tempdb..syslkstats table is dropped and re-created each time sp_object_stats is executed.

• The structure of tempdb..syslkstats is:

  Column name	Datatype	Description
  dbid	smallint	Database ID
  objid	int	Object ID
  lockscheme	smallint	Integer values 1–3: Allpages = 1, Datapages = 2, Datarows = 3
  page_type	smallint	Data page = 0, or index page = 1
  stat_name	char(30)	The statistics represented by this row
  stat_value	float	The number of grants, waits or deadlocks, or the total wait time

  The values in the stat_name column are composed of three parts:

  • The first part is “ex” for exclusive lock, “sh” for shared lock, or “up” for update lock.

  • The second part is “pg” for page locks, or “row” for row locks.

  • The third part is “grants” for locks granted immediately, “waits” for locks that had to wait for other locks to be released, “deadlocks” for deadlocks, and “waittime” for the time waited to acquire the lock.

• If you specify a table name, sp_object_stats displays all tables by that name. If more than one user owns a table with the specified name, output for these tables displays the object ID, but not the owner name.

Permissions

Only a system administrator can execute sp_object_stats.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

See also