I have some of the auto generated `_WA_Sys_` statistics in my database, which have not been updated in a while (in comparison to other statistics in the same table).

The rule of thumb appears to be in tables >500 rows, statistics are updated after (20% + 500 rows) changes.

However, I can see using the following query:

    SELECT	t.name,
    FROM	sys.sysindexes i
    		JOIN sys.tables t
    			ON i.id = t.object_id
    		JOIN sys.stats s
    			ON s.object_id = t.object_id AND i.name = s.name
    		CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
    WHERE	rowmodctr > 0
    ORDER BY i.rowmodctr DESC

...that there is a table with a number of `_WA_Sys_` statistics that are way out of date (and have a `rowmodctr` that is higher than 20% + 500).

If a run a query against the table and add one of the columns associated with the out of date `_WA_Sys_` statistics in the `WHERE` clause, and check the *updated date* of the statistic, I can see it has updated.

If I run the same query with the `WHERE` clause again, the statistic doesn't update.

It seems like the `_WA_Sys` statistics update when a query is run that will use them and they are out of date?
Top Answer
meme (imported from SE)
SQL Server only automatically updates the statistics it uses. It may not be actively using the `_WA_Sys` statistics for cardinality estimation, though it may load them as part of the process.

To see which statistics are being used for your query, add these undocumented trace flags to the end of your query, and look in the SSMS *Messages* tab:


If your database is in a compat level prior to 2014, you'll need these:


These won't tell you _why_ it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.

Keep in mind that statistics updates don't occur on modification, only when queries run that use them.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.