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

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.