Shows lock contention, lock wait-time, and deadlock statistics for tables and indexes.
sp_object_stats interval[, top_n[, dbname, objname[, rpt_option]]]
specifies the time period for the sample. It must be in HH:MM:SS form, for example “00:20:00”.
is the number of objects to report, in order of contention. The default is 10.
is the name of the database to report on. If no database name is given, contention on objects in all databases is reported.
is the name of a table to report on. If a table name is specified, the database name must also be specified.
must be either rpt_locks or rpt_objlist.
Reports lock statistics on the top 10 objects server-wide:
sp_object_stats "00:20:00"
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
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"
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-30 shows the meaning of the values.
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-31.
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.
Only a System Administrator can execute sp_object_stats.
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 |
|
Commands alter table
