IQ index usage statistics

[Former Member]

Does anybody know what is the meaning of NOpt value returned by sp_iqindexuse procedure.
NQry and NConstraint is clear, but what does NOpt (used by the optimizer) stands for?

[Gisung]

Hi,

The NOpt is the number of times that the index is accessed by the internal optimizer such as medatadata or uniqueness accesses.

When optimizer needs to make better decisions, query optimizer is using the "non-fp" index to check the number of distinct values.

In this case, the no. of NOpt is increased to 1 from 0.

==

Gi-Sung Jang

[Former Member]

Hi Gi-sung Jang,

Thanks for you answer. Well, I know what the "book" says:

NOpt – number of metadata/uniqueness accesses

NQry – number of query accesses

NConstraint – number of accesses for unique or RI Checks 

So, in case NOpt is relatively high number, but NQry and NConstraint is 0,

can we say that this particular index is an overhead for the optimizer, just cheating?

B/R

Ferenc