Updating statistics in sql server 2016
If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling.
Updating statistics in sql server 2016 video
For more information about UPDATE STATISTICS, see UPDATE STATISTICS (Transact-SQL).
For more information about statistics, see Statistics.[ @resample =] 'resample' Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS statement.
In the previous example of a 1 million row table, there is no longer the need to update 200,000 rows to update the statistics on the table if trace flag 2371 is activated.
In SQL Server 2016, you don't need to turn on trace flag 2371 because this new method is used by default by the SQL Server engine when determining when to update the statistics.
But if the SQL Server instance is upgraded from an old version or the database is restored from a backup taken from earlier SQL Server version, the compatibility level will not be (130) and the new change for the auto update statistics will not be applicable.
We will delete 10,000 records from our test table, which is less than the 12,000 threshold (20% of 60K): USE [MSSQLTips Demo] GO ALTER DATABASE [MSSQLTips Demo] SET COMPATIBILITY_LEVEL = 130 GO DROP TABLE [dbo].[Statistics Demo] GO CREATE TABLE [dbo].[Statistics Demo]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL ) ON [PRIMARY] GO DECLARE @Number Of Rows INT SET @Number Of Rows = 60000; WHILE (@Number Of Rows It is clear from the results that the threshold becomes dynamic in SQL Server 2016 and the threshold is decreased with increasing number of rows in a table.For disk-based tables, sp_updatestats updates only the statistics that require updating based on the modification_counter information in the sys.dm_db_stats_properties catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.Statistics on memory-optimized tables are always updated when executing sp_updatestats.sp_updatestats displays messages that indicate its progress.When the update is completed, it reports that statistics have been updated for all tables.sp_updatestats updates statistics on disabled nonclustered indexes and does not update statistics on disabled clustered indexes.Because of this importance, these statistics should be up to date in order to get the best performance when executing a query.