The statistics automatically created by SQL Server will start with _WA_ in their name.Keep Auto Update Statistics enabled: This database property allows SQL Server to automatically update the statistics when they are deemed outdated.However you can identify the statistics that satisfy one of the thresholds for the automatic update above but still hasn’t been updated; this is a good indication of unused statistics.
Scheduling the statistics maintenance during off hours will help reduce the need to update statistics during peak times.The need and frequency of this proactive maintenance will depend on your environment; frequent data changes causes the statistics to become outdated more quickly.Outdated statistics can lead the optimizer in choosing inefficient execution plan and can dramatically affect overall performance.Steps must therefore be taken in order to keep statistics up to date.Column statistics: Created manually by the DBA using the ‘CREATE STATISTICS’ command, or automatically if the “Auto Create Statistics” option is set to “True”.
Column statistics can be created, modified and dropped at will.
The distribution is a representation of the data contained in the first column of the statistic.
This information is stored in a histogram; the histogram contains up to 200 steps with a lower and upper limit and contains the count of values that fall between both limits.
The following script from Kendal Van Dyke will identify all single column statistics that are covered by an existing index statistic (share the same leading column) in a database and generates the TSQL commands to drop them.
WITH autostats ( object_id, stats_id, name, column_id ) AS ( SELECT sys.stats.object_id , sys.stats.stats_id , sys., 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.AS [Column] , sys.AS [Overlapped] , AS [Overlapping] , 'DROP STATISTICS [' OBJECT_SCHEMA_NAME(sys.stats.object_id) '].[' OBJECT_NAME(sys.stats.object_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 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 !
When the data in the database changes the statistics become stale and outdated.