Azure SQL Server - Cannot get rid of the sort operator

1 vote

1 answer

41 views

index-tuning azure-sql-database sort-operator

                          I'm using Azure SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8   Apr  3 2019 13:27:00   Copyright (C) 2019 Microsoft Corporation), Premium tier 4 (500 DTUs).

Query: offer progress has a history of statuses for each offer and I want to get the first row for each offer/status. So, for offer #1 I want the first time it was set to status 1, status 2, etc.

            SELECT OfferProgressId, OfferId, DateCreated, OfferStatusDate, OfferStatusId, Name  
    		FROM   
    		(  
    			SELECT   
    				op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId,  u.Name,  
    				ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus  
    			FROM OfferProgress op   
    			LEFT JOIN [User] u ON u.UserId = op.UserId  
    			WHERE   
    				op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17) AND 
    				op.DateCreated >= '8/1/2017' 
    		) t   
    		WHERE   
    			t.FirstStatus=1 

Execution plan: https://www.brentozar.com/pastetheplan/?id=rkhP--LcN 

My goal is to get rid of the sort operator that has a warning.

My indexes: IX_OfferProgress_DateCreated, IX_OfferProgress_OfferId_OfferStatusId_DateCreated, IX_OfferProgress_OfferStatusId_DateCreated_OfferId_UserId_with_include
                        

Asked by Francisco Goldenstein (147 rep)

Apr 18, 2019, 01:55 PM
Last activity: Apr 18, 2019, 02:26 PM

Azure SQL Server - Cannot get rid of the sort operator

Related Questions