Incremental statistics – New feature in SQL Server 2014

Incremental Statistics is one of the new feature added in SQL Server 2014, let us have a quick look to understand this feature!

Statistics is one of the important criteria to be validated during performance analysis as improper statistics directly impacts query execution plan hence query performance. Updating statistics with full scan has to read each record of a given table. This new feature allows to update the statistics only on a specific partition minimizing CPU and I/O cost. This can be used only on partitioned tables, very helpful for huge data warehouse tables.

Following is a simple demo to update the statistics only one specific partition….

• Create table with Partition using AdventureWorks2012.Production.TransactionHistory
• Insert some data into only few partitions

use AdventureWorks2012

-- Create Partition function
CREATE partition FUNCTION TranStatFn(datetime) AS range LEFT FOR VALUES ( 
N'2007-10-01', N'2008-01-01', N'2008-04-01', 
N'2008-07-01', N'2008-10-01'); 

-- Create Partition schema
CREATE partition scheme TranStatSch AS partition TranStatFn ALL TO ([PRIMARY]) 
GO

-- Create a table with partition
CREATE TABLE demo_part_TransactionHistory(
	[TransactionID] [int],
	[ProductID] [int] NOT NULL,
	[ReferenceOrderID] [int] NOT NULL,
	[ReferenceOrderLineID] [int] ,
	[TransactionDate] [datetime] ,
	[TransactionType] [nchar](1) NOT NULL,
	[Quantity] [int] NOT NULL,
	[ActualCost] [money] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL ) 
ON TranStatSch([TransactionDate])

-- Insert some data into the table 
insert into demo_part_TransactionHistory  
select *  from Production.TransactionHistory 
WHERE TransactionDate < N'2008-05-01'


-- Check the partition
SELECT * FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('demo_part_TransactionHistory')

• Create Statistics with INCREMENTAL = ON
• Insert remaining data and update statistics only on partitions where the data is added
• Update statistics only on specific partitions

-- Create Statistics 
CREATE STATISTICS IncrStat
ON demo_part_TransactionHistory ([TransactionDate])
WITH FULLSCAN, INCREMENTAL = ON
GO

-- Check statistics available 
DBCC SHOW_STATISTICS('demo_part_TransactionHistory', IncrStat)
with histogram

-- Insert remaining data into table 
insert into demo_part_TransactionHistory  
select *  from Production.TransactionHistory 
WHERE TransactionDate >= N'2008-05-01'

-- Check partition numbers
SELECT * FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('demo_part_TransactionHistory')
GO

-- Update statistics of specific partition 
UPDATE STATISTICS demo_part_TransactionHistory (IncrStat)
with resample ON PARTITIONS(4,5)

-- Check statistics 
DBCC SHOW_STATISTICS('demo_part_TransactionHistory', IncrStat)
with histogram

• Clean up

-- Clean up of new table, Partition Schema and Partition function
drop table demo_part_TransactionHistory
DROP PARTITION SCHEME TranStatSch
drop PARTITION FUNCTION TranStatFn

Reference:
Check out all new features of SQL Server 2014 @ http://msdn.microsoft.com/en-us/library/bb510411.aspx

Advertisements
This entry was posted in SQL, SQL Query, sql2014 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s