Depends on mod, like that graphics one for GTA IV was pretty amazazazing.
But yeh I've never really bothered with mods except in Diablo 2 really.
That said SA: MP is awesome in that it lets you play with others and really opens up a lot of options for customisation (as said endless...