SQL 2014 Statistics Enhancement – Incremental Statistics

Available in SQL 2014 Enterprise Edition since only used for partitioned indexes

Statistics are VERY important to the SQL database engine and are what helps the optimizer make good execution plans. So, maintaining statistics is a super important part of any DBA’s job. In the past updating statistics on large tables can be very resource consuming and many times ended up with an entire large table being scanned or not enough data scanned if using sp_updatestats.

With the addition of the INCREMENTAL option statistics on large partitioned indexes, statistics maintenance can be managed in a more deliberate way. The INCREMENTAL option allows statistics creation per partition by setting the INCREMENTAL option to ON in the CREATE STATISTICS statement. So, when we update stats we can now tell SQL what partition to update meaning that we can maintain the partition that is most rapidly changing without having to scan data that does not change.

— Create a partition function and scheme

CREATE PARTITION FUNCTION RouteDtRangePF1(DATETIME)

AS RANGE RIGHT FOR VALUES (‘20130101’, ‘20140101’, ‘20150101’)

GO

CREATE PARTITION SCHEME RouteDtRangePS1 AS PARTITION RouteDtRangePF1 TO

([PRIMARY], [PRIMARY], [PRIMARY])

GO

CREATE TABLE RunningRoutes

(RouteID [int] NOT NULL,

RaceKey [int] NOT NULL,

RaceDate [DATETIME] NOT NULL,

MileageKey [int] NOT NULL)

ON  RouteDtRangePS1(RaceDate)

GO

— Create stats on a column with INCREMENTAL = ON

CREATE STATISTICS rrincrstats ON dbo.RunningRoutes(RaceDate)

WITH FULLSCAN, INCREMENTAL = ON

GO

— Update stats on specific partitions

UPDATE STATISTICS RunningRoutes(rrincrstats)

WITH RESAMPLE ON PARTITIONS(2, 3)

GO

There are limitations. Incremental statistics is not supported in the following conditions:

• Statistics created with indexes that are not partition-aligned with the base table.
• Statistics created on AlwaysOn readable secondary databases.
• Statistics created on read-only databases.
• Statistics created on filtered indexes.
• Statistics created on views.
• Statistics created on internal tables.
• Statistics created with spatial indexes or XML indexes.

https://msdn.microsoft.com/en-us/library/ms187348.aspx