Statistics
Overview
The Statistics report answers which statistics are stale enough to be giving the optimizer bad estimates – measured against SQL Server’s own auto-update threshold, not against a number this application invented and not against age.
That distinction is the whole report. Age on its own says almost nothing: ten days is nothing on a static lookup table and a catastrophe on one taking a million rows an hour. What matters is modifications relative to the threshold at which SQL Server would update the statistic itself.
The bar is drift against that threshold, so 1.0 is the point SQL Server would act.
What this fixes
Five things were wrong with the report this replaces:
- It ranked mostly on age. Its severity test asked “updated in the last ten days” first, so a five-hundred-million-row table with two hundred million modifications updated nine days ago came back as Looking Fine.
- It read
rowmodctrfromsys.sysindexes– a deprecated compatibility view where that column has been an approximation since SQL Server 2005 and is routinely zero on a modern instance. The old query turned a zero into “no rows changed – no need to update”, so the report was actively telling people to skip statistics that needed updating. Everything now comes fromsys.dm_db_stats_properties, which carries the realmodification_counteralong with the sample size and histogram step count. - It only showed index-backed statistics. Auto-created column statistics and anything from a plain
CREATE STATISTICSnever appeared. Those go stale the same way and are now included. STATISTICS_NORECOMPUTEwas invisible. A statistic marked that way will never be auto-updated however far it drifts, which turns a slow problem into a permanent one.
Where to find it
A database-level report that can also run instance-wide. Select a database in the tree and open Statistics, then use the All Databases button to widen the scope.
The verdicts
Ordered worst to best, because the grid and the chart both sort on this.
| Verdict | When |
|---|---|
| Never | The statistic has never been updated. |
| Overdue | Drift has passed 1.0 – SQL Server’s own auto-update threshold. |
| Sampled | The histogram was built from too thin a sample to trust. |
| Drifting | Past 0.5 – halfway to the threshold. Worth showing, not yet worth acting on. |
| Current | Nothing to do. |
The thresholds
| Value | Meaning |
|---|---|
| Drift 1.0 | Overdue. This is SQL Server’s number, not one this application picked. |
| Drift 0.5 | Drifting – halfway there. |
| Sample below 2% of the table | The histogram is a guess. |
| Under 1,000,000 rows | Thin samples are not flagged – SQL Server reads small tables in full anyway, and flagging them would bury the large tables where it matters. |
The Sampled verdict is the one people have not usually thought about. SQL Server’s default sample rate falls away sharply as tables grow, and two percent of a large table read into two hundred histogram steps is where estimates start coming out orders of magnitude wrong. A statistic can be perfectly current and still be built on a sample too thin to describe the data.
Reading the chart
Reading the grid
| Column | What it is |
|---|---|
| Drift vs Threshold | Modifications as a multiple of the auto-update threshold. Drawn with a bar so the grid scans like the chart. |
| Table | The table. |
| Statistic | The statistic name. |
| Kind | Index-backed, auto-created, or user-created. |
| Rows | Table row count. |
| Modified | The real modification_counter. |
| Sampled | What fraction of the table the histogram was built from. |
| Steps | Histogram step count. |
| Notes | Context, including NORECOMPUTE. |
| Last Updated | When it was last updated. |
In All Databases scope a Database column is added at the front.
Sampled and Steps together are what tell you whether to trust a histogram. Two hundred steps built from two percent of a billion-row table is not a description of that table.
The toolbar
| Group | Buttons |
|---|---|
| Scope | This Database · All Databases |
| Top N | Top 100 · Top 500 · All |
Create Script ▾ · Refresh |
Create Script is one button with a menu rather than six buttons, and everything under it writes a script and nothing under it runs one – the distinction that matters on this page. The grid supports multi-select, so you can script a batch.
How to read the report
- Look at Never and Overdue first. They are ordered to the top already.
- Check the Sampled column on your largest tables. A current statistic built from a two percent sample can still be the reason a plan is wrong.
- Read the Notes column for
NORECOMPUTE. Those will never fix themselves. - Compare Rows against Modified. Drift is the ratio that matters, but the absolute numbers tell you how big the update will be.
- Select the ones you want and use Create Script. Read it, then run it in a window where a scan is acceptable.
Common patterns
A very large table marked Overdue. The most likely cause of a plan that used to be fine and is not. Update with a full scan if you can afford it.
A current statistic with a 1% sample on a huge table. The Sampled verdict. Consider a higher sample rate or FULLSCAN on a schedule for that one statistic.
Statistics marked NORECOMPUTE. Somebody turned off auto-update, usually to stop an update running at a bad time, and never built the scheduled replacement. These drift permanently.
Auto-created _WA_Sys_ statistics near the top. Legitimate and previously invisible – the old report filtered them out by name.
Everything Current on a busy database. Good, and worth confirming auto-update is actually on rather than assuming.
Where the data comes from
sys.stats joined to sys.dm_db_stats_properties, which is the modern, accurate source for modification counts, sample size and step count. Nothing is stored.
Frequently asked questions
Why does this report disagree with my old one about what needs updating? Because the old one read rowmodctr from a deprecated compatibility view where it is routinely zero, and treated zero as “nothing changed”.
What is drift measured against? SQL Server’s own auto-update threshold for that statistic. A drift of 1.0 is the point SQL Server would update it itself, if auto-update is on and NORECOMPUTE is not set.
Why are small tables not flagged as under-sampled? Because SQL Server reads them in full anyway. Flagging them would bury the large tables where sampling actually matters.
Why do auto-created statistics appear now? Because they are statistics and they go stale the same way. The old report filtered anything named _WA_%.
Can this report update statistics for me? No. It writes scripts. An update on a large table is a scan, and that is a decision about timing.