Controls collection of workload monitor usage information, and reports monitoring collection status.
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’
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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.
Column name |
Description |
|---|---|
MonMode |
Table, index, or column |
Status |
Started or stopped |
Rowcount |
Current number of rows collected |
Sample output from the sp_iqworkmon procedure:
MonMode Status Rowcount
index started 15
table started 10
column started 31
“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
