Second, I think there are semantic issues here. I can certainly see the tedium in the Civ 4 unlimited pile of stuff model. But would two units per hex really create a litany of horrors? Do tactics vanish when you can put more than one unit in a hex?
Agreed. The problem with war in Civ4 isn't the concept of stacking. The main problem is the sheer number of units that come into play later in the game, which is tedious to manage and reduces the significance of any single unit to almost nothing. The other problem is that there's no limit on stacking, which means you can concentrate all that force on one point.
This is why I liked Civ5's 1upt rule at first. It forces units to spread out more, but goes too far in that direction. It doesn't fit the scale of a typical Civ map, so the land gets cluttered with units. It's also part of the reason why human players can get such ridiculous K/D ratios against the AI. Since they can't concentrate their units, you never need to face anywhere near their full force at once. In Civ4 I actually fear surprise invasions from the AI, because it can load several galleons full of units, declare war, and ram them all into one of my coastal cities on the same turn. In Civ5 I barely even care if the AIs declares war on me, since it's unlikely that they'll actually capture cities.
I think it would be fun to allow mini stacks of 4 units, make units somewhat cheaper, and limit each civ to 20 units or so. You'd still have to make interesting decisions about how to compose your stacks and where to deploy them. It would allow more concentration of force, but not too much.