Updating statistics sql 2016 r2

I've provided a sample of the output generated by the collection of SQL statements executed above.

As you can see, the engine still will review the statistics to see if they warrant updating.

If you're like me, you have a SQL Agent job in place to rebuild or reorganize only the indexes in your databases that truly require such actions.

If you rely on the standard maintenance plans in Microsoft SQL Server, a policy of rebuilding all indexes occurs.

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.

updating statistics sql 2016 r2-29

I can assure you that each of the databases in the listing above had AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS both set to ON, yet the following results are indicative of statistics that can become outdated.

The database had the Auto Update Statistics option enabled, and I had added almost 25,000 rows to a table with 121,000 rows.

The number of rows added via the bulk import was just over the limit to trigger an auto-update of statistics for the next query, which I showed.

The next step is to copy this text, paste it into a query window in SQL Server Management Studio, then execute it against the instance.

Alternately you may choose only to execute it against select databases, but that is entirely up to you.

Leave a Reply