Attribute Indexes in SQL geodatabase

[AndreaB_]

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-geoda... 

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]

I'm also not a full-fledged DBA, but you can check and see if an index is fragmented. If it's fragmented, then that means it's not going to function as efficient as it could be. This site has information about how to find fragmented indexes in SSMS.

You can also follow some best practices and rebuild your indexes on a regular basis, which you can do either through Esri or SSMS.

[AndreaB_]

Thank you for the link, that is very helpful.

I do run rebuild indexes on a regular basis. However, our IT bureau people provide a report showing index fragmentation.

The indexes are being rebuilt every week using https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html and also using ArcGIS Pro Rebuild Indexes. https://pro.arcgis.com/en/pro-app/3.3/tool-reference/data-management/rebuild-indexes.htm 

Do you have any idea why an A table would still show 66% index fragmentation in the IT report?

*Edit - whoops, mixing together Statistics and Indexes here. I have questions on both. But we can stick to Index fragmentation here. Thanks!

Thank you

[RyanUthoff]

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]

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