If you could only change one thing to make a database system faster, what would that be? More memory, faster drives? Better queries, more indexes? Less indexes?
Well, I’d spend my one “database wish” here: Don’t get what you don’t need.
I’m constantly amazed at the code I look at when someone brings it to me for tuning. Without even looking at it, I ask “Do you REALLY need EVERY ONE of these columns?” If it’s not their code, they just say “I don’t know/care”. If it’s their code, they say “of course” – and then we find out “of course not”.
The fastest work is the work you don’t have to do – and the same holds true for the database. Yesterday I tuned a query and completely eliminated a join to another table. This particular change saved a full round trip to the server, per query. Multiplied by hundreds of times per minute, and just as the government says “A billion here and a billion there, and pretty soon you’re talking about some real money!”
So the first place to start in tuning a query is to ask yourself “do we really need all this stuff?”
And why is it the most difficult tip ever? Because sometimes you don’t own the code, or can’t make the change, or can’t find someone to give you a good reason for the selections they’ve made. In that case, you know what to do, but can’t do it, and that’s tough for a DBA. Just document what you’ve found, and perhaps in time you can revisit the code and make that change.