dwain.c (8/7/2012)
For what it's worth, here's my opinion on Tuning Advisor.If you put sugar on dog food it may taste better but do you really want to eat it?
The meaning of course is that adding a covering index to a poor performing query may make it run faster, but you're better advised to get your query running at optimal performance without using an index first. Then if it still needs to be faster, see what Tuning Advisor recommends.
Heh, I like the way you phrased that...
From further talking with one of the devs, it seems the primary place that people are complaining about the performance is in various search boxes in the application. Internally, the application uses the KeyID field (which has the clustered index on it) so that goes fine. It's when a user tries to search for someone in the data that the performance issue comes up. To me, it sounds like it does this because the application can't search by the KeyID as it won't know the KeyID until it finds the entry in the data, so SQL has to do a table scan to find it. So I do think there isn't much to be done to improve the query(ies) that are running into this.
I'm trying to go into this with a "light touch" philosophy, make as small and as few changes as possible, for the greatest gain.
Thanks
Jason