Select to view content in your preferred language

Reindex GDB Tables

649
5
Jump to solution
03-14-2024 05:08 AM
CodyPatterson
Regular Contributor

Hey All,

Enterprise 11.2 Geodatabase.

I have been reindexing our Enterprise Geodatabase every day, as we write quite often to this DB, and have noticed using a fragmentation query, that we have a few tables that don't seem to reindex, these include GDB_ITEMS and SDE_column_registry. These are unable to be selected via ArcGIS Pro, so I was curious if these should be reindexed, or if these should just be disregarded, they are quite large.

CodyPatterson_0-1710418108124.png

 

Thanks in advance!

Cody

2 Solutions

Accepted Solutions
KimberleeHermann1
New Contributor
Hello...if you are using SQL Server it is super easy to use the Maintenance Plan wizard and do a reindex of everything on a database. Usually doesn't take long at all. I have it scheduled for all of my databases each weekend and that seems to keep thing "all good".

View solution in original post

MarceloMarques
Esri Regular Contributor

@CodyPatterson - I usually avoid using the ArcGIS Pro GP Tools to gather new statistics and to rebuild indexes (attribute indexes and spatial indexes ) in my production geodatabases because these tools to not offer advanced options that you find with the SQL Server SQL Commands to do the same. I suggest you use the SQL Server Tools to do this maintenance. 

You can find my maintenance scripts for SQL Server in my community.esri.com blog below, download the database template scripts for SQL Server.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

Your question about the GDB_ITEMS table and any other ArcSDE Repository Tables. Yes, those tables must be reindexed as well, probably not every day, as you will not be creating new featureclasses, etc. in the geodatabase, but you should reorganize indexes weekly and rebuild indexes monthly at least for the ArcSDE Repository Tables, and right after that gather new statistics.

For reference to others about this discussion follow below the links for the ArcGIS Pro GP Tools.

Enterprise geodatabase maintenance tasks—ArcGIS Pro | Documentation
Modify a spatial index—ArcGIS Pro | Documentation
Rebuild Indexes (Data Management)—ArcGIS Pro | Documentation
Update database statistics—ArcGIS Pro | Documentation


I hope this helps.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov

View solution in original post

5 Replies
KimberleeHermann1
New Contributor
Hello...if you are using SQL Server it is super easy to use the Maintenance Plan wizard and do a reindex of everything on a database. Usually doesn't take long at all. I have it scheduled for all of my databases each weekend and that seems to keep thing "all good".
CodyPatterson
Regular Contributor

Hey @KimberleeHermann1 

Thank you for that tip, I was not sure if the SQL way would be preferable, or the ArcGIS Pro way, but it seems that the SQL way is more preferred! I will give that a shot and go through with it that way, my initial assumption was that since they are ArcGIS Pro provided indexes through GDB functionality, then they could only be rebuilt within Pro, but we learn as we go!

Cody

MarceloMarques
Esri Regular Contributor

@CodyPatterson - I usually avoid using the ArcGIS Pro GP Tools to gather new statistics and to rebuild indexes (attribute indexes and spatial indexes ) in my production geodatabases because these tools to not offer advanced options that you find with the SQL Server SQL Commands to do the same. I suggest you use the SQL Server Tools to do this maintenance. 

You can find my maintenance scripts for SQL Server in my community.esri.com blog below, download the database template scripts for SQL Server.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

Your question about the GDB_ITEMS table and any other ArcSDE Repository Tables. Yes, those tables must be reindexed as well, probably not every day, as you will not be creating new featureclasses, etc. in the geodatabase, but you should reorganize indexes weekly and rebuild indexes monthly at least for the ArcSDE Repository Tables, and right after that gather new statistics.

For reference to others about this discussion follow below the links for the ArcGIS Pro GP Tools.

Enterprise geodatabase maintenance tasks—ArcGIS Pro | Documentation
Modify a spatial index—ArcGIS Pro | Documentation
Rebuild Indexes (Data Management)—ArcGIS Pro | Documentation
Update database statistics—ArcGIS Pro | Documentation


I hope this helps.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
CodyPatterson
Regular Contributor

Hey @MarceloMarques 

Thank you for the tip! I will check that out to see what I can manage with that! I spoke with another commenter and said that I was not confident on which method to use, since my assumption was that since it's a Pro index, I must do it in Pro, but it looks like that's incorrect.

I appreciate the documentation too!

Cody

KimberleeHermann1
New Contributor

You are very welcome!! So glad it worked for you.

0 Kudos