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.