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:
SELECT indid, o.name AS Table_Name, i.name AS Index_Name, STATS_DATE(o.id,i.indid) AS Date_Updated, rowmodctr--, i.* , st.is_incremental FROM sysobjects o JOIN sysindexes i ON i.id = o.id JOIN sys.stats st ON st.object_id = o.id and st.stats_id = i.indid WHERE xtype = 'U' AND i.name IS NOT NULL and o.id = object_id('dbo.FactOnlineSales') ORDER BY Date_Updated sac
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:
SELECT t.name [table], p.rows, p.partition_number, v.boundary_id, v.value FROM sys.tables t JOIN sys.partitions p On p.object_id = t.object_id INNER JOIN sys.partition_range_values v ON v.boundary_id = p.partition_number WHERE t.object_id = object_id('dbo.FactOnlineSales') order by [table]
So let us go forward together and update 1.745.589 rows on the second partition with the following update statement:
update dbo.FactOnlineSales set DateKey = dateAdd( ss, 1, DateKey ) where DateKey < '2007-06-01'
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,
SELECT indid, o.name AS Table_Name, i.name AS Index_Name, STATS_DATE(o.id,i.indid) AS Date_Updated, rowmodctr--, i.* , st.is_incremental FROM sysobjects o JOIN sysindexes i ON i.id = o.id JOIN sys.stats st ON st.object_id = o.id and st.stats_id = i.indid WHERE xtype = 'U' AND i.name IS NOT NULL and o.id = object_id('dbo.FactOnlineSales') ORDER BY Date_Updated asc GO
Now it is the time to issue a simple query on our table, using the DateKey column as a predicate:
select count(*) from dbo.FactOnlineSales where DateKey < '2007-09-01'
after that consulting our statistics again. Now we can see a whole new situation:
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:
update dbo.FactOnlineSales set DateKey = dateAdd( ss, 1, DateKey ) where DateKey > '2009-10-01'
But now lets update the statistics for the partition by issuing the following command:
UPDATE STATISTICS dbo.FactOnlineSales (PtdFactOnlineSalesIncremental) with RESAMPLE ON PARTITIONS(4)
to be continued ...