I've usually found two things are more than adequate to tell me exactly what's slowing things down. The Activity Monitor in Management Studio, and a server-side trace on all scripts/procs/etc.
One tells me what's going on right this moment, well enough to debug, and the other tells me what's been going on for the last week or so.
And Jeff is totally correct, that it's MUCH easier to make sure the code is well-written in the first place. He's also right that whole careers can be made off of fixing junk code that's already in use and never should have been.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon