We have a SQL Server Enterprise Geodatabase on version 10.4.1, which uses the SQL Geometry storage type.
We have some python tools which run overnight and use the DeleteFeatures and Append tools to refresh some Feature Classes with change only updates. The tools use Layer objects (arcpy.MakeFeatureLayer_management) and selection sets to only delete/append a few features at once.
The scripts were written in ArcMap/Python2.7, and it was noticed that behind the scenes the DeleteFeatures and Append tools were running DROP INDEX and CREATE INDEX statements behind the scenes, this is related to the MaintainSpatialIndex environment variable. We do not want this behavior as it is affecting DB performance.
Maintain Spatial Index (Environment setting)—Help | ArcGIS for Desktop
To try and mitigate this the scripts were tweaked to run from ArcGIS Pro/Python 3, because it seems like the Pro/Python 3 DeleteFeatures and Append tools do not try and Drop/Create Indexes behind the scenes, but recently we have noticed some Spatial Indexes got dropped from the database.
Maintain Spatial Index (Environment setting)—ArcGIS Pro | Documentation
Has anyone else encountered such problems with DeleteFeatures and Append affecting Spatial Indexes adversely in SQL Server?
Am I right in thinking that ArcGIS Pro DeleteFeatures and Append tools do not try and Drop/Create Indexes behind the scenes?
Solved! Go to Solution.
I have not encountered the issue with ArcGIS Pro/Python 3.x and spatial indexes. But when I encountered this issues with ArcMap/Python 2.7, the solution was to use a database user who didn't have permission to modify the indexes (e.g. data_writer). You could try the same approach and see if the performance improves when using ArcGIS Pro/Python 3.x.
I have not encountered the issue with ArcGIS Pro/Python 3.x and spatial indexes. But when I encountered this issues with ArcMap/Python 2.7, the solution was to use a database user who didn't have permission to modify the indexes (e.g. data_writer). You could try the same approach and see if the performance improves when using ArcGIS Pro/Python 3.x.
Thanks David, using an Operating System auth account with no DDL permissions seems faster and has not caused a blocking CREATE/DROP INDEX statement in the DB. Evidence suggests that the ArcGIS Pro version of arcpy.Append with an account which has the necessary privileges is still running these CREATE/DROP INDEX statements behind the scenes, more investigation needed to know for sure.