Reduce cost of clustered index seek

TheLuBu 2013-11-27 12:59:07

I would love to hear some suggestions, how i can reduce the costs of the "large" clustered index seek.

As you can see, the estimated value is very high but the actual returned rows are always lower.

i tried several things but can't get to an better result than that.

Thanks for your time,

Lucas

— Edit

I added another file, not anonymized and after an "UPDATE STATISTICS" for the table

Aaron Bertrand 2013-11-27 13:33:55
Tough to get a lot out of it because the query is anonymized. Based on what I can see, the estimates are way off across the board. What happens if you update statistics (especially on the table with the most I/O, "Table7")?
TheLuBu 2013-11-27 13:48:31
see first post, i added another file
Aaron Bertrand 2013-11-27 18:37:29
One thing I would try is the following filtered index:

CREATE NONCLUSTERED INDEX SK_Helper ON PWC.OUTBOUND_CONTACTS
(
  LAST_CALL_DATE, LOT_ID, USER_ID, NEGATIVE_RESULT_REASON_ID
)
INCLUDE 
(
  CUSTOM_FIELDS, ID
)
WHERE Status = 7;

This should be skinnier than your clustered index, contain much fewer rows (depending on the cardinality of the status column), and should provide more accurate statistics. No guarantee it will be better than the clustered index seek, but worth a shot.

(This is with no insight whatsoever into other, similar queries you might run, or how much the maintenance of this index will affect your write workload. Like the missing index DMVs, this is merely a suggestion based on the goal to improve this query.)

Another thing I might try is persisting the results of those function calls, and get them out of the SELECT list. You can calculate those values at insert/update time or with a trigger, and store them somewhere, or potentially use an indexed view. Even if you reduce the cost of the clustered index seek I think those function calls will still be your bottleneck.