or
searle1986 imported from SE
sql-server sql-server-2014
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,
    		i.name,
    		i.rowcnt,
    		i.rowmodctr,
    		p.last_updated
    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
Erik Darling
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:

`OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);`

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

`OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);`

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.
When do _WA_Sys_ statistics Get Updated?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.