SQL SERVER – Understanding Incremental Statistics

Statistics are critical for SQL Server’s query optimizer to generate efficient execution plans. Out-of-date statistics can lead to poor query performance. Traditionally, statistics are updated automatically or manually rebuilt using full scans, which can be resource-intensive for large tables. SQL Server 2014 introduced a new feature called incremental statistics to provide more efficient statistics management.

What are Incremental Statistics?

With incremental statistics enabled, SQL Server tracks changes to the data and only updates the impacted statistics subsets instead of rebuilding the full statistics. This means smaller and more frequent updates focused on the changed data.

Some key points:

• Only available for SQL Server 2014 and newer versions.
• Enabled at the index or statistics level using the STATISTICS_INCREMENTAL = ON option.
• Best suited for large tables with frequent data changes on small subsets.
• Can reduce resource usage of statistics maintenance while keeping stats more up-to-date.
• The STATISTICS_NORECOMPUTE setting overrides and disables incremental stats.

How Do Incremental Statistics Work?

Incremental stats divide the data into partitions and maintain statistics on each partition. As data changes, only the affected partitions are updated. Some examples of data changes that trigger incremental stats updates:

• INSERT, UPDATE, DELETE, or MERGE statements that modify data within the table
• Bulk insert operations using BULK INSERT or OPENROWSET(BULK...)

SQL Server determines which partitions were affected using tracking bitmaps and only updates stats on those partitions.

Behind the scenes, incremental stats are implemented as a multi-part statistics object. The header contains global summary stats for the whole table. Each partition has its own subset of statistics.

Managing Incremental Statistics

To enable incremental statistics on an index or stats object:

CREATE STATISTICS stats_name ON table_name(column) WITH INCREMENTAL = ON;

CREATE INDEX index_name ON table_name(column) WITH (STATISTICS_INCREMENTAL = ON);

This feature is enabled by default when creating statistics on a memory-optimized table. To disable incremental stats, set STATISTICS_INCREMENTAL = OFF. This will rebuild the stats as full-scan non-incremental statistics.

Use UPDATE STATISTICS to manually rebuild incremental stats on demand. These increments changed partitions and leave unchanged partitions, unlike full scan.

Check the _STATS_DATE columns in sys.indexes and sys.stats to see when statistics were last updated.

Tips for Using Statistics

• Test on your workload and data to see if incremental stats improve performance over full-scan stats.
• Consider enabling on large tables with clear partition patterns like date ranges.
• Be aware incremental stats require more disk space than typical stats.
• Monitor to ensure incremental stats are updating as expected.
• Disable STATISTICS_NORECOMPUTE for incremental stats to work properly.

I hope this overview helps explain how you can leveragethis feature in SQL Server! Let me know if you have any other questions.

You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)