Excessive archive growth from duplicate/no-op history records — has anyone dealt with archive bloat?

Question

I'm investigating a performance issue in an ArcGIS Enterprise geodatabase on SQL Server and wanted to see whether others have run into similar archive growth problems.

Environment:

* ArcGIS Enterprise 11.3
* Traditional versioning with archiving enabled
* SQL Server backend
* Feature class has approximately 118.5K active features
* Underlying SQL table has grown to approximately 16M+ rows
* The ArcGIS Pro client and ArcGIS Enterprise environment are on the same LAN.
* Separate app and database servers.
* DB Server: 32GB RAM, 2.70 GHz - 4 sockets, 4 virtual processors 
* App Server: (same specs as DB server)

A bit about my background. I'm a software developer with some DBA experience . I'm working with our GIS analyst to keep this system running. We do not have a  dedicated ArcGIS technical expert on staff.

Problem 1: Slow feature loading in ArcGIS Pro

We are seeing performance issues in ArcGIS Pro when this feature class is added to a map. Features take a long time to load and display.

As a test, we exported the feature class to a local file geodatabase and the performance problems disappeared. However, the file geodatabase export did not appear to preserve archive history, so I am not sure whether that is a meaningful comparison. It just proved the obvious: that less records yields a quicker experience.

Problem 2: Archive row inflation

We run a daily synchronization process that updates individual features with data from an external system. Turns out there was a bug in the script causing unchanged rows to be updated, which creates a new archive row. The script only targets a subset of all features in the class, but it still creates 20k new archive rows daily.

After the sync process completes we rebuild indexes.

Our feature class SQL table is 67GB, the index is 9GB.

Question 1:

Even with the reindexing, could the excess archive rows be causing the speed issues?

Question 2

Is there a way to safely perform SQL deletes of the duplicate archive rows (that don't represent actual changes). Then repair the gap.

Example:

Given a feature with GlobalID =1234, there are the rows: A B C D E F G.
G is the current row
A - F are the archive rows. B - F are all duplicates of A

Actions
1. Delete the duplicates
DELETE records B C D E F
2. Repair the gap
SET A.[GDB_TO_DATE] = G.[GDB_FROM_DATE]

ryanuthoff · Answer

I'll start with the disclaimer saying that every organization is different, but I have a table that is versioned with 2.4 million records in it, with 17.3 million records in the archive table, and we don't have any performance issues with it.

I don't think having a large archive table would impact the performance of a viewing the table in ArcGIS Pro. In this case since you are using a versioned feature class, it creates a separate archive table which ArcGIS Pro wouldn't be accessing when viewing only the feature class.

Are you reconciling and compressing the database? That would be the first thing to look at imo.

Also, when you reindex, make sure you're also doing the spatial index too and not just the attribute index.

For your second question.......all I can say is to be very careful about that. I'm not going to say that it's impossible, but it is a table that Esri manages so you might experience unexpected behavior if you directly modify it.

George_Thompson · Answer

For Problem 1:

* Since you have traditional versioning implemented, have done some of the recommended maintenance on the versioned data; https://doc.esri.com/en/arcgis-pro/latest/help/data/geodatabases/overview/recommended-version-administration-workflow.html?
       Mainly focused on the compression operation in this workflow.
* How many versions do you currently have?
* How many records are in the delta tables; https://community.esri.com/t5/arcgis-enterprise-documents/check-delta-counts-for-enterprise-geodatabase/ta-p/1559800?

Problem 2:

* Is there a reason that you enabled archiving? As you mentioned in Problem #2, it is working as expected.

I would run the admin related tasks and re-test. Having lots of unreconciled / uncompressed records can cause the behavior that you are seeing in Problem 1.