Your processor is going to be an AM2 socket, or maybe socket 754. So you'll be a bit limited to what processor you can get- I Think Sempron, Athlon 64 X2 and Phenom use Am2 (but older mobos may not support BIOS for Phenom).
As far as CPUs go, I'd currently stick with Intel, they are ahead at the moment in my opinion.
All that aside, improving your CPU will give some improvement in performance, although increasing your RAM will also help. For watching Unc's little film, a decent video card would be useful too.
It depends how far you want to go. Personally, I wouldn't spend the money on a CPU to fit that board, I'd save up and rebuild a better system. but if you're swapping to an Intel based system you will likely need to change most things.......