The optimizer uses a statistic called the total density to estimate the number of rows in a joined table that match a particular value during the join.
See “Density values and joins” for more information.
The query optimizer uses the total density to estimate the number of rows that will be returned for each scan of the inner table of a join. For example, if the optimizer is considering a nested-loop join with a 250,000-row table, and the table has a density of .0001, the optimizer estimates that an average of 25 rows from the inner table match for each row that qualifies in the outer table.
optdiag reports the total density for each column for which statistics have been created. You can also see the total density used for joins in dbcc traceon(302) output.
