Hi Friends,

There is a very good new feature introduced in SQL Server 2014 Update Statistics Using ‘Incremental Statistics’. Internally Statistics created by this option are per partition statistics. We can specify this option at the time of index creation (Keep in mind that this option can’t be used for every type of indexes because it also has some limitations). So that the statistics automatically created by index creation will be incremental statistics. Here I am going to show you a scenario with the help of which you can easily understand the importance of this new feature.

Environment: I have a table xtstatsdemo with columns id, name and balance. Table is having 3 partitions with id as Key. First partition is having rows with key range 1-10000; Second partition is having rows with key range 10001-20000; Third partition is having rows with key range 20001-30000. Here I have one clustered index on id column. Here Statistics also created automatically due to index creation.

Problem: before incremental statistics the problem with above scenario was statistics update will occurs only when number of changes reached to the threshold vales (without specifying any statistics related Trace Flag) with respect to complete table rather than Partition. Let me simplify it for you. Suppose I have made 3000 rows changes in third partition i.e. 30% of third Partition but 10% of complete table. So statistics (created due to index creation) will not be updated because it’s below threshold vales with respect to table.

Solution: By using Incremental Statistics option, SQL Server will update the statistics automatically based on partition level because internally statistics created at partition level. Let me show you this practically:

Now our environment has been setup. Here we also specify Statistics_Incremental option during index creation. It will help us to resolve the problem specified in above problem block. Now let me show you the status of statistics auto created by index.

1_SQL_Server_2014_Statistics_Update_Using_Incremental_Statistics

2_SQL_Server_2014_Statistics_Update_Using_Incremental_Statistics

So finally stats has been updated automatically. Now it is showing stats fo 27000 records. this is due to the reason of using incremental statistics option.

HAPPY LEARNING!

Regards

Prince Rastogi

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook