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

8 thoughts on “SQL SERVER – Find Automatically Created Statistics – T-SQL

  1. 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,

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  3. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s