Monitoring Databases  «Prev  Next»

Lesson 9 Viewing statistics
Objective View statistic information.

View Statistical Information in SQL Server 2025

After creating or updating statistics, as the previous lesson covered, the natural next step is actually looking at what was created: confirming statistics exist on a table, and checking the distribution of the data they describe. SQL Server gives three ways to do this: one console command, and two sets of catalog views and dynamic management objects that return the same information in query form. All three answer the same underlying question, how does the optimizer see this data, just at different levels of detail and in different formats depending on whether a person is reading the result or a script is.

It helps to be concrete about why this matters before getting into the commands themselves. When the query optimizer compiles SELECT * FROM dbo.Orders WHERE OrderDate = '2026-03-15', it does not count the actual matching rows before choosing a plan; counting first would defeat the purpose of having a plan at all. Instead it reads the statistics object covering OrderDate, walks its histogram to the step containing that date, and estimates a row count from that step's data. If the estimate is close to reality, the optimizer's choice of a seek versus a scan, and its choice of join order against any other table in the query, tend to be good ones. If the estimate is far off, because the statistics are stale, too coarsely sampled, or were never created at all, the resulting plan can be badly wrong even though the underlying indexes are perfectly healthy. Viewing statistics directly, the subject of this lesson, is how to confirm which side of that line a given table is actually on, rather than inferring it indirectly from a slow query.

Finding What Statistics Exist: sys.stats

sys.stats lists every statistics object on a table, whether created automatically, created by hand with CREATE STATISTICS, or created as a side effect of an index.

SELECT s.name AS StatisticsName, s.auto_created, s.user_created, s.stats_id
FROM sys.stats AS s
WHERE s.object_id = OBJECT_ID('dbo.Customers');
sys.stats by itself only gives a statistics object's own name and ID, not the columns underneath it. Joining it to sys.stats_columns and sys.columns resolves which column, or columns, each statistics object actually covers:

SELECT s.name AS StatisticsName, c.name AS ColumnName, sc.stats_column_id
FROM sys.stats AS s
JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE s.object_id = OBJECT_ID('dbo.Customers')
ORDER BY s.name, sc.stats_column_id;
Running this against dbo.Customers after the previous lesson's examples would show CustStat1 against ZipCode alone, and CustStat2 against both State and ZipCode in that order, confirming exactly what got created without having to remember it or dig through a script history.

StatisticsName   auto_created   user_created   stats_id
CustStat1        0              1              2
CustStat2        0              1              3
_WA_Sys_...      1              0              4
That third row, with a name starting _WA_Sys_, is worth recognizing on sight: it is SQL Server's own naming convention for a statistics object it created automatically, as opposed to one a person named explicitly. auto_created = 1 confirms the same thing more directly. Neither kind behaves differently once created; the distinction only matters when deciding whether a given statistics object is safe to drop without losing something someone deliberately set up.

DBCC SHOW_STATISTICS: The Detailed, Console-Based View

DBCC SHOW_STATISTICS returns the actual histogram and density information behind a specific statistics object, index, or auto-created column statistic, the detail that sys.stats alone cannot show.

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
[ WITH [ NO_INFOMSGS ] { STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM } [ , ...n ] ]
  • target: the name of an index, a statistics object, or a column. A column name returns the automatically created statistic on that column, if one exists; if none exists, SQL Server raises an error rather than returning an empty result.
  • STAT_HEADER / DENSITY_VECTOR / HISTOGRAM: limits the output to just that one result set. Leaving all three out returns all three.
  • NO_INFOMSGS: suppresses informational messages.
  • STATS_STREAM: informational only; not supported for actual use.
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate');
Run without a WITH clause, it returns three result sets.

1. The header.

Name                  Updated               Rows   Rows Sampled   Steps   Density         Average Key Length
--------------------- --------------------- ------ -------------- ------- --------------- -------------------
IX_Orders_OrderDate   2026-10-28 09:15:00    48213  48213          200    0.0000207429    8.0
Updated, Rows, Rows Sampled, and Steps (capped at 200) describe how current and how thorough the statistics are. If Rows Sampled is less than Rows, every value below is an estimate rather than an exact count, a fact worth checking before treating a histogram as ground truth.

2. The density vector.

All Density      Average Length   Columns
---------------- ---------------- --------------------------------
0.0000207429     8.0              OrderDate
0.0000006184     12.0             OrderDate, CustomerID
All Density is the inverse of the number of distinct values for that column or column combination; a lower number means higher uniqueness. Each additional row down the list adds one more column to the combination, which is what lets the optimizer estimate selectivity on a multi-column filter or join, not just a single column, the same correlation idea the previous lesson's multi-column State, ZipCode example relied on.

