Chapter 1: System Procedures

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

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.

Table 1-18 shows the meaning of the values.

**Table 1-18: Output of sp_object_stats**
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 described in Table 1-19.

**Table 1-19: Columns in the tempdb..syslkstats table**
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:

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