My statistics are up to date, histogram looks good, but still getting terrible execution plans. Estimated vs actual is always way off.
I don't necessarily agree with the design but it should be better than this. I do very aggressive statistics maintenance every night and every value is represented in the histogram with no range rows. They cluster on an incrementing bigint, but the primary key is the partitioning key and a guid. I hate that and can't change it. It keeps the clustered indexes unfragmented but the non-clustered indexes fragment every day. I fix them every night. Most of the joins are using non-clustered index seeks and one clustered index seeks but the row estimates and spilling are bad. It's a system-wide thing and I don't know why other than the design I described above and can't change. Any help would be appreciated. I'm tempted to stalk Kimberly Tripp to get some help 🙂