How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158

Question: How can I identify and, when justified, drop user-created statistics in SQL Server?

Answer: I see this question in SQL Server forums quite often. A DBA may find statistics created manually or by a tuning exercise and wonder whether they should be removed. There is a useful catalog query for finding them. The dangerous leap is assuming that “user-created” means “unused.” A manually created statistic can be exactly the information the optimizer needs.

Start in the intended user database, inspect each candidate, and compare query plans and workload evidence before deciding to drop anything. This query only prints proposed statements; it does not run them. It excludes statistics associated with an index, whose management belongs with that index. QUOTENAME protects names that contain spaces or punctuation.

-- Connect to the user database you intend to inspect first.
IF DB_ID() <= 4
BEGIN
    SELECT N'Select the intended user database first.' AS ReviewMessage;
    RETURN;
END;

SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
       o.name AS ObjectName,
       s.name AS StatisticsName,
       STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,
       N'DROP STATISTICS '
       + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.'
       + QUOTENAME(o.name) + N'.'
       + QUOTENAME(s.name) + N';' AS ProposedStatement
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id
WHERE s.user_created = 1
  AND o.type IN (N'U', N'V')
  AND NOT EXISTS
  (
      SELECT 1
      FROM sys.indexes AS i
      WHERE i.object_id = s.object_id
        AND i.index_id = s.stats_id
  )
ORDER BY SchemaName, ObjectName, StatisticsName;

The output is a review list, not a maintenance script to execute wholesale. If you verify that one specific standalone statistic is redundant, copy and run only its DROP STATISTICS [schema].[object].[statistic] statement in a controlled change window, and compare the affected workload afterward. In particular, deleting a statistic is not the same as dropping an index. My older explanation mixed those up; the catalog query above keeps them separate.

If the concern is stale information, check whether statistics are outdated and look at their last update dates before considering deletion. If you have a useful way to assess a particular statistic, share it in the comments and I will credit you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.