There are really no cons to applying SP4, only caveats. You just need to prepare for the installation. Read through these stories from folks who have already done it and given their insight.
And, read about those post-SP3 hotfixes that Microsoft did not include
This was first published in October 2002