Luis Cazares (1/2/2015)
Thanks to all of you.I just re-read all of the posts and Jack's comment puzzles me. I'll try to find out and if I do, I'll come back.
This table is used as source for several fact tables and most of the time it's used entirely which should be the reason for having more scans than seeks on the clustered index (unless my reasoning is wrong). And yes, most columns are used in the queries, so indexes (even covering indexes) might be too large to be practical.
I'm not sure what you mean by puzzles you. Basically the data shows over 130k user lookups on the clustered index, and I usually see an equal or greater number of user seeks on the non-clustered indexes and I don't see that here. The user lookups on the clustered index are the key/bookmark lookups caused by non-covering non-clustered indexes. That could be explained by indexes being dropped and new ones being created.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question