3. The histogram.

RANGE_HI_KEY   RANGE_ROWS   EQ_ROWS   DISTINCT_RANGE_ROWS   AVG_RANGE_ROWS
------------   -----------  --------  ---------------------  ----------------
2026-01-05     0            112       0                      0
2026-01-19     187          95        14                     13.36
Each row is one step, up to 200 of them, describing the data between the previous step's value and RANGE_HI_KEY: how many rows fall strictly inside that range (RANGE_ROWS), how many equal the boundary value exactly (EQ_ROWS), how many distinct values exist inside the range (DISTINCT_RANGE_ROWS), and the average rows per distinct value in that range (AVG_RANGE_ROWS). This is the data the optimizer actually consults when it estimates how many rows a query filtering on OrderDate will return, the same estimate that, done badly, leads to the wrong plan this lesson's introduction mentioned.

The 200-step cap is worth understanding rather than just memorizing, since it explains why a very large table's histogram is necessarily a compression of the real data rather than an exact record of it. A table with 48,000 distinct OrderDate values cannot get one histogram step per date; SQL Server groups nearby values together into at most 200 steps, choosing step boundaries that try to keep the row counts inside each step roughly even. That is also why AVG_RANGE_ROWS matters as much as the boundary values themselves: within a step, the optimizer assumes an even distribution across every distinct value in that range, an assumption that holds up well for smoothly distributed data and considerably less well for data with sharp spikes, a single enormously popular CustomerID among many ordinary ones, for example. Filtered statistics, from the previous lesson, exist specifically to give that kind of lopsided column its own, more accurate histogram rather than forcing it to share one with the rest of the table.

Permissions, corrected from a common oversimplification: DBCC SHOW_STATISTICS requires SELECT permission on the table, specifically covering every column in the statistics object and any filter condition it has, not a blanket VIEW DEFINITION grant. If any of those columns carry Dynamic Data Masking, UNMASK permission or db_ddladmin membership is also required, and the table cannot have a row-level security policy applied. Before SQL Server 2012 SP1, the requirement was stricter: table ownership, or membership in sysadmin, db_owner, or db_ddladmin. Trace flag 9485 restores that older, stricter behavior if a specific environment still needs it.

The Programmatic Equivalents: sys.dm_db_stats_properties and sys.dm_db_stats_histogram

Everything DBCC SHOW_STATISTICS prints to a results grid is also available as dynamic management objects a script can filter, join, or feed into an alert. sys.dm_db_stats_properties, introduced in the previous lesson for checking staleness, covers the header information; sys.dm_db_stats_histogram, available since SQL Server 2016 SP1 CU2, returns exactly the same rows as DBCC SHOW_STATISTICS WITH HISTOGRAM, one row per step, in query form.

SELECT hist.step_number, hist.range_high_key, hist.range_rows,
       hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) AS hist
WHERE s.object_id = OBJECT_ID('dbo.Orders') AND s.name = 'IX_Orders_OrderDate';
The practical difference between the two approaches is the same one this course has run into with other DBCC commands and their DMV equivalents, going all the way back to DBCC SHOWCONTIG versus sys.dm_db_index_physical_stats earlier in this module: DBCC SHOW_STATISTICS is quick for a one-off look during an investigation, while the DMVs are the better choice when the result needs to feed a script, a comparison across many tables, or an automated check, since a DBCC command's console output is not something a query can filter or join against directly.

Both views require the same permission tier as the DBCC command they mirror: specific permission on the statistics columns, or table ownership, or membership in sysadmin, db_owner, or db_ddladmin.

One more reason to reach for the DMV form specifically: a query built on sys.dm_db_stats_histogram can compare histograms across every table in a database in one pass, something no amount of individually run DBCC SHOW_STATISTICS calls can do without a loop and a temporary table to collect results into. A scheduled health check built on top of this course's earlier lessons on SQL Server Agent, which this module will return to shortly, is exactly the kind of script that benefits from this: a single job step that flags any table whose modification_counter has drifted well past the compatibility-level-aware threshold from the previous lesson, without a person having to remember to go check each table by hand.

STATS_DATE: A Quick Last-Updated Check

STATS_DATE returns just the last-updated timestamp for a single statistics object, a narrower, faster alternative to sys.dm_db_stats_properties when that one value is all that's needed.

STATS_DATE ( object_id, stats_id )
It returns NULL rather than an error for a statistics object with no actual data behind it yet, a new empty table or a filtered statistic whose predicate matches nothing. Permissions require membership in db_owner, or permission to view the table or indexed view's own metadata.

