Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Its is a feature announced with SQL Server 2014. It can be very useful if you have a partition in your database. If you don’t have a partition, it won’t do you any good. If you set Auto Update Statistics to True, the statistics are updated when the row change in the table exceeds 20%. If you set Auto Create Incremental Statistics to True, this will be done on partition basis. In large databases, you can perform statistics update on partition basis.
With the help of the script below, you can set auto create incremental statistics to true at database level. After performing this operation, when a new statistic will be created in the tables that have been partitioned, it will be in incremental structure. Its db-based.
1 2 | ALTER DATABASE test SET AUTO_CREATE_STATISTICS ON ( INCREMENTAL = ON ) |
You can find out whether an existing statistic is incremental or not.
1 2 3 4 5 6 7 8 9 | USE AdventureWorks2014 GO SELECT OBJECT_NAME(object_id) TableName ,name ,is_incremental ,stats_id FROM sys.stats WHERE name = 'PK_EmployeeID' |
You can also update a statistic on a partition basis with the following script.
1 2 | UPDATE STATISTICS [DBName].[SchemaName].[TableName] (StatisticName) WITH RESAMPLE ON PARTITIONS(1) |
If the statistic is not incremental, you will receive an error like below.
Msg 9111, Level 16, State 1, Line 13
UPDATE STATISTICS ON PARTITIONS syntax is not supported for non-incremental statistics.
You can also convert an existing statistic to incremental structure with the help of the following script. After running this script, you will now be able to update your statistics in partitioned tables on partition basis without receiving the above error.
With the following script, you can generate the update statistics script for all statistics in all partitioned tables in the database.
You can generate the update statistics script for a specific table by removing the comment in the script.
1 2 3 4 5 6 7 8 9 10 11 | SELECT DISTINCT 'UPDATE STATISTICS ' + QUOTENAME(object_name(s.object_id)) + ' (' + QUOTENAME(s.name) + ') WITH INCREMENTAL = ON' FROM sys.tables tbl INNER JOIN sys.indexes ind ON tbl.object_id = ind.object_id INNER JOIN sys.data_spaces ds ON ind.data_space_id = ds.data_space_id INNER JOIN sys.stats s ON ind.object_id = s.object_id WHERE ds.type = 'PS' --AND tbl.name='WriteYourTableName' AND s.is_incremental = 0 |
To learn more about statistics, I recommend reading below articles
“Statistic Concept and Performance Effect on SQL Server“,
“DBCC SHOW_STATISTICS in SQL Server”
