sp_iqindexadvice Procedure

Displays stored index advice messages. Optionally clears advice storage.

Syntax

sp_iqindexadvice ( [ resetflag ] )

Privileges

Requires ALTER ANY INDEX or ALTER ANY OBJECT system privileges. Users without one of these system privileges 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