With only 4 cables, two of which are ground, it seems like it has way too many pins. Why is this so?
The connector shown in the image is a 15-pin SATA connector. Pin description:
The connector can have 5 wires. And this particular connector shown in the question is missing the 3.3 V (orange) wire.
The new SATA power connector contains many more pins for several reasons:
3.3 V is supplied along with the traditional 5 V and 12 V supplies. To reduce impedance and increase current capability, each voltage is supplied by three pins in parallel, though one pin in each group is intended for precharging.
Five parallel pins provide a low-impedance ground connection.
Two ground pins, and one pin for each supplied voltage, support hot-plug precharging. Ground pins 4 and 12 in a hot-swap cable are the longest, so they make contact first when the connectors are mated. Drive power connector pins 3, 7, and 13 are longer than the others, so they make contact next. The drive uses them to charge its internal bypass capacitors through current-limiting resistances. Finally, the remaining power pins make contact, bypassing the resistances and providing a low-impedance source of each voltage.
This two-step mating process avoids glitches to other loads and possible arcing or erosion of the SATA power connector contacts.
Pin 11 can function for staggered spinup, activity indication, both, or nothing.
Source: wikipedia article on Serial ATA.
Can't answer this directly, but I will add to the above answer- the 3.3V pins have been repurposed for pins 1 and 2. SATA 3.3 specifications create a power disable, to allow the manual or top-down control of devices. This is intended to allow power cycling of a drive, perhaps remotely, for instance.
So, that 3.3v line is apparently 3.3 no longer- at least for 2 of 3...
To sum up, for products supporting the optional SATA 3.3 power disable (PWDIS) function, the third pin (P3) of the SATA connector is now assigned as the Power Disable Control pin. If P3 is driven HIGH (2.1V-3.6V), the power to the drive circuitry will be cut. All drives with this optional feature will not power up if a legacy SATA connector is used. This is because P3 driven HIGH will prevent the drive from powering up. The easy, and not so elegant, solution is to use a 4-pin Molex to SATA connector or a power supply equipped with SATA connectors that follow the SATA 3.3 specification. Source-Toms Hardware Western Digital Whitepaper
The comments below, in the Tom's Hardware article, point out that it would have made much more sense to have the Power disable occur when the pin is driven low, rather than high- as this would completely avoid the problem proposed above.
As mentioned previously, according to the SATA-IO Standards group, the 11th pin provides staggered spinup AND an activity indicator- presumably for flashing LEDs and such.
From a SATA-IO Press Release (SATA-IO is the International Organization that owns and manages Serial ATA specifications as open industry standards.):
Additional advancements in the revision 3.3 specification include:
• Power Disable: Allows for remote power cycling of SATA drives to help ease maintenance in the data center.
• Single-Pin Activity Indicator and Spin-Up Control: An activity indicator and staggered spin-up can be controlled by the same pin, adding flexibility and providing users with more choices.
• Transmitter Emphasis Specification: A new transmitter specification increases interoperability and reliability in electrically demanding environments. SATA-IO Press Release
You would think the answer would be found, in depth on the SATA-IO website, but I have been unable to find it. Much of it is behind a paywall, unfortunately. Perhaps someone with this arcane answer can provide more info as to the thought process behind it. It would be in the 1.0 specifications I imagine.
A closely related discussion, I have cross-posted as well- don't shoot me.