I've been lucky with SP2 -- five installations -- no problems yet. :-) A small percentage of users experience problems, all the way from trivial to disaster. Scot Finnie has been tracking it from the beginning, and has a comprehensive guide on installing and fixing the problems with SP2.
http://www.scotsnewsletter.com/63.htm
http://www.scotsnewsletter.com/best_of/xpsp2links.htm
"Learn About Windows XP Service Pack 2": What to do before you install SP2.
http://www.microsoft.com/windowsxp/sp2/preinstall.mspx
Some spyware, worms and viruses will prevent SP2 from installing correctly. Sweep your computer for spyware and other parasites before you start.
An example is T.V. Media. Your computer will become locked in an endless reboot cycle if it is set to reboot after a crash. Microsoft has a knowledge base article that tells you how solve the problem.
"Your XP Service Pack 2 Survival Guide" -- From the January 2005 issue of "PC World" magazine. A short but excellent article on how to Install SP2 successfully.
James A. Eshelman's "WINDOWS XP SERVICE PACK 2
FAQs & BEST PRACTICES" has links to many questions that can come up.
http://aumha.org/win5/a/sp2faq.htm
Read all about Microsoft's battle to deliver secure software in eWEEK.com's special report on securing Windows. "Don't Toss That Personal Firewall" -- E-Week article
Many programs are "broken" by SP2. Luckily, most of them are obscure or special programs that most users don't use. Microsoft has two lists of the effected programs, plus other information. [842242] [884130]