http://www.sqlservercentral.com/blogs/kendalvandyke/2011/04/05/updated-overlapping-statistics-scripts/

Printed 2014/10/21 02:13PM

Updated Overlapping Statistics Scripts

By Kendal Van Dyke, 2011/04/05

Last year I published a script for identifying automatically generated column statistics that overlapped index statistics. I've updated the script to take filtered indexes into consideration in SQL 2008 and am sharing both the 2005 and 2008 versions. Also, a friendly reminder that the scripts run within the scope of the currently selected database. If you run into any problems with them let me know by leaving a comment below.


/* For SQL 2008 and up - takes filtered indexes into consideration */
WITH    cteAutostats ( object_id, stats_id, name, has_filter, filter_definition, column_id )
         
AS ( SELECT   ColumnStats.object_id ,
                       
ColumnStats.stats_id ,
                       
ColumnStats.name ,
                       
ColumnStats.has_filter ,
                       
ColumnStats.filter_definition ,
                       
StatsColumns.column_id
              
FROM     sys.stats AS ColumnStats
                       
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                             
AND ColumnStats.stats_id = StatsColumns.stats_id
              
WHERE    ColumnStats.auto_created = 1
                       
AND StatsColumns.stats_column_id = 1
            
)
   
SELECT  OBJECT_SCHEMA_NAME(ColumnStats.object_id) AS SchemaName ,
           
OBJECT_NAME(ColumnStats.object_id) AS TableName ,
           
ObjectColumns.name AS ColumnName ,
           
ColumnStats.name AS Overlapped ,
           
cteAutostats.name AS Overlapping ,
           
'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(cteAutostats.name) + ';' AS DropStatement
   
FROM    sys.stats AS ColumnStats
           
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                           
AND ColumnStats.stats_id = StatsColumns.stats_id
           
INNER JOIN cteAutostats ON StatsColumns.object_id = cteAutostats.object_id
                                      
AND StatsColumns.column_id = cteAutostats.column_id
           
INNER JOIN sys.columns AS ObjectColumns ON ColumnStats.object_id = ObjectColumns.object_id
                                                      
AND StatsColumns.column_id = ObjectColumns.column_id
   
WHERE   ColumnStats.auto_created = 0
           
AND StatsColumns.stats_column_id = 1
           
AND StatsColumns.stats_id != cteAutostats.stats_id
           
AND ( ( cteAutostats.has_filter = 1
                   
AND ColumnStats.has_filter = 1
                   
AND cteAutostats.filter_definition = ColumnStats.filter_definition
                 
)
                  OR (
cteAutostats.has_filter = 0
                      
AND ColumnStats.has_filter = 0
                    
)
                )
            AND
OBJECTPROPERTY(ColumnStats.object_id, 'IsMsShipped') = 0
   
ORDER BY OBJECT_SCHEMA_NAME(ColumnStats.object_id) ,
           
OBJECT_NAME(ColumnStats.object_id) ,
           
ObjectColumns.name ;
GO

/* For SQL 2005 only */
WITH    cteAutostats ( object_id, stats_id, name, column_id )
         
AS ( SELECT   ColumnStats.object_id ,
                       
ColumnStats.stats_id ,
                       
ColumnStats.name ,
                       
StatsColumns.column_id
              
FROM     sys.stats AS ColumnStats
                       
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                             
AND ColumnStats.stats_id = StatsColumns.stats_id
              
WHERE    ColumnStats.auto_created = 1
                       
AND StatsColumns.stats_column_id = 1
            
)
   
SELECT  OBJECT_SCHEMA_NAME(ColumnStats.object_id) AS SchemaName ,
           
OBJECT_NAME(ColumnStats.object_id) AS TableName ,
           
ObjectColumns.name AS ColumnName ,
           
ColumnStats.name AS Overlapped ,
           
cteAutostats.name AS Overlapping ,
           
'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ColumnStats.object_id)) + '.' + QUOTENAME(cteAutostats.name) + ';' AS DropStatement
   
FROM    sys.stats AS ColumnStats
           
INNER JOIN sys.stats_columns AS StatsColumns ON ColumnStats.object_id = StatsColumns.object_id
                                                           
AND ColumnStats.stats_id = StatsColumns.stats_id
           
INNER JOIN cteAutostats ON StatsColumns.object_id = cteAutostats.object_id
                                      
AND StatsColumns.column_id = cteAutostats.column_id
           
INNER JOIN sys.columns AS ObjectColumns ON ColumnStats.object_id = ObjectColumns.object_id
                                                      
AND StatsColumns.column_id = ObjectColumns.column_id
   
WHERE   ColumnStats.auto_created = 0
           
AND StatsColumns.stats_column_id = 1
           
AND StatsColumns.stats_id != cteAutostats.stats_id
           
AND OBJECTPROPERTY(ColumnStats.object_id, 'IsMsShipped') = 0
   
ORDER BY OBJECT_SCHEMA_NAME(ColumnStats.object_id) ,
           
OBJECT_NAME(ColumnStats.object_id) ,
           
ObjectColumns.name ;
GO


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.