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
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
-- 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
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);
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
