rja.carnegie (9/17/2009)
I need to read this article carefully, but my immediate reaction is that the indexes that aren't used or don't even exist are the ones that should be considered for attention. And that's what the Tuning Wizard is for - that, and statistics. I will admit that I do not yet "get" statistics.I am often surprised when I design tables with carefully chosen indexes and SQL Server ignores them, whereas if I was still programming a database "row by agonising row" I myself would use the index strategy I designed for the query. Maybe it's the statistics. I look at the Execution Plan and I'm like, "Why aren't you using the nice index I made for you!" Incidentally, I'm single.
This normally happens for one of two reasons.
1. The index doesn't cover the query 100% and the amount of data being queried is large enough that the index won't help the query.
2. You use non SARG-able statements in the where clause or the Join criteria. Things like DATEADD, DATEDIFF, LEFT, RIGHT, SUBSTRING, etc in the where clause cause SQL to start table or at best NC index scanning.