This may be a terrible question, because I'm not sure how information I can include to help.
We have data segregated by customer. One customer apparently has higher volume of data. The same query ran for a small customer returns in 2 seconds, and the result is 11 rows. The larger customer takes 47 seconds, and the result is 6600 rows. This is a complicated query with 11 joins. This is just for a report, but the report is timing out and the operator is complaining. It's possible the difference is just the volume of data, but I want to investigate.
When I look at the query plan, the percentages for the query cost are exactly the same between the two. There are no suggestions for indexes. In both query plans, I can see the highest cost is from a join to a table that has 3.8 million rows. However, both sets of data are joining with that same table. In both cases the resulting "actual number of rows" is 3.8 million. In both cases this join has a clustered index scan that is 39% of the query "cost".
So my question is this: Do the percentages and query cost even mean anything? are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds? Is SQL server lying to me and the actual difference in cost is the "nested loop" for the inner join that produces the final row count even though it lists that as 0% of the cost?
These images show the two elements with the highest cost. They have identical properties between both the query plans. The difference in the final row totals is the result of joins with other tables. But those joins all have a low relative cost.