sp_help_spatial_geography_index Primary_Filter_Efficiency=0

Question

Hi,

I am currently testing SQL's spatial features to see, if it can improve some features in our application. Before I forget it, here the configuration:

-SQL 2008R2 -Standard with SP1

I have a table "Positions" that contains points stored in a geography data type. I decided to go for Geography, as the points captured are sent from GPS devices and they are stored as Latitude and Longitude. The table contains about 200000 points. The points are located in an area of 50x50 kms.

The query we are running against this table needs to retrieve just 1st nearest neighbor to a given coordinate. The query took around 1s to complete and I could improve performance by rewriting this query as shown in this link: http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx

As well, I set the spatial Index Grid Levels from MMMM to HHHH.

Now, the query is down to 100ms, sometimes even less, which is ok for our application. So, where is the problem?

When I was looking into the spatial index and how to tune it, I used the sp_help_spatial_geography_index SP to analyse index usage. Whatever setting I choose for my index, the Primary_Filter_Efficiency = 0!

I do have this value: Number_Of_Rows_Selected_By_Primary_Filter 193

This would mean to me, that the Primary Filter was able to get 193 rows from the 200k rows in the table, so the efficency would be something around 99%.

For me that would mean that the Spatial Index itself is more or less useless on this table. But, when I remove it and the table is scanned on the clustered index, my query is much slower than it is with the Spatial Index.

I add my query + full output of sp_help_spatial_geography_index below.

Thanks,

Bernd

---

DECLARE  @Location GEOGRAPHY 
SET @Location = GEOGRAPHY::STPointFromText('POINT (6.17492333333333 49.868855 )',4326)

execute sp_help_spatial_geography_index positions, idxsp_geopoint, 1, @Location
go

Result:

Base_Table_Rows 403350
Bounding_Box_xmin -180
Bounding_Box_ymin -90
Bounding_Box_xmax 180
Bounding_Box_ymax 90
Grid_Size_Level_1 256
Grid_Size_Level_2 256
Grid_Size_Level_3 256
Grid_Size_Level_4 256
Cells_Per_Object 16
Total_Primary_Index_Rows 202525
Total_Primary_Index_Pages 797
Average_Number_Of_Index_Rows_Per_Base_Row 0
Total_Number_Of_ObjectCells_In_Level0_For_QuerySample 1
Total_Number_Of_ObjectCells_In_Level4_For_QuerySample 1
Total_Number_Of_ObjectCells_In_Level4_In_Index 202525
Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index 202521
Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample 1
Total_Number_Of_Border_ObjectCells_In_Level4_For_QuerySample 1
Total_Number_Of_Border_ObjectCells_In_Level4_In_Index 4
Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.0
Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.0
Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.0
Average_Cells_Per_Object_Normalized_To_Leaf_Grid 0.0
Average_Objects_PerLeaf_GridCell 0.0
Number_Of_SRIDs_Found 1
Width_Of_Cell_In_Level1 1.40625
Width_Of_Cell_In_Level2 0.0054931640625
Width_Of_Cell_In_Level3 2.14576721191406E-05
Width_Of_Cell_In_Level4 8.38190317153931E-08
Height_Of_Cell_In_Level1 0.703125
Height_Of_Cell_In_Level2 0.00274658203125
Height_Of_Cell_In_Level3 1.07288360595703E-05
Height_Of_Cell_In_Level4 4.19095158576965E-08
Area_Of_Cell_In_Level1 253.125
Area_Of_Cell_In_Level2 0.98876953125
Area_Of_Cell_In_Level3 0.00386238098144531
Area_Of_Cell_In_Level4 1.50874257087708E-05
CellArea_To_BoundingBoxArea_Percentage_In_Level1 0.390625
CellArea_To_BoundingBoxArea_Percentage_In_Level2 0.00152587890625
CellArea_To_BoundingBoxArea_Percentage_In_Level3 5.96046447753906E-06
CellArea_To_BoundingBoxArea_Percentage_In_Level4 2.3283064365387E-08
Number_Of_Rows_Selected_By_Primary_Filter 193
Number_Of_Rows_Selected_By_Internal_Filter 0
Number_Of_Times_Secondary_Filter_Is_Called 193
Number_Of_Rows_Output 0
Percentage_Of_Rows_NotSelected_By_Primary_Filter 99.9521507375728
Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter 0
Internal_Filter_Efficiency 0.0
Primary_Filter_Efficiency 0

---

The query in the SP using the index:

declare @Point geography;

set @Point = geography::STPointFromText('POINT(' + @Coords + ')', 4326);

SELECT top 1 
     some rows here ...
  FROM [GeoCache].[dbo].[Positions]  
  WHERE Geopoint.Filter(@Point.STBuffer(@radius))=1
 ORDER BY [Geopoint].STDistance(@Point)
  

Answer 1

