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:

CREATE NONCLUSTERED INDEX index_name
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:

SELECT 
    _ma.BusinessUnitId,
    _ma.PartyRoleId,
    ROW_NUMBER() OVER (
        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
    (1,2,3,4,5,8,9,10,11)
  AND _a.AgreementTypeId = 1

…then avoids a sort:

Plan

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?
Thanks.
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.