Maintenance can end up updating two statistics objects for the same leading column. Duplicate statistics can survive after a new index arrives, leaving extra work behind. Find the candidates through metadata, compare their definitions and freshness, and remove only the auto-created objects that a tested review identifies as redundant.
Understand Why Duplicate Statistics Remain
Automatic statistics can be created on a column before an index uses that column as its leading key. The new index brings its own statistics object, but SQL Server does not automatically remove every older auto-created object that resembles it. Both objects can then participate in maintenance.
I check the actual columns before calling the pair duplicate. A single-column automatic statistic and a multicolumn index statistic share a histogram on the leading column, but the index statistic also contains density information for its key prefixes. Filter definitions and sampling also affect what each object describes.
The goal is a careful redundancy review, not a contest to minimize the number of objects in sys.stats. Automatically created objects exist because the optimizer needed information at some point. A similar index arriving later makes a candidate worth inspecting. The metadata looks duplicated. The optimizer still deserves a vote before you clean its desk.
Treat duplicate statistics as candidates until their column order, filters, sampling, and workload roles have been compared.
Match the Column to the First Index Key
The following query selects ordinary auto-created, single-column statistics and matches their first column to an index's leading key. It uses key_ordinal equal to one, not merely membership anywhere in the index. A later key column does not provide the same histogram relationship.
The query excludes filtered objects and disabled indexes from this initial candidate set. That keeps the example focused on a straightforward overlap. It also excludes hypothetical indexes. The output retains both statistics names and the shared column so the relationship can be inspected directly.
I keep the schema and table with each candidate. Statistics names alone are not a useful drop target. The generated statement is text for review, protected with QUOTENAME at every identifier boundary. Returning that text does not execute it. Save the result and check the actual definitions before selecting any candidate for a change.
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,t.name AS TableName,
s.name AS AutoStatistic,i.name AS IndexName,c.name AS LeadingColumn,
N'DROP STATISTICS '+QUOTENAME(SCHEMA_NAME(t.schema_id))+N'.'
+QUOTENAME(t.name)+N'.'+QUOTENAME(s.name)+N';' AS ReviewStatement,
t.object_id,s.stats_id,i.index_id
INTO #StatisticCandidates
FROM sys.tables AS t
JOIN sys.stats AS s ON s.object_id=t.object_id AND s.auto_created=1 AND s.has_filter=0
JOIN sys.stats_columns AS sc ON sc.object_id=s.object_id AND sc.stats_id=s.stats_id AND sc.stats_column_id=1
JOIN sys.columns AS c ON c.object_id=sc.object_id AND c.column_id=sc.column_id
JOIN sys.index_columns AS ic ON ic.object_id=t.object_id AND ic.column_id=sc.column_id AND ic.key_ordinal=1
JOIN sys.indexes AS i ON i.object_id=ic.object_id AND i.index_id=ic.index_id
WHERE i.type IN(1,2) AND i.has_filter=0 AND i.is_disabled=0 AND i.is_hypothetical=0
AND NOT EXISTS
(SELECT 1 FROM sys.stats_columns AS extra
WHERE extra.object_id=s.object_id AND extra.stats_id=s.stats_id AND extra.stats_column_id>1);
SELECT * FROM #StatisticCandidates ORDER BY SchemaName,TableName,AutoStatistic;Compare Freshness and Sampling Side by Side
sys.dm_db_stats_properties returns last_updated, rows, rows_sampled, and modification_counter for a statistics object. Compare the automatic object with the matched index statistic. Their update histories can differ even when the leading-column relationship is similar.
The next query reads the temporary candidate table, so run it in the same session. It keeps candidates whose properties are unavailable by using OUTER APPLY. NULL properties need investigation rather than an invented date. Empty tables and metadata visibility can affect what is returned. Retain the raw values and inspect the relevant object before drawing a conclusion.
Which object currently gives the optimizer better information for the affected query? Compare its statistics usage in the plan and inspect representative estimates. A fresh automatic statistic beside a stale index statistic is not a reason to drop the fresher object immediately. Improve the replacement's information and test the workload before removing the candidate. Freshness is one input, not a complete redundancy definition.
SELECT c.SchemaName,c.TableName,c.AutoStatistic,c.IndexName,
a.last_updated AS AutoLastUpdated,a.rows_sampled AS AutoSampledRows,
a.modification_counter AS AutoModifications,
x.last_updated AS IndexLastUpdated,x.rows_sampled AS IndexSampledRows,
x.modification_counter AS IndexModifications
FROM #StatisticCandidates AS c
OUTER APPLY sys.dm_db_stats_properties(c.object_id,c.stats_id) AS a
OUTER APPLY sys.dm_db_stats_properties(c.object_id,c.index_id) AS x;
Inspect the Histogram and Query Estimates
DBCC SHOW_STATISTICS provides the header, density vector, and histogram for a named object. Run it for the automatic statistic and the index in a test copy. Compare the leading-column distribution and sampling information, then inspect the query plans that matter to the workload.
The example below reads the names from the candidate table, so run it in the same session as the first query. Change the schema and table filter to a reviewed candidate. A statistics name copied from a different table will not answer the intended question. Check the table context and retain the result with the candidate identifiers.
Do not judge only by matching histogram step counts. The estimates that use the information are the practical evidence. Include representative parameter values and skewed regions of the data. Also check queries that use the column without the same index access path. The automatic object can still provide useful selectivity information even when a particular query does not read through the index.
DECLARE @TableName nvarchar(300),@AutoStatistic sysname,@IndexName sysname;
SELECT TOP (1) @TableName=QUOTENAME(SchemaName)+N'.'+QUOTENAME(TableName),
@AutoStatistic=AutoStatistic,@IndexName=IndexName
FROM #StatisticCandidates
WHERE SchemaName=N'dbo' AND TableName=N'StatisticDemo';
DBCC SHOW_STATISTICS(@TableName,@AutoStatistic);
DBCC SHOW_STATISTICS(@TableName,@IndexName);Remove Only Reviewed Duplicate Statistics
Test the selected DROP STATISTICS statement on a suitable copy first. Keep user-created statistics outside this cleanup because they can encode a deliberate design decision. The initial query's auto_created filter is part of that safety boundary, not a decorative column.
After removal, compare the relevant queries and their estimates. A new compilation can use a different information set, so include plan behavior in the review. Preserve the original object definition and baseline when your change process requires restoration evidence.
SQL Server can recreate automatic statistics if a later query needs them. That is supported behavior. If a supposedly redundant object returns, investigate the request that needed it rather than scheduling another automatic drop. Repeatedly deleting and recreating the same information adds churn and obscures the workload requirement. A cleanup should reduce unnecessary maintenance, not argue with every future optimizer request.
Keep Maintenance Focused on Useful Information
Measure the maintenance work on your own server before claiming savings. The presence of two objects does not establish a specific duration or resource cost. Capture the relevant update statements and workload evidence instead of promising that one dropped object transforms the maintenance window.
Revisit the candidates after index changes because the overlap relationship changes with key order and filters. An index removal can leave the automatic statistic as the remaining useful histogram for that column. A historical cleanup decision should not become a permanent deletion rule.
Duplicate statistics reviews work best as small, evidence-backed changes. Match the exact leading column, compare definitions and freshness, test the important queries, and remove only the auto-created objects whose replacement is adequate. Keep useful optimizer information available while avoiding maintenance that serves no current purpose.
Related reading on this blog: Find Outdated Statistics: SQL in Sixty Seconds #137 and The Comprehensive Guide to STATISTICS_NORECOMPUTE.
A duplicate statistics candidate is not a removal order, it is an object that needs a definition and workload check.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
