Problem: Reduced spatial query performance on multiprocessor servers running SQL Server 2008
The Microsoft SQL Server 2008 R2 query optimizer fails to choose the spatial index despite the index's beneficial effect on query performance.
Usually, this issue occurs on a multiprocessor system that is lightly loaded. The problem is less likely to occur on a heavily loaded system. It has not been seen on a system with only a few processor cores.
If users are experiencing unexplained, poor spatial query performance, it is a good practice to verify that the spatial index is being used with the query. Users can determine if they are experiencing this specific problem by doing the following:
2. Temporarily set the server's MAXDOP (maximum degree of parallelism) to 1.
3. Perform a SQL trace of the query using the SQL Server profiler, and verify that the spatial index is being used.
4. Set the MAXDOP to its original setting.
The problem is a bug in Microsoft SQL Server's query optimizer: Microsoft Support - FIX: Slow performance when running a parallel query in SQL Server 2008 R2
Solution or Workaround
This fix is available in the following downloads:
After applying the fix, enable SQL Server traceflag 4199 globally. Use the dbcc command:
DBCC TRACEON(4199, -1)