sp_object

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

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

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.

Permissions

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

Setting	Description
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:

Information	Values
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