Its a massive subject, places to look are the estimated and the actual query plans, use profiler to see actually whats running on SQL, use perfmon to look at the server and see if anythings slowing down - cpu, buffer io, memory etc, use sp_who2 to see if there are locks knocking about, make sure the stats and indexes are up to date (make sure the tipping point of the index isn't being compromised). If you are using 2005 use the Performance Management download from MS. It contains a whole load of reports and stuff to have a look at. And dont forget the basics, well written SQL, simply coded looking at tables with minimal complexity will always perform better and more consistently than monsters!