Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

SQL 2014 Learning Series # 5 – New Feature – Incremental Statistics (Part 1)

Posted by blakhani on April 15, 2014


Many times you might have to update the statistics (index stats or normal stats) with fullscan, either manually or via scheduled job. If the table is large then time taken would be considerably huge because it has to scan each and every record of the table. Let’s assume that we have created partitions on the table and we only modified data for one partition. Earlier version of SQL didn’t offer any choice to update statistics only for one partition. Only choice we had was “FULLSCAN”.

To overcome this, SQL Server 2014 introduced “INCREMENTAL” keyword under Create Statistics. As per books online “When ON, the statistics created are per partition statistics. When OFF, stats are combined for all partitions. The default is OFF.”

To have some sample data, I have downloaded base AdventureWorks sample database from: http://msftdbprodsamples.codeplex.com/downloads/get/417885

I have restored it in SQL Server 2014 and named as AdventureWorks2014. I would be playing around with [AdventureWorks2014].[Production].[TransactionHistoryArchive] table which has transactions from 2005-05-17 to 2007-08-31. I have created quarterly partition.

Use AdventureWorks2014;
GO

CREATE partition FUNCTION [QuarterlyDate](datetime) AS range LEFT FOR VALUES ( 
N'2005-05-01T00:00:00', N'2005-08-01T00:00:00', N'2005-11-01T00:00:00', 
N'2006-02-01T00:00:00', N'2006-05-01T00:00:00', N'2006-08-01T00:00:00', 
N'2006-11-01T00:00:00', N'2007-02-01T00:00:00', N'2007-05-01T00:00:00', 
N'2007-08-01T00:00:00', N'2007-11-01T00:00:00'); 
GO

CREATE partition scheme [TransactionDatePS] AS partition [QuarterlyDate] TO ( 
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], 
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);
GO

CREATE TABLE Production.TransactionHistoryArchive_IncrementalDemo(
    TransactionID int NOT NULL,
    ProductID int NOT NULL,
    ReferenceOrderID int NOT NULL,
    ReferenceOrderLineID int NOT NULL, 
    TransactionDate datetime NOT NULL,
    TransactionType nchar(1) NOT NULL,
    Quantity int NOT NULL,
    ActualCost money NOT NULL,
    ModifiedDate datetime NOT NULL)
ON TransactionDatePS(TransactionDate);
GO

Now, I am going to populate partitioned table. To show before and after effect, we would populate only one partition at this point. Since our PF is defined as “LFET” which mean boundary value would go to left side. If I have to populate 8th partition, the range would be TransactionDate > N’2006-11-01T00:00:00′ and TransactionDate <= N’2007-02-01T00:00:00′. Here is the query to dump data into newly created table.

INSERT INTO Production.TransactionHistoryArchive_IncrementalDemo
SELECT * FROM Production.TransactionHistoryArchive
WHERE TransactionDate > N'2006-11-01T00:00:00' and 
TransactionDate <= N'2007-02-01T00:00:00'

(10324 row(s) affected). Now, let’s look at partitions

SELECT * FROM sys.partitions
  WHERE object_id = OBJECT_ID('Production.TransactionHistoryArchive_IncrementalDemo');

 

image

As expected, we have 10324 rows in 8th partition. Use below to create “incremental” statistics. Notice that I have added new clause which is available in SQL Server 2014.

CREATE STATISTICS IncrementalStatsDemo 
ON Production.TransactionHistoryArchive_IncrementalDemo (TransactionDate) 
WITH FULLSCAN, INCREMENTAL = ON;

Execute DBCC SHOW_STATISTICS command to look at histogram

DBCC SHOW_STATISTICS('Production.TransactionHistoryArchive_IncrementalDemo', IncrementalStatsDemo)
with histogram

image

 

Added more rows to different partition

INSERT INTO Production.TransactionHistoryArchive_IncrementalDemo
SELECT * FROM Production.TransactionHistoryArchive
WHERE TransactionDate > N'2007-02-01T00:00:00' and 
TransactionDate <= N'2007-08-01T00:00:00'

(27318 row(s) affected)

image

UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo (IncrementalStatsDemo)
with resample ON PARTITIONS(9, 10)

Now, lets look at stats again.

image

If we compare with earlier histogram image, the highlighted range was at step 81 earlier which has moved to step 60 now and Total number of steps have increased to 192 (as compared to 81 earlier). This means that update statistics command has read those partitions which were specified (9 and 10) and merged with earlier created statistics. As per books online “ON PARTITIONS – Forces the leaf-level statistics covering the partitions specified in the ON PARTITIONS clause to be recomputed, and then merged to build the global statistics. WITH RESAMPLE is required because partition statistics built with different sample rates cannot be merged together.”

There is more to cover because there are various syntax added on different places. In next blog, I would cover those.

Stay tuned.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • About these ads

    4 Responses to “SQL 2014 Learning Series # 5 – New Feature – Incremental Statistics (Part 1)”

    1. karthik said

      Thank you sir…

    2. […] 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 […]

    3. manu said

      Thank you

    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

     
    Follow

    Get every new post delivered to your Inbox.

    Join 886 other followers

    %d bloggers like this: