How To Update Statistics In Sql Server

This commodity 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 agree statistical information about the distribution of values in tabular array columns.

The SQL Server query optimizer uses the statistics to determine to create an estimated cardinality. The cardinality value is so 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 appointment is of import, as wrong statistic results may atomic number 82 to a costly query plan and high resource usage. A poor execution programme from the query optimizer can lead to bug, such as blocking and deadlocks.

What is SQL Server Execution Plan?

An execution programme or a query plan refers to a serial 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 tin use the SQL Server Direction 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 choice. You lot can view the last fourth dimension the statistics for the columns were updated every bit:

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 tin as well view the details of a statistic using a T-SQL query. Consider the instance query shown below:

use salesdb;
select
stats.proper noun,
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 use sys.dm_db_stats_properties(stats.object_id, stats.stats_id) as stats_properties
where object_name(stats.object_id) = 'Customers'
order past last_updated desc;

The query above should return an instance result as:

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

dbcc show_statistics ( 'Customers', 'CustomerPK' );

The query to a higher place takes 2 parameters: tabular array proper noun and target statistics, respectively.

SQL Server Update Statistics

There are diverse means 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 Automated Tab.

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

update statistics dbo.Customers;

Where dbo.Customers represent the target table.

You can as well employ the update stats command every bit:

Output is as:

Determination

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

Nigh the author

My name is John and am a boyfriend geek like you. I am passionate virtually all things computers from Hardware, Operating systems to Programming. My dream is to share my noesis with the globe and assistance out boyfriend geeks. Follow my content past subscribing to LinuxHint mailing list