The sickled cells aren't flexible enough to pass through our capillary beds, and so they lodge there, causing obstruction. The results are varied but uniformly bad: excessive pain (from ischaemia), organ damage and rampant haemolysis (from splenic removal of the atypical red blood cells) can all occur. The disease is often fatal.
Why would natural selection permit this terrible allele to remain in the gene pool? There are many factors that contribute here, but the most striking (and most important) is that people heterozygous for the allele show a marked resistance to malaria infection.
Let me explain. Sickle cell disease is an autosomal recessive trait, meaning that a person has to have two (out of two) copies of this bad allele before he shows symptoms. Heterozygotes (that is, people with only one bad copy; the other one is normal) generally don't show symptoms of sickle cell disease, but they do display a marked resistance to malaria. It appears that they produce enough sickled cells to disrupt the malaria parasite, which usually makes extensive use of normal red blood cells.
So this is the chief reason why the sickle cell allele is maintained by natural selection. Its undoubted deleterious effects in a homozygous individual are balanced by its positive effects in a heterozygous person. Geneticists would identify this as an example of a balanced polymorphism (a polymorphism is a situation in which there is more than one allele for a gene).
Of course having resistance to malaria only counts as a positive if you otherwise stood a chance of getting it; in areas without malaria, the sickle cell allele would have a uniformly negative effect on fitness, with no balancing redemption. All of this neatly explains the close match between the historical distribution of malaria:
... and the ancestral distribution of the sickle cell allele.
Modern day descendents of these people who now live thousands of miles away (e.g. African-Americas) continue to suffer from sickle cell disease. Evolution is simply not quick enough to delete this allele from the gene pool in a few hundred years, but it is doubtless trying!