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
Check out all new features of SQL Server 2014 @ http://msdn.microsoft.com/en-us/library/bb510411.aspx