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.

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:

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.

sys.dm_db_incremental_stats_properties_2

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:

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