FAQ:  Why does ArcSDE not hint the use of SQL Server 2008 spatial indexes?

Why does ArcSDE not hint the use of SQL Server 2008 spatial indexes?

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. Microsoft is aware of the problem, and has implemented a fix in SQL Server 2008 SP1 (see Microsoft Support Article 968369). This fix should cause the optimizer to use the spatial index when its use is appropriate. For more information about SQL Server 2008 SP1, contact Microsoft.

Some users have requested that ESRI include table hints in queries on SQL Server 2008 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. ArcSDE does not have access to the same SQL Server internals that the query optimizer does for chosing 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, as it has in SQL Server 2008 SP1. 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.