FAQ: Why Do ArcGIS Applications Not Hint the Use of SQL Server Spatial Indexes?

Answer

Microsoft SQL Server 2008 introduced the spatial data types 'geometry' and 'geography'. These data types each include a spatial index. With the first release of SQL Server 2008, the query optimizer did not always select an efficient execution plan for queries with spatial predicates, resulting in slower query performance. This issue, and several others have been addressed by Microsoft in subsequent Service Packs, Cumulative Update Patches, and major releases. Microsoft continues to improve the performance of its spatial types.

Some users have requested that Esri include table hints in queries on SQL Server spatial type columns. A hint would force the optimizer to use a specific spatial index. Esri has considered whether adding hints is a good idea and has decided that in this case it is not. There are several reasons for this decision.

1. Spatial columns do not always have a single spatial index. There may be no spatial index, or multiple spatial indexes, on any spatial column. Spatial indexes can be created over different spatial extents of a single column and at different tessellation levels. Further, indexes can be added or dropped at any time. ArcGIS applications do not have access to the same SQL Server internals that the query optimizer does for choosing an appropriate index to hint. Therefore, the hinted spatial index may not necessarily be the best spatial index for a query.

2. Hinting the optimizer to use the spatial index can be detrimental when the query is attribute-driven, where selectivity is higher when considering other attribute constraints before considering spatial constraints.

3. Indexes, including spatial indexes, can be poorly optimized. Hinting the use of these indexes can reduce performance.

4. Microsoft will change the operation of its optimizer over time. Changes to the optimizer may render any preexisting hints inappropriate. This can lead to poorer performance with the preexisting hint, forcing Esri to change its software to take the optimizer changes into account.

Article ID:000010604

Software:

ArcGIS Server

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Why do ArcGIS applications not hint the use of SQL Server spatial indexes?

Answer

Discover more on this topic