SQL SERVER – Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2

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)