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

exec sp_updatestats;

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

On my computer here are the results that I have:
You 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:

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]

Basically 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:

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

I can clearly see that that we have really update our table the desired number of times (ignore the doubled value of the rowmodctr):
.

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'

Now we have updated 1.310.162 rows as you can see from the screenshot below:

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

UPDATE STATISTICS dbo.FactOnlineSales (PtdFactOnlineSalesIncremental)
     with RESAMPLE ON PARTITIONS(4)

Here are the results and it is clear that we have easily updated our partition in question:

to be continued ...