sp_object_stats

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

Usage

There are additional considerations when using sp_object_stats:
  • 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:
    • 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.

  • 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

    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.

    stat_value

    float

    The number of grants, waits or deadlocks, or the total wait time

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

See also:
  • alter table in Reference Manual: Commands

Permissions

The permission checks for sp_object_stats differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage server privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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