The noise comes from the processor’s power circuit and can only be heard when the processor is in C3 (clock-stopped or sleep) power state. The noise is generated from the processor's power circuit, which is caused by a phenomenon referred to as the Piezoelectric Effect. When a specific voltage is applied to these solid state components, they begin to resonate producing sounds that fall within the range of human hearing (15 – 20 KHz).
Misleading gibberish but roughly true. The noise is caused by periodically varying voltage across ceramic caps. It doesn't happen when the CPU is in C3 state, it happens when the CPU periodically changes C states or P states (possibly including turbo boost). There is also some inter-core dependence, in that usually all cores run from the same voltage and it has to be the highest voltage requested by any core.
You can play with it using tools like c2ctl or k10ctl. The noise goes away if you configure equal voltage on all P states and perhaps disable some of the deep C states (or keep one core at 100%).
It's been awhile, I don't remember details.
edit
I also think that no resonance is required, it will work at any frequency. That being said, equal excursion at lower frequency yields lower SPL from any acoustic transducer, so you typically only hear the highest frequencies. As for the real content of the noise, it's likely 1kHz and tons of its harmonics because many things in software are scheduled with millisecond granularity.