Resources for rebuilding indexes and updating statistics in an SDE geodatabase

[KenGalliher1]

Tuning your RDBMS is an important aspect of maintaining your enterprise geodatabase. Two tasks found in all Esri-supported RDBMS are updating DBMS statistics and rebuilding indexes. If you are loading data in bulk or running query-intensive DML operations with SQL, these tasks need to be done more often. Fortunately for us, these tasks are relatively straightforward to accomplish.



The scripts found in Esri Knowledge Base Article 24518 provide the SQL to both update statistics and rebuild indexes.

NOTE:

• Oracle databases can be set to automatically update statistics.
• The SQL Server script in KB24518 is still applicable for geodatabases in SQL Server 2008, 2008R2 and 2012.

Also, if you are using ArcGIS 10.2 or 10.1, there are some new geoprocessing tools in the ArcToolbox that can accomplish this:Data Management Tools > Geodatabase Administration > Analyze Datasets:
Updates the database statistics of base tables, delta tables, and archive tables, along with the statistics on those tables’ indexes. This tool is used in enterprise geodatabases to help get optimal performance from the RDBMS query optimizer. Stale statistics can lead to poor geodatabase performance.Data Management Tools > Geodatabase Administration > Rebuild Indexes:
Updates indexes of datasets and system tables stored in an enterprise geodatabase. This tool is used in enterprise geodatabases to rebuild existing attribute or spatial indexes. Out-of-date indexes can lead to poor geodatabase performance.

No matter which one you choose, these resources can be run as automated tasks using SQL, or the ArcToolBox tools can be exported to Python and automated as well.Ken G. - Geodata Support Analyst

---