Two recent examples of torture apparently working (if you include waterboarding as torture) Khalid Sheikh Mohammed and Abu Zubaydah. Reportedly both those individuals became quite cooperative and compliant after extended periods of waterboarding. My understanding from some readings I have done on the subject is that quite often the mere threat of being sent for questioning in Egypt (part of the rendition program) or similar countries was sufficient to gain cooperation.
Personally I do not believe waterboarding is torture although it does push the senses of any human to the extreme. I guess I think of torture as actual physical damage such as cutting, beating, burning, and other extreme conditions and not psychological damage such as standing, sleep deprivation, and waterboarding. Finally, I do believe the fear factor as you mentioned of being physically tortured is a great motivator for someone to talk.