| 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.
- 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.
- 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.
- 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.
- 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.
- 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
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.
