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

interval

specifies the time period for the sample. It must be in HH:MM:SS form, for example “00:20:00”.

top_n

the number of objects to report, in order of contention. The default is 10.

dbname

the name of the database to report on. If no database name is given, contention on objects in all databases is reported.

objname

the name of a table to report on. If a table name is specified, the database name must also be specified.

rpt_option

must be either rpt_locks or rpt_objlist.

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

Permissions

Only a System Administrator can execute sp_object_stats.

See also

Commands alter table