Statistics - Pro SQL Server Internals - page 55

Figure 3-3. Execution plan of the query

There are two important properties that most execution plan operators have. Actual Number of Rows indicates
how many rows were processed during query execution. Estimated Number of Rows indicates the number of rows
SQL Server estimated during the Query Optimization stage. In our case, SQL Server estimates that there are 2,625 rows
with ISBN s starting with '114. If you look at the histogram shown in Figure 3-2 , you will see that step 10 stores the
information about the data distribution for the ISBN interval that includes the values that you are selecting. Even with
linear approximation, you can estimate the number of rows close to what SQL Server determined.

■ SQL Server stores additional information in the statistics for the string values called Trie Trees . this allows
SQL Server to perform better cardinality estimation in the case of string keys. this feature is not documented, and it is
outside of the scope of the topic.

Note

There are two very important things to remember about statistics.

1. The histogram stores information about data distribution for the leftmost statistics (index)

column only. There is information about the multi-column density of the key values in
statistics, but that is it. All other information in the histogram relates to data distribution
for the leftmost statistics column only.

2. SQL Server retains at most 200 steps in the histogram, regardless of the size of the table.

The intervals covered by each histogram step increase as the table grows. This leads to less
accurate statistics in the case of large tables.