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

Posted by Pinal Dave on SQL Authority See other posts from SQL Authority or by Pinal Dave
Published on Wed, 14 May 2014 01:30:43 +0000 Indexed on 2014/05/26 21:46 UTC
Read the original article Hit count: 485

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.

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 (http://blog.sqlauthority.com)


Filed under: PostADay, SQL, SQL Authority, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: SQL Statistics, Statistics

© SQL Authority or respective owner

Related posts about PostADay

Related posts about sql