Show Navigation | Hide Navigation
You are here:
Geodatabases and ArcSDE > Administering ArcSDE geodatabases > Tuning an ArcSDE geodatabase

Tuning spatial indexes

Release 9.2
Last modified December 3, 2010
E-mail This Topic Printable Version Give Us Feedback

Print all topics in : "Tuning an ArcSDE geodatabase"

Related Topics

About the spatial index

NOTE: ArcSDE Enterprise only

NOTE: Beginning with ArcSDE 9.2 geodatabases, the spatial index is calculated for you when you create a new dataset and provide a coordinate system in ArcGIS Desktop. You shouldn't need to tune such spatial indexes. However, if you are using an older release of ArcSDE, you can follow the steps below to tune the index.

The multilevel spatial index is used to decrease the time it takes to locate features that match a spatial query. ArcSDE uses a multilevel spatial index for data stored in the ArcSDE compressed binary and Open Geospatial Consortium well-known binary (OGCWKB) geometry storage types. These geometry storage types are only available if you are using Oracle or SQL Server databases, so the steps provided below for tuning the spatial index only apply to those databases and those geometry storage types.

NOTE: IBM DB2 Spatial Extender also uses a multilevel grid spatial index. Since the spatial index is generated by the Spatial Extender, you should use the Index Advisor tool provided by IBM to help tune this index. You can read more about this tool at IBM's Information Center.

Learn more about spatial indexes generated by the DB2 Spatial Extender

The multilevel spatial index defines imaginary x/y grids for a feature class. There may be one, two, or three of these grid levels defined per feature class. The size of each grid in an ArcSDE feature class must be at least three times the previous grid size. Most feature classes need only one grid level, but more levels may be needed if the average sizes of the feature envelopes (the bounding boxes of each feature) vary widely.
Learn more about spatial indexes

Each feature in the feature class is indexed using only one of the grid levels; small features are indexed in the first level and larger features in the second or third grid levels, if these levels are present. ArcSDE places an entry (a row) in the spatial index table for every instance in which a single feature intersects a single cell in the one grid level used for that feature.

When a spatial query is issued, ArcSDE performs primary filtering and secondary filtering to return all features that meet the requirements of the spatial query.

For primary filtering

Secondary filtering is done to reduce the result set to the features that satisfy the spatial query.

The default grid size settings are designed to ensure that the data can be loaded. In most circumstances, the default grid sizes will be appropriate for fast spatial queries. However, depending on the characteristics of your data, they may not be the optimal size. Tuning the grid sizes might result in better spatial query performance.

When you tune the spatial index, you must balance selectivity against reducing the number of entries in the spatial index. The greater the number of entries in the spatial index, the fewer features there will be in the result sets, which the secondary spatial filter must examine and possibly eliminate to meet the requirements of the spatial query. However, the more spatial index entries there are, the larger the spatial index will be, which will slow down queries on the spatial index table.

When performance tuning, the only way to tell if you have made a positive change is to monitor the results of each change. ArcSDE provides statistics about the spatial index which, along with performance testing, can ease the tuning process. The administration command sdelayer –o si_stats is the primary tool for reporting spatial index grid statistics used to tune the spatial index.

NOTE: Building a new spatial index for an ArcSDE feature class is a server-intensive operation; you should not do it for very large feature classes when a large number of users are logged in to the server.

How to tune a spatial index

The general steps to follow when tuning a spatial index are

  1. Establish a repeatable test to measure spatial query performance. This may be a manual test or you might create an automated test that performs and times a defined set of spatial queries.

  2. Use sdelayer –o si_stats to gather beginning spatial index statistics. Be sure to record the original size of the grid levels in case you decide to revert to them. You might decide to do this if you find the changes you made to the grid size cause spatial query performance to decline.

  3. The syntax for sdelayer –o si_stats is

    sdelayer –o si_stats –l <business_table,spatial_column> 
    [–i <service>] [–s <server_name>] [–D <database>]
    –u <DB_user_name> –p <DB_user_password> [–q]

    NOTE: If you are not the owner of the table/feature class, you must qualify the table name as owner.table.

    The following is an example of what gets returned by sdelayer –o si_stats:

    Layer Administration Utility
Layer 5 Spatial Index Statistics:
    Level 1, Grid Size 4.940464025
 Grid Records: 966                                                       
     Feature Records: 23                                                    
     Grids/Feature Ratio: 42                                                
     Avg. Features per Grid: 16.37                                       
     Max. Features per Grid: 96                                           
     % of Features Wholly Inside 1 Grid: 59.71                     
                   Spatial Index Record Count By Group          
    Grids:        <=4    >4    >10    >25    >50   >100    >250   >500
Features:      23     0      0      0     0      0      0      0        
    % Total:      100%    0%     0%     0%    0%     0%     0%     0%

    The statistic Grids/Feature Ratio shows the ratio of the number of entries in the spatial index table versus the number of features in the feature class. Fewer entries in the spatial index table equate to faster queries. Optimally, the Grids/Feature Ratio should be less than 2. If it exceeds 4, consider modifying the spatial index settings. As you can see in the example above, with a Grids/Feature Ratio of 42, this spatial index needs modifying.

    At the end of the output is a histogram showing how many spatial index records (entries) exist for each feature. The majority of the features should have fewer than four records. Even in a well-tuned spatial index, if there are a few features that are significantly larger than the others, these features will have more spatial index entries. If many features have more than four spatial index entries, consider modifying the spatial index.

  4. Change the spatial index grid settings. You can use ArcCatalog or the sdelayer command to do this.
  5. Learn how to modify the spatial index in ArcCatalog

    To use sdelayer, do the following:

  6. Run your query performance test again, and check the spatial index statistics to see if the changes had the desired effect. If not, you could try again or you might choose to undo the changes, especially if they have a negative effect on performance. You would do this by repeating step 3 using the grid index sizes you recorded in step 2.

  7. Repeat steps 3 and 4 until you run out of reasonable changes to make or you find that remaining changes have a negligible effect on performance.


  • A good starting place is to set the grid size to three times the length of the edge of an average-sized feature.

  • Where possible, use only one spatial index grid level. Because it needs to search each level used in each feature class, ArcSDE usually performs best when a single spatial index grid level is used. However, feature classes with highly variable feature envelope sizes may benefit from multilevel spatial indexes. You may choose to experiment with a multilevel spatial index to see if it will improve the spatial index statistics and the query performance.

  • Where possible, specify a grid size where a high percentage of features fall wholly within one grid cell. If the percentage falls below 80 percent, consider modifying the spatial index settings.

  • Keep the average number of features per grid fairly low, between 100 and 300. Try to keep the maximum number of features per grid below 4,000. A spatial query that happens to include a grid cell with many associated features will return all those features to ArcSDE to be processed by the secondary spatial filter.

  • For details on the use of sdelayer and other administration commands, consult the Administration Command Reference that is provided with the software. You can also download a PDF version of the file from the ESRI support site. (Login required)


Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.