SQL 2012 - Query Optimizer may not choose to use a Spatial Index

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

Propiedades

Id. de artículo: 2757097 - Última revisión: 16 nov. 2012 - Revisión: 1

Comentarios