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:
--Create a database for Demo at default files location
Create database StatsDemo2014
--Create Partition Function
create partition function xpfstatsdemo (int)
as range right for values
--Create Partition Scheme
create partition scheme xpsstatsdemo
as partition xpfstatsdemo
ALL to ([Primary])
--Create table by using Partition Scheme on id column
create table xtstatsdemo
id int not null,
balance int not null,
) on xpsstatsdemo (id)
--Create a clustered index on id column [This will cover complete table]
--Here I have also specify statistics_Incremental option
create clustered index CI_xtstatsdemo
on xtstatsdemo (id) with (statistics_incremental=ON);
--You can check here about how many partitions belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtstatsdemo')
-- Now I am inserting 30000 rows in the table. That means each partition will contain 10000 rows here
-- Here id and balance both are having same values for demo purpose
set nocount on
declare @counter int =1
declare @balance int =1
insert into xtstatsdemo values(@counter,@balance,'data1')
insert into xtstatsdemo values(@counter,@balance,'data2')
set @counter = @counter + 1;
set @balance = @balance + 1;
--You can check number of rows inside the table
Select count(*) from xtstatsdemo
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.
--Statement to update stats automatically because # of rows have been changed from 0 to 30000
Select * from xtstatsdemo where id>20000
-- Current stats are showing that table is having total 30000 rows. That’s right.
-- Now I am going to delete 3000 rows from third partition.
delete from xtstatsdemo where id>27000
--Statement to update the stats automatically
Select id,name from xtstatsdemo where id>20000
--Stats after 10% rows deletion with respect to table while 30% with respect to third partition
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.