Eu aş crea un index (non-clustered) care să conţină şi coloana termination_date (în afară de effective_date), de exemplu:

CREATE NONCLUSTERED INDEX index3 ON my_table (area_sink_id, data_status_id, data_type_id, data_source_id, effective_date, termination_date)
-- am trecut mai sus şi celelalte coloane din WHERE, chiar dacă fac parte deja din indexul clustered, în măsura în care ai condiţie cu =, nu cu IN
-- ar fi bun şi eventual un INCLUDE în care treci toate coloanele care sunt în SELECT sau în WHERE, cu excepţia celor scrise mai sus

Ai putea să rescrii condiţia respectivă (eliminând OR-ul) astfel:

'2008-09-11 10:00PM' BETWEEN effective_date AND ISNULL(termination_date,'99991231 23:59:59.997')

... deşi nu cred că o să aibă un efect semnificativ.

Oricum, în cel mai bun caz cred că o să obţii un index seek care are ca "Seek Predicates" doar un "effective_date<=@DataRespectiva", iar condiţia pe termination_date va fi pusă doar la "Predicate" şi asta înseamnă că acel seek ar putea citi o mare parte din paginile din indexul respectiv (dacă mai ales dacă data e mai recentă), aşa că e important ca acel index să includă toate coloanele de care ai nevoie, dar să fie cât mai mic.

De fapt, cred că problema este că obţii acea selectivitate foarte bună doar din combinaţia condiţiilor de mai sus, însă Bogdan a întrebat care ar fi numărul de rânduri obţinute dacă foloseşti fiecare parte a condiţiei respective, de exemplu:
1. câte rânduri obţii dacă foloseşti WHERE area_source_id IN (15, 37) ?
2. câte rânduri obţii dacă foloseşti WHERE area_sink_id IN (15, 37) ?
3. câte rânduri obţii dacă foloseşti WHERE data_status_id = 1 ?
4. câte rânduri obţii dacă foloseşti WHERE data_type_id = 2 ?
5. câte rânduri obţii dacă foloseşti WHERE data_source_id = 3 ?
şi mai ales:
6. câte rânduri obţii dacă foloseşti WHERE effective_date <= '2008-09-11 10:00PM' ?
7. câte rânduri obţii dacă foloseşti WHERE termination_date >= '2008-09-11 10:00PM' ?
8. câte rânduri obţii dacă foloseşti WHERE termination_date IS NULL ?

Răzvan