I haven't had any trouble with SP2 on 3 different XP computers. As Evo mentions, you rarely hear about the successful stories, only the ones with trouble. To me it is worth it and very necessary.
It does cause problems, but that is usually because software developers took advantage of the sloppy security of previous Windows versions. Now that they are trying to scure the system, some things will break.
The security warnings can be turned off for those that know what they are doing, but to me are a good thing to have for those that don't. Until the general population becomes more comfortable with computers, there is some need to simplify things.
Best way to decide is to see look at the progrmas you use and see if there are any issues to be worried about. Most software developers put something on their websites about compatibility with SP2.
goates