Hi all, I am trying to rectify a query whcih runs once a second, and every time it runs, it pounts the CPU tremendously. I have a big wide transaction table (let's pretend it is normalized) with a status id column, primary key is a surrogate key and a secondary clustered index is on name. Let's say that the status IDs are 1-active 2-inactive 3-in progress 4-lost, and this is in a status table. There are 1 million + rows. Only about 5 rows or so are ever 'in progress'. There is a nonclustered index on statusid in the transaction table and a clustered index on the status lookup table. When I issue this query (which runs every second): select name from xtrans a join status b on a.statusid = b.statusid where b.statusname = 'in progress' why does it do a clustered index scan (according to the execution plan) and still go through just about all 1 million rows? Even if I give an index hint on StatusID it still goes through 250K rows. When I issue two separate queries i.e. select @x = statusid from status where statusname = 'in progress' and then select name from xtrans where statusid = @x this runs much faster, and only goes through the 5 rows that I am looking for. Why is that, and is there a way I can get exactly what I want (just do a seek on the 5 rows) by using one query with the join instead of 2 queries? Thanks in advance for any answers!