Earlier, I wrote about my experience at an organization here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script. This blog post briefly narrates another experience I had at the same organization.
When I was there, I also looked at the statistics and found something that I would like to bring into the limelight. As the developers ran many non-production queries on the production server, many statistics were automatically created on the table. These stats were not useful as they were created by several queries which ran one-time or ad-hoc. Because of this, we really had to identify all the useless statistics and drop them off in order to clean up the statement.
Note: If you are going to say that it is not advisable to do as explained, I suggest you read the circumstances of the client I told in the earlier blog post.
Running the following script will return all the automatically created statistics:
SELECT name, OBJECT_NAME(OBJECT_ID)
FROM sys.stats
WHERE auto_created = 1;
You can drop the useless statistics by running the following script on each table:
DROP STATISTICS TableName.StatisticsName
Please note that not all automatically created statistics are useless, although the statistics created by the ad-hoc workload may not be that useful.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Good information. Can I know how many time a statistics is used?
Like I can see for the index in the dmv.
Pinal,
this leads me back to one of my “questions of the week”:
is there such thing as too many statistics? What happens if you create too many different and even overlapping statistics on a table? Is there a performance degradation for updating statistics, if they are too many and the data in the table changes?
http://feodorgeorgiev.com/blog/2010/08/question-of-the-week-too-many-statistics/
Hi Pinal,
Can we have query to find the statistics created by adhoc query, because this script is returning the name and tables on which statics created. can we have details on that so we can know that is useless or not? also return so many different statistics on same table. I want to know among them which are useless and not required on same table.
Thanks,
Thanks for the script.