4

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:

Without hint Adding a WITH (FORCESEEK) on the Documents table results in the following plan, which performs much better.

With hint

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.

  • 6
    Can you capture the query and add to the question please? If nothing else, EF queries can be entertaining to database folk. – Mark Storey-Smith Nov 27 '14 at 16:38
  • 2
    That's a beautiful query... – Mark Sinkinson Nov 27 '14 at 16:49
  • Are the statistics up to date? – datagod Nov 27 '14 at 17:09
  • 3
    Look at your statistics - 50k estimated 4.2 MILLION actual. Investigate that first, that's most likely the problem. – Sean Gallardy Nov 27 '14 at 17:14
  • @datagod - Yeah, also tried rebuilding the indexes. – Lee Nov 27 '14 at 17:15
1

In the version of the query with FORCESEEK, the majority of the query cost is in the key lookup. You can eliminate this key lookup by creating a nonclustered index on GridRunId and MancoId.

Have a look at the estimated cost figure in the SELECT (root) node of your plans. The optimizer will chose the plan with the lowest estimated cost. By adding a covering nonclustered index, you eliminate the need for the key lookup. This should make the estimated cost of the index seek less than the index scan. You might also want to run the query with OPTION (RECOMPILE) to make sure it uses a fresh execution plan. The index could look something like this:

CREATE NONCLUSTERED INDEX [IX_GridRunId_MancoId] ON [dbo].[Documents]
(
    [GridRunId] ASC,
    [MancoId] ASC
)
WITH 
(
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY];

The optimizer would ordinarily cost the scan higher than the seek, but the effect of the TOP means the optimizer computes costs for the desired number of rows (a 'row goal') rather than for the whole set (it estimates that a partial scan will locate the 15 desired rows more efficiently than the seek + lookup combination).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.