Many of the people here are missing the point about why MP won't work well in the games that we currently enjoy single player. Kahunaburger got the idea so to expand from his well made point, it goes as follows: if the game has to cater for the possibility of being played by two (or more) players, together, then compromises HAVE to be made to account for this. Further, development time, money and resources have to go into this feature as well.
The first point I make includes scalability but is not just limited to that. The game has to be designed to allow for multiple players, including world/level design, story and in the case of RPGs, skills, spells, talents, abilities, etc. A compromise would need to made on every single thing in that list. It may even entail, as with Splinter Cell: Conviction, an entirely different story/campaign. Which leads onto the other point...
The second point is the (now) age-old question that is raised every time we hear about Day 1 DLC, ODLC (On-Disc Locked Content), etc. If the developers are spending time on something, anything, other than the single-player experience, it will inevitably be worse than if they did not. That time could be better used, f.ex, making the "world" bigger, the game more stable, the textures better looking or myriad other things.
I have no objection to multiplayer games, I love some of them. Borderlands, SC: Conviction and after tonight, possibly Saint's Row 3, designed for that purpose are great. But I want to blaze my own path through Skyrim (with my choice of mods), Deus Ex, The Witcher 1/2, at my pace and get wrapped up as the protagonist in a sweeping tale at my own pace. On a 2nd playthrough I may want to cheat and have more fun with the game that way.
I understand the feeling that "it would be nice to share this particular experience with a friend who'd appreciate it as much as me" (As I suggested, the "Civil War" chain in Skyrim would lend itself well to opposing players). But it passes and I continue enjoying the game just fine without it. There are many free-to-play MMOs now like DCUO, City of Villains, LotRO which can be played with friends. Please, please leave our SP games alone, I beseech you.