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 (https://blog.sqlauthority.com)
