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

Work anywhere from any device with Microsoft 365

Upgrade to Microsoft 365 to work anywhere with the latest features and updates.

Upgrade now

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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×