At least by the time it arrives any problems should have come to the surface.
I have read, (BBC site I think) that a number of programs, most of them Miscrosoft products, don't work with the new service pack, although a few 'tweaks' (MS spokesman's term) are all tha't needed to get them to work.
Remember, though. Microsoft is not likely to test non-Microsoft products. Even if they did test anything non-microsoft, they may not publish the results. Not that I think Microsoft will intentionally stab Netscape in the back with this patch, of course ;-).
Just set Firewall and Virus Checking to manual control and then you can manage them yourself with your non-Microsoft products. There are other add-ons unrelating to this that are definitely worth it like the improvments to networking, etc..
I'm not running XP so I can't really talk from experience but I have heard that a number of programs will stop running with SP 2 for XP. Additionally USB devices can stop functioning and in some cases the entire machine will blue screen and will have to be re-installed.
The company I work for has advised everyone not to install the service pack and when I talked to a friend who works for IBM he advised that they had installed a patch onto all of their machines that would prevent SP 2 from installing...