Why is there a sort showing up in my execution plan?

Question

I have the sql query below that is running very slowly. I took a look at the execution plan and it is claiming that a sort on Files.OrderId is the highest cost operation (53%). Why would this be happening if I am not ordering by OrderId anywhere? Is my best bet to create an index on File.OrderId?

Execution plan if anyone is interested.

with custOrders as
(
    SELECT c.firstName + ' ' + c.lastname as Customer, c.PartnerId , c.CustomerId,o.OrderId,o.CreateDate, c.IsPrimary
    FROM Customers c
    LEFT JOIN CustomerRelationships as cr
        ON c.CustomerId = cr.PrimaryCustomerId
    INNER JOIN Orders as o
       ON c.customerid = o.customerid 
           OR (cr.secondarycustomerid IS NOT NULL AND o.customerid = cr.secondarycustomerid)
    where c.createdate >= @FromDate + ' 00:00' 
       AND c.createdate <= @ToDate + ' 23:59' 
),
 temp as
(
SELECT Row_number() 
         OVER ( 
           ORDER BY c.createdate DESC)                    AS 'row_number', 
       c.customerid as customerId, 
       c.partnerid as partnerId, 
       c.Customer, 
       c.orderid as OrderId, 
       c.createdate as CreateDate, 
       Count(f.orderid)                                   AS FileCount, 
       dbo.Getparentcustomerid(c.isprimary, c.customerid) AS ParentCustomerId, 
       au.firstname + ' ' + au.lastname                   AS Admin, 
       '' as blank, 
       0  as zero
FROM   custOrders c 
       INNER JOIN files f 
               ON c.orderid = f.orderid 
       INNER JOIN admincustomers ac 
               ON c.customerid = ac.customerid 
       INNER JOIN adminusers au 
               ON ac.adminuserid = au.id 
       INNER JOIN filestatuses s 
               ON f.statusid = s.statusid 
WHERE  ac.adminuserid IS NOT NULL 
       AND f.statusid NOT IN ( 5, 6 ) 
GROUP  BY c.customerid, 
          c.partnerid, 
          c.Customer, 
          c.isprimary, 
          c.orderid, 
          c.createdate, 
          au.firstname, 
          au.lastname 
)

Answer 1

SQL Server has three algorithms to choose from when it needs to join two tables. The Nested-Loops-Join, the Hash-Join and the Sort-Merge-Join. Which one it selects it bases on cost estimates. In this case it figured, that based on the information it had available a Sort-Merge-Join was the right choice.

In SQL Server execution plans a Sort-Merge is split into two operators, the Sort and the Merge-Join, because the sort operation might not be necessary, for example if the data is sorted already.

For more information about joins check out my join series here: http://sqlity.net/en/1146/a-join-a-day-introduction/ The article about the Sort-Merg-Join is here: http://sqlity.net/en/1480/a-join-a-day-the-sort-merge-join/

---

To make your query faster, I first would look at indexes. You have a bunch of clustered index scans in the query. If you can replace a few of them with seeks you will be most likely better off. Also check if the estimates that SQL Server produces match the actual row counts in an actual execution plan. If they are far off, SQL Server often makes bad choices. So providing better statistics can help your query performance too.