What are statistics properties details and what those terms means to me.?

Problem: What are statistics properties and what those terms means to me.?

Solution:

From my last two articles I am exploring more on the statistics on how to create and update statistics.

In this article I would like to go through the details page of the statistics properties from a SQL server management studio dialog box when you right click on the statistics and open properties, the properties window shows the information collected in the currently selected statistic object. This information is used by the query optimizer to create the best possible query plan.

Here are some new terms in the statistics properties which needs some understanding in order to improve query optimizer performance.

When you navigate through the table and right click on the properties and then go the details page you will see the below screen.

Rows Sampled
Total number of rows sampled for statistics calculations. If Rows Sampled < Rows, the displayed histogram and density results are estimates based on the sampled rows.

Steps
Number of steps in the histogram. Each step spans a range of column values followed by an upper bound column value. The histogram steps are defined on the first key column in the statistics. The maximum number of steps is 200.

Density
Calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values. This Density value is not used by the query optimizer and is displayed for backward compatibility with versions before SQL Server 2008.

Average Key Length
Average number of bytes per value for all of the key columns in the statistics object.

String Index
Yes indicates the statistics object contains string summary statistics to improve the cardinality estimates for query predicates that use the LIKE operator; for example, WHERE ProductName LIKE '%Bike'. String summary statistics are stored separately from the histogram and are created on the first key column of the statistics object when it is of type char, varchar, nchar, nvarchar, varchar(max), nvarchar(max), text, or ntext.

Filter Expression
Predicate for the subset of table rows included in the statistics object. NULL = non-filtered statistics.

Unfiltered Rows
Total number of rows in the table before applying the filter expression. If Filter Expression is NULL, Unfiltered Rows is equal to Rows.

The following information describes the columns returned in the result set for the Density Vector.

All Density
Density is 1 / distinct values. Results display density for each prefix of columns in the statistics object, one row per density. A distinct value is a distinct list of the column values per row and per columns prefix. For example, if the statistics object contains key columns (A, B, C), the results report the density of the distinct lists of values in each of these column prefixes: (A), (A,B), and (A, B, C). Using the prefix (A, B, C), each of these lists is a distinct value list: (3, 5, 6), (4, 4, 6), (4, 5, 6), (4, 5, 7). Using the prefix (A, B) the same column values have these distinct value lists: (3, 5), (4, 4), and (4, 5).

Average Length
Average length, in bytes, to store a list of the column values for the column prefix. For example, if the values in the list (3, 5, 6) each require 4 bytes the length is 12 bytes.

Columns
Names of columns in the prefix for which All density and Average length are displayed.

The following information describes the columns returned in the result set for the Histogram.

RANGE_HI_KEY
Upper bound column value for a histogram step. The column value is also called a key value.

RANGE_ROWS
Estimated number of rows whose column value falls within a histogram step, excluding the upper bound.

EQ_ROWS
Estimated number of rows whose column value equals the upper bound of the histogram step.

DISTINCT_RANGE_ROWS
Estimated number of rows with a distinct column value within a histogram step, excluding the upper bound.

AVG_RANGE_ROWS
Average number of rows with duplicate column values within a histogram step, excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0).

Example:

Example 1:

In this example I would like to show how we can retrieve available statistics property of a table from system views and see the modification on the statistics:

SELECT

        OBJECT_NAME(stats.object_id) AS TableName,

        stats.name AS StatisticsName,

        stats_properties.last_updated,

        stats_properties.rows_sampled,

        stats_properties.rows,

        stats_properties.unfiltered_rows,

        stats_properties.steps,

        stats_properties.modification_counter

FROM sys.stats stats

OUTER APPLY sys.dm_db_stats_properties(stats.object_id, stats.stats_id) as   stats_properties

WHERE OBJECT_NAME(stats.object_id) = 'Orders'

ORDER BY stats.name;

OUTPUT:

TableName	StatisticsName	last_updated	rows_sampled	rows	unfiltered_rows	steps	modification_counter
Orders	_WA_Sys_00000002_2C3393D0	2016-10-05 15:50:45.4330000	2	2	2	1	163
Orders	_WA_Sys_00000003_2C3393D0	2016-10-05 15:52:23.5500000	2	2	2	1	160
Orders	_WA_Sys_00000004_2C3393D0	2016-10-05 15:52:23.5430000	2	2	2	1	114
Orders	_WA_Sys_00000005_2C3393D0	2016-10-10 09:49:00.9370000	3	3	3	1	61
Orders	PK__MobileUn__44F5ECB5B4A1E1C9	2016-11-03 10:12:29.9730000	4	4	4	3	0