The Sort and the Index Seek are the most expensive nodes in IO without returning any rows. How could it be?

Ptr 2016-10-17 14:02:52

Aaron Bertrand 2016-10-17 19:13:08
Remember this label is just an estimated cost. SQL Server didn't know, when determining the estimated cost, how many rows would actually be returned at runtime (it expected to sort 500+ rows). This mis-estimate is common throughout the plan, and likely has to do with the combination of UDF / APPLY / open-ended date range. On 2014 SP1 and up, you'd be able to generate the actual plan from within Plan Explorer, use our Live Query Profile feature, and after execution was complete we could re-label all of the operators, replacing the compiled, estimated costs with actual observed costs.
Ptr 2016-10-17 20:12:49
This is actual plan, so I interpret that values as actual, not estimated. Is this incorrect? And what do columns "Reads" and "Writes" mean in the result grid? They do not correspond with Table I/O tab values. I use only inline TVF. What could be the source of mis-estimating in this case?
Aaron Bertrand 2016-10-17 20:16:04
The plan operators by default always show estimated cost, regardless of whether the plan is actual or estimated. For newer versions of SQL Server we are able to adjust those costs based on reality, however that information isn't exposed in SQL Server 2012.

The reads and writes are based on total I/O, like you would see in a trace. It is not restricted to Table I/O and can be attributed to other things like UDFs, which the optimizer can't quite see (I explained some of this here).

I already suggested my guesses for the source of the mis-estimation, and I am betting the UDF is a big part of it.