I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL):
SELECT TOP 15 HouseHoldingRuns.Id, ( SELECT TOP 1 ManCoId FROM ( SELECT TOP 1 GridRuns.Id FROM GridRuns WHERE HouseHoldingRuns.Id = GridRuns.HouseHoldingRunId ) AS [FirstGridRun] INNER JOIN Documents ON FirstGridRun.Id = Documents.GridRunId ORDER BY Documents.Id ) FROM HouseHoldingRuns ORDER BY HouseHoldingRuns.Id
Which produces the following plan:
WITH (FORCESEEK) on the Documents table results in the following plan, which performs much better.
Why is SQL Server getting it so wrong? I'm guessing the problem lies in the large difference between estimated and actual rows, but I don't know how to fix that... (I've tried updating all stats and rebuilding indexes).
Note: I don't really want the
ORDER BY Documents.Id in there, but put it in for Evgeny to see the results of his suggestion.