Execution plan - cost of sort

Question

I've got a procedure with a Sort operator of 92%. Here is the execution plan: here is the script:

CREATE   PROCEDURE [Specification].[GetSpecificationsByPage]  
(  
  @Next int,  
        @Limit int  
)  
AS  
BEGIN  
 SELECT   
 ps.Id
 psdc.Timestamp,  
 ps.CreationStatusId,  
 ps.ErrorReason  
 FROM   
 [Specification].[Specifications] ps WITH (NOLOCK)   
 OUTER APPLY (SELECT TOP 1 psd.Timestamp  
      FROM Specification.SpecificationDetails psd  WITH (NOLOCK)  
      WHERE psd.SpecificationId =  ps.Id  
      ORDER BY psd.Timestamp) psdc  
  
 ORDER BY ps.[Id] asc  
 offset @Next rows  
 fetch next @Limit rows only  
END  

I've added an index for column SpecificationId for table :Specification.SpecificationDetails that includes column: Timestamp even added index for Timestamp asc but it still have the cost of 92%.

The only way i've got is to replace to : ORDER BY ps.Id or remove the line in the select: psdc.Timestamp

what index do i need to continute?

Answer 1

The SORT operator after each seek iteration can be avoided with a composite index:

CREATE INDEX IX_SpecificationDetails_Id_Timestamp ON 
    Specification.SpecificationDetails(Id, Timestamp);

The leftmost Id in the index key will facilitate the seek (as in the current plan). The Timestamp key column will allow an ordered scan in the seek operator, eliminating the need for a subsequent SORT to satisfy the TOP 1...ORDER BY Timestamp criteria.