Professional Microsoft® SQL Server® 2008 Administration

3.6. Post-Upgrade Checks

The information in the following sections is really about product behaviors that have surprised a lot of people. Paying attention to this information will ensure that you do not need to place a call to Microsoft Premier Support.

3.6.1. Poor Query Performance After Upgrade

A possible reason for poor query performance after upgrading to SQL Server 2008 is that the old statistics are considered outdated and cannot be used by the query optimizer. For most situations, this should not be an issue as long as you have enabled the auto-update statistics and autocreate statistics options. This enables statistics to be automatically updated by default when needed for query compilation. It is important to keep in mind that the statistics built from these features are only built from data sampling. Therefore, they can be less accurate than statistics built from the entire dataset. In databases with large tables, or in tables where previous statistics were created with fullscan, the difference in quality may cause the SQL Server 2008 query optimizer to produce a suboptimal query plan.

Anytime an index is created, the statistics that are part of that dataset are based on fullscan. In SQL Server 2005 and SQL Server 2008, they are created at index-creation time.

To mitigate this issue, you should update the statistics immediately after upgrading to SQL Server 2008. Using sp_updatestats with the resample argument will rebuild statistics based on an inherited sampling ratio ...