How to ensure your spatial index is being used

I've answered quite a few questions lately about how to make sure that your spatial index is being used. Use of a spatial index with a large or complex spatial dataset can mean the difference between a query that takes minutes to execute and subsecond execution. You also want to use an index hint as a last resort, the spatial index should not need a hint. Here's a few things to try, in order of importance.

1. Apply SQL Server 2008 SP1!!! I can't stress this one too much. There was a change to query costing that affected spatial index use.
2. Make the query as uncomplicated as possible. Don't try to combine STIntersects with a call to STBuffer, MakeValid or other nested spatial method calls or subqueries. Use multiple statements if needed.
3. If you're running the code in SSMS, use sp_executesql around the spatial query (or use your own stored procedure with the spatial value as a parameter) to ensure the query coster "knows" the parameter value at the time its creating the query plan, that is, at beginning of the batch or on entry to a stored procedure or sp_executesql.Here's an example:

— does not use the spatial index without a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
select, a.shape.STAsText()
from zipcodes a
where a.shape.STIntersects(@latlonPoint)=1

— this does use the spatial index without using a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
exec sp_executesql
N'select, a.shape.STAsText()
from zipcodes a
where a.shape.STIntersects(@latlonPoint)=1', N'@latlonPoint geometry', @latlonPoint

— so does this
create procedure find_zipcode (@g geometry)
select, a.shape.STAsText()
from zipcodes a
where a.shape.STIntersects(@latlonPoint)=1

declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
execute find_zipcode(@latlonPoint)

4. If you're using client code, make sure you use a parameterized query and that it passes the value in the SqlParametersCollection. ADO.NET will change this into a call that uses sp_executesql.

5. Don't depend of passing in a string literal to give the query coster the right info, because the code "creates" the point inline, after the query plan has been created.:

— use a parameterized query with sp_executesql or stored procedure instead
select, a.shape.STAsText()
from zipcodes a
where a.shape.STIntersects('POINT (45.518066 -122.767464)')=1

6. Check the query plan (actual or estimated plan will work) to ensure the index is being used. The Spatial Index step in the query plan is easy to locate.

7. Make sure you have an appropriate spatial index with your data AND for your query sample. You can check this with the spatial index diagnostic stored procedures. To see how to use these procedures, start with the blog series here.

8. Use a hint as a last resort and see if it makes a difference in the query speed. If using a spatial index hint causes an error "Could not create plan", it may mean that your query is too complex. See step 2.

I hope this helps your query response be as fast as possible.

2 thoughts on “How to ensure your spatial index is being used

  1. Is it possible to pass through a geometry data type from ASP.NET (through ADO.NET)?

    I’m dealing with ZIP codes and I’ve been passing in my ZIP as a varchar() and then calculating the Centroid (@centroid – geometry data type) before passing this into my query i.e. … WHERE geom.STDistance(@centroid) < @distance. I could look up the ZIP centroid ahead of time and pass through as a geometry data type if this is possible and will likely offer optimization opportunities.


  2. Hi Stephen,

    If I understand what I think you’re saying then "yes". If you precalculate the centroid in the ASP.NET layer, that’s just a point. Then pass it as a Geometry (or Geography) point into you’re query.

    You could also precalculate the STCentroid of every zipcode and store either of both the zipcode polygon and the zipcode centroid.

    If I’m not understanding what you’re asking then write back.


Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.