Share via

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

Comments

  • Anonymous
    August 31, 2008
    The comment has been removed

  • Anonymous
    August 31, 2008
    Howdy, The optimizer is cost-based---it tries to pick the cheapest plan---but it doesn't always make the right choice, primarily because it can't perfectly estimate the costs.  There are things we can do---some easy, some not---to improve this, but I suspect there will always be some cases where hinting is required.  (Note, of course, that hints are not spatial-specific things---even without spatial we sometimes need them.) But, no: you shouldn't always hint the spatial index.  For example, if there is some other, more selective predicate, then the system may be better off answering that first, and not using the spatial index.  Again, ideally the optimizer would always make the right choice for you, but things are rarely ideal. Cheers, -Isaac

  • Anonymous
    December 11, 2008
    The comment has been removed

  • Anonymous
    December 11, 2008
    **** UPDATE *** I got my query to run by changing an option for the database - I don't think it made a difference which one (but for completeness: I changed the parameterization back and forth between the options simple and forced). And then it just worked - did I find a bug?

  • Anonymous
    December 11, 2008
    Hi DaL, I'm a little confused.  It sounds like setting parameterization to one value works, while setting it the other way does not.  Which setting is which? The error indicates that the query optimizer could not find a plan that satisfied the hint you provided.  That seems a little strange in your case... Cheers, -Isaac

  • Anonymous
    December 11, 2008
    The comment has been removed

  • Anonymous
    December 18, 2008
    Just an FYI for those having performance issues with spatial indexes.  The Query Optimizer (QO) team has looked into the issue of poor costing decisions with spatial indexes, and a fix is being included in the March cumulative update (likely called Cumulative Update 3 for SQL Server 2008).  My guess, and hope, is that this will obviate the need to hint spatial indexes with most datasets. Reference URL: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=367772

  • Anonymous
    January 22, 2009
    The comment has been removed

  • Anonymous
    March 25, 2009
    The comment has been removed

  • Anonymous
    March 31, 2009
    We just installed Cumulative Update 4 and it looks like the spatial indexes are still not being used in some instances.  Does anyone have any more info? Thank you.