sp_iqindexadvice Procedure

Displays stored index advice messages. Optionally clears advice storage.

Syntax

sp_iqindexadvice ( [ resetflag ] )

Permissions

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

Usage

Parameters

Parameter

Description

resetflag

Lets the caller clear the index advice storage. If resetflag is nonzero, all advice is removed after the last row has been retrieved.

Description

Allows users to query aggregated index advisor messages using SQL. Information can be used to help decide which indexes or schema changes will affect the most queries.

INDEX_ADVISOR columns:

sp_iqindexadvice columns

Column name

Description

Advice

Unique advice message

NInst

Number of instances of message

LastDT

Last date/time advice was generated

Examples

Sample output from the sp_iqindexadvice procedure:

Sample sp_iqindexadvice output

Advice

NInst

LastDT

Add a CMP index on DBA.tb (c2, c3) Predicate: (tb.c2 = tb.c3)

2073

2009-04-07 16:37:31.000

Convert HG index on DBA.tb.c4 to a unique HG

812

2009-04-06 10:01:15.000

Join Key Columns DBA.ta.c1 and DBA.tb.c1 have mismatched data types

911

2009-02-25 20:59:01.000

Related reference
sp_iqcolumnuse Procedure
sp_iqindexuse Procedure
sp_iqtableuse Procedure
sp_iqunusedcolumn Procedure
sp_iqunusedindex Procedure
sp_iqunusedtable Procedure
sp_iqworkmon Procedure