Hi Friends,
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.
HAPPY LEARNING!
Regards
Prince Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
