Incremental statistics in SQL SERVER 2014

Next Post
By Admin at 11 Jul 2016, 12:11 PM
  • Introduction

    SQL Server 2014 introduced “INCREMENTAL” keyword under Create Statistics. Statistics are key for Tuning Performance on SQL Server. With earlier versions of SQL SERVER, when the latest partition changes triggered an update to the statistics, it performed a table scan all partitions, even the ones that weren’t changed

    Who need this?

    If partitions are present in a database, incremental statistics could prove a useful feature. Even if it’s a single partition and our table data is at a single place, updating statistics would still need to be done in the same way.

    Features

    • Incremental statistics updates just necessary the partitions, the info will be merged with the current to final generate the statistics.
    • The impact is that it smartly merges the new data with the old statistics and then updates the entire statistics without performing a FULLSCAN of the entire table.
    • Significantly improves the impact of the performance.

    Syntax

    1. CREATE STATISTICS incrstats ON dbo.TransactionHistory (TransactionDate) WITH FULLSCAN, INCREMENTAL = ON;

    To disable the Incremental statistics,

    1. PDATE STATISTICS dbo.TransactionHistory(incrstats) WITH FULLSCAN, INCREMENTAL = OFF;

    Another benefit derived is that lower ratio of data changes are necessary for triggering an auto update of the statistics because it targets it at the partition’s level.

    Incremental and auto statistics can be enabled together on the database.
    You need to use the clause INCREMENTAL = ON with ALTER DATABASE command statement as well as the AUTO_CREATE_STATISTICS set ON.

    Conclusion:

    The Incremental Statistic feature is more intelligent and reduces server operations as compared to the earlier versions


    Comments

     

    Post a comment

    Please correct the following: