Grant Fritchey (5/27/2015)

---

GilaMonster (5/27/2015)

---

WhiteLotus (5/26/2015)

---

I don’t think eliminate distinct will make any difference ….

By which you mean you've tested it and it didn't make any difference? ....

Despite the fact that the DISTINCT SORT operator takes 66% of the cost of the query.

Looking at the plan, it reflects everything said so far. You're returning 45,000 rows out of 197,000. That's 22% of the total. That's going to be a scan no matter what you do. You have the CAST that's occurring. While that's not free I doubt it's seriously impacting the system since it's doing nothing else. Then you have the DISTINCT SORT operator that, per the query optimizers estimates, is 66% of the cost. That's more than the index scan itself. I'll bet you serious money it makes a difference in performance.

Your statistics are good since your estimates and actuals match perfectly. Except for removing the DISTINCT clause, there's nothing you can do with this query as long as it's returning such a large percentage of the data.