SQL SERVER – Find Automatically Created Statistics – T-SQL

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)

,
Previous Post
SQL SERVER – Quickly Upgrade Your SQL Server
Next Post
SQLAuthority News – Download – Microsoft SQL Server 2008 R2 Best Practices Analyzer Whitepaper

Related Posts

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.

    Reply
  • 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?

    Reply
  • 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,

    Reply
  • Thanks for the script.

    Reply
  • Thx a lot. This script with drop statistics, can fix problem “the provided statistics stream is corrupt”

    Reply
  • Thx for the script. With using drop statistic, it solve the problem “the provided statistics stream is corrupt”

    Reply
  • 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

    Reply
  • 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’

    Reply

Leave a Reply

Menu