I was working for a client on a very critical and heavily used OLTP database. The application was an e‑commerce website with an approximate ratio of 1 write to 400 reads. Most queries completed in sub‑second response times, but occasionally some would jump to 4–5 seconds, which was not acceptable for the business.
When I started troubleshooting, server resources (CPU, memory, disk) were generally quiet and did not indicate a clear resource bottleneck. I reviewed the queries, table design, and indexes; everything looked reasonable at first glance, and automatic statistics updates were enabled, with an additional SQL Agent job updating statistics for key tables every few hours.
Eventually I noticed a pattern in the transaction activity. Most of the write workload was concentrated on a small subset of “hot” customers. That concentrated activity caused the statistics on specific indexes and columns to become stale for those hot ranges, even though overall row change percentages for the table were not high enough to obviously trigger concern. To confirm this and to get better visibility, I wrote a query to check when each statistic was last updated and whether automatic statistics updates were enabled for it.
You can use and customize the following scripts on SQL Server 2016 and higher.
Basic script: Last updated time and AUTOSTATS
This first script shows, for each statistics object in the current database, when it was last updated and whether automatic statistics updates are enabled for that statistic.
SELECT DB_NAME() AS [Database], s.name AS [Schema Name], o.name AS [Object Name], st.name AS [Statistic Name], CASE st.no_recompute WHEN 1 THEN 'OFF' ELSE 'ON' END AS [AUTOSTATS], st.auto_created AS [Auto Created], STATS_DATE(st.object_id, st.stats_id) AS [Last Updated] FROM sys.stats AS st JOIN sys.objects AS o ON o.object_id = st.object_id JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE OBJECTPROPERTY(st.object_id, 'IsSystemTable') = 0 -- Uncomment this line if you want to see only user tables: -- AND OBJECTPROPERTY(st.object_id, 'IsUserTable') = 1 -- Comment out the next line if you want to include auto-created stats: AND st.auto_created = 0 ORDER BY [Last Updated];
A few notes:
- Statistic Name is the name of the statistics object, which for index statistics usually matches the index name; auto-created and column stats can have system-generated names.
- AUTOSTATS is derived from no_recompute; if it is OFF, automatic statistics updates are disabled for that statistic.
- STATS_DATE returns NULL if the statistics object has never been updated or is not applicable.
This is a good starting point to quickly see which objects have very old statistics or unexpected AUTOSTATS settings.
Index-focused view: Last updated per index
If you want to focus on index statistics only (ignoring purely column-based or auto-created stats), you can use a view based on sys.indexes. For each non‑heap index, SQL Server maintains statistics tied to the index definition.
SELECT DB_NAME() AS [Database], s.name AS [Schema Name], o.name AS [Object Name], i.name AS [Index Name], STATS_DATE(i.object_id, i.index_id) AS [Last Updated] FROM sys.indexes AS i JOIN sys.objects AS o ON o.object_id = i.object_id JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.is_ms_shipped = 0 AND i.type > 0 -- exclude heaps ORDER BY [Last Updated];
This is useful when you are specifically correlating index maintenance (rebuild/reorganize) with statistics freshness and want a quick inventory by index name.
Advanced: rows changed and percent modified (SQL 2012+)
On SQL Server 2012 and higher (including 2016+), you can get richer information per statistics object using sys.dm_db_stats_properties. This DMV shows the last update time, row count, and how many rows have been modified since the last statistics update.
SELECT DB_NAME() AS [Database], s.name AS [Schema Name], o.name AS [Object Name], st.name AS [Statistic Name], CASE st.no_recompute WHEN 1 THEN 'OFF' ELSE 'ON' END AS [AUTOSTATS], st.auto_created AS [Auto Created], sp.last_updated AS [Last Updated], sp.rows AS [Row Count], sp.modification_counter AS [Rows Modified], CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS decimal(10, 2)) AS [Percent Rows Changed] FROM sys.stats AS st JOIN sys.objects AS o ON o.object_id = st.object_id JOIN sys.schemas AS s ON s.schema_id = o.schema_id CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp WHERE OBJECTPROPERTY(st.object_id, 'IsSystemTable') = 0 AND OBJECTPROPERTY(st.object_id, 'IsUserTable') = 1 -- Comment out to include auto-created statistics: AND st.auto_created = 0 ORDER BY [Percent Rows Changed] DESC;
This script lets you:
- Identify statistics that have a high percentage of rows changed since the last update, even if the table as a whole is large.
- Focus manual updates on objects whose change pattern (for example, “hot” customer segments) is likely to cause poor cardinality estimates and plan instability.
Microsoft’s documentation calls out sys.dm_db_stats_properties and STATS_DATE as the primary ways to detect out-of-date statistics for SQL Server 2016+.
How to use this in practice (SQL 2016+)
Here are some ways to apply these scripts in a SQL Server 2016+ environment:
- Spot-check problematic queries: when you see sudden latency spikes, capture the affected tables and use these scripts to verify last stats update times and row-change percentages.
- Validate maintenance jobs: confirm that your index and statistics maintenance jobs (such as Ola Hallengren’s solution) are refreshing statistics at the expected cadence.
- Tune selective updates: instead of blanket UPDATE STATISTICS on all tables, use the “Percent Rows Changed” view to drive targeted updates where they’ll actually help.
In the original e‑commerce scenario, this approach made it clear that a small number of stats on customer-centric tables were going stale frequently. Adjusting the statistics maintenance to refresh those objects more often stabilized cardinality estimates and brought those 4–5 second outliers back into sub‑second territory.