sqlsql-serverdatabaset-sqlquery-tuning

Execution plan quotes spill level 1

Running a very simple query:

SELECT TOP 10 *
FROM WH.dbo.vw_data m
ORDER BY DateCompleted

Takes around 4 minutes.

96% of the execution is taken up by the following:

enter image description here

What does the warning mean and how is it interpretted?

The field DateCompleted isn't indexed: does this mean unless we hit an alternative field with an index, or add an index to DateCompleted it will always be slow?

Solution

  • Definitely index DateCompleted. You can see from the execution plan that 96% of the cost occurs when sorting this field, therefore it makes sense to add an index. 

    CREATE NONCLUSTERED INDEX IX_DATE_COMPLETED
    ON YourTable (DateCompleted);