Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.