DEFAULT GEOMETRY_AUTO_GRID for SQL Server 2012 Spatial Index

07-15-2014 09:21 AM
Status: Implemented
Labels (1)
Occasional Contributor II

The SDE_dbtune table in a SQL Server 2012 Enterprise Geodatabase allows you to specify "WITH" parameters for a default spatial index. It also lets you specify the default grid densities and cells per object in the A_MS_SPINDEX and B_MS_SPINDEX parameters. However, all of these options require that you use a GEOMETRY_GRID tessellation. SQL Server's default spatial index at 2012 and later is the GEOMETRY_AUTO_GRID and for good reason. For most feature classes, MEDIUM, MEDIUM, MEDIUM, MEDIUM and 16 cells per object does NOT perform well for large datasets. GEOMETRY_AUTO_GRID performs far better than that defualt and as good as a custom-tuned index most of the time.

The really critical reason for this request comes from data that is reloaded on a regular basis. Many of our tables are created by another entity and sent to us nightly and weekly. They are large enough that the spatial index is dropped and recreated when loading the data. When they get recreated, they get reset to the default MMMM, 16 which brings our maps to a crawl. We have to run an index drop and build procedure with custom settings every night. Quite the workaround for just a simple default.

Please allow the DBTune table to specify GEOMETRY_AUTO_GRID as the default spatial index for the adds and base tables of a feature stored in SQL Server GEOMETRY type.



Similar (but not duplicate) idea for Pro: 

Status changed to: Implemented

Support for SQL Server Auto Grid spatial indexes was provided in 10.3.1 for SQL Server database compatibility level 110 and higher.