Database Reference
In-Depth Information
--Retrieve 10000 rows;
SELECT *
FROM dbo.Test1
WHERE C1 = 2;
Figure 12-18 shows execution plans of these queries.
Figure 12-18. Execution plans of small and large result set queries
From the statistics, the optimizer can find the number of rows needed for the preceding two queries.
Understanding that there is only one row to be retrieved for the first query, the optimizer chose an Index Seek
operation, followed by the necessary RID Lookup to retrieve the data not stored with the clustered index. For the
second query, the optimizer knows that a large number of rows (10,000 rows) will be affected and therefore avoided
the index to attempt to improve performance. (Chapter 6 explains indexing strategies in detail.)
Besides the information contained in the histogram, the header has other useful information including
the following:
The time statistics were last updated
The number of rows in the table
The average index key length
The number of rows sampled for the histogram
Densities for combinations of columns
Information on the time of the last update can help you decide whether you should manually update the
statistics. The average key length represents the average size of the data in the index key columns. It helps you
understand the width of the index key, which is an important measure in determining the effectiveness of the index.
As explained in Chapter 6, a wide index might be costly to maintain and requires more disk space and memory pages
but, as explained in the next section, can make an index extremely selective.
 
Search WWH ::




Custom Search