Duplicate Statistics | ctrl-alt-geek

Conclusion: Automatically created statistics can be rendered obsolete by subsequent index creations but they will still be updated and managed by SQL Server – consuming resources. I’ll leave with a script that I’ve based on ideas from Erin Stellato that will pick out any duplicate statistics in a given database.

;with stats_on_indexes([object_id],[table_column_id],[index_name])
 as(
  select
   o.[object_id] as [object_id],
   ic.[column_id] as [table_column_id],
   i.name
  from sys.indexes i
  join sys.objects o on i.[object_id] = o.[object_id]
  join sys.stats st on i.[object_id] = st.[object_id] and i.name = st.name
  join sys.index_columns ic on i.index_id = ic.index_id and i.[object_id] = ic.[object_id]
  where o.is_ms_shipped = 0
  and i.has_filter = 0
  and ic.index_column_id = 1
 )
select
 o.[object_id] as [ID],
 o.name as [Table],
 c.name as [Column],
 s.name as [AutoCreatedStatistic],
 stats_on_indexes.index_name as [Index]
from sys.stats s
 join sys.stats_columns sc
  on s.stats_id = sc.stats_id and s.[object_id] = sc.[object_id]
 join sys.objects o 
  on sc.[object_id] = o.[object_id]
 join sys.columns c 
  on sc.[object_id] = c.[object_id] and sc.column_id = c.column_id
 join stats_on_indexes 
  on o.[object_id] = stats_on_indexes.[object_id] and stats_on_indexes.table_column_id = c.column_id
where s.auto_created = 1
and s.has_filter = 0