sp_iqworkmon procedure

Function

Controls collection of workload monitor usage information, and reports monitoring collection status.

Syntax

sp_iqworkmon ‘action’ ] [ , ‘mode’ ]
action = ‘start’ , ‘stop’ , ’status’ , ‘reset’
mode = ‘index’ , ‘table’ , ‘column’ , ‘all’

For example:

sp_iqworkmon  ‘start’ , ‘all’ 

If one argument is specified, it can only be action. For example:

sp_iqworkmon  ‘stop’ 

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

action Specifies the control action to apply. A value of start starts monitoring for the specified mode immediately. A value of stop stops monitoring immediately. A value of reset clears all collected usage information. A value of status (the default) displays the current status without changing state.

mode Specifies the type of monitoring to control. The INDEX, TABLE, and COLUMN keywords individually control monitoring of index usage, table usage, and column usage respectively. The default ALL keyword controls monitoring of all usage monitoring features simultaneously.

There is always a result set when you execute sp_iqworkmon. If you specify a specific mode (such as index), only the row for that mode appears.

Usage is collected only for SQL statements containing a FROM clause; for example, SELECT, UPDATE, and DELETE.

Table 7-72: sp_iqworkmon columns

Column name

Description

MonMode

Table, index, or column

Status

Started or stopped

Rowcount

Current number of rows collected

Example

Sample output from the sp_iqworkmon procedure:

MonMode     Status      Rowcount index       started     15 table       started     10 column      started     31

See also

“Monitoring workloads,”Chapter 3, “Optimizing Queries and Deletions,” in the Performance and Tuning Guide.

“sp_iqcolumnuse procedure”, “sp_iqindexadvice procedure”, “sp_iqdbspace procedure”, “sp_iqindexuse procedure”, “sp_iqtableuse procedure”, “sp_iqunusedcolumn procedure”, “sp_iqunusedindex procedure”, and “sp_iqunusedtable procedure”

“INDEX_ADVISOR option” in Chapter 2, “Database Options,” in Reference: Statements and Options