Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 | USE AdventureWorks2014 GO UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] GO |
Update All Statistics in a Table
You can update all statistics of the Department table in the HumanResource schema with the following script.
1 2 3 4 | USE AdventureWorks2014 GO UPDATE STATISTICS [HumanResources].[Department] GO |
Update All Statistics in a Database
You can update all statistics of the Database with the following script.
1 2 3 | USE AdventureWorks2014 GO EXEC sp_updatestats |
Update Statistic Options
- FULLSCAN
- SAMPLE
- RESAMPLE
FULLSCAN
This method updates the statistic by scanning all rows of the related table or indexed view. You can use it as follows.
1 2 3 4 | USE AdventureWorks2014 GO UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH FULLSCAN GO |
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.
1 | Select Count(*) FROM [HumanResources].[Department] |
or
1 | sp_spaceused '[HumanResources].[Department]' |
1 2 3 4 | USE AdventureWorks2014 GO UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH SAMPLE 10 rows GO |
In the following script, it updates the statistic by sampling 50% of the number of rows of the related table or indexed view.
1 2 3 4 | USE AdventureWorks2014 GO UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH SAMPLE 50 PERCENT GO |
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:
1 2 3 4 | USE AdventureWorks2014 GO UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH RESAMPLE GO |
You can update all statistics of the Database with RESAMPLE.
1 | EXEC sp_updatestats 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“.
