SQL Server 2016 – sys.dm_db_incremental_stats_properties for Incremental Stats

Hello Friends,

During various troubleshooting scenarios, we generally check the statistics information on the objects. These statistics information helps us a lot to detect the root cause. There is a DMF that we use to check the statistics information i.e. sys.dm_db_stats_properties. This DMF requires the object id and stats id to return the details.

SELECT *
FROM sys.dm_db_stats_properties(Object_id,Stats_id);

SQL Server 2014 introduces a new kind of statistics i.e. Incremental statistics. If you wants yo know more about incremental statistics then you can click here: Link1 and Link2.

In this post, we will look a limitation of sys.dm_db_stats_properties which has been resolved by introducing new DMF  sys.dm_db_incremental_stats_properties for Incremental Stats. Here I am using the same code that I used in my above post:

--Create a database for Demo at default files location
CREATE DATABASE StatsDemo2016
GO
USE [StatsDemo2016]
GO
--Create Partition Function
CREATE PARTITION FUNCTION xpfstatsdemo (INT)
AS RANGE RIGHT FOR VALUES
(10000,20000)
GO
--Create Partition Scheme
CREATE PARTITION SCHEME xpsstatsdemo
AS PARTITION xpfstatsdemo
ALL TO ([Primary])
go
--Create table by using Partition Scheme on id column
CREATE TABLE xtstatsdemo
(
id INT NOT NULL,
balance INT NOT NULL,
name VARCHAR(25)
) ON xpsstatsdemo (id)
GO
--Create a clustered index on id column [This will cover complete table]
--Here I have also specify statistics_Incremental option
CREATE CLUSTERED INDEX CI_xtstatsdemo
ON xtstatsdemo (id) WITH (STATISTICS_INCREMENTAL=ON);
GO
--You can check here about how many partitions belongs to the table
SELECT * FROM sys.partitions WHERE object_id=OBJECT_ID('xtstatsdemo')
GO
-- Now I am inserting 30000 rows in the table. That means each partition will contain 10000 rows here
-- Here id and balance both are having same values for demo purpose
set nocount on
declare @counter int =1
declare @balance int =1
while (@counter<=30000)
begin
    if (@counter%2=0)
        begin
        insert into xtstatsdemo values(@counter,@balance,'data1')
        end
    else
        begin
        insert into xtstatsdemo values(@counter,@balance,'data2')
        end
    set @counter = @counter + 1;
    set @balance = @balance + 1;
end
go
--You can check number of rows inside the table
SELECT COUNT(*) FROM xtstatsdemo
GO

sys.dm_db_incremental_stats_properties

In above output, you can see that we have three partitions and total number of rows in these partitions are 30000.

--Statement to update stats automatically because # of rows have been changed from 0 to 30000
Select * from xtstatsdemo where id>20000
go
SELECT * FROM sys.dm_db_stats_properties(OBJECT_ID('xtstatsdemo'),1);
GO

sys.dm_db_incremental_stats_properties_2

-- Now I am going to delete 3000 rows from third partition.
delete from xtstatsdemo where id>27000
go
--Statement to update the stats automatically
Select id,name from xtstatsdemo where id>20000
go
--Stats after 10% rows deletion with respect to table while 30% with respect to third partition
SELECT * FROM sys.dm_db_stats_properties(OBJECT_ID('xtstatsdemo'),1);
GO

sys.dm_db_incremental_stats_properties_3

One important thing to note here is: we are using incremental statistics and as per these incremental stats we should have three stats one for each partition here. While we only get the information without any partition information. With the above information we can’t say which partition statistics have been updated here. Now we will use the new DMF sys.dm_db_incremental_stats_properties to get the partition level information here as mention below:

SELECT *
FROM sys.dm_db_incremental_stats_properties(OBJECT_ID('xtstatsdemo'),1);

sys.dm_db_incremental_stats_properties_4

From the above output, you can easily identify that partition level stats updated only for the third partition. This new DMF is really very useful in such scenarios.

PS: This DMF is available from SQL Server 2014 SP2 and SQL Server 2016 SP1 onwards.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *