No more buggy than any other piece of Microsoft software. But, if you have spyware on your computer, you may have severe problems.
A range of problems I have seen when people attempt to install Service Pack 2 on a spyware-infected or virii'd system:
* Bluescreen on reboot
* Missing files errors on reboot
* Welcome screen with no usernames (just the XP logo)
* Windows activation errors when trying to login
* Internet connection stops working completely
* CD drives stop working (no shit)
Windows XP Service Pack 2 statistics at my job (after cleaning spyware which EVERYONE FUCKING HAS) are something like 2000 fully successful, 50 recoverable failure, 5 total failure. (Total failures were post-install unrecoverable systems, and does not include SP2 installs which refused to even begin.)
But as I said, if your system is clean you should have no problems.