Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 116

Understanding Statistics
Statistics provide critical information needed by SQL Server when performing query optimization.
SQL Server statistics contain details about the data, and what the data looks like in each table
within the database.
The query optimization process uses statistics to determine how many rows a query might need
to access for a given query plan. It uses this information to develop its cost estimate for each step
in the plan. If statistics are missing or invalid, the Query Optimizer can arrive at an incorrect cost
for a step, and thus choose what ends up being a bad plan.
You can examine the statistics for any table in the database by using SQL Server Management Studio,
expanding the Object Explorer to show the table you are interested in. For example, Figure 5-2 shows
the person.Address table in the AdventureWorks2012 database. Expand the table node, under
which you will see a Statistics node. Expand this, and you will see a statistic listed for each index that
has been created, and in many cases you will see additional statistics listed, often with cryptic names
starting with _WA. These are statistics that SQL Server has created automatically for you, based
upon queries that have been run against the database. SQL Server creates these statistics when the
AUTO_CREATE_STATISTICS option is set to ON.
FIGURE 5-2
To see the actual statistic values, you can select an individual statistic, right-click it, and select the
Properties option from the menu options. This will show you the Properties dialog for the statis-
tic you selected. The i rst page, General, displays the columns in the statistic and when it was last
updated. The Details page contains the real guts of the statistic, and shows the data distribution.
For the PK _ Address - AddressID statistic on the person.Address table in AdventureWorks2012,
you should see something similar to Figure 5-3.