If your in desperate need of more security then go for it. If you like to actually use your computer wait a few months until they get the bugs out.
Here is what MSoft has to say about it:
http://support.microsoft.com/default.aspx?kbid=842242 Here is a brief list of programs (games only) that SP2 crashes out with:
Age of Empires II: Age of Kings
Chess Advantage III: Lego Chess
Combat Flight Simulator 3
Command & Conquer: Generals
Command & Conquer: Generals Zero Hour
Command & Conquer: Red Alert 2
Conflict: Desert Storm
Dead Man's Hand
Earth & Beyond
Elite Forces Vietnam: Special Assignment 2
Freedom Force
Max Payne 2: The Fall of Max Payne
Medieval Total War
Midnight Outlaw: Illegal Street Drag
Moto Racer
NASCAR Racing 2003
NBA Live 2000
Need for Speed: Hot Pursuit 2
Roller Coaster Factory
Scrabble
Serious Sam: The Second Encounter
SimCity 4
Star Trek: Starfleet Command III
Star Wars: Knights of the Old Republic
Starcraft
Tom Clancy's Rainbow Six 3: Raven Shield
Tribes 2
Unreal II
Unreal Tournament 2003
Unreal Tournament Game of the Year Edition
Warcraft III: Reign of Chaos Collector's Edition
So like I said, if security is an issue for you then it might be a worthwile update. If you play any of the games listed, and other non-gaming programs that aren't listed, you will have problems.
Hell, where my brother works they were installing SP2 on some 1ghz machines and 3 out of 5 wouldn't boot and needed a new installation because even rescue didn't help...