The Awesomeness of Incremental Statistics
Published: Mar 19, 2017
I did a post a while ago about why you need to be careful with Sampled Statistics and therefore why a FULLSCAN is really the only reliable way to go… BUT there has always been the problem that a FULLSCAN update can take a VERY long time.

Oh, and yes… this is also to do with partitioning!!!

As with online rebuilding of a partition, this is a feature which lets you reduce the impact of your maintenance on large tables but without incurring the performance cost that usually goes hand in hand with that.

What SQL Server 2014 Enterprise allows is for you to create statistics flagged as Incremental. This creation can be at index creation, custom stats creation or, if set at the database level, on all auto generated stats on partitioned tables.

SQL Server will then allow you to perform a FULLSCAN stats update of just one partition and then it will merge these into those of the table as a whole. Therefore you get the benefit of the FULLSCAN but without having to read the entire table. Good yes?

The only downside is that this does NOT fix the issue of having only 200 steps in the stats histogram, but it’s still a massive improvement as a whole.

So how do we go about setting this on the server?

First we’ll create the same table I’ve been using in all my previous posts…

use AdventureWorks2012
go

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

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

create
partition function testPartitionFunction(date)
  
as range left
  
for values('2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01')
go

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

create table testPartition
(
  
id int identity,
  
orderDate date,
  
accountNumber varchar(20),
  
purchaseOrderNumber varchar(20),
  
totalDue decimal(19, 8),
  
constraint pk_testPartition primary key clustered(id, orderDate)
)
on testPartitionScheme(orderDate)
go

insert into testPartition
select orderDate, AccountNumber, PurchaseOrderNumber, TotalDue
from sales.SalesOrderHeader
where orderDate between '2007-12-01' and '2008-05-01'
go


Okay, so let’s have a look at the stats that have come with our clustered primary key:

select *
from sys.stats
where object_id = object_id('testPartition')


As you can see, these aren’t incremental… so how do we fix that? Let’s drop re-create the primary key:

alter table testPartition drop constraint pk_testPartition
go

alter table testPartition add constraint pk_testPartition
  
primary key clustered (id, orderDate) with (statistics_incremental = on)
go

select *
from sys.stats
where object_id = object_id('testPartition')
go


That same option…

with (statistics_incremental = on)


… can be used on any index on a partitioned table.

If you want to create your own custom statistics then you would do so like this:

create statistics st_orderDate on testPartition(orderDate) with incremental = on
go


And if you want to enable this at the database level you would do the following:

alter database AdventureWorks2012 set auto_create_statistics on (incremental = on)
go


Note that enabling this means all automatic statistics created on partitioned objects will be enabled for Incremental Updates… but this does not affect statistics on non-partitioned tables, therefore it’s perfectly safe to enable.

Then all we need is the command to perform the single partition stats update:

-- Single partition
update statistics testPartition(st_orderDate) with resample on partitions(1)
go

-- Multi-partition
update statistics testPartition(st_orderDate) with resample on partitions(2, 3)
go


And that’s all there is to it.
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