It's only a good idea if the stock fan isn't capable of keeping your CPU below a certain temperature in your given setup. So the largest variables will be: Case, Case Fans, Number of Heat Emitting Components (i.e. video card(s), hard drives) and of course Ambient Temperature.
So take my CPU (e6600) for example; When it ran at stock frequency with the stock fan, it would exceed 65C-70C under full load. This was largely due to this rooms high ambient temperature in the summer and I had little choice but to upgrade the cooler to something more capable.