deleting unused statistics - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

deleting unused statistics

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-07-04 : 13:22:25

I have "auto create statistics" turned on. Many of my tables seem to have dozens of statistics associated with them. I believe many of them are old and no longer used. Can I delete all of the statistics that result from this query...


SELECT o.id, case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE (INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0))

The presence of hundreds of unused statistics probably causes my daily "update statistics" to take longer than it needs to.

Is it supposed to be a 1 to 1 relationship between indexes and statistics?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources