This past week Erin Stellato, of SQLskills, shared with the world her first steps when starting to assess performance issues with a new client. Erin presents a series of 5 steps that she uses to confront a new environment, assessing the system and gathering data about what is wrong. As you read the piece, you may start to wonder why you've found performance tuning to be difficult in the past.
Tuning is a system is a bit of an art. The complex interactions of code at the platform and application levels, the hardware reactions to our instructions and more can be hard to understand. If you haven't seen similar problems in the past, you are relying on extrapolating your knowledge to fit this new situation. That's why the more experience you have, on a wide variety of systems and applications, the better you react in new environments. It's why it's worth paying experts like SQLskills lots of money for their time when poor performance in your environment is costing you money.
However there's a lot of science to performance tuning as well. As Erin shows, a good portion of starting to work with an environment is gathering data about the system. Having a methodical approach, a set of pre-written scripts, can help you to organize the data you can gather into a form that helps you. Glenn Berry's diagnostic scripts can easily give you a serie of data points that may clue you in to potential problems.
Most of the time you'll likely find that poorly written code or a lack of indexes are your main problems. These might be easy or hard to fix, but in either case, this is where your experience and skill come into play. You'll have to use the data you gather to make decisions about what to change, which is part of the art of tuning.
You can get better, but it takes practice. I'd suggest you try tuning queries in your own environment, even if they are running well. Find the most costly, or slowest, queries on your system and tune them, even if no one is complaining. You never know when they might start, and you want to be prepared.