Problem: Reduced Spatial Query Performance on Multiprocessor Servers Running SQL Server 20

Description

The Microsoft SQL Server 2008 R2 query optimizer fails to choose the spatial index despite the index's beneficial effect on query performance.

Note:
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:

1. Perform a SQL trace of the query using the SQL Server profiler, and verify that the spatial index is not being used.

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.

Cause

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:

• SQL Server 2008 R2 Cumulative Update package 9

• SQL Server 2008 R2 SP1 Cumulative Update package 3

• SQL Server 2008 R2 SP2

After applying the fix, enable SQL Server traceflag 4199 globally. Use the dbcc command:

Code:
DBCC TRACEON(4199, -1)

Article ID:000011249

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Reduced spatial query performance on multiprocessor servers running SQL Server 2008

Description

Cause

Solution or Workaround

Discover more on this topic