Stock coolers are fine for the lower heat processors. Ryzen 7600/5800 and below, and Intel 13100/12400 and below. More power hungry processors will produce more heat, and while a stock cooler can keep an eight core CPU cool 90% of the time, during high loads it's going to shoot up to max temperature and throttle. That's less bad on AMD than on Intel, because Intels lose a lot more performance from limiting power (which is what throttling is), but it's not great either way. You're paying for performance you're not getting.
If your chassis can fit it, for desktop PCs I'd always recommend a 240mm liquid cooler (AIOs are fine). The advantage here is that water has a very high capacity to store heat, so you can run the fans at very low speeds and still keep the CPU from throttling, which makes them very quiet. Just make sure you mount it so that the pump hoses are located beneath the hoses on the radiator, AIOs aren't always bled well and if you get air in the pump it's going to cause a very annoying bubbling noise, but mounting the radiator above the pump will let any air bubbles collect there, where they won't make any noise.
If you're making a server, get an air cooler. Especially 4U can fit some very effective coolers, and the extra noise doesn't matter anyway because you've hopefully got a closet or basement to put it in.