If you haven't read it yet there is a nice document posted here that has overall view of the great enhancements made in SP2. For your convenience the extract of the document is below…
Performance and Availability Improvements
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 include several fixes and enhancements designed to improve performance, availability, and stability in your server farms.
Index fragmentation is a major cause of performance degradation in Microsoft® SQL Server™ databases. This problem is addressed in Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 with a new, enhanced version of the Database Statistics timer job. Previously, the sole responsibility of the Database Statistics job was to update query optimization statistics for content databases. As of SP2, the job also rebuilds content database indexes every time it runs. This automatic index rebuild helps to stop fragmentation and maintain database performance.
The Database Statistics job uses the online index rebuild feature of SQL Server 2005 and should run without any impact on performance for content databases of up to the recommended size limit of 100 GB.
Read-Only Content Databases
While performing some maintenance tasks, you may need to mark content databases as read-only. On farms that are running Office SharePoint Server 2007 with SP2 or Windows SharePoint Services 3.0 with SP2, the site collection associated with a read-only content database is set to be read-only automatically, and the user interface is modified so that users cannot perform tasks that require writing to the database. Previously, the user interface was not modified, and users received error messages when they attempted to perform operations that wrote information to the content database.
Content Deployment and Site Variations
Office SharePoint Server 2007 SP2 includes several enhancements that improve the performance and stability of the content deployment and variations features. In particular, Office SharePoint Server 2007 SP2 adds a new operation to the Stsadm command-line tool that enables a SharePoint administrator to scan sites that use the variations feature for errors. The operation reports on errors encountered and, where possible, fixes the underlying issues. For example, the operation can find and repair missing peer relationships and corrupted entries in the internal variations relationships list. The Office SharePoint Server 2007 SP2 enhancements also provide a more manageable way to handle the propagation of large quantities of variation content.
Other Stsadm Enhancements
In addition to the site variation enhancements, SP2 includes other new and extended commands for the Stsadm command-line tool. Most notably, SP2 adds Stsadm operations and parameters that ease the removal of orphaned objects from SharePoint databases. Orphaned objects—typically sites and site collections without corresponding entries in the configuration database—posed challenges in previous upgrades. Other additions include a operation that you can use to tune various aspects of search query processing and the preupgradecheck operation, which this paper discusses in a subsequent section.
SP2 includes a broad range of fixes and enhancements that improve the performance, security, stability, and usability of Excel Services. SP2 makes it easier to configure Excel Web Access Web Parts on new sites. Several rendering, calculation, and security issues have been resolved.
SP2 also offers a range of improvements for users of InfoPath Forms Services. Performance enhancements in SP2 reduce both the memory requirements and the page load times for large browser-rendered forms. SP2 also improves the browser rendering of various controls, such as the Cannot be blank red asterisk and the rich text field.
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 introduce substantial improvements to the way that SharePoint Products and Technologies integrate with other products and platforms. In particular, SP2 offers support for a broader range of Web browsers. SP2 also provides enhancements to user experience with forms authentication.
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2 offer a substantially improved client integration experience for forms-based authentication users. Previously, forms-based authentication provided a poor experience when users attempted to use the client integration features of Windows SharePoint Services 3.0 or Office SharePoint Server 2007; the client application would repeatedly prompt users for credentials whenever they executed a command in a document. To avoid this, administrators were advised to disable SharePoint client integration features in forms-based authentication environments.
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2, together with the SP2 updates for Microsoft Office Client applications, provide a much smoother user experience and make client integration a viable option for forms-based authentication users. Users of Microsoft Windows Vista™ must install Vista SP2 to take advantage of the improved user experience.
Web browser support in SharePoint Products and Technologies is divided into two levels. Level 1 Web browsers provide an optimal user experience for all SharePoint sites, through the use of advanced Microsoft ActiveX® control features. Level 2 Web browsers support all of the basic features of SharePoint Products and Technologies, including read and write functionality and site administration tasks.
As of SP2, Level 1 browser support is extended to include Microsoft Internet Explorer 8. Level 2 browser support is extended to include Firefox 2 and Firefox 3.
Getting Ready for SharePoint Products and Technologies "14"
As well as improving performance, availability, and stability, Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2 provide the groundwork for future major releases of SharePoint Products and Technologies. In particular, SP2 includes a new Stsadm operation that can help you to prepare your SharePoint deployment for an upgrade to SharePoint Products and Technologies "14".
The preupgradecheck operation
The Stsadm preupgradecheck operation can be used to scan your server farm to establish whether it is ready for upgrade to SharePoint Products and Technologies "14". It identifies issues that could present obstacles to the upgrade process. It also checks for SharePoint Products and Technologies "14" system requirements, including the presence of Microsoft® Windows Server® 2008 and a 64-bit machine, and provides feedback and best practice recommendations for your current environment, together with information on how to resolve any issues that the tool discovers.
After installing SP2, scanning your server farm by using preupgradecheck gives you the benefit of time to resolve any potential issues in your SharePoint environment, ensuring a smooth and painless upgrade to SharePoint Products and Technologies "14".
This posting is provided "AS IS" with no warranties, and confers no rights