Summary
Consider the following Scenario.
-
You have an instance of SQL Server 2012 on a computer that has more than one processor and you run a parallel query against a table that has a large amount of spatial data.
-
You may notice that the query optimizer does not use the spatial index
We had a problem with the Query Optimizer in SQL Server 2012 , version 11.0.2100.60, whereby it was not choosing to use the only Spatial Index on a table with a single GEOMETRY column and is instead performing a table scan, which results in incorrect performance.
We had a similar issue fixed in SQL Server 2008 R2 http://support.microsoft.com/kb/2570501
We can use the workaround mentioned in above KB:
To work around this issue, use one of the following methods:
-
Use the spatial index hint option in the query.
-
Disable the parallel execution plan option for the query.
* We could also use the TF 4199 (with caution*) in the query. In my case, reducing the MAXDOP to less than 8 helped too.
More Information
Defect 630366: Spatial index not used on multiproc machine unless MAXDOP set downward
SQL BU Defect Tracking 20179508 - Spatial index not used on multiproc machine unless MAXDOP set downward
