Its very easy to get bogged down trying to squeeze the last drops of performance out of your system by fanatically creating indexes, changing obscure system settings, re-writing queries to run 0.5% faster, etc.
Take the easy wins first, there will always be a few simple changes you can make that will give the biggest gains. Have automated jobs setup to collect and log various metrics, such as disk space, file and table size, index fragmentation, I/O, etc. Using this info (and some appropriate server-side trace results) you can easily identify your top 'X' most expensive queries, your worst fragmented tables, your largest tables, etc. These will all give immediate boosts if taken care of properly.
I admit this is advice based on larger systems (1/4TB+), but is just as relevent on small databases suffering performance problem.
Collect data, analyse it, identify which "fixes" will return the greatest benefits for the least amount of effort, and do those first. Then sit back and see how it goes. As time passes, you will identify more "easy fixes", which can then be taken care of.