Erin Stellato

UPDATEs to Statistics

October 23, 2017 by in SQL Statistics | 6 Comments
Get a unique view of resource utilization for VMware hosts and VMs, including vSphere topology.  More
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts

The last several releases of SQL Server have introduced a slew of new features, as well as improvements in existing functionality. One area of the engine which is easy to overlook is statistics. After all, statistics still get created the same way, they still tell you about the distribution of data, they’re still used by the Query Optimizer… what’s different? The basic function of statistics remains the same – but how they’re used by the Query Optimizer does change depending on the Cardinality Estimator you’re using. There are also several noteworthy changes related to updating statistics and new functionality has been added around viewing statistics information. Altogether, these changes across the latest releases can cause a variation in SQL Server behavior that you weren’t expecting.

Note: This post is most applicable to SQL Server 2012 and higher, but some detail for prior releases is included for reference (and fun).

SQL Server 7.0

  • The number of steps in a histogram is limited to 300. In SQL Server 6.5 and earlier a histogram would have the number of steps that could fit on a 2K page, based on the size of the first column in the key.
  • The ‘automatically update statistics’ database option is introduced; previously statistics were only updated manually.

SQL Server 2000

  • The number of steps in the histogram is reduced from 300 to 200 (technically 201, if you include the step for NULL, assuming the first column in the key allows NULLs).

SQL Server 2005

SQL Server 2008

SQL Server 2008R2 SP1

  • Trace Flag 2371 is made available, which can be used to reduce the number of modifications required for automatic updates to statistics to occur. As a reminder, I’m a fan of updating statistics on a regular basis through a scheduled job and leaving the auto update enabled as a safety.

SQL Server 2008R2 SP2

  • The function sys.dm_db_stats_properties is included, which provides the same information found in the header of DBCC SHOW_STATISTICS, as well as a modification column that could be used to track changes and programmatically determine if an update was needed. Remember my preference for using a job to update stats? That job just got a lot smarter with this DMF…now I can look to see how much data has been modified and ONLY update statistics if a certain percentage of data has changed. Slick.

SQL Server 2012

SQL Server 2012 SP1

  • DBCC SHOW_STATISTICS only requires the SELECT permission – previously it required a user to be a member of sysadmin, or a member of the db_owner or db_ddladmin database role. This can be globally disabled with trace flag 9485.
  • Includes sys.dm_db_stats_properties (see 2008R2 SP2 note above)

SQL Server 2012 SP2

  • Cumulative Update 1 introduces a fix related to ascending keys not being properly identified even with trace flags 2389 and 2390 in use. This requires trace flag 4139 in addition to CU1, as noted in KB 2952101.

SQL Server 2014

  • The new Cardinality Estimator is introduced, implemented by setting the database compatibility mode to 120, or by using trace flag 2312. If you haven’t read anything about the new CE I recommend starting with the Cardinality Estimation documentation and then reading Joe Sack’s whitepaper, Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator, for in-depth details.
  • The behavior from Trace Flags 2389 and 2390 for ascending keys is now implemented via the database compatibility mode. If your databases compatibility mode is set to 120 (or higher in later releases), you do not need to use Trace Flags 2389 and 2390 for SQL Server to identify statistics that have ascending keys.
  • Incremental statistics are introduced for partitions, and can be viewed through the new DMF sys.dm_db_incremental_stats_properties. Incremental statistics provide a way to update statistics for a partition without updating them for the entire table. However, the additional statistics information from the incremental statistics is not used by the Query Optimizer, but it is folded into the main histogram for the table.
  • CU2 includes the same fix mentioned above for SQL Server 2012 SP2 that also requires trace flag 4139.

SQL Server 2014 SP1

  • Trace flag 7471 is back-ported to CU6, originally available in SQL Server 2016 as noted below.

SQL Server 2016

SQL Server 2016 SP1

  • The query hint option ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS is introduced, along with the FOR HINT argument, which is the equivalent of trace flag 4139.
  • The DMF sys.dm_db_stats_histogram is exposed in CU2, which is an alternative to the histogram output from DBCC SHOW_STATISTICS. The information in both is the same, use what’s easier for you or better fits the problem you need to solve.
  • The option PERSIST_SAMPLE_PERCENT is introduced in CU4, which can be used to force the same sampling rate to be used every time a statistic is updated going forward, and examples of this behavior can be found in the post, Persisting statistics sampling rate.

SQL Server 2017

  • The attributes StatsInfoType and OptimizerStatsUsageType are added to the Query Plan, which lists statistics used during query optimization. This is pretty cool! I haven’t had a chance to play with this yet, but to get this information previously you had to use undocumented trace flags.

Summary

If you are looking to upgrade to a newer release, or if you’ve recently upgraded, take note as to how these changes impact your solution. We’ve had many clients contact us after upgrading from 2005/2008/2008R2 to 2014 or 2016, complaining of performance issues. In many cases, adequate testing was not completed prior to the upgrade.

This is something we really focus on when we’re helping a client upgrade. Beyond the steps to move a production instance from one version to another with little downtime, we want to make sure that the day after the upgrade is a boring one for DBAs and developers.

We don’t simply test the upgrade process, we test what the system looks like after the upgrade. Are the same trace flags from the old environment needed in the new one? What database settings need to be adjusted? Does query performance change – for better or worse? If you don’t know the answers to those questions before you upgrade production, then you’re setting yourself up for one to many days of fire-fighting, Sev 1 calls, meals at your desk, not enough sleep and who knows what else.

Take the time up front to understand the impact of the new features and changes in functionality listed above, plan the upgrade, and test as much as possible.