none
Sql server:Spatial Index not use by query a view

    Question

  • Hi Guys,

    I was working with SQL Server 2008 Spatial Data, but I got a weird problem that the Spatial Index created for the tables doesn’t work when I query them with the view which is created based on this table. Following is the scripts I was using:

    declare @Point geometry = geometry::STGeomFromText('POINT(937767.89433333278 -230404.864666667)', 102003)

    select * from vw_StateForAddToMap 

    where @Point.STWithin(shape)=1

    go

    I googled a lot and found an workaround at 

    http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/

     , but seems like this workaround just works when the queried geometry is point type, for polygons, like the script as following:

    declare @Geometry2 geometry = geometry::STGeomFromText(

    'POLYGON((-2079214.0399 1392052.275,-2079214.0399 -1156112.025,

    1981332.1069 -1156112.025,1981332.1069 1392052.275,

    -2079214.0399 1392052.275))', 102003) go

    The spatial still doesn’t work. Anybody knows how to deal with this situation? Seems like the Microsoft doesn’t give a good instructions about this.

    Any response will be appreciated.

    Wednesday, November 06, 2013 9:24 AM

Answers

  • What service pack on SQL Server 2008 are you on? One of the service packs (I believe it's SP1, but it's always a good idea to be on the latest SP) had changes to the query optimizer to allow it to use spatial index more often without hinting. If this doesn't work by itself, try the hint "option (querytraceon 4199)" to ensure you're getting the latest optimizer changes. If the SP does not cause the spatial index to be used by the hint does consider added the 4199 trace flag to your startup, if you're doing a lot of spatial.

    Also, you need to allow the optimizer to sniff the value of your parameter before it creates a plan. In your script above, SQL Server creates the plan before you set the parameter value (at the beginning of the batch) and therefore has no knowledge of the parameter value when the plan is created. The easiest ways to work around this is to either use a stored procedure for your query that takes a geometry type as a parameter or wrap your query in sp_executesql and pass in a geometry parameter to that. Query plans for stored procedures (and sp_executesql really is a stored procedure itself) are created on entry to the procedure, and, doing it that way, SQL Server can detect your parameter value and use it to "decide to" use the index in the plan.

    Hope this helps, Bob

    Wednesday, November 06, 2013 4:16 PM

All replies

  • What service pack on SQL Server 2008 are you on? One of the service packs (I believe it's SP1, but it's always a good idea to be on the latest SP) had changes to the query optimizer to allow it to use spatial index more often without hinting. If this doesn't work by itself, try the hint "option (querytraceon 4199)" to ensure you're getting the latest optimizer changes. If the SP does not cause the spatial index to be used by the hint does consider added the 4199 trace flag to your startup, if you're doing a lot of spatial.

    Also, you need to allow the optimizer to sniff the value of your parameter before it creates a plan. In your script above, SQL Server creates the plan before you set the parameter value (at the beginning of the batch) and therefore has no knowledge of the parameter value when the plan is created. The easiest ways to work around this is to either use a stored procedure for your query that takes a geometry type as a parameter or wrap your query in sp_executesql and pass in a geometry parameter to that. Query plans for stored procedures (and sp_executesql really is a stored procedure itself) are created on entry to the procedure, and, doing it that way, SQL Server can detect your parameter value and use it to "decide to" use the index in the plan.

    Hope this helps, Bob

    Wednesday, November 06, 2013 4:16 PM
  • Thanks a lot, It really helpful.
    Tuesday, November 12, 2013 5:41 AM