INDEX Advisor : sp_iqindexadvice not returning any result

[Former Member]

Hi ,

I have enabled index_advisor on  . I can see all recommendation or Advice in iqmsg file .  But sp_iqindexadvice does not return any result ..

set option.index_advisor='on'

Thanks

Ajay Pandey

[hans-juergen_schwindke]

Hi,

you have to set the option index_advisor_max_rows as well. E.g.

set option public.index_advisor_max_rows=1000

This means that 1000 index advices are stored in the internal table / structure.

The advices are stored in memory only. Consequently the table / structure is empty after an IQ reboot. So it might be a good idea to create a table and transfer the advices into this table regularly. After that you can truncate the advices by running

sp_iqindexadvice(1)

Best regards,

Juergen

[Former Member]

Thanks I have enables . Will check the result ..