O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required