What is the appropriate Index defnition to get rid of the sort operation?

dgate 2016-09-12 18:59:47

I tried creating different versions of a Nonclustered index but I am not able to avoid the sort operation. The difficulty I have is understanding how the PARTITION BY in the ROW_NUMBER() function affects the sort operation especially when the columns referenced in the PARTITION BY clause come from two different tables. What is the index I need to create to completely remove the sort operation?
Thank you so much for the advice.

SQLkiwi 2016-09-13 08:49:36
Derived DDL:

CREATE TABLE dbo.Agreement
    AgreementId integer NOT NULL,
    AgreementTypeId integer NOT NULL
CREATE TABLE dbo.MemberAgreement
    MemberAgreementId integer NOT NULL PRIMARY KEY,
    AgreementId integer NOT NULL,
    BusinessUnitId integer NOT NULL,
    PartyRoleId integer NOT NULL,
    StartDate_UTC datetime2 NOT NULL,
    [Status] integer NOT NULL

A suitable index would be:

ON dbo.MemberAgreement (PartyRoleId, StartDate_UTC, MemberAgreementId)
INCLUDE (AgreementId, BusinessUnitId, [Status])
WHERE [Status] IN (1,2,3,4,5,8,9,10,11);

The query:

        PARTITION BY _ma.PartyRoleId, _a.AgreementTypeId 
        ORDER BY _ma.StartDate_UTC, _ma.MemberAgreementId) rowNum
  FROM MemberAgreement _ma
  INNER JOIN Agreement _a ON _ma.AgreementId = _a.AgreementId
  WHERE _ma.[Status] IN
  AND _a.AgreementTypeId = 1

…then avoids a sort:


In general, the index required is POC: PARTITION BY, ORDER BY, Covering. It is lucky in this case that AgreementTypeId is a constant.

dgate 2016-09-13 15:01:39
Thanks SQLkiwi. I created the index but the optimizer is ignoring the new index and I am still seeing the sort operation. The sort operation is 73% of the total cost. Is there anything else I can consider?
SQLkiwi 2016-09-14 01:55:07
You could try forcing the index with a hint. Or maybe remove the filter on it. Or add table definitions to your question (I had to guess at the DDL) and I'll have another look.