What is Incremental Statistics in SQL Server

dbtut

5 years ago

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.

ALTER DATABASE test
SET AUTO_CREATE_STATISTICS ON ( INCREMENTAL = ON )

1 2	ALTER DATABASE test SET AUTO_CREATE_STATISTICS ON ( INCREMENTAL = ON )

You can find out whether an existing statistic is incremental or not.

USE AdventureWorks2014
GO
SELECT 
    OBJECT_NAME(object_id) TableName
   ,name 
   ,is_incremental
   ,stats_id
FROM sys.stats
WHERE name = 'PK_EmployeeID'

USE AdventureWorks2014

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.

UPDATE STATISTICS [DBName].[SchemaName].[TableName] 
(StatisticName) WITH RESAMPLE ON PARTITIONS(1)

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.

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

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”