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 go Use StatsDemo2014 go --Create Partition Function create partition function xpfstatsdemo (int) as range right for values (10000,20000) go --Create Partition Scheme create partition scheme xpsstatsdemo as partition xpfstatsdemo ALL to ([Primary]) go --Create table by using Partition Scheme on id column create table xtstatsdemo ( id int not null, balance int not null, name varchar(25) ) on xpsstatsdemo (id) go --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); go --You can check here about how many partitions belongs to the table select * from sys.partitions where object_id=OBJECT_ID('xtstatsdemo') go -- 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 while (@counter<=30000) begin if (@counter%2=0) begin insert into xtstatsdemo values(@counter,@balance,'data1') end else begin insert into xtstatsdemo values(@counter,@balance,'data2') end set @counter = @counter + 1; set @balance = @balance + 1; end go --You can check number of rows inside the table Select count(*) from xtstatsdemo go
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 go -- Current stats are showing that table is having total 30000 rows. That’s right. DBCC SHOW_STATISTICS('xtstatsdemo','CI_xtstatsdemo') go
-- Now I am going to delete 3000 rows from third partition. delete from xtstatsdemo where id>27000 go --Statement to update the stats automatically Select id,name from xtstatsdemo where id>20000 go --Stats after 10% rows deletion with respect to table while 30% with respect to third partition DBCC SHOW_STATISTICS('xtstatsdemo','CI_xtstatsdemo') Go
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.