When developers and SQL Server database administrators work together to make an app faster, it shouldn’t just be the DBA working alone in the dark. If the DBA takes the slow code, goes into his cube, tweaks it (and indexes) and comes back out with a faster system, that fixes the problem once – but it doesn’t fix the long-term problem.
For the best long-term results, a DBA should pick a member of the application team and cross-train them on database tuning skills. Do performance tuning sessions together, walking the developer through the analysis. That way, they see the performance problems with their own eyes, they learn to recognize signs of problematic code, and they know how to avoid writing it in the first place.
Perfect example: a few weeks ago, a couple of our developers rewrote a slow user-defined function from scratch. They then tested it in their development environment and were surprised to find that it was slower than the live version. Another emergency came up, and now that I’ve got some time to tune it for them, they don’t want me to bother. They don’t want to waste my time tuning something that they’re probably going to rewrite again anyway.
I told them to give me the code anyway, and at least I can review it to see what made it slower than they’d expect. Even if this code will never see the light of day, it’ll at least be a good training exercise.