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
Advertisements

Author: Protiguous

C# Software Developer, Father, and seeker of Truth.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s