EdVassie (12/7/2016)
As already said, the DDL and plan is really needed to give the best answer.However, the index suggested by Luis is guaranteed to require a clustered index lookup to satisfy your query, which could easily cause the optimiser to ignore it due to the cost of the lookup.
A covering index has a better chance of being used:
CREATE INDEX IX_GiveAnAppropriateName ON hcs.AdminObservation( TherapyAdmin_ID) INCLUDE(VisitObservation_ID);
However, even with what seems to be the best index possible, if the optimiser thinks that just about every extent for the table will be accessed by your query then a clustered index scan may still be the best performing access path.
Actually, you don't need to make it a covering index. The nonclustered indexes include the clustered index key on them. Here's a demonstration that there's no index lookup on AdminObservation with the index I proposed.
CREATE TABLE AdminObservation(
VisitObservation_ID int,
TherapyAdmin_ID int,
DateColumn datetime
);
CREATE CLUSTERED INDEX CI_AdminObservation ON AdminObservation( VisitObservation_ID);
INSERT INTO AdminObservation
SELECT TOP(2000000)
ISNULL(ABS(CHECKSUM(NEWID())) % 100000, 0),
ISNULL(ABS(CHECKSUM(NEWID())) % 100000, 0),
CAST( '2010' AS datetime) + RAND(CHECKSUM(NEWID()))*10000
FROM sys.all_columns, sys.all_columns b;
CREATE TABLE TherapyAdmin(
TherapyAdmin_ID int
);
INSERT INTO TherapyAdmin
SELECT TOP(100) TherapyAdmin_ID
FROM AdminObservation
ORDER BY NEWID();
CREATE CLUSTERED INDEX CI_TherapyAdmin ON TherapyAdmin( TherapyAdmin_ID);
SET STATISTICS XML ON;
select TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber from AdminObservation
where TherapyAdmin_ID in (select TherapyAdmin_ID from TherapyAdmin )
order by VisitObservation_ID;
SET STATISTICS XML OFF;
CREATE INDEX IX_GiveAnAppropriateName ON AdminObservation( TherapyAdmin_ID);
SET STATISTICS XML ON;
select TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber from AdminObservation
where TherapyAdmin_ID in (select TherapyAdmin_ID from TherapyAdmin )
order by VisitObservation_ID;
SET STATISTICS XML OFF;
DROP TABLE TherapyAdmin, AdminObservation;