I'd like to understand a section of the query plan that seems to mess up my query.
The picture shows that there's a clustered index seek, followed by compute scalar, followed by sort.
The part that I do not understand, is if Compute Scalar's Estimated Rows are 327,561, than why is Sort's Estimated Number of Rows only 14? Does it assume Compute Scalar will only return 14 results even though it's input is estimated at 327,561 rows?
I tried running update statistics on the
WITH SAMPLE 100 PERCENT, it didn't seem to change behavior at all.
The suggested indexes improve performance of the clustered index seek, but the sort still estimates number of rows at 14, and so the query plan remains same.
What I need it to do is pick a different path, there's another table in query which when picked speeds up query by about 14 times (it goes from running in 14-15 seconds to 1 second). I believe the under-estimate on the number of rows in the sort is the problem, and is why the slow plan gets picked.
I'm not looking to change the query, because application generates it in a specific way, and it wouldn't be possible to change it at this point. I'm looking into how to force SQL Server to give a better estimate for the rows in the sort, which I believe would result in SQL Server picking a different execution plan.