As far as this system is concerned, it's a DW backend for a Microstrategy backend, so many of the peaks are not tunable. End suers pick a few things and it generates 50 lines of SQL (or more). So we've built it for the 80-90% of the load. Just can't get the last without a huge upgrade, and even then who knows.

I agree that most of your tuning is in the application and possible indexes. I've had a couple seminars where we really run profiler over a few days, capturing SQL and then grouping and sorting to find the top 10 worst performers and focusing there.  We also look at the frquency of these queries and use that to gauge whether to tune them. I might have a 2 hour query, but if it's run once a year I might not tune it. But a query that takes 2 minutes and it run 100 times a day might be something to focus on.

The reality is that the most frequently complained about queries are the ones we focus on, trying to tune or rework them (and the app) to get the perception of performance down.