Hi,

I would have similar observation as Luis and would concentrate on the requirements and constraints. Your task is to tune a bad performed query written by someone else. Your constraints say that you cannot add/change indexes, clear cache, update statistics etc. So, you need to perform two tasks:

1. to understand why the query performs poor

2. to rewrite the query

For the first task you would need to observe and analyze the execution plan, to check discrepance between estimated and actual rows, if you find the discrepance to check statistics, to check if the problem is caused by using cached plan previosly created for some other parameters or literals...

After you are done with analysis and you have an idea how to rewrite the query you should bear in mind that you need to return the same result to the client. You cannot eliminate some columns because of Key Lookup (as Luis said, you don't know which columns are required - this is a business logic and it is unknown to you) and if the business logic requires all persons having 'uis' somewhere in the last name you can warn them that this could be slow but you still have to provide the result. The same is for Sort operator. Yes, it is memory consumer and you could eventually remove it if it's caused by final ORDER BY. You can ask business guys if they really need ordered results and you could eliminate it. If business guys are available at the time you tune the query. And if they say OK. First is very rare the case. Second you can guess. Other Sorts are usually in execution plan because an index is missing, but you cannot eliminate them due to constrains you have.

The point with functions in WHERE clause is OK, but with explanations done by Luis. You should try to rewrite predicates to make them SARG-able, but not forget that you cannot change the results - if business needs NOT LIKE, you don't have a choice, but to accept it. You can try to rewrite JOINS to subqueries and vice versa, to use CTE, temp tables, table variables, to reach the same logical result with different queries.

Remember that as someone who tunes a query written by someone else you usually don't have contact persons to discuss with them if it's a good idea to return given columns and given number of rows. In this particular task someone expects from you to make the query execution faster in short time without changing the query interface.

HTH.