Statistics - Pro SQL Server Internals - page 51

Chapter 3
Statistics
SQL Server Query Optimizer uses a cost-based model when choosing an execution plan for queries. It estimates the
cost of the different execution plans and chooses the one with the lowest cost. Remember, however, that SQL Server
does not search for the best execution plan available for the query, as evaluating all possible alternatives is time
consuming and expensive in terms of the CPU. The goal of Query Optimizer is finding a good enough execution plan,
fast enough .
Cardinality estimation (estimation of the number of rows that need to be processed on each step of the query
execution) is one of the most important factors in query optimization. This number affects the choice of join
strategies; amount of memory (memory grant) required for query execution, and quite a few other things.
The choice of indexes to use while accessing the data is among those factors. As you will remember, Key and RID
Lookup operations are expensive in terms of I/O, and SQL Server does not use nonclustered indexes when it estimates
that a large number of Key or RID Lookup operations will be required. SQL Server maintains the statistics on indexes
and, in some cases on columns, which help in performing such estimations.
Introduction to SQL Server Statistics
SQL Server statistics are the system objects that contain the information about data distribution in the index key
values and, sometimes, regular column values. Statistics can be created on any data type that supports comparison
operations, such as > , < , = , and so on.
Let's examine the IDX_BOOKS_ISBN index statistics from the dbo.Books table we created in Listing 2-15 in the
previous chapter. You can do this by using DBCC SHOW_STATISTICS ('dbo.Books',IDX_BOOKS_ISBN ) command.
The results are shown in Figure 3-1 .