Is my spatial index being used?

  • Article

I've seen a problem commonly phrased something like:

I have X million records in a spatial table, and have created a spatial index, yet a simple select query takes me Y hours to complete.

Ouch!

If the problem is this extreme, the solution is usually pretty consistent: the index isn't being used.  You can tell pretty easily by looking at the query plan.  For example, take a query like this:

 SELECT *
FROM T
WHERE g.STIntersects(@g) = 1

If the index isn't being used, you'd expect a query plan that looks like this:

image

If the index is being used, then you'll see something completely different:

image

You can generally tell just from the complexity of the plan the index has been picked up, but if you look closely at the highlighted portion you can be sure:

image

Note the "(Spatial)" annotation on the index seek.

Okay, so what do we do if the index isn't being used?  Well, we can hint it.  If our spatial index were named "g_idx", we could rewrite the query above as:

 SELECT *
FROM T WITH(INDEX(g_idx))
WHERE g.STIntersects(@g) = 1

Making sure the index is being used isn't the end of spatial query tuning, but it can make the world of difference.  Quoting for a recent thread in the spatial forum:

The query went from over an hour to less than 2 seconds. I don't believe that in my 25+ year career that I can lay claim to a 180,000+% increase in performance.

That's pretty sweet.  Go forth and index.

Cheers,

-Isaac