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)
8 Comments. Leave new
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?
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.
Thx a lot. This script with drop statistics, can fix problem “the provided statistics stream is corrupt”
Thx for the script. With using drop statistic, it solve the problem “the provided statistics stream is corrupt”
I am looking for the script to update system generated statistics on a particular database.
script run for 2 or 3 hours (duration parameter input into procedure)? If the procedure is passed the duration limit, it should stop, and on the next run. It should pick up from where it stops
The problem may be in the damaged statistics, and probably in the one that was created automatically
To search for problem statistics you can use:
EXEC sp_updatestats
exec will iterate over all the tables and statistics, and if there are problems, it will show which statistics, more precisely the table, is the problem.
To remove auto_created statistics, you can use the following script:
select ‘drop statistics [‘ + OBJECT_NAME (OBJECT_ID) + ‘].[‘ + Name + ‘]’ from sys.stats where auto_created = 1 and OBJECT_NAME (OBJECT_ID) = ‘DB PROBLEM TABLE NAME’
The script will generate rows for deleting auto_created statistics for the table “DB PROBLEM TABLE NAME” table
After running the generated lines, rerun
EXEC sp_updatestats
to check for errors.
Not superfluous would be
exec sp_msforeachtable N’UPDATE STATISTICS? WITH FULLSCAN’