sp_iqindexuse Procedure

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.

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
...
Related reference
sp_iqcolumnuse Procedure
sp_iqindexadvice Procedure
sp_iqtableuse Procedure
sp_iqunusedcolumn Procedure
sp_iqunusedindex Procedure
sp_iqunusedtable Procedure
sp_iqworkmon Procedure