>You need to look at the query plan, if it's doing a table scan then yes it'll tank the server under load all of the time. If you have a clustered index on ClientId assuming that is an integer identity column and a non-clustered index on Client.Name then the query should perform well.

All the indexes on all tables are non-clustered but the primary key of each table which is clustered. Is there anything to adjust in there?

>But you really need to examine the query plan to see what SQL Server is ending up doing. The sizes of the lines between objects is also a good indication if SQL making an appropriate optimization choice. If you see it pushing millions of rows when it should be tens of rows then the query needs to be rewritten. You may need to update statistics on the table too. You may need to add a (WITH HINT) to the query to keep the SQL optimizer from being too stupid. I've seen a HINT greatly help performance in a database we are moving from 2000 to 2005 because the query optimizer doesn't really understand as much about the table as us humans that designed the table.

Thanks