I still have a s939 Athlon64 X2 3800+ running at stock 2.0 GHz.
I have it fit with 2 Gb of DDR-SDRAM in dual channel. I can push it up to 2.4 GHz with but a BIOS setting (and some RAM tuning) without having to change its voltage; since I can't push my BIOS further than a 20% clock increase, I used other utilities to push it past that, and it reached 2.6 before I had to change voltage, cooling and memory settings - at which point I got bored, and stopped.
But I didn't leave it alone: to its base clock speed it went back.
Reason 1: I built this rig to be silent. That means fans blowing noise is a big no-no;
Reason 2: the heaviest game I run on it never takes more than 80% CPU time (WoW, all settings up @ 1680x1050, will start showing a lower frame rate in the middle of the day in Dalaran, only because my Radeon HD 4850 doesn't have enough RAM), so I don't NEED the extra clock speed;
Reason 3: I mainly run under Linux, which looks at a CPU's microcode to set up a CPU's frequencies, so even if I set the CPU's speed in the BIOS to 2.4 GHz, it'll actually run @2.0 - except if I disable CnQ (and then , see reason 1).
Long story short: this almost 4-years-old rig (apart from the CG) has enough power for what I ask of it.
And since, under Linux, I could build Xvid to my precise CPU specs, I can encode movies very strongly at more than real time while watching the original material, I must say that not only do current apps have more juice than needed in my old CPU, I would gain zero time on CPU-intensive tasks I'd throw at my rig if I had a more powerful one.
I did overclocks on P75s (upped to 112), Cyrix 6x86 P150+/120 MHz (@133, anything higher and it would have melted), Pentium 133 (@166, only because my 430TX-based Shuttle didn't support anything more than a 83 MHz FSB), Celeron 300A (@460, once at 504 but it required such a voltage increase that I deemed it unreasonable), and Duron 950 (@1150); the latter actually showed LOWER performance at a higher clock speed, and actually got its greatest performance boost @933 with a very high FSB and very low RAM latency.
So, I learned my lesson: a good overclock is a way to improve performance, that's for sure; but the MHz-hunt doesn't necessarily bring more performance, and I consider now that a good balance between speed, stability and comfort is what to look for in such rad tuning.
As it stands, my X2 3800+ works very well @stock for my use - not for lack of trying.
I may change it the day I need more than 2 Gb of RAM.