Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

USE AdventureWorks2012;  
GO  
EXEC sp_updatestats;  

Resample Option

USE MyDatabase
GO
EXEC sp_updatestats 'Resample'
GO

sp_updatestats  executes UPDATE STATISTICS against all the tables on a database, that require an update. Sp_updatestats accepts the @resample argument. This forces the UPDATE STATISTICS resample option.

Using  RESAMPLE updates the statistics based on the latest sample rate.  To view the latest sample rate use the DBCC SHOW STATISTICS command.

If RESAMPLE is not used , UPDATE STATISTICS based on a sample default rate. The sample default rate is determined by SQL Server . 

Deciding on which method to use is dependant on the level of granular control required.

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017

Last modified: November 18, 2019

Author

Comments

Write a Reply or Comment