We stayed in a mountain bungalow during summer vacation, there was a waterfall close enough to hear (and be loud) but far enough away that it's noise sounded like a pleasant version of TV static.
I had my best night's sleep in years. My theory was that it drowned out ambient bumps and crashes that usually wake me up at night.
I recall camping at some relatives that had a waterfall near enough that you heard it any time you were outside. After about the second day or so you hardly even noticed.
As a white noise lover, that would be completely awesome sleep for me :-)