View Statistical Database Information

To view statistical database information in SQL Server 2022, you can leverage system catalog views and dynamic management views (DMVs) to access metadata about database statistics. Start by querying the `sys.stats` catalog view, which provides details about statistics objects for tables and indexes. For example, running `SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('YourTableName')` lists all statistics for a specific table, including the statistic’s name, whether it’s auto-created, and the column it applies to. To dig deeper into the columns associated with each statistic, join `sys.stats` with `sys.stats_columns` and `sys.columns` to retrieve column names and their positions in the statistic. This approach helps you understand which columns have statistics and how they’re configured, crucial for query optimization.
For more detailed statistical information, such as histogram data and density vectors, use the `DBCC SHOW_STATISTICS` command. Execute `DBCC SHOW_STATISTICS ('YourTableName', 'StatisticName')` to display the statistics for a specific table and statistic. This command outputs three result sets: the header (with metadata like updated date and sample size), the density vector (showing selectivity for column combinations), and the histogram (detailing data distribution for the leading column). These insights are vital for understanding how the query optimizer uses statistics to generate execution plans. Ensure you have appropriate permissions, as viewing statistics requires at least `VIEW DEFINITION` on the table.
To monitor statistics health and usage, query the DMV `sys.dm_db_stats_properties`. For instance, `SELECT * FROM sys.dm_db_stats_properties (OBJECT_ID('YourTableName'), 1)` returns properties like the last update time, row count, and modification counter for a specific statistic (replace `1` with the `stats_id` from `sys.stats`). This helps identify outdated statistics that may need updating with `UPDATE STATISTICS YourTableName`. Additionally, the `sys.dm_db_stats_histogram` DMV provides histogram details for a statistic, offering a programmatic way to analyze data distribution. Combining these tools allows you to effectively view and manage statistical information, ensuring optimal query performance in SQL Server 2022.
After creating statistics you occasionally will want to view statistics information. Usually you will do this to verify you have created statistics on a table and to check the distribution of your data. SQL Server has two commands to monitor statistics.

The command dbcc show_statistics will display statistics for an index or statistic group on a table. The syntax is:

DBCC SHOW_STATISTICS (table, index_or_statistic_group)

The command, dbcc show_statistics will display distribution information for the table.

The DBCC SHOW_STATISTICS command in SQL Server 2022 provides insight into how the query optimizer estimates row counts. It returns three detailed result sets for a specified table or indexed/statistical column.
1. Statistics Header

Statistics for object 'OrderID'
Updated               Rows   Rows Sampled   Steps   Density         Avg Key Length
--------------------- ------ -------------- ------- --------------- -----------------
2025-07-02 14:30:00   2155   2155           200     0.0011922787    8.0
(1 row(s) affected)
  

• Updated: Timestamp when statistics were last updated.
• Rows: Total number of rows in the table at the time of statistics creation.
• Rows Sampled: Number of rows sampled to generate statistics.
• Steps: Number of steps (buckets) in the histogram.
• Density: Represents how unique values are; lower values indicate higher selectivity.
• Average Key Length: Average size in bytes of the index or column key.

2. Density Vector

All Density      Columns
---------------- --------------------------------
0.0012048193     OrderID
0.0004640371     OrderID, ProductID
(2 row(s) affected)
  

• All Density: Inverse of the number of distinct values for the listed columns. Used in join and filter selectivity estimation.
• Columns: Individual or composite column combinations analyzed.

3. Histogram

RANGE_HI_KEY   RANGE_ROWS   EQ_ROWS   DISTINCT_RANGE_ROWS   AVG_RANGE_ROWS
------------   -----------  --------  ---------------------  ----------------
10248          0            3         0                      0
10251          1            1         1                      1
10253          2            1         2                      1
...
(200 row(s) affected)
  

• RANGE_HI_KEY: The upper-bound value of a histogram step.
• EQ_ROWS: Estimated number of rows equal to the step's value.
• RANGE_ROWS: Estimated number of rows between this value and the previous one.
• DISTINCT_RANGE_ROWS: Estimated number of distinct values in the range.
• AVG_RANGE_ROWS: Average number of rows per distinct value within the range.

These three result sets help database administrators and developers troubleshoot query performance, inspect cardinality estimates, and validate if statistics need to be updated manually or through automatic updates.
Tip: For a more programmatic approach to statistics in SQL Server 2022, use views like sys.stats, sys.dm_db_stats_properties, and sys.stats_histogram.

The output of `DBCC SHOW_STATISTICS` is still used and fully supported in SQL Server 2022.
✅ Purpose of `DBCC SHOW_STATISTICS`
`DBCC SHOW_STATISTICS` is used to display statistics information about the distribution of key values in the specified index or statistics object. This helps DBAs and developers understand how SQL Server estimates row counts for query optimization.
🔍 Syntax

DBCC SHOW_STATISTICS (table_or_view_name, target)

Where `target` is the name of an index or statistics object.
📊 Output Sections
It returns three result sets:

1. Header: Basic stats like the number of rows, rows sampled, density, and when the stats were last updated.
2. Density Vector: Used by the optimizer for join and filter selectivity.
3. Histogram: Distribution of key values, showing how rows are spread across ranges.

🆕 SQL Server 2022 Context
Although `DBCC SHOW_STATISTICS` is not deprecated, SQL Server 2022 also supports modern alternatives:
🧠 Alternative: `sys.dm_db_stats_properties`
A DMV that gives programmatic access to similar metadata:

SELECT * 
FROM sys.dm_db_stats_properties(object_id('dbo.MyTable'), stats_id);

📈 Alternative: `sys.stats` + `sys.stats_columns` + `sys.stats_histogram`
These catalog views and functions allow detailed inspection and scripting of statistics.

Still very useful for:

• Debugging cardinality estimation issues
• Investigating stale or auto-updated statistics
• Validating optimizer behavior

📘 Best Practice
Use `DBCC SHOW_STATISTICS` in read-only investigations or performance tuning scripts, but prefer `sys.dm_db_stats_properties` and `sys.stats_*` views when building reusable tools or automation.

To find out when statistics were updated on a table or an index group, use the scalar function stats_date. The syntax is:
STATS_DATE(table_id, index_id_or_statistic_group_id)
To find out when statistics were updated on every table and every index/statistics group, use the following SQL:

SELECT 'Table Name' = o.name, 'Index Name' = i.name, 
    'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE  o.id = i.id
order by 1,2

The next lesson will cover how to use sqlmaint.exe.