>>What is the datatype of Client.Name? What are your indexes on client? Have you looked at the query plan? Is it doing index seeks and index scans vs table scans?
>
>Client.Name is Char(20). Client.Name is indexed.
>
>>Update: Also SQLServer, like VFP, can not optimize a LIKE query if it starts with non-static text: %x can not be optimized with an index. x% can be optimized if you have a proper index on the column.
>
>The value entered starts with a character. But, one thing for sure, SQL Server is going non optimized. That is why it brings the server down at every SQL searches of this type.

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.

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.