Last post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL.
N = Number of rows in the table
O = Number of rows output
P = Number of rows selected by primary filter (by the index)
S = Number of rows selected by internal filter (by the index optimizations)
Then,
P-S = Number_Of_Times_Secondary_Filter_Is_Called (number of times they ran the expensive operation)
(N-P)/N = Percentage_Of_Rows_NotSelected_By_Primary_Filter
S/P = Percentage_Of_Primary_Filter_Rows_Selected_By_internal_Filter
S/O = Internal_Filter_Efficiency %
O/P = Primary_Filter_Efficiency %
<Percentage_Of_Rows_NotSelected_By_Primary_Filter>9.942714746408677e+001
<Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter>9.095871716137836e+001
<Internal_Filter_Efficiency>9.593378913278158e+001
<Primary_Filter_Efficiency>9.481405663596043e+001
So how efficient is the index? Let's change this info into statements, using the values from the previous blog entry:
N = 511650, O = 2779, P = 2931, S = 2666
<Number_Of_Times_Secondary_Filter_Is_Called>265</Number_Of_Times_Secondary_Filter_Is_Called>
<Percentage_Of_Rows_NotSelected_By_Primary_Filter>9.942714746408677e+001
<Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter>9.095871716137836e+001
<Internal_Filter_Efficiency>9.593378913278158e+001
<Primary_Filter_Efficiency>9.481405663596043e+001
1. They had to run STIntersects 265 times, rather than 511650 times if they didn't use the index.
2. 99.4% of the rows were eliminated by using the index
3. The Internal_Filter (internal optimizations) eliminated 90% of the times they would have had to run Intersects, even if the used the index (primary_filter).
4. The Internal_Filter was able to "deduce" 95.9% of the rows in the answer.
5. The Primary_Filter was able to find 94.8% of the rows in the answer.
Pretty good index, huh? And pretty good internal filter. Probably is going to be used.
Let's look at one more, but this time from using a geoemtry index. The index is over in geometry column that contains only points. The query sample is a polygon.
<Base_Table_Rows>11267
<Number_Of_Rows_Selected_By_Primary_Filter>265
<Number_Of_Rows_Selected_By_Internal_Filter>149
<Number_Of_Times_Secondary_Filter_Is_Called>116
<Number_Of_Rows_Output>413
<Percentage_Of_Rows_NotSelected_By_Primary_Filter>9.764799857992367e+001
<Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter>5.622641509433962e+001
<Internal_Filter_Efficiency>3.607748184019371e+001
<Primary_Filter_Efficiency>1.558490566037736e+002
Note that in this case, the internal filter only elimates 56.2% of the rows that the primary filter finds. It only finds 36% of the output rows. But note that in this case, primary filter efficiency = 155%. How can that be? Well, this is geometry and the primary filter didn't find all the rows in the answer. There must be some outside the bounding box. Remember that Primary_Filter_Efficiency is defined as Output_Rows / Primary_Filter rows.
More coming…