Oftentimes, even with ever increasing speed and the reduced price of hardware, a process will be too slow. Your users will typically be the ones complaining of slow performance, or you might have batch processes that don't finish within their time window. Faster processing will also help with deadlocks and concurrency issues.
This paper details basic SQL Server optimization techniques which have been successful for me.
The first rule of optimization, as one of my professors used to say, is: "Don't". That's also the second rule, too.
What he meant, assuming I understood correctly, was that in the general course of writing systems, very few things will actually need to be optimized. Either because they run rarely or because the datasets are not large or the speed of the hardware and compiler will make up for our shortcomings. Furthermore, we are not likely to avoid the weakest parts by vigilance at the coding stage--it's like looking for a needle in a haystack. It's usually 1% or less in a system that is too slow. If we waste our time paying attention to performance all the time, it will ending up costing far more than it will likely benefit.
When coding, you'll do better by disregarding optimization and instead being mindful of the best practices and the functional requirements.
So, after much of the coding is complete, you will test a system and find that some aspects of it are too slow. Or more likely, you will monitor your production system CPU with Performance Monitor and see that it maxes out more than you would like for longer than you would like. Or you may be hit with a process, likely one that runs in batch mode, that just doesnt seem to finish.
In SQL programming it's usually pretty easy to narrow down your search for slow processes because the starting point for processes is easily identifiable as a single stored procedure call. And, having that starting point, SQL trace will pinpoint exactly which statements take the most amount of time.
On the other hand, if your entire system is just slow, and CPU usage seems too high and you don't know why, you can easily setup a Profiler trace to find the offending code.
What to Look For
The usual case is a statement or process that takes up too much CPU time. This is the first thing I primarily look for. More rare nowadays is the process that experiences poor performance from too many disk accesses.