The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX agreement_idx_agreementid ON dbo.Agreement (AgreementID);
CREATE INDEX person_idx_personid ON dbo.Person (PersonID);
CREATE INDEX personagreementinv_idx_personagreementinvolvementrole ON dbo.PersonAgreementInvolvement (PersonAgreementInvolvementRoleID);
CREATE INDEX personagreementinv_idx_value_personagreement ON dbo.PersonAgreementInvolvementRole (Value,PersonAgreementInvolvementRoleID);

The optimized query:

SELECT
        ag.StartDate,
        ag.EndDate,
        ag.PriceRate,
        ag.TaskDescription,
        pc.Name AS ClientName,
        pec.Name AS EndClientName 
    FROM
        dbo.Agreement ag 
    INNER JOIN
        dbo.PersonAgreementInvolvement pai 
            ON ag.AgreementID = pai.AgreementID 
    INNER JOIN
        dbo.Person pc 
            ON pai.PersonID = pc.PersonID 
    INNER JOIN
        dbo.PersonAgreementInvolvementRole pairc 
            ON pai.PersonAgreementInvolvementRoleID = pairc.PersonAgreementInvolvementRoleID 
    INNER JOIN
        dbo.Person pec 
            ON pai.PersonID = pec.PersonID 
    INNER JOIN
        dbo.PersonAgreementInvolvementRole pairec 
            ON pai.PersonAgreementInvolvementRoleID = pairec.PersonAgreementInvolvementRoleID 
    WHERE
        pairec.Value = 'Client' 
        AND pairc.Value = 'EndClient'