Reduce cost of 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,
I added another file, not anonymized and after an "UPDATE STATISTICS" for the table
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.