The perfect index for the above query would be a multi-column index spanning all three columns in matching sequence and with matching sort order:

Don't worry about performance too early though - it's more important to get the design right, and "right" in this case means using the database the way a database is meant to be used, as a transactional system.

The only issue left to tackle is the performance issue. In many cases it actually turns out to be a non-issue because of the clustered index on AgreementStatus (AgreementId, EffectiveDate) - there's very little I/O seeking going on there. But if it is ever an issue, there are ways to solve that, using triggers, indexed/materialized views, application-level events, etc.