Friday , March 29 2024

What is Incremental Statistics in SQL Server

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.

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

You can also update a statistic on a partition basis with the following script.

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.

To learn more about statistics, I recommend reading below articles

Statistic Concept and Performance Effect on SQL Server“,

“DBCC SHOW_STATISTICS in SQL Server”

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories