I asked this same question when I though about water cooling. I had a zalman fan/hs and it was pretty decent. Pushing my Opteron 170 as hard as it could go at 2.88Ghz @ 1.475volts, I was at around 67C at peak load temperature. I lapped the CPU and heatsink and it brought it down to 63C.
After setting up my water cooling system (good pump, heatercore from a car and good waterblock) I saw about a twenty degree drop in temperatures. To me that's huge and made watercooling totally worth it.
The real world end result however, was that I could only push another 150Mhz out of my processor. For the $250 - $300 dollars I spent, I could have bought a new MB, RAM and CPU to get even better clocks and performance.
The good thing is that I won't have to spend $300 again on a cooling set up. Most of all though, it was fun to set up. It was a bit challenging=fun.
So the answer to your question "how effective is it?" really depends on your objectives. To lower temps -yes.
To reduce noise -yes (won't lower temps as well though, a highly efficient temperature removing setup is louder than an air cooled setup)
To increase your overclock - NO. It isn't very efficient at this. (But you will be able to hit slightly higher clocks. More important though, your proccesor will appreciate running at lower temps)
