This is the second part of the series Incremental Statistics. Here is the index of the complete series.
- What is Incremental Statistics? – Performance improvements in SQL Server 2014 – Part 1
- Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2
- DMV to Identify Incremental Statistics – Performance improvements in SQL Server 2014 – Part 3
In part 1 we have understood what is incremental statistics and now in this second part, we will see a simple example of incremental statistics. This blog post is heavily inspired by my friend Balmukund’s must-read the blog post. If you have a partitioned table and lots of data, this feature can be specifically very useful.
Prerequisite
Here are two things you must know before you start with the demonstrations.
AdventureWorks – For the demonstration purpose I have installed AdventureWorks 2012 as an AdventureWorks 2014 in this demonstration.
Partitions – You should know how partition works with databases.
Setup Script
Here is the setup script for creating Partition Function, Scheme, and the Table. We will populate the table based on the SalesOrderDetails table from AdventureWorks.
-- Use Database USE AdventureWorks2014 GO -- Create Partition Function CREATE PARTITION FUNCTION IncrStatFn (INT) AS RANGE LEFT FOR VALUES (44000, 54000, 64000, 74000) GO -- Create Partition Scheme CREATE PARTITION SCHEME IncrStatSch AS PARTITION [IncrStatFn] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) GO -- Create Table Incremental_Statistics CREATE TABLE [IncrStatTab]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [ModifiedDate] [datetime] NOT NULL) ON IncrStatSch(SalesOrderID) GO -- Populate Table INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [ModifiedDate]) SELECT [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [ModifiedDate] FROM [Sales].[SalesOrderDetail] WHERE SalesOrderID < 54000 GO
Check Details
Now we will check details in the partition table IncrStatSch.
-- Check the partition SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('IncrStatTab') GO
You will notice that only a few of the partition is filled up with data and remaining all the partitions are empty.
Now we will create statistics on the Table on the column SalesOrderID.
However, here we will keep adding one more keyword which is INCREMENTAL = ON. Please note this is the new keyword and feature added in SQL Server 2014. It did not exist in earlier versions.
-- Create Statistics CREATE STATISTICS IncrStat ON [IncrStatTab] (SalesOrderID) WITH FULLSCAN, INCREMENTAL = ON GO
Now we have successfully created statistics let us check the statistical histogram of the table.
Now let us once again populate the table with more data. This time the data are entered into a different partition than earlier populated partition.
-- Populate Table INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [ModifiedDate]) SELECT [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [ModifiedDate] FROM [Sales].[SalesOrderDetail] WHERE SalesOrderID > 54000 GO
Let us check the status of the partition once again with following script.
-- Check the partition SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('IncrStatTab') GO
Statistics Update
Nowhere has the new feature come into action. Previously, if we have to update the statistics, we will have to FULLSCAN the entire table irrespective of which partition got the data.
However, in SQL Server 2014 we can just specify which partition we want to update in terms of Statistics. Here is the script for the same.
-- Update Statistics Manually UPDATE STATISTICS IncrStatTab (IncrStat) WITH RESAMPLE ON PARTITIONS(3, 4) GO
Now let us check the statistics once again.
-- Show Statistics DBCC SHOW_STATISTICS('IncrStatTab', IncrStat) WITH HISTOGRAM GO
Upon examining statistics histogram, you will notice that now the distribution has changed and there are way more rows in the histogram.
Summary
The new feature of Incremental Statistics is indeed a boon for the scenario where there are partitions and statistics needs to be updated frequently on the partitions. In the earlier version to update statistics, one has to do FULLSCAN on the entire table which was wasting too many resources. With the new feature in SQL Server 2014, now only those partitions which are significantly changed can be specified in the script to update statistics.
Cleanup
You can clean up the database by executing the following scripts.
-- Clean up DROP TABLE [IncrStatTab] DROP PARTITION SCHEME [IncrStatSch] DROP PARTITION FUNCTION [IncrStatFn] GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
How to split a single partition into two partitions
ALTER PARTITION FUNCTION IncrStatFn ()
SPLIT RANGE (50000);
How to Merge partition from two partitions to one partitions
ALTER PARTITION FUNCTION IncrStatFn ()
MERGE RANGE (50000);
I wanted to add some details about it. I got 188 rows from histogram after first “dbcc show_statistics (‘IncrStatTab’,’IncrStat’) with histogram” and 155 rows after second. It seems Sql server merge some ranges of the histogram into one. after “UPDATE STATISTICS IncrStatTab (IncrStat)
WITH RESAMPLE ON PARTITIONS(3, 4)”. i got it on sql2014 sp1
That’s quite possible. Histogram depends on data distribution.