SQL SERVER – DMV to Identify Incremental Statistics – Performance improvements in SQL Server 2014 – Part 3

This is the third part of the series Incremental Statistics. Here is the index of the complete series.


In earlier two parts we have seen what is incremental statistics and its simple example. In this blog post we will be discussing about DMV, which will list all the statistics which are enabled for Incremental Updates.

SELECT OBJECT_NAME(sys.stats.OBJECT_ID) AS TableName,
sys.columns.name AS ColumnName,
sys.stats.name AS StatisticsName
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.OBJECT_ID = sys.stats_columns.OBJECT_ID
AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN sys.columns ON sys.stats.OBJECT_ID = sys.columns.OBJECT_ID
AND sys.stats_columns.column_id = sys.columns.column_id
WHERE sys.stats.is_incremental = 1


If you run above script in the example displayed, in part 1 and part 2 you will get resultset as following.

SQL SERVER - DMV to Identify Incremental Statistics - Performance improvements in SQL Server 2014 - Part 3 incrstats

When you execute the above script, it will list all the statistics in your database which are enabled for Incremental Update. The script is very simple and effective. If you have any further improved script, I request you to post in the comment section and I will post that on blog with due credit.

Reference: Pinal Dave https://blog.sqlauthority.com/ )

, ,
Previous Post
SQL SERVER – Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2
Next Post
SQL SERVER – SSIS Look Up Component – Cache Mode – Notes from the Field #028

Related Posts

2 Comments. Leave new

Leave a Reply

Menu