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.