quote:

---

...I think searching all my sp's for JOIN statements, and indexing any FK keys found there would be a better optimization. In our case, every sql statement is an SP, so if there is no join, i can assume the FK is not relevant to performance issues...

---

I think that would be a bad assumption. Even if there are no joins, an index will be helpful if the field is in a WHERE clause (SARG) and the data in the field is selective. I do think that there is no one-pass index optimization query that can be run; indexes should be carefully selected and placed on those fields which are used most often in WHERE clauses (JOIN clauses are a special type of WHERE condition...), and where the data in those fields contains fairly to highly distinct values. I wouldn't advise running any end-all-be-all script to identify fields on which to place indexes. That would be a recipe for disaster in my opinion. Spending the time to carefully look at the data in each prospective field is well worth the effort.

🙂

Jay