IT Giant

Hav­ing both auto cre­ated stat­ist­ics and index stat­ist­ics on the same column caused the query optim­izer to choose a dif­fer­ent — and less than optim­al — exe­cu­tion plan than when only the index stat­ist­ics existed.

From Author : Kend­al Van Dyke
http://​sqlserver​pedia​.com/​b​l​o​g​/​s​q​l​-​s​e​r​v​e​r​-​2​0​0​5​/​t​u​n​i​n​g​-​t​i​p​-​i​d​e​n​t​i​f​y​i​n​g​-​o​v​e​r​l​a​p​p​i​n​g​-​s​t​a​t​i​s​t​i​cs/

WITH    autostats ( object_id, stats_id, name, column_id )
          AS ( SELECT   sys.stats.object_id ,
                        sys.stats.stats_id ,
                        sys.stats.name ,
                        sys.stats_columns.column_id
               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
               WHERE    sys.stats.auto_created = 1
                        AND sys.stats_columns.stats_column_id = 1
             )
    SELECT  OBJECT_NAME(sys.stats.object_id) AS [Table] ,
            sys.columns.name AS [Column] ,
            sys.stats.name AS [Overlapped] ,
            autostats.name AS [Overlapping] ,
            'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)
            + '].[' + OBJECT_NAME(sys.stats.object_id) + '].['
            + autostats.name + ']'
    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 autostats ON sys.stats_columns.object_id = autostats.object_id
                                    AND sys.stats_columns.column_id = autostats.column_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.auto_created = 0
            AND sys.stats_columns.stats_column_id = 1
            AND sys.stats_columns.stats_id != autostats.stats_id
            AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0