I am a Gold HHonors member (because of an e-Rewards promotion last year, not earned) and have been pleased with the service and upgrades at Hilton properties.
But the program I swear by is not a points-based program--it's Wyndham ByRequest. For the money (especially with the discounts I've found this year) Wyndham has some of the nicest properties around, and the extras you get as a ByRequest member are amazing (free calls, including long distance, free high-speed Internet access, upgrades, newspapers, snacks, etc.). I stay at Wyndhams as often as possible.