3

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?

Darren
  • 68,902
  • 24
  • 138
  • 144
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • 1
    As I can see, you are using the view? View can contain many JOINs. So your request can be executed for a long time. – Devart May 23 '13 at 13:20
  • Whoever down-voted please can you explain the problem with this question? – whytheq May 23 '13 at 15:51
  • No worries - I've asked for a moderator to look as Darren Davies got down-voted as well - his answer seems like a good suggestion and no real reason why it should be down voted. You are correct I'm querying a view but if I switch to using the DateKey in the script then it runs very quickly. – whytheq May 23 '13 at 17:00

2 Answers2

8

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);
Darren
  • 68,902
  • 24
  • 138
  • 144
6

Sort Warnings are raised by SQL Server when a sort operation cannot be done in memory and must spill to tempdb

This article answers your question on Sort Warnings (link updated again, thanks to @quarkonium:

http://blogs.solidq.com/en/sqlserver/identifying-solving-sort-warnings-problems-sql-server/

Stoleg
  • 8,972
  • 1
  • 21
  • 28
  • +1 excellent reference (hoping to get some training with those guys some time in the future) – whytheq May 23 '13 at 15:29
  • Frankly I think this is the real answer to the question but alas it seems the URL has changed over time. The info is now hosted here: http://www.solidq.com/identifying-solving-sort-warnings-problems-sql-server/ – deroby Mar 14 '14 at 12:41
  • Moved again: http://blogs.solidq.com/en/sqlserver/identifying-solving-sort-warnings-problems-sql-server/ – quarkonium Mar 02 '15 at 02:12