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 status (the default) displays the current status without changing state.
The statistics are persisted until they are cleared with the reset argument, or until the server is restarted. Statistics collection does not automatically resume after a server restart, and it needs to be restarted using start.
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
