Spatial index slowing down query

Question

Background

I have a table that contains POLYGONS/MULTIPOLYGONS which represent customer territories:

• The table contains roughly 8,000 rows
• Approximately 90% of the polygons are circles
• The remainder of the polygons represent one or more states, provinces, or other geographic regions. The raw polygon data for these shapes was imported from US census data.
• The table has a spatial index and a clustered index on the primary key. No changes to the default SQL Server 2008 R2 settings were made. 16 cells per object, all levels medium.

Here's a simplified query that will reproduce the issue that I'm experiencing:

DECLARE @point GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-76.992188 39.639538)', 4326)

SELECT terr_offc_id
FROM tbl_office_territories
WHERE terr_territory.STIntersects(@point) = 1

What seems like a simple, straightforward query takes 12 or 13 seconds to execute, and has what seems like a very complex execution plan for such a simple query.

In my research, several sources have suggested adding an index hint to the query, to ensure that the query optimizer is properly using the spatial index. Adding WITH(INDEX(idx_terr_territory)) has no effect, and it's clear from the execution plan that it is referencing my index regardless of the hint.

Reducing polygons

It seemed possible that the territory polygons imported from the US Census data are unnecessarily complex, so I created a second column, and tested reduced polygons (w/ Reduce() method) with varying degrees of tolerance. Running the same query as above against the new column produced the following results:

• No reduction: 12649ms
• Reduced by 10: 7194ms
• Reduced by 20: 6077ms
• Reduced by 30: 4793ms
• Reduced by 40: 4397ms
• Reduced by 50: 4290ms

Clearly headed in the right direction, but dropping precision seems like an inelegant solution. Isn't this what indexes are supposed to be for? And the execution plan still seems strangly complex for such a basic query.

Spatial Index

Out of curiosity, I removed the spatial index, and was stunned by the results:

1. Queries were faster WITHOUT an index (sub 3 sec w/ no reduction, sub 1 sec with reduction tolerance >= 30)
2. The execution plan looked far, far simpler:

My questions

1. Why is my spatial index slowing things down?
2. Is reducing my polygon complexity really necessary in order to speed up my query? Dropping precision could cause problems down the road, and doesn't seem like it will scale very well.

Other Notes

• SQL Server 2008 R2 Service Pack 1 has been applied
• Further research suggested running the query inside a stored procedure. Tried this and nothing appeared to change.

Answer 1

My first thoughts are to check the bounding coordinates of the index; see if they cover the entirety of your geometries. Second, spatial indexes left at the default 16MMMM, in my experience, perform very poorly. I'm not sure why that is the default. I have written something about the spatial index tuning on this answer.

First make sure the index covers all of the geometries. Then try reducing cells per object to 8. If neither of those two things offer any improvement, it might be worth your time to run the spatial index tuning proc in the answer I linked above.

Final thought is that state boundaries have so many vertices and having many state boundary polygons that you are testing for intersection with, it very well could take that long without reducing them.

Oh, and since it has been two years, starting in SQL Server 2012, there is now a GEOMETRY_AUTO_GRID tessellation that does the index tuning for you and does a great job most of the time.