Hugo Kornelis (11/3/2013)
* Are you running on SQL Server 2008? If so, check SELECT @@VERSION to see what, if any, service pack you have installed. If you are on RTM, that might be the cause - there were some huge imprevements to the costing of spatial queries that solved a lot of issues with spatial indexes not being used in SP1. See http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/[/url].
Definitely make sure you are not running 2008 RTM, otherwise hinting is really the only sure way to go and that can also cause issues.
Ville-Pekka Vahteala
Second guess is that bounding box of spatial index is incorrect.
Agreed, when creating the index make your bounding box as tight as possible.
Are you using the same SRIDs between you Geometries? In your query you build the point using SRID 0. Is the SRID of the geometries in GEO2IP also 0?
Assuming you having polygons in GEO2IP, you will want to use STContains or STIntersects.
With the LEFT OUTER JOIN on the query, the spatial comparison is rather redundant. Try an INNER JOIN.
If you could post the create script for your spatial index and the DDL for the GEO2IP table that would be helpful. Approximately how many records does that have?
Are the coordinates Lat/Lon's or projected?