How to Update Statistics SQL Server

How to Update Statistics SQL Server

This article will understand the basics of SQL Server statistics and various methods to perform a statistics update.

What are SQL Server Statistics?

SQL Server statistics are large binary objects that are used to hold statistical information about the distribution of values in table columns.

The SQL Server query optimizer uses the statistics to determine to create an estimated cardinality. The cardinality value is then used to create an optimized and high-quality execution plan.

Cardinality refers to the number of rows.

Therefore, keeping the SQL Server statistics up to date is important, as incorrect statistic results may lead to a costly query plan and high resource usage. A poor execution plan from the query optimizer can lead to problems, such as blocking and deadlocks.

What is SQL Server Execution Plan?

An execution plan or a query plan refers to a series of organized steps to query and access data in a database. The query optimizer will generate a graphical representation of the high-optimized method to fetch data for the specified query.

View SQL Server Statistics

In SQL Server, you can use the SQL Server Management Studio or T-SQL Query to view the statistics of a specific object.

In SSMS, navigate to Databases -> Your Target Database -> Tables -> Target Table -> Statistics.

As shown:

To view the details of a specific statistic object, right-click and select the properties option. You can view the last time the statistics for the columns were updated as:

To view the distribution and the frequency of the histogram, use the details tab in the properties window.

SQL Server View Statistics: Transact-SQL

You can also view the details of a statistic using a T-SQL query. Consider the example query shown below:

use salesdb;
select
    stats.name,
    stats_properties.last_updated,
    stats_properties.rows,
    stats_properties.rows_sampled,
    stats_properties.unfiltered_rows,
    stats_properties.modification_counter,
    stats_properties.steps
from sys.stats as stats
outer apply sys.dm_db_stats_properties(stats.object_id, stats.stats_id) as stats_properties
where object_name(stats.object_id) = ‘Customers’
order by last_updated desc;

The query above should return an example result as:

SQL Server also provides the DBCC command to show the details of a specific statistic. Example:

dbcc show_statistics (‘Customers’, ‘CustomerPK’);

The query above takes two parameters: table name and target statistics, respectively.

SQL Server Update Statistics

There are various ways to update statistics in SQL Server. To enable automatic statistics update, right-click your target database, navigate the options tab, and set the “Auto Update Statistics” to true under the Automatic Tab.

To update statistics manually using T-SQL query, use the query as shown:

update statistics dbo.Customers;

Where dbo.Customers represent the target table.

You can also use the update stats command as:

exec sp_updatestats;

Output is as:

Conclusion

This guide covers how to work with SQL Server statistics and various methods to perform statistics updates in SSMS and T-SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *