Updating statistics sql 2016 r2

29 Jan

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.

Recently, for example, I watched a query go from taking hours to taking seconds just because we got a good set of statistics on the data.

The topic of statistics and their maintenance is not straightforward.

Then there are other questions that get asked later on, in conversation. There is no essential difference between the statistics on an index and the statistics on a table.

This command gives much better granularity of control: [cc lang=”sql”] — Update all statistics on a table UPDATE STATISTICS Sales.

Sales Order Detail — Update a specific index on a table UPDATE STATISTICS Sales.

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.