Incremental Statistics Performance
Published: Mar 26, 2017
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron