Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Updated Overlapping Statistics Scripts

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

Comments

Posted by Jason Brimhall on 5 April 2011

Oh what a nugget this is.  Adding this to the toolbox.

Posted by mathew.walters on 6 April 2011

Hi Kendal

FYI there's a spelling mistake in the 2005 script

FROM    sys.stats AS ColumnStats

           INNER JOIN sys.stats_columns AS StatsColuumns ON ColumnStats.object_id = StatsColumns.object_id

                                                           AND ColumnStats.stats_id = StatsColumns.stats_id

Should be

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

Cheers

Mat

Posted by mathew.walters on 6 April 2011

One other thing....

Awesome script, thanks very much :)

Mat

Posted by Kendal Van Dyke on 6 April 2011

Thanks Mat,

Good catch! Looks like the tool I used to convert the script into HTML friendly code put the extra "u" in there for me. I've fixed it on my blog. The content here is syndicated from it so hopefully it'll automatically reflect the update.

Regards,

Kendal

Posted by mathew.walters on 6 April 2011

No problem, using this script I've identified 101 overlapping statistics on one of our DBs.

Thanks again

Mat

Posted by Hotfix 4SQL on 30 December 2011

I did not notice double 'u'.  Post was published on April 5th, comments on April 6th.  It is a good script Kendal.

Leave a Comment

Please register or log in to leave a comment.