sp_iqindexadvice procedure

Function

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

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.

Table 7-32 lists INDEX_ADVISOR columns.

**Table 7-32: sp_iqindexadvice columns**
Column name	Description
Advice	Unique advice message
NInst	Number of instances of message
LastDT	Last date/time advice was generated

Examples

Table 7-33 illustrates sample output from the sp_iqindexadvice procedure.

**Table 7-33: 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