Incremental Statistics Performance
Published: 20th May 2015
Based on my last post we now know how to create incremental statistics on a partitioned table and I mentioned that it was a massive improvement for performance of maintenance, but just how much?

So I thought I’d do a simple and small experiment to see what impact this makes compared to sampled stats and a non-partitioned fullscan.

I’m not going to use a massive table for this, but I am going to use my expanded version of Sales.SalesOrderDetail from AdventureWorks.

use AdventureWorks2012
go

if object_id('testSalesOrderDetail') is not null drop table testSalesOrderDetail
go

select *
into dbo.testSalesOrderDetail
from sales.SalesOrderDetailEnlarged
go

create unique clustered index ix_testSalesOrderDetail
  
on testSalesOrderDetail(salesOrderDetailID)
go

set statistics time on
set statistics
io on
go

update statistics dbo.testSalesOrderDetail
  
(ix_testSalesOrderDetail)
  
with sample
go

update statistics dbo.testSalesOrderDetail
  
(ix_testSalesOrderDetail)
  
with fullscan
go

-- partition the table

if exists
(
  
select *
  
from sys.partition_schemes
  
where name = 'testPartitionScheme'
)
begin
   drop
partition scheme testPartitionScheme
end

if
exists
(
  
select *
  
from sys.partition_functions
  
where name = 'testPartitionFunction'
)
begin
   drop
partition function testPartitionFunction
end

-- rigged to make the last partition have just 100,000 records
-- representing 1 month of data
create partition function testPartitionFunction(int)
  
as range left
  
for values(1000000, 2000000, 3000000, 4000000, 4750000)
go

create partition scheme testPartitionScheme
  
as partition testPartitionFunction
  
all to ([primary])
go

-- re-create index as partitioned and with incremental statistics

create unique clustered index ix_testSalesOrderDetail
  
on testSalesOrderDetail(salesOrderDetailID)
  
with (drop_existing = on)
  
on testPartitionScheme(salesOrderDetailID)
go

create unique clustered index ix_testSalesOrderDetail
  
on testSalesOrderDetail(salesOrderDetailID)
  
with (drop_existing = on, statistics_incremental = on)
  
on testPartitionScheme(salesOrderDetailID)
go

-- check which is the max partition number (should be 6)

select max(partition_number)
from sys.partitions
where object_id = object_id('testSalesOrderDetail')
and
rows > 0
go

-- now re-scan just the final partition

update statistics dbo.testSalesOrderDetail
  
(ix_testSalesOrderDetail)
  
with resample on partitions (6)
go


As you can see… with incremental statistics you can effectively get a fullscan update faster than you can otherwise even get a Sampled update on a large table.

That’s an impressive saving when Sampled statistics are otherwise your only option due to duration of performing a fullscan.
Comments:
NB: Comments will only appear once they have been moderated.