A bit of an odd question, but looking for methods of finding inefficient indexes. As a basic example
I have a query that in its original state ran with 66 logical reads, clustered index seek and a profiler duration of 205 (cached) returning 1 record. So nothing that screams "FIX ME"!! With the addition of a non clustered index I got it down to 5 logical reads, non cluster index seek (could be a touch better, but doing a SELECT * - its only a test) and a (cached) duration of 9.
The only reason that I looked at this, was because I am a little bored and its a section of the DB that I already had select statements for. Im not looking for specific advise about this table and query, but more of a general approach on finding these little inefficiencies.... I have been doing a fair bit of looking on here and google, but everything I find is about the blatantly obvious.