Useful Resources for Rebuilding Indexes and Updating Statistics in an SDE Geodatabase for SQL Server and ORACLE :
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 will need to be done often. Fortunately for us, these are relatively straightforward to accomplish.
The scripts found here provide the SQL to both update statistics and rebuild indexes. Oracle databases should be set to automatically update stats. The Sql Server script is still applicable for geodatabases in Sql Server 2008, 2008R2 and 2012 even though it says Sql Server 2000/2005.
Also, if you are using ArcGIS 10.1 there are some new geoprocessing tools that can do this.
ArcGIS 10.1 – ArcToolBox:
Data Management Tools>Geodatabase Administration>Analyze Datasets
Updates 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’s 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 as SQL or the ArcToolBox tools can be exported to Python and automated as well. Either way, do it.
Thanks
NJ
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 will need to be done often. Fortunately for us, these are relatively straightforward to accomplish.
The scripts found here provide the SQL to both update statistics and rebuild indexes. Oracle databases should be set to automatically update stats. The Sql Server script is still applicable for geodatabases in Sql Server 2008, 2008R2 and 2012 even though it says Sql Server 2000/2005.
Also, if you are using ArcGIS 10.1 there are some new geoprocessing tools that can do this.
ArcGIS 10.1 – ArcToolBox:
Data Management Tools>Geodatabase Administration>Analyze Datasets
Updates 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’s 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 as SQL or the ArcToolBox tools can be exported to Python and automated as well. Either way, do it.
Thanks
NJ
No comments:
Post a Comment