So, what's the final verdict on SP2? Well, all in all, it's a mixed bag, really. The enhanced security features are certainly a step in the right direction, and will go a long way to defending a lot of non tech-savvy consumers from all those internet nasties out there. For those of us that are a little more experienced, some of the new features are somewhat limited - the Windows Firewall, for example, provides no means for controlling outbound traffic - so if you've got a trojan on your computer that makes a connection with a host somewhere else, Windows will assume it is a "good" connection. Of course, if you're a more experienced user, you shouldn't have a trojan on your computer in the first place!
Many people have complained of incompatibility issues after installing SP2 - there have been plenty of horror stories about constant "Application not compatible with current service pack" messages appearing. I have to admit that this wasn't an issue for me at all - while I didn't go and run every program on the test computer, I fired up Office, Photoshop, and Age of Empires 2 without any problems whatsoever. In fact, I noticed what a few other people have - what seems to be a slight increase in the responsiveness of the explorer shell after installing SP2. Whether this can be verified through benchmarking will be up to others, though it's something I'll definitely watch with interest.
Overall, if you've managed to keep your computer up to date with all the critical security patches, have no need for a firewall, and are somewhat reticent about installing SP2, you can probably do without it - for the time being. Ultimately, though, I expect it will come to be a standard Service Pack slipstreamed into everybody's XP distribution, and if you think it might be of use now, I'd encourage you to install it (after backing up first, of course) and at least giving it a test run. Not having to use third-party popup blockers is definitely an advantage, and those of you running AMD 64/Opteron systems will be secure in the knowledge that Microsoft's new NX (No eXecute) security is protecting you somewhat better than the rest of us plebeians.
Whichever choice you make, have the best of tweaking!
How much to upgrade to x64 Edition?