A database can collect statistics that nobody on your team named. Those _WA_Sys objects are auto-created statistics, and they leave clues about the columns the optimizer needed to understand. The clue is useful only when you read the metadata behind the name.
Why the Engine Makes Auto-Created Statistics
With AUTO_CREATE_STATISTICS enabled, the optimizer can create a single-column statistic when a query predicate needs a histogram and no suitable one exists. It does this to estimate how many rows qualify. The familiar _WA_Sys prefix marks statistics created by the engine. The name contains internal identifiers, but the catalog views are the reliable way to identify the table and column. Decoding the name by eye is a party trick with a short shelf life.
I look for these statistics when a query plan makes a surprising row estimate. They show where the engine sought help from data distribution. They do not prove that every query on the server uses that column, and they do not tell me that an index is missing. A statistic contains distribution information. An index provides an access path. Those are different jobs.
Map Each Name to Its Column
Join sys.stats to sys.stats_columns, then to sys.columns for the actual column name. Filter on auto_created rather than only a name prefix. The query below stays inside the current database and lists the auto-created statistics a login is allowed to see. The stats_column_id value shows column order within a statistics object, although automatic statistics here are usually single-column objects.
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS table_name,
s.name AS statistics_name,
c.name AS column_name,
sc.stats_column_id
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id
JOIN sys.stats_columns AS sc
ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id
JOIN sys.columns AS c
ON c.object_id = sc.object_id AND c.column_id = sc.column_id
WHERE s.auto_created = 1
AND o.type = 'U'
ORDER BY schema_name, table_name, statistics_name;Run it in the database that owns the workload. A missing row is not evidence that nobody filters on a column. Automatic creation can be disabled, a useful histogram can already exist, or the optimizer can decide that creation is unnecessary. Metadata visibility also depends on permissions. Start with a question about a real query, then read this list as supporting evidence.
Check Freshness Before Blaming the Plan
A correct statistic can become stale as rows change. sys.dm_db_stats_properties exposes the last update, sampled rows, and modification counter for a statistics object. The counter is useful context, not a fixed threshold that works for every table. SQL Server decides when an update is needed according to its statistics rules and workload. A small skewed slice can matter even when the whole table looks stable.
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS table_name, s.name AS statistics_name,
p.last_updated, p.rows, p.rows_sampled,
p.modification_counter
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.auto_created = 1 AND o.type = 'U'
ORDER BY p.modification_counter DESC;I compare those values with the date of the bad plan and the data change that preceded it. An empty last_updated value needs investigation before any update command. The statistic can be new, empty, or unavailable through the function for that object. The quickest response is not always UPDATE STATISTICS across an entire database. That turns one unexplained estimate into a large maintenance job.
Use the Clues to Ask Better Index Questions
Suppose several costly queries filter on CustomerId and OrderDate, and both columns appear in auto-created statistics. That is a reason to inspect the predicates, joins, row estimates, and existing indexes. It is not a command to create two new single-column indexes. A useful composite index depends on which column narrows the search, the sort order, included columns, and the writes the table must support.
The same clue can suggest a multi-column statistic when a relationship between columns drives poor estimates. Separate single-column histograms do not describe every correlation. Build a small query reproduction and compare estimates before and after a deliberate change. What does the actual plan show at the point where estimated and actual rows part company? That gap is more valuable than a list of _WA_Sys names.
Do Not Turn Auto-Created Statistics Into a Cleanup Queue
Automatic statistics are normal objects. Dropping them to tidy a database can force the engine to recreate them when the same predicates run again. It also removes the very histogram that helped the plan. If a deployment is blocked by a user-created statistic, investigate that object and the change path. Newer SQL Server versions handle automatic statistic removal during schema changes differently from old releases, so a blanket cleanup rule ages badly.
Keep a small record of which query led you to inspect a statistic. A name alone does not carry that context. The plan, predicate, estimated rows, actual rows, and statistic update time make a useful tuning note. Without those, an inventory becomes another spreadsheet nobody trusts.
Follow the Query Back to Its Source
Look at the application statement or stored procedure that caused the statistic to appear. A parameter value, implicit conversion, or function around a column can explain why the optimizer struggled. Changing statistics without fixing that query shape can make the improvement brief. Test under the same compatibility level and SET options as the application when possible.
The engine made the object to answer a question about data. Use it to find the question, then check whether the estimate and access path are good enough. A statistic that was created automatically still deserves the same careful reading as one you created by hand. Its name is a breadcrumb, not a diagnosis.
For a recurring query, compare the plan before and after a targeted statistics update on a test copy. Keep the query text, parameters, estimated row counts, and actual row counts together. If the estimate improves but the plan still scans far more data than needed, the access path deserves attention. If the estimate stays poor, check correlated predicates and parameter-sensitive behavior before creating another statistic. One change at a time makes the result explainable. I also check whether an existing composite index already gives the optimizer a useful leading-column histogram. Duplicating that information can add upkeep without helping the query. When the evidence points to a new statistic, name it for the columns and purpose so the next DBA can tell it from the engine-created trail.
Related reading on this blog: How to Enable Auto Update Statistics and Auto Create Statistics with T-SQL: Interview Question of the Week #108 and Find Oldest Updated Statistics: Outdated Statistics.
An auto-created statistic is not an index recommendation, it is evidence that a query needed a better estimate.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
