Same query plan, different data set, very different query duration SQL Server 2012

Question

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.

Answer 1

Do the percentages and query cost even mean anything?

Not really--They have meaning, but they're unreliable.

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?

Exactly--The cost is an estimate. If you look at the detail pop-up when you hover on an operator, you'll see that what you see as "Cost" in the graphical plan is actually "Estimated Operator Cost." It is based on the pre-execution estimate, and will be the same even when actual costs vary.

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?

It's not quite lying, but it's not reality either. Again, since these are based on estimates, they can be wrong. In particular, sometimes you'll see Cost% add up to more than 100%--another indicator it's not based on real performance.

There's a better way

IMHO, SQL Sentry Plan Explorer is the easiest way to look at the actual execution performance data. It will give you actual CPU & IO information for each statement in your batch, which you can use to zero in on the most expensive parts of your batch. In this case, you can test the same statement for different customers and look at the actual execution results.

You might also want to try using SET STATISTICS IO,TIME ON and running the results through statisticsparser.com. That will give you a good sense of the IO/CPU differences between the two queries for different customers.