Updating Statistics Fixes Poor SQL Server Query Performance

By: Steven Wort, Ross LoForte, Brian Knight


This article is about product behaviors that have surprised a lot of people after upgrading to SQL Server 2012. There is nothing worse than successfully upgrading your environment and then having end users blame the upgrade for poor query performance. Proactive attention to post-upgrade issues lessens the risk that your careful planning and hard work will be tainted by post-upgrade problems.

A possible reason for poor query performance after upgrading to SQL Server 2012 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. The statistics built from these features are built only 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 2012 query optimizer to produce a suboptimal query plan.

NOTE:

With SQL Server 2012, when you create an index, the statistics use the query optimizer’s default sampling algorithm.

To mitigate this issue, you should immediately update the statistics after upgrading to SQL Server 2012.

Using sp_updatestats with the resample argument rebuilds statistics based on an inherited sampling ratio for all existing statistics. Typically, that is a full sample for index-based statistics and sampled statistics for the rest of the columns. An additional benefit that you could gain from this process is that if the data is less than 8MB (the minimum sampling size), the statistics are also built with fullscan.

Trackbacks

Leave a Reply