Attribute Indexes in SQL geodatabase

Question

Hi all,

How do I check on the attribute index of a feature class in a SQL geodatabase? What it is, if it's functioning as it should, etc? I see this: "If you're choosing an indexing strategy for an enterprise geodatabase, refer to your DBMS documentation for more detailed guidance." from https://pro.arcgis.com/en/pro-app/3.3/help/data/geodatabases/overview/attribute-indexes-in-the-geodatabase.htm

I can see 2 listed in the layer properties of the layer in my ArcGIS Pro map - under Indexes. Add and Delete are greyed out, as expected.

This came up because we were joining a table to a feature class within our SQL geodatabase in ArcGIS Pro 3.2.4. And there is a check box for 'Index join fields'. Which failed - presumably because it's a SQL geodatabase, it doesn't work like that, and it couldn't get a schema lock. But that's a guess.

Any info appreciated. I'm not a full-fledged DBA 😀

Thank you,

Andrea

ryanuthoff · Accepted Answer

It depends on how soon the report was ran after rebuilding the indexes. If you rebuild the indexes today, but the report isn't generated until the next day, it's possible that the indexes could become fragmented between then.

If you're able to run the report immediately after rebuilding the index, then that would be ideal. If it is showing very low index fragmentation, then the rebuilding of the index is probably working correctly and you might need to rebuild indexes on a more frequent basis.

VinceAngelo · Accepted Answer

Index fragmentation is an issue with large tables, but no so much with small/tiny ones. If your Adds table is tiny, then you're probably wasting time by indexing it, and any subsequent INSERT or UPDATE is going to massively fragment the index again, so as Ryan has written, the timing of the report is important.

If, using Traditional Versioning, you rebuild indexes then compress and post, then tables (and therefore their indexes) may have had significant changes, which would impact the index fragmentation, and require another REINDEX. Having a clean rowid column index before compress is probably worth the effort, but you should probably wait until after geodatabase maintenance to update all indexes.

- V