sp_iqindexadvice Procedure

Displays stored index advice messages. Optionally clears advice storage.

Syntax

sp_iqindexadvice ( [ resetflag ] )

Parameters

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

Privileges

You must have EXECUTE privilege on the system procedure. You must also have one of the following system privileges:

ALTER ANY INDEX
ALTER ANY OBJECT

Remarks

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

Example

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