Thursday , March 28 2024

UPDATE STATISTICS in SQL Server

In this article, I will explain you to how to update your statistics with the UPDATE STATISTICS command by table or database basis. I recommend that you read the article named “Statistic Concept and Performance Effect in SQL Server” before this article.

Sample Commands

Update a Statistic

You can update the statistic of the index named AK_Department_Name in the Department table in the HumanResources schema with the help of the script below.

Update All Statistics in a Table

You can update all statistics of the Department table in the HumanResource schema with the following script.

Update All Statistics in a Database

You can update all statistics of the Database with the following script.

Update Statistic Options

  1. FULLSCAN
  2. SAMPLE
  3. RESAMPLE

FULLSCAN

This method updates the statistic by scanning all rows of the related table or indexed view. You can use it as follows.

SAMPLE

Update the statistic by sampling the number or percentage of rows that you specify after the SAMPLE statement. You can use it in the following ways.

I update this statistic by sampling 10 rows. (The table has 16 rows)

You can learn the number of rows in the table with the help of the following queries.

or

In the following script, it updates the statistic by sampling 50% of the number of rows of the related table or indexed view.

RESAMPLE

Updates the statistic according to the latest sample rate. For example, if you updated the statistic with WITH FULLSCAN previous time and if you run it with RESAMPLE next time, it will update the statistic with FULLSCAN.

You can use it as follows:

You can update all statistics of the Database with RESAMPLE.

NOTE: My advice is to update the statistics by default, without any parameters, unless there is a problem. Because the SQL Server Query Optimizer determines the sample rate itself to create the best query plan.

You can run the DBCC SHOW STATISTICS command to get detailed information about a statistic. You can find details in the article “DBCC SHOW_STATISTICS in SQL Server“.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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

Categories