The science behind it, as described when I had a couple of sessions, is that when you do excercise you tear muscle fibers - its a natural process and you can't avoid. After exercise those fibres heal, but they don't always heal in a nice ordered way so instead of the fibres being all nicely aligned, they heal in a lump - i.e. a knot. These knots restrict blood flow to part of the muscle (reducing muscle performance and purging of lactic acid and making you more susceptible to cramp), they cause stress points and make you susceptible to injuries. The sports massage breaks down these knots and, along side the cruicial and required after massage stretching, gives a chance for these fibres to heal in a way where they are aligned and don't cause a knot. It worked for me as I was getting a recurring injury. The key is that you have to spend time stretching post exercise to prevent the formation of knots.
I'm sure there are other supposed benefits, but the one above is what was explained to me and it worked - before I was as knotted up as a piece of old rope, after I had very few knots and no more recurring injuries and less cramp. I now also do alot of stretching post exercise and this has helped keep things nice. Its worth a try, though I can't justify or afford to doing it on a monthly or so basis as you're supposed to.
It does hurt, the girl who did mine got her elbow into the knots and her full body weight behind it to forcebly break them down - it can be exctuciating, and it is sore for a good 24hrs after, though the second massage was far less brutal as there were less knots to go at and those that were there took less effort to break down.