UPDATE STATISTICS in SQL Server

dbtut

5 years ago

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.

USE AdventureWorks2014
GO 
UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] 
GO

USE AdventureWorks2014

UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name]

Update All Statistics in a Table

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

USE AdventureWorks2014
GO 
UPDATE STATISTICS [HumanResources].[Department]
GO

USE AdventureWorks2014

UPDATE STATISTICS [HumanResources].[Department]

Update All Statistics in a Database

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

USE AdventureWorks2014
GO 
EXEC sp_updatestats

USE AdventureWorks2014

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.

USE AdventureWorks2014
GO 
UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name]  WITH FULLSCAN
GO

USE AdventureWorks2014

UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH FULLSCAN

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.

Select Count(*) FROM [HumanResources].[Department]

1	Select Count(*) FROM [HumanResources].[Department]

sp_spaceused '[HumanResources].[Department]'

1	sp_spaceused '[HumanResources].[Department]'

USE AdventureWorks2014
GO 
UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name]  WITH SAMPLE 10 rows
GO

USE AdventureWorks2014

UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH SAMPLE 10 rows

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

USE AdventureWorks2014
GO 
UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name]  WITH SAMPLE 50 PERCENT
GO

USE AdventureWorks2014

UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH SAMPLE 50 PERCENT

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:

USE AdventureWorks2014
GO 
UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name]  WITH RESAMPLE
GO

USE AdventureWorks2014

UPDATE STATISTICS [HumanResources].[Department] [AK_Department_Name] WITH RESAMPLE

You can update all statistics of the Database with RESAMPLE.

EXEC sp_updatestats 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“.