Primary filter efficiency is a measure of how accurate SQL Server's "first guess" was, based on the primary filter -  it is calculated as the number of rows in the output as a %age of the number of rows selected by the primary filter. The more false positives returned by the primary filter, the lower the efficiency.

If you look at the output above, your primary filter is selecting 193 rows, but your output is 0 rows. So, 0 as a %age of 193 is 0.

So, the next question is why are you getting 0 output rows? To answer that question, you need to look at the supplied query_sample. Your query sample, @Location, is a single point. So, the only possible output row you're going to get from sp_help_spatial_geography_index is if that @Location point is exactly equal to one of the rows in the positions table and, it looks like it isn't.

Instead of supplying a Point, try supplying a query_sample that is a Polygon of a typical size for the sorts of queries you run against this table. For example, if your nearest-neighbours are typically found within 100m, try supplying a circular Polygon of radius 100m instead. You can do this by changing your @Location to the following:

SET @Location = GEOGRAPHY::STPointFromText('POINT (6.17492333333333 49.868855 )',4326).STBuffer(100)

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

Thanks for your answer. Looking at your explanation makes it fully clear for me why there is a 0 for Primary Efficency. I ran the query again and got the results that I post below.

If I have a look at our scenario, we keep GPS positions of vehicles moving in this table. We have 200.000 records in that table today, but it will certainly grow to some million records over the next couple of monthes. 

Here's the query again for fetching the data:

declare @Point geography;

set @Point = geography::STPointFromText('POINT(' + @Coords + ')', 4326);

SELECT top 1 
     some rows here ...
  FROM [GeoCache].[dbo].[Positions]  
  WHERE Geopoint.Filter(@Point.STBuffer(@radius))=1
 ORDER BY [Geopoint].STDistance(@Point)

---

The Geopoint.Filter will do the filtering for the Primary Filter and avoid calculating STDistance for every point in our table, what would be equal to a table scan. is this correct?

If yes, then today the Geopoint.Filter today returns 200 messages, but in some monthes it might return 2000 or 20000 messages or even more. If my query starts slowing down in some monthes as it needs to call STDistance for 20000 times or more, would there be anymore option to tune it?

----

Base_Table_Rows 409658
Bounding_Box_xmin -180
Bounding_Box_ymin -90
Bounding_Box_xmax 180
Bounding_Box_ymax 90
Grid_Size_Level_1 256
Grid_Size_Level_2 256
Grid_Size_Level_3 256
Grid_Size_Level_4 256
Cells_Per_Object 16
Total_Primary_Index_Rows 205691
Total_Primary_Index_Pages 880
Average_Number_Of_Index_Rows_Per_Base_Row 0
Total_Number_Of_ObjectCells_In_Level0_For_QuerySample 1
Total_Number_Of_ObjectCells_In_Level4_For_QuerySample 2
Total_Number_Of_ObjectCells_In_Level4_In_Index 205691
Total_Number_Of_Intersecting_ObjectCells_In_Level4_For_QuerySample 2
Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index 205687
Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample 1
Total_Number_Of_Border_ObjectCells_In_Level4_In_Index 4
Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.0
Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.0
Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.0
Average_Cells_Per_Object_Normalized_To_Leaf_Grid 0.0
Average_Objects_PerLeaf_GridCell 0.0
Number_Of_SRIDs_Found 1
Width_Of_Cell_In_Level1 1.40625
Width_Of_Cell_In_Level2 0.0054931640625
Width_Of_Cell_In_Level3 2.14576721191406E-05
Width_Of_Cell_In_Level4 8.38190317153931E-08
Height_Of_Cell_In_Level1 0.703125
Height_Of_Cell_In_Level2 0.00274658203125
Height_Of_Cell_In_Level3 1.07288360595703E-05
Height_Of_Cell_In_Level4 4.19095158576965E-08
Area_Of_Cell_In_Level1 253.125
Area_Of_Cell_In_Level2 0.98876953125
Area_Of_Cell_In_Level3 0.00386238098144531
Area_Of_Cell_In_Level4 1.50874257087708E-05
CellArea_To_BoundingBoxArea_Percentage_In_Level1 0.390625
CellArea_To_BoundingBoxArea_Percentage_In_Level2 0.00152587890625
CellArea_To_BoundingBoxArea_Percentage_In_Level3 5.96046447753906E-06
CellArea_To_BoundingBoxArea_Percentage_In_Level4 2.3283064365387E-08
Number_Of_Rows_Selected_By_Primary_Filter 214
Number_Of_Rows_Selected_By_Internal_Filter 0
Number_Of_Times_Secondary_Filter_Is_Called 214
Number_Of_Rows_Output 90
Percentage_Of_Rows_NotSelected_By_Primary_Filter 99.9477613033311
Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter 0
Internal_Filter_Efficiency 0
Primary_Filter_Efficiency 42.0560747663551