For $999, you can get your genome partially sequenced.
23andme will wend you a "spit kit" by mail. Within 6-8 weeks after you return it, they'll sequence your DNA at more than 500,000 loci of interest using an SNP array. Information at some of these loci can indicate susceptibility to certain diseases and conditions, like prostate cancer, sensitivity to alcohol, or lactose intolerance. Common differences in these loci can also help determine your ancestry.
You can later retrieve your profile results on the web, and read about how recent medical findings may affect you, learn how your genes relate to those of your relatives or others around the world.
I'd guess that that most users today are doing it for the novelty and trivia factors. This will probably change as these tests (and our knowledge of genetics) become even more sophisticated.
23andme's waiver asks you to acknowledge that "You may learn information about yourself that you do not anticipate." Indeed, one testimonial in a comment on their blog reads:
I handed out 23andme kits to my relatives at Christmas, and a few weeks later my wife found out that her "father" was not really her father. Needless to say, it turned out to be not such a great Christmas gift! But I love it anyway...