DBCC SHOW_STATISTICS: A Deep Dive with Practical Examples

In this Article we describes the command step by step and demonstrates several actual examples. With such results, you will be able to optimize queries and performance.

“DBCC” The meaning of “DBCC” is Database Console Command. “SHOW_STATISTICS” request the order to show the system information about the statistics within indexes. The statistics of each index are on a table.

Understanding DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS displays optimizer statistics of the current query on a table or an indexed view. Query optimizer uses such statistics to determine the approximate number of rows a query will retrieve which is essential in selection of the most efficient execution plan. Obsolete statistics or out-of-date statistics may result in poor plans and poor query performance.
Its simple syntax is:
DBCC SHOW_STATISTICS ( table_name , target )
table_name: The indexed view or table's name.
target: _WA_Sys_<column_id>_<hex_id> for auto-generated statistics, or the name of the index or statistics. Statistics information for every statistic in the table is returned if the target is left out.

The result of DBCC SHOW_STATISTICS output is categorized in three ways:

Statistics Header: In Statistics header, the name of the object, last-updated date, number of rows, and number of rows sampled are mentioned.
Density Vector: Density vector indicates how the data values are unique. The more unusual the values are the denser the pattern is.
Histogram: The histogram of the leading column will indicate the range of values that there are and the number of rows contained in each range.

Examples

Let's consider a table named `SalesOrders` with the following structure (simplified for clarity):

--create table
CREATE TABLE SalesOrders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);
Let's populate the table with some sample data:
--insert data on SalesOrders  table
INSERT INTO SalesOrders (OrderID, CustomerID, OrderDate, TotalAmount) VALUES
(1, 101, '2023-01-15', 150.00),
(2, 102, '2023-01-20', 200.00),
(3, 101, '2023-02-01', 100.00),
(4, 103, '2023-02-10', 250.00),
(5, 102, '2023-02-15', 180.00),
(6, 101, '2023-03-01', 120.00),
(7, 104, '2023-03-10', 300.00),
(8, 102, '2023-03-15', 220.00),
(9, 103, '2023-04-01', 170.00),
(10, 101, '2023-04-10', 130.00);
Lets get data

--OutPut
OrderID     CustomerID  OrderDate  TotalAmount
----------- ----------- ---------- ---------------------------------------
1           101         2023-01-15 150.00
2           102         2023-01-20 200.00
3           101         2023-02-01 100.00
4           103         2023-02-10 250.00
5           102         2023-02-15 180.00
6           101         2023-03-01 120.00
7           104         2023-03-10 300.00
8           102         2023-03-15 220.00
9           103         2023-04-01 170.00
10          101         2023-04-10 130.00
  Let's populate the table index with some sample
CREATE INDEX IX_Orders_CustomerID ON SalesOrders (CustomerID);
CREATE INDEX IX_Orders_OrderDate ON SalesOrders (OrderDate);
Example 1: Examining Statistics on an Index

To view the statistics for the IX_Orders_CustomerID index, we can use the following command:


DBCC SHOW_STATISTICS (SalesOrders, IX_Orders_CustomerID)   
GO

 This report has three sections:
Statistics header
Rows: number of rows that the table contains (10, in this case)
Rows Sampled: the number of rows sampled, or number of rows less than the total when based on a sample and therefore less accurate;
in this case 0 (this data item was very low on our list during the test). Last updated- Indicates the date when the statistics were last time checked and updated
Density Vector
The value of the density is the reciprocal of the count of different values in the CustomerID column.
The smaller the value of density, the more sharp the values which is preferable.
In our example of 4 different CustomerID values (101, 102, 103, 104), density would be approx. 0.25.
Histogram
The occurrence of the CustomerID values is depicted in the histogram.
It takes the range of values and divides it into steps and indicates the number of rows that will lie within each step.