Which table in the example is tableA and which is tableB? Which table is showing the clustered index seek? Can you post the execution plan? When was the last time you updated the statistics?

Wouldn't this be the same query?

select distinct

kw_id

from

StageDB.dbo.tt_wf_49b955a9_gtsang_2BC

Where

kw_id <> 6675

I mean, since the your sub-query is filtering on kw_id = 6675 and returning kw_id all the rows returned by it will return the same kw_id, 6675.