Statistics: Incremental (2014) – Part 2

This post is a continuation from the previous post on the new Incremental Statistics feature in SQL Server 2014:

In this part lets see how the update of the partitioned table will work.
To kick off, I decided to update all statistics in my Database by executing

Let us see what kind of statistics information we have and when was it updated, by executing the following query:

On my computer here are the results that I have:
Screen Shot 2014-01-28 at 12.39.24You can clearly see that my manually created statistics ptdFactOnlineSales & PtdFactOnlineSalesIncremental are quite actual.

Now, I do not have any trace flags active and so for this table with 12.627.608 rows, I am expecting statistics update when over 20% of the rows will be changed. This means that any update with less than 2.525.522 rows should not invoke any statistics update. Also, do not forget that as per previous posts in this series we have 4 different partitions with the last one being empty and not even listed:

Screen Shot 2014-01-28 at 12.48.34Basically we have 4.000.025 rows on our 2nd partition, and so I decided to test Incremental statistics by updating data in it.

So let us go forward together and update 1.745.589 rows on the second partition with the following update statement:

This number of rows represent less then 15% of the total number of rows in the dbo.FactOnlineSales table and so it should not provoke any statistics update, but from the other side it is also representing over 40% of the rows at the 2nd partition – which means that our incremental statistics should be updated next time we run a query using our DateKey column.

After I re-execute the query bringing the information on the last statistics update and the number of modifications made to the table,

I can clearly see that that we have really update our table the desired number of times (ignore the doubled value of the rowmodctr):
Screen Shot 2014-01-28 at 13.02.22.

Now it is the time to issue a simple query on our table, using the DateKey column as a predicate:

after that consulting our statistics again. Now we can see a whole new situation:
Screen Shot 2014-01-28 at 13.06.29
Indeed, the incremental statistics have been updated – you can see it has jumped to the last position in the results and the update date is definitely after all other columns. Also notice that the rowmodctr has been reset to zero – a clear sign of the statistics update.

This is very awesome – now we can really have our statistics being updated automatically when we reach the current threshold on a partition level. But what if we update just some registers and then we want to update our statistics directly without waiting, lets try it out:
Update the 3rd partition:

Now we have updated 1.310.162 rows as you can see from the screenshot below:
Screen Shot 2014-01-28 at 13.22.03

But now lets update the statistics for the partition by issuing the following command:

Here are the results and it is clear that we have easily updated our partition in question:
Screen Shot 2014-01-28 at 13.26.58

to be continued …

Leave a Reply

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