This feature is mostly aimed at gamers and gives better control over the mouse.
It's also a matter of personal preference.
With a heavier mouse you have to exert more force to move it, so that slight movements that aren't meant to be relayed to the mouse through the hand get negated. If you have a very slick mouse-pad, which is a feature gamers like, less weight would make it gather less momentum, and vice versa.
The idea behind using extra weight is so help you keep from over overshooting your motions. With a lighter mouse your initial reaction may be faster but you may not have the friction desired to proper stopping and need to backtrack a little to correct the aim, thereby negating the speed gained from the lighter weight.
Some games may require exact motions, so a heavier mouse helps. Some games require quick motions, so a lighter mouse helps.
It also depends on the physical attributes of the user, as some people have larger and stronger hands. Adjusting the mouse's weight is one way of making it comfortable to use.
Also, some people feel that a heavier mouse is better class, and a lighter one feels cheap.
Some mouses can be loaded with multiple weights, and are sold with multiple different weights. By combining weights, the user can arrive at the exact weight he feels comfortable with in his work or with his game.
For example, from Logitech G5 Laser Gaming Mouse Review :
The Logitech G5 has an advanced weight
tuning system to fine tune the inertia
of the mouse, or even throw it off
The system is comprised of two parts,
a transparent orange cartridge, and a
tin case for 16 circular weights -
half of them are 4.5g, the other half
1.7g. Up to eight weights snap into the cartridge's staggered array to
give the G5 up to an additional 36
grams of weight. Gamers can now
imitate the weight of AA batteries in
a corded mouse, go as light as
possible without even using the
cartridge, or go somewhere in between.
The G5 36 gram adjustable weight
cartridge gives you hundreds of
variations on balance and heft -
including one that's perfect for your
unique gaming style.
Part of the fun of owning this mouse
has been experimenting with different
total weights and weight "balances" -
I finally settled on a featherweight
setup with 4.5g weights at the top
corners so it "digs" just a little
when I push on the mouse, and I might
change it again. There's no wrong way
to load your mouse; like I said, it's
fun to see how just a little more or
less weight changes the experience.