I would say that you don't need in-depth application knowledge to tune SQL performance at all.

You do need to figure out why the query is written the way it is, and that to some degree involves a basic amount of business knowledge. IF you can read T-SQL and understand database design, you can figure out the business as you go by asking questions and not making assumptions.

You definitely need to know how to read T-SQL, run profiler (if you don't know what the problem is ) and know how to read a query plan and use IO statistics to see where the hard-hitters are.

The first thing I usually do is re-write the T-SQL trying to minimize problem areas in the plan.

Basically I try to reduce the number of rows searched and looped through... maybe take a sort out of the plan or an aggregation.

If all that fails, I'll add an index or add TO an index, or maybe even use an indexed view in a pinch.

Thanks for reading!