Statistics and Cost Estimation - Inside the SQL Server Query Optimizer - page 75

DBCC SHOW_STATISTICS ( 'Sales.SalesOrderDetail' , IX_SalesOrderDetail_ProductID )
Listing 3-6.
This will display the following density vector, which shows the densities for the
ProductID column, as well as a combination of columns ProductID , SalesOrderID ,
and then ProductID , SalesOrderID and SalesOrderDetailID .
All density Average Length Columns
------------ -------------- -------------------------------------------
0.003759399 4 ProductID
8.242868E-06 8 ProductID, SalesOrderID
8.242868E-06 12 ProductID, SalesOrderID, SalesOrderDetailID
Listing 3-7.
Density, which is defined as 1 / "number of distinct values," is listed in the All density
field, and it is calculated for each set of columns, forming a prefix for the columns in
the statistics object. For example, the statistics object in Listing 3-7 was created for the
columns ProductID , SalesOrderID and SalesOrderDetailID , and so the density
vector will show three different density values: one for ProductID , another one for
ProductID and SalesOrderID combined, and a third one for the combination of
ProductID , SalesOrderID , and SalesOrderDetailID . The names of the analyzed
columns will be displayed in the Columns field, and the Average Length column will
show the average number of bytes for each density value. In the previous example, all
the columns were defined using the int data type, so the average lengths for each of the
density values will be 4, 8 and 12 bytes. Now that we've seen how density information is
structured, let's take a look at how it's used.
Density information can be used to improve the Query Optimizer's estimates for GROUP
BY operations, and on equality predicates where a value is unknown, as in the case of
local variables. To see how this is done, let's consider, for example, the number of distinct
values for ProductID on the Sales.SalesOrderDetail table: 266. Density can be