Friday , February 26 2021

DBCC SHOW_STATISTICS in SQL Server

With DBCC SHOW_STATISTICS we can get detailed information about a statistic. As many of you know, statistics have one of the most important roles in the decision making of the sql server engine when creating a query plan. The SQL Server Engine looks at the statistics and decides whether or not an index should be used. So first of all, our statistics need to be up to date.

Before you read this article, I recommend that you read the following articles to better understand why you need this command.

Statistic Concept and Performance Effect on SQL Server“,

Index Concept and Performance Effect on SQL Server“,

What is Execution Plan on SQL Server

DBCC SHOW_STATISTICS shows detailed information about a statistic in a table or indexed view.

The output of this command consists of 3 elements.

  • Statistic Header
  • Density Vector
  • Histogram

Let us explain the above concepts and details through an example. Run the following command in the AdventureWorks database.

In this command,

HumanReources is the schema name,

Department is the table name and

AK_Department_Name is the statistics name.

We see a result set as follows.

In the following tables, you can see the descriptions of the columns in the result set.

Statistic Header
NameStatistic Name
UpdatedLast update date of statistic
RowsShows the number of rows on the table or indexed view when the statistic were last updated.
Rows SampledThe number of rows sampled when creating or updating statistics. You can find more information in the article “UPDATE STATISTICS in SQL Server”
StepSpecifies the number of steps in the histogram. We will mention later.
DensityNot used after SQL Server 2008.
Average Key LengthThe average size of the columns of statistic(in bytes)
String IndexIf the columns of the statistic are char, varchar, nchar, nvarchar, varchar (max), nvarchar (max), text, or ntext, the value of this column is YES.
Filter ExpressionIf the statistic is filtered, it shows the filter expression.
Unfiltered RowsShows the number of unfiltered rows. If there is no filter, it will show the number of rows in the corresponding table or indexed view.
Density Vector
All DensityThis refers to the value “1 / n”. “n” is the number of different records in the table. The closer this row is to 0, the more singularity is achieved.
Average LengthThe average size of the columns of statistic(in bytes)
ColumnsThis column shows the corresponding columns.
HistogramThis is a graph showing the data distribution. When SQL Server generates a statistic, it first sorts the column values. It then divides the data into specific groups and sets the upper limit of each group. For example, consider a column with values ​​from 1 to 1000. In this column, statistics were grouped and divided into 100 group(1 to 10, 10 to 20…990 to 1000).  The first group(1 to 10) is defined as step 1. Because of we divide the statistic into 100 group, this means statistic has 100 steps.
RANGE_HI_KEYThis is the key value that specifies the upper limit for the Histogram step. So you can think of it as a line separating each step.
RANGE ROWSEstimated number of rows per histogram step
EQ_ROWSNumber of records equal to each other at step limit
DISTINCT_RANGE_ROWSEstimated number of non-duplicated rows per histogram step
AVG_RANGE_ROWSNumber of duplicated records in the corresponding step

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *