In part 1 of Incremental Statistics, we saw demo about “CREATE STATISTICS …INCREMENTAL = ON” and “UPDATE STATISTICS… RESAMPLE ON PARTITIONS(9, 10). As I mentioned in the closing note of last post, there are few more places where syntax has been enhanced. So, we would cover those in this post.

ALTER DATABASE in SQL 2014 provides an option for auto-stats to be created on incremental basis. Here is the command.

ALTER DATABASE [AdventureWorks2014]
 SET AUTO_CREATE_STATISTICS ON  (INCREMENTAL=ON)

UPDATE STATISTICS also has clause INCREMENTAL ON|OFF. Why? When we specify ON, per partition statistics are recreated whereas OFF would drop existing statistics and recompute the statistics. 

UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo (IncrementalStatsDemo)
with INCREMENTAL= OFF

To identify whether a statistics is created as with incremental or not, we can use catalog view sys.stats. In SQL Server 2014 a new column is_incremental has been introduced.

UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo(IncrementalStatsDemo)
with INCREMENTAL= ON
go
select * from sys.stats where is_incremental = 1
go
UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo(IncrementalStatsDemo)
with INCREMENTAL= OFF
go
select * from sys.stats where is_incremental = 1
go

In first select query output, we should see IncrementalStatsDemo and since we turned it off later, the second select should not show the output.

There are some error messages which you should be aware of.

Msg 9111, Level 16, State 1, Line 1

UPDATE STATISTICS ON PARTITIONS syntax is not supported for non-incremental statistics.

Msg 9108, Level 16, State 1, Line 1

This type of statistics is not supported to be incremental.

First error is self-explanatory. If we attempt to update traditional statistics and provide partition clause, we would get first error. Second error is more generic and can be caused in various situation. Books online motioned those limitation here. I am pasting them below.

<Snip from books online>

Incremental stats are not supported for following statistics types:

• 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.

</Snip from books online>

Hope this blog helped you in learning new feature.