CODEWITHSUNDEEP
sqlsql-server-2008spatial-index
tpcolson
tpcolson

Reputation: 681

Spatial index hints don't work in SQL Server 2008?

enter image description hereUsing the following

SELECT * 
FROM dbo.GRSM_WETLAND_POLY    
CROSS APPLY (SELECT TOP 1 Name, shape     
             FROM GRSM.dbo.GRSM_Trails --WITH(index(S319_idx))    
             WHERE GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) IS NOT NULL     
            ORDER BY GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) ASC) fnc

runs very slow on 134 rows (56 seconds), however, with the index hint uncommented, it returns

Msg 8635, Level 16, State 4, Line 3
The query processor could not produce a query plan for a query with a spatial index hint. Reason: Spatial indexes do not support the comparator supplied in the predicate. Try removing the index hints or removing SET FORCEPLAN.

Execution plan shows the filter cost at 98%, it's querying against 1400 rows in the other table, so the total cost is 134 * 1400 individual seeks, which is where the delay is. On their own, the spatial indexes in each table perform great, with no fragmentation, 99% page fulness, and use medium for all 4 grid levels with 16 cells per object. Changing the spatial index properties on either table had no effect on performance.

Documentation suggests that spatial index hints can only be used in queries in SQL Server 2012, but surely there's a work around for this?

Upvotes: 3

Views: 931

Answers (1)

Diego
Diego

Reputation: 36176

Main question would be why are you forcing the the hint? If SQL Server didn't choose the index on the plan it generated, forcing another plan will almost always result in decreased performance.

What you should do is analyse each node of the resulting execution plan to see where is the bottle neck that is taking so long. If you post a print screen maybe we can help

Upvotes: -1

Related Questions