Auto Update Statistics Enhancement in SQL Server 2016

Problem

Statistics are small objects that describe the data distribution for tables and indexes
in a SQL Server database. These statistics are
used by the SQL Server
Query Optimizer to determine the
best plan to execute a query. Because of this
importance,
these statistics should be up to date in order to get the best performance when
executing a query. The mechanism that automatically updates the statistics in SQL Server
depends on the number of rows that are modified in a table which exceeds
a specific threshold. The downside is that the threshold is very high for tables
with a large
number of rows which leads to updates occurring less frequently which can cause
performance issues.

Solution

In order to trigger the auto update statistics feature in SQL Server, the number
of updated rows should exceed 20% of the table rows. In the case of large tables,
such as a table with 1 million rows, more than 200,000 records need to be updated in order
to update the table statistics. This can lead to performance problems due to bad
query plans created by the query optimizer that still uses the old statistics.

To overcome this issue, trace flag 2371 was introduced in SQL Server 2008 R2 SP1
that overrides the default threshold of the auto update statistics feature
for tables with more than 25,000 rows. If you turn on this trace flag, the new
threshold to update the statistics will be lower for tables with a high number
of rows. In the previous example of a 1 million row table, there is no longer
the need to update 200,000 rows to update the statistics on the table if trace flag 2371 is activated.

In SQL Server 2016, you don't need to turn on trace flag 2371 because this
new method is used by default by the SQL Server engine when determining when to update the statistics.

Let's walk through a small demo to see how the auto update statistics behaves.

SQL Server 2014 Compatibility Mode Database Test

We will create a simple table in the MSSQLTipsDemo database and fill it with 60,000 rows:

In order to create the statistics on the table, we will run a heavy SELECT statement
as below:

SELECT * FROM StatisticsDemo WHERE ID>1000

From
SQL Server Management Studio (SSMS), expand the table node from the Object Explorer, then expand the
Statistics node to view the newly created statistics. The statistics that
start with _WA means that it
is automatically created using the AUTO_CREATE_STATISTICS feature:

The DBCC Show_Statistics command can be used to show the statistics details,
such as the updated date and the number of rows in the table.

To use, you just need to
specify the table name and statistic name as follows:

As previously mentioned, to trigger the auto update statistics feature, more
than 20% of the table rows should be updated. In our case, the table has 60,000
rows, so more than 12,000 records should be modified to update the statistics, but using SQL Server 2016
the database engine will override this rule for large tables.

If we query the compatibility_level property of our database from the sys.databases
system table we can see the current compatibility level:

SELECT compatibility_level FROM sys.databases WHERE name = 'MSSQLTipsDemo';

The result is (120), which is SQL Server 2014 compatibility:

Although we are using SQL Server 2016 version, we need to make sure the
compatibility level of the database is (130) which is the default compatibility
level for newly created databases in SQL Server 2016. But if the
SQL Server
instance is upgraded from an old version or the
database is restored from a backup
taken from earlier SQL Server version, the compatibility level will not be (130)
and the new change for the auto update statistics will not be applicable.

We will delete 10,000 records from our test
table, which is less than the 12,000 threshold (20% of 60K):

DELETE FROM StatisticsDemo WHERE ID <=10000
SELECT * FROM StatisticsDemo WHERE ID>1000
DBCC Show_Statistics('StatisticsDemo','_WA_Sys_00000001_27F8EE98') with stat_header

Querying the statistics again, the statistics do not update and the number
of rows did not change:

SQL Server 2016 Compatibility Mode Database Test

We will change the compatibility level of the database to (130) using the
below ALTER DATABASE statement and drop and recreate the table.

The compatibility level can also be changed in SSMS from the Options tab of the Database Properties:

Then run the statements again which will refresh the statistics:

SELECT * FROM StatisticsDemo WHERE ID>1000
DBCC Show_Statistics('StatisticsDemo','_WA_Sys_00000001_239E4DCF') with stat_header

Here are the stats showing 60,000 rows.

If we run these statements again, we can see the stats will update.

DELETE FROM StatisticsDemo WHERE ID <=10000
SELECT * FROM StatisticsDemo WHERE ID>1000
DBCC Show_Statistics('StatisticsDemo','__WA_Sys_00000001_239E4DCF') with stat_header

The statistics are now
updated and the number of rows has changed even though 10,000 rows is under the default
20% threshold and we didn't use trace flag 2371 in our demo.

It is clear from the results that the threshold becomes dynamic in SQL
Server 2016 and the threshold is decreased with increasing number of rows in a table. In this
way the statistics will be updated more frequently and will guarantee better query
performance.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.