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

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

dbname

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

objname

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

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands alter table