sp_iqindexuse procedure

Function

Reports detailed usage information for secondary (non-FP) indexes accessed by the workload.

Syntax

sp_iqindexuse 

Permissions

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

Description

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.

Table 7-37: sp_iqindexuse columns

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).

Example

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
...

See also

“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