I quote “wrong” because with the info the optimizer has, it thinks it has the right index. :)
1. Look for cardinality problems. You can do this by looking at the actual rows versus estimated rows in the query plan. If they are off, chances are you are not following the best practices listed here.
2. Conversions in the WHERE clause can cause issues. Be sure to look at query plan for implicit conversions
3. Complicated logic in the WHERE clause can also cause problems. Consider indexed persisted computed columns or indexed views.
4. Constraints give the optimizer more info when choosing a plan with the “right” index. It can take a PK or unique constraint into consideration but it does not know about similar logic in a trigger.
5. Large datasets are being returned but the index is not covering. An index with just the columns in the WHERE clause is ok if just a few rows are returned but it does not take much for an index scan to be more efficient.
#1 is the most important one because the link gives 5 or 7 more reason why the “wrong” index is chosen.
P.S. If all else fails, update stats and if that does not work, make the index covering.
posted @ Friday, October 31, 2008 12:55 AM by Marc Brooks
Name (required)
Email (required)
Website
Notify me of followup comments via e-mail