Secondary Index

[Former Member]

Hi,

We have created a secondary index on a table in ERP on SQL 2008 server. But the optimizer is not really using the secondary index. Is there any way we can force the optimizer to utilize the secondary index?

Any ideas?

Thanks,

Kris

[Former Member · Accepted solution]

Did you make sure to build the index on the where clause of the query you are trying to improve?

[former_member188883 · Accepted solution]

Hi,

You might need to update missing database statistics. After its completion try to analyze the situation again.

Also as pointed above refer to the SQL query and index creation fields. It should be similar to achieve best results.

Regards,

Deepak Kori

[Former Member]

Also, as a side note, SQL 2008 uses a cost-based optimizer which will determine what indexes it uses based on what will perform the best and use the least amount of resources. Forcing the query to use a different index is basically reverting the optimizer back to rules-based, which I would not recommend.

Perhaps it would be better to address your problem from a different angle. What is it you are trying to do? Make a particular query perform faster?