I do very little consulting these days, perhaps 4-5 weeks per year, and that only to make sure I maintain 'real world' skills that I need to stay credible in the classroom. Much of that consulting is basic performance reviews of existing code, and today I've got a couple notes about things I've noticed over the past few years:

• Most clients will prefer that you identify index changes or simple tweaks to existing code (hints for example) that require minimal testing to verify the performance improvement and that the results are still correct
• Most clients do NOT want to rearchitect their databases, don't want to change data types, and don't want to change their business processes in the name of performance. They want you to make what they have work if at all possible.
• It's common to find queries that do a lot more work than needs to be done, bringing back all rows without a where clause for example. Fixing them is easy technically, but it means the DB manager has to sell the change to the business (or analyst) - and they tend to be averse to change, "we might need to see the order history from 1995 some day".
• Clients fall into two categories, those that want you to figure out the changes and implement them for testing, and those that prefer you spend time identifying problems but not necessarily writing the code to correct them. Neither is wrong, just make sure you know which type you have.
• Document your recommendations/changes as you go, leave them with a nice roadmap of what you did and why - with luck it will help you get your head in the game faster if you get invited back.
• The only thing the client cares about is duration. We typically tune to lower reads and that results in lower duration, but they only care about duration because that is all the user sees!

I learn something every time I do these engagements, and actively look for those aha moments.Much of is mundane, getting rid of scans and key lookups. More fun - but harder - is digging through complex processes (especially ETL) to see where you can make a nice gain. A day of it is fun, a week is exhausting.

Good stuff to think about if you're bringing in a consultant, make sure you know and they know your expectations.