What makes one of these queries faster?

Question

I have a sql query that I tried executing (below) that took 10 seconds to run, and since it was on a production environment I stopped it just to be sure there is no sql locking going on

SELECT TOP 1000000 *
  FROM Table T
  Where CONVERT(nvarchar(max), T.Data) like '%SearchPhrase%' --T.Data is initially XML

Now if I add an order by on creation time (which I do not believe is an index), it takes 2 seconds and is done.

SELECT TOP 1000000 *
  FROM Table T
  Where CONVERT(nvarchar(max), T.Data) like '%SearchPhrase%' --T.Data is initially XML
  order by T.CreatedOn asc

Now the kicker is that only about 3000 rows are returned, which tells me that even with the TOP 1000000 it isn't stopping short on which rows it's still going through all the rows.

I have a basic understanding of how SQL server works and how the query parsing works, but I'm just confused as to why the order by makes it so much faster in this situation.

The server being run is SQL server 2008 R2

Answer 1

The additional sort operation is apparently enough in this case for SQL Server to use a parallel plan.

The slower one (without ORDER BY) is a serial plan whereas the faster one has a DegreeOfParallelism of 24 meaning that the work is being done by 24 threads rather than just a single one.

This explains the much reduced elapsed time despite the additional work required for the sort.