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

Statistics
SQL Server creates and maintains statistics to help the Query Optimizer with
cardinality estimation . A cardinality estimate is the estimated number of records
that will be returned by filtering, JOIN predicates or GROUP BY operations. Selectivity is
a concept similar to cardinality estimation, which can be described as the percentage of
rows from an input that satisfy a predicate. A highly selective predicate returns a small
number of rows. Rather than say any more on the subject here, we'll dive into more detail
about these concepts later in this chapter.
Creating and updating statistics
To get started, let's take a look at the various ways statistics can be created and updated.
Statistics are created in several ways: automatically by the Query Optimizer (if the default
option to automatically create statistics, AUTO_CREATE_STATISTICS , is on); when an
index is created; or when they are explicitly created, for example, by using the CREATE
STATISTICS statement. Statistics can be created on one or more columns, and both
the index and explicit creation methods support single- and multi-column statistics.
However, the statistics which are automatically generated by the Query Optimizer are
always single-column statistics. As I've already mentioned briefly, the most important
components of statistics objects are the histogram, the density information, and the
string statistics. Both histograms and string statistics are created only for the first
column of a statistics object, the latter only if the column is of a string data type.
Density information (which I'll discuss in plenty of detail later in this chapter) is
calculated for each set of columns forming a prefix in the statistics object. Filtered
statistics , on the other hand, are not created automatically by the Query Optimizer,
but only when a filtered index is created, or by issuing a CREATE STATISTICS statement
with a WHERE clause. Both filtered indexes and statistics are a new feature introduced in
SQL Server 2008, which we will touch upon later.