Databases Reference
In-Depth Information
RANGE_HI_KEY RANGE_ROWS
EQ_ROWS
DISTINCT_RANGE_ROWS
AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
1
0
4
0
1
19
119
1
16
7.4375
54
190
12
31
6.129032
75
103
12
18
5.722222
126
288
11
47
6.12766
...
(Partial Results Shown)
The interpretation of this histogram is that between the step for RANGE_HI_KEY values of 75 and
126 there are 288 rows. Eleven rows are exactly equal to the range key of 126. There are 47 distinct
other values in the step, with about 6.12 rows per value. Sometimes after explaining this data, light
bulbs go off. It can't be overstated how important this set of data is to the optimizer to determine how
many pages are going to be needed and make guesses about how many rows will be filtered by the
predicates provided.
Include Actual Execution Plan Misconception
There is a common misconception that the Include Actual Execution Plan menu option in the SSMS
UI renders the cost estimates as actual cost numbers. That is incorrect. Cost estimates are still cost
estimates — not actuals (see the section ''Cost-Based Optimization''). What this option does is include
effects of current server operations. Typically, the cost estimates are the same regardless of whether you
select this menu option or the Display Estimated Execution Plan menu option. The only difference is that
selecting the former will result in actually executing the plan and providing accurate row count numbers.
Use sp_helpindex to Examine Indexes
This system-stored procedure is essential if you are writing T-SQL queries. It allows you to see the
indexes available on a table and the columns that make up that index. Figure 9-1 describes the indexes
on Sales.SalesOrderDetail when this query is run:
sp_helpindex 'Sales.SalesOrderDetail'
Figure 9-1
You should now have an understanding of the query optimizer's function, as well as the components
it draws upon to fulfill its mission. Armed with that knowledge, you'll move on to gathering facts
concerning those components. Finally, you'll use that information to formulate your own
optimized query.
Search WWH ::
Custom Search