DEFAULT GEOMETRY_AUTO_GRID for SQL Server 2012 Spatial Index

Idea created by use2b311 on Jul 15, 2014
    New
    Score80
    • 1_tsptsp
    • dhouseknecht
    • Tao_Li
    • tthayer
    • use2b311
    • rgrichards
    • tpcolson
    • graeme
    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.

    Thanks.