Unfortunately, as you astutely point out, football and other sports bring in "big crowds paying lots of money" -- something that academic competitions can't bring in. There isn't a National Computer Science League that gets televised each Sunday. American football exists as an American high school institution precisely because mainstream American culture heavily advertises and hypes it.
I'd love to see schools make sports subservient to academics, but I fear it'll never happen purely for financial reasons.
I found that I could think more clearly, could sleep better at night, and was more awake during the day during the swim season. In other words, while I was in shape and health my mental abilities and general well-being improved. I would not have really internalized this lesson unless I experienced it first hand.
Sports have a place in school. It helped me learn some social skills, the value of physical activity, and that there can be a life outside of a computer. I was a pretty good swimmer (won more than I lost), but I was by far the worst on the team. I didn't gain any popularity by swimming. This falls in line with what PG said; you have to be a good athlete to gain popularity from sports.
This isn't to say that I like how important sports are in school, but they have a place. So yeah, reduce the importance of sports but I wouldn't be in favor of eliminating them.
One way to make nerds more popular is to make more of a big deal of that kind of activity. For those of us who were high school nerds, what about sponsoring math and science competitions for high school students? I'm remembering a conversation I had recently with a friend who teaches CS at the local university. He had organized some sort of programming competition for high school students. Even $500 to be used as prize money would go a long way to enhancing the prestige of something like that and of the kids who participate in it.
PG had some good points in his essay and follow-up. The kids he's talking about aren't just some random nobodies - they're US a few years ago.
After I graduated my (private) high school spent several million dollars in redoing the football field. They even put down artificial turf. And they stopped preping kids for the CS AP test. [sigh]