Reports detailed usage information for secondary (non-FP) indexes accessed by the workload.
sp_iqindexuse
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
Each secondary index accessed by the workload displays a row. Indexes that have not been accessed do not appear. Index usage is broken down by optimizer, constraint, and query usage.
Indexes from tables created in SYSTEM are not reported.
Column name |
Description |
|---|---|
IndexName |
Index name |
TableName |
Table name |
Owner |
User name of index owner |
UID** |
Index unique identifier |
Type |
Index type |
LastDT |
Date/time of last access |
NOpt |
Number of metadata/uniqueness accesses |
NQry |
Number of query accesses |
NConstraint |
Number of accesses for unique or referential integrity checks |
**UID is a number assigned by the system that uniquely identifies the instance of the index (where instance is defined when an object is created).
Sample output from the sp_iqindexuse procedure.
IndexName TableName Owner UID Type LastDT NOpt NQry NConstraint n_nationkey_hg nation DBA 29 HG 20070917 22:08:06~ 12 0 12 n_regionkey_hg nation DBA 31 HG 20070917 22:08:06~ 12 0 0 r_regionkey_hg region DBA 47 HG 20070917 22:08:06~ 12 0 12 s_suppkey_hg supplier DBA 64 HG 20070917 22:08:06~ 12 0 12 p_partkey_hg part DBA 87 HG 20070917 22:08:06~ 6 0 6 s_suppkey_hg supplier DBA 64 HG 20070917 22:08:06~ 12 0 12 ...
“Monitoring workloads,”Chapter 3, “Optimizing Queries and Deletions,” in the Performance and Tuning Guide.
“sp_iqcolumnuse procedure”, “sp_iqdbspace procedure”, “sp_iqindexadvice procedure”, “sp_iqtableuse procedure”, “sp_iqunusedcolumn procedure”, “sp_iqunusedindex procedure”, “sp_iqunusedtable procedure”, and “sp_iqworkmon procedure”
“INDEX_ADVISOR option” in Chapter 2, “Database Options,” in Reference: Statements and Options
