SQL SERVER – Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2
- by Pinal Dave
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 from my friend Balmukund’s must read blog post. If you have 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 are 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
Now here 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 is 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 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 following scripts.
-- Clean up
DROP TABLE [IncrStatTab]
DROP PARTITION SCHEME [IncrStatSch]
DROP PARTITION FUNCTION [IncrStatFn]
GO
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: SQL Statistics, Statistics
