Updating statistics in sql server 2016

20-Jun-2016 09:05

In order for automatic updates of statistics to occur, the AUTO UPDATE STATISTICS database option must be enabled for the database: This option is enabled by default for every new database you create in SQL Server 2005 and higher, and it is recommended to leave this option enabled.

If you’re not sure if this option is enabled, you can check in the UI or you can use the following T-SQL: With the option enabled, SQL Server marks statistics as out of date based on internal thresholds.

I had many people ask why statistics did not update immediately after the bulk load of the 25,000 rows completed, so I want to step through the demo that I did at Summit to clarify when an automatic update will occur.

The Setup Start with a copy of the USE [Adventure Works2012]; GO SELECT * INTO [Sales].[Test Sales Order Detail] FROM [Sales].[Sales Order Detail]; GO CREATE CLUSTERED INDEX [PK_Sales Order Detail_Sales Order ID_Sales Order Detail ID] ON [Sales].[Test Sales Order Detail] ([Sales Order ID], [Sales Order Detail ID]); GO CREATE UNIQUE NONCLUSTERED INDEX [AK_Test Sales Order Detail_rowguid] ON [Sales].[Test Sales Order Detail] ([rowguid]); GO CREATE NONCLUSTERED INDEX [IX_Test Sales Order Detail_Product ID] ON [Sales].[Test Sales Order Detail] ([Product ID]); GO SELECT OBJECT_NAME([sp].[object_id]) AS "Table", [sp].[stats_id] AS "Statistic ID", [s].[name] AS "Statistic", [sp].[last_updated] AS "Last Updated", [sp].[rows], [sp].[rows_sampled], [sp].[unfiltered_rows], [sp].[modification_counter] AS "Modifications" FROM [sys].[stats] AS [s] OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp] WHERE [s].[object_id] = OBJECT_ID(N'Sales.

Looking for training resources, events and advice from peers?

Join the Microsoft Training and Certification Community.

With default database settings, SQL Server automatically creates and updates statistics to use during query optimisation in order to build efficient query execution plans.

Let’s take a look at how these statistics operations are processed internally and determine which operations run in parallel (across multiple threads) and which operations run in serial (single-threaded).

During the session I went through a demo that showed the automatic update of a statistic.The query optimizer uses them to create query plans that improve query performance.If you have AUTO_UPDATE_STATISTICS option turned on for the database the query optimizer will automatically determine when statistics might be out-of-date and then update them when they are used by a query.The default sample size is calculated using a non-linear algorithm where the sample size decreases as the table size increases.

The default sampling rate algorithm is as follows: The sampling algorithm does not randomly sample rows, it actually samples all of the rows on “randomly” selected pages as highlighted by Conor Cunningham.The database had the Auto Update Statistics option enabled, and I had added almost 25,000 rows to a table with 121,000 rows.