SELECT o.name AS TableName, s.name AS StatisticsName,
       STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM sys.stats AS s
JOIN sys.objects AS o ON s.object_id = o.object_id
ORDER BY o.name, s.name;
That query, built on sys.objects and sys.stats, is the direct modern equivalent of an older version built on sysobjects and sysindexes. Both are backward-compatibility views dating to SQL Server 2000, still present for script compatibility but not the views to write new code against, and sys.stats in particular exists specifically for statistics metadata in a way sysindexes was never designed for; a query still written against the old pair is worth updating the next time it is touched, not only when it breaks.

Putting It Together: Reading a Histogram to Explain a Bad Plan

Seeing these tools used in sequence against a real question makes the lesson more concrete than treating each one as a separate, isolated fact.

  1. A query against dbo.Orders filtering on a specific, recent OrderDate is choosing a full scan instead of the index seek it should use. The execution plan's estimated row count is far smaller than the actual rows returned, the same cardinality mismatch mentioned in this lesson's introduction.
  2. Confirm statistics exist on the right column. The sys.stats join shown earlier confirms IX_Orders_OrderDate does cover OrderDate, ruling out a missing statistics object as the cause.
  3. Check when it was last updated and how large the drift has been. sys.dm_db_stats_properties, from the previous lesson, shows a modification_counter in the hundreds of thousands against a table that has grown substantially since the statistics object's last update, well past what the compatibility-level-dependent threshold covered in that lesson would require before an automatic update fires.
  4. Read the actual histogram to see the gap directly. DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate') WITH HISTOGRAM; shows the most recent step's RANGE_HI_KEY sitting weeks before the date actually being queried. Every order placed since that step was recorded is invisible to the optimizer's estimate entirely, which is exactly why the row count estimate came back so low.
  5. Fix it, then confirm. UPDATE STATISTICS dbo.Orders WITH FULLSCAN;, from the previous lesson, resolves the immediate problem; rerunning DBCC SHOW_STATISTICS or querying sys.dm_db_stats_histogram afterward confirms the histogram's top step now reaches current data before assuming the fix worked.
That is the practical payoff of this lesson: not just knowing the four commands exist, but being able to read a histogram's actual values and recognize when they explain a real problem, rather than treating "update statistics and see if it helps" as the only available move.

Choosing the Right Tool for the Question Being Asked

With four ways now covered to look at the same underlying statistics, which one to reach for comes down to what the question actually is. Confirming statistics exist at all, and on which columns, is sys.stats's job. Reading the full histogram and density detail during a one-off investigation, the kind of work that happens once and gets discarded, is what DBCC SHOW_STATISTICS is for. Feeding that same detail into a script, a scheduled health check, or a comparison across many tables at once calls for sys.dm_db_stats_properties and sys.dm_db_stats_histogram instead, since their output is a table a query can act on. And when the only question is simply when something last ran, STATS_DATE answers it in one call without pulling in everything else sys.dm_db_stats_properties also returns.

ToolBest For
sys.stats (+ sys.stats_columns)Confirming which statistics exist, and on which columns
DBCC SHOW_STATISTICSA full, human-readable look during a one-off investigation
sys.dm_db_stats_propertiesScripted staleness checks across many tables at once
sys.dm_db_stats_histogramScripted, queryable access to the histogram itself
STATS_DATEA single quick last-updated timestamp, nothing more

This closes out the connection running through this entire module: a database can look structurally healthy by every DBCC integrity check covered earlier, and still perform poorly because the optimizer's working picture of the data is out of date. Structural integrity and statistical accuracy are different properties of the same database, checked with different tools, and this lesson's four commands are specifically the ones for the second property. A monitoring routine that only schedules CHECKDB and never looks at a statistics object directly is missing half of what this module has actually covered.

What's Ahead

The next lesson moves to scheduling and automating the database maintenance this module has covered, DBCC integrity checks, statistics updates, and the rest, rather than running each command by hand every time. That work runs through SQL Server Agent jobs executing the actual maintenance T-SQL directly, the graphical Maintenance Plan Wizard in SSMS, or the community maintained scripts already introduced earlier in this module, Ola Hallengren's IndexOptimize and Microsoft's own Adaptive Index Defrag, rather than through sqlmaint.exe, a command-line utility whose xp_sqlmaint wrapper has been on Microsoft's deprecated features list since SQL Server 2008 and has no place in a current maintenance routine.

SEMrush Software 9 SEMrush Banner 9