Poor Performance - 10.5 SQL Server 2012R2

GreggRoemhildt1 — Sun, 12 Dec 2021 09:56:39 GMT

I'm sure this has been asked before, but I couldn't find anything relevant to 10.5 yet... We just updated ArcGIS Server/ArcMap and I have ran the upgrade geodatabase tool to get the schema up to date with 10.5.

I'm experiencing really poor performance with running some data updates. I'm trying to do a field calculation on a field, the field calculation is simply the name of another text field, on about 3000 records, so it should run fairly quickly I would think. However, as soon as I start the field calculation, I see on the serer, the CPU spikes to about 50% and the calculation takes about 2 minutes to complete, ArcMap hangs during this time.

The data is "versioned" so I need to start an editing session in order to start editing.

Anyone have any suggestions on troubleshooting this?

I have done some basic monitoring in sql server management studio, and here are some of the bigger queries I've found being ran. It seems a few of them are getting run A LOT.

Right after starting the field calculation:

select  OBJECTID,  CID,  SUBDIV,  PLAN_LENGTH,  BUILD_YEAR,  DIAMETER,  FROM_IE,  TO_IE,  PLAN_ID,  PLAN_PAGE,  PIPE_TYPE,  ALT_PLAN_ID,  
SAN_ZONE,  ENABLED,  SOURCE_ACC,  SOURCE_DES,  PROJ_NAME,  MAINT_NUM,  MAINT_NAME,  ROOT_MAINT,  MAINT_CAT,  MATERIAL,  
OWNER,  NOTES,  FROM_ID,  TO_ID,  SHAPE,  GLOBALID,  Shape.STLength(),  OLD_PLAN_ID,  GIS.DBO.SDE_GEOMETRY11.CAD  
from (

SELECT /* ArcSDE NORMAL_FILTER */ b.OBJECTID,b.cid,b.subdiv,b.plan_length,b.build_year,b.diameter,b.from_ie,b.to_ie,b.plan_id,b.plan_page,b.pipe_type,
b.alt_plan_id,b.san_zone,b.Enabled,b.source_acc,b.source_des,b.proj_name,b.maint_num,b.maint_name,b.root_maint,b.maint_cat,b.material,b.owner,b.notes,
b.from_id,b.to_id,b.Shape,b.GlobalID,b.old_plan_id ,0 SDE_STATE_ID 
FROM  gis.DBO.san_pipe b  
LEFT JOIN ( 

SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID 
FROM GIS.DBO.d16 
WHERE SDE_STATE_ID = 0 AND DELETED_AT IN (

SELECT l.lineage_id 
FROM gis.dbo.SDE_state_lineages l 
WHERE l.lineage_name = @P1 AND  l.lineage_id <= @P2) ) d 
ON b.OBJECTID = d.SDE_DELETES_ROW_ID 
WHERE d.SDE_STATE_ID IS NULL 
UNION ALL 

SELECT a.OBJECTID,a.cid,a.subdiv,a.plan_length,a.build_year,a.diameter,a.from_ie,a.to_ie,a.plan_id,a.plan_page,a.pipe_type,a.alt_plan_id,a.san_zone,
a.Enabled,a.source_acc,a.source_des,a.proj_name,a.maint_num,a.maint_name,a.root_maint,a.maint_cat,a.material,a.owner,a.notes,a.from_id,a.to_id,
a.Shape,a.GlobalID,a.old_plan_id ,a.SDE_STATE_ID 
FROM  GIS.DBO.a16 a  
LEFT JOIN ( 

SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID 
FROM GIS.DBO.d16 
WHERE SDE_STATE_ID > 0 AND DELETED_AT IN (
SELECT l.lineage_id 
FROM gis.dbo.SDE_state_lineages l 
WHERE l.lineage_name = @P3 AND  l.lineage_id <= @P4) ) d 
ON (a.OBJECTID = d.SDE_DELETES_ROW_ID) AND (a.SDE_STATE_ID = d.SDE_STATE_ID) 
WHERE a.SDE_STATE_ID IN (

SELECT l.lineage_id 
FROM gis.dbo.SDE_state_lineages l 
WHERE l.lineage_name = @P5 AND  l.lineage_id <= @P6) AND d.SDE_STATE_ID IS NULL ) V__16 
LEFT JOIN GIS.DBO.SDE_GEOMETRY11 
ON GIS.DBO.SDE_GEOMETRY11.GEOMETRY_ID = V__16.OBJECTID AND GIS.DBO.SDE_GEOMETRY11.SDE_STATE_ID = V__16.SDE_STATE_ID  
where (to_id  = 335)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Right after doing the field calculate and then clicking "Save":

select  V__8.*  from (SELECT /* ArcSDE NORMAL_FILTER */ 
b.OBJECTID,b.cid,b.plan_page_num,b.Enabled,b.build_year,b.rem_year,
b.inv_elev,b.num_inv,b.gps_date,b.source_acc,b.source_des,b.fl_elev,b.top_elev,
b.category,b.owner,b.material,b.notes,b.storm_zone,b.has_grate,b.created,b.modified,
b.Shape,b.GlobalID,b.AncillaryRole,b.est_horiz_acc,b.est_vert_acc,b.gps_top_elev,
b.old_plan_id,b.plan_id FROM  gis.DBO.storm_struc b  LEFT JOIN ( 

SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID 
FROM GIS.DBO.d8 
WHERE SDE_STATE_ID = 0 AND DELETED_AT IN (

SELECT l.lineage_id 
FROM gis.dbo.SDE_state_lineages l 
WHERE l.lineage_name = @P1 AND  l.lineage_id <= @P2
) 
) d 
ON b.OBJECTID = d.SDE_DELETES_ROW_ID 
WHERE d.SDE_STATE_ID IS NULL 
UNION ALL 
SELECT a.OBJECTID,a.cid,a.plan_page_num,a.Enabled,a.build_year,a.rem_year,a.inv_elev,a.num_inv,a.gps_date,a.source_acc,a.source_des,
a.fl_elev,a.top_elev,a.category,a.owner,a.material,a.notes,a.storm_zone,a.has_grate,a.created,a.modified,a.Shape,a.GlobalID,a.AncillaryRole,
a.est_horiz_acc,a.est_vert_acc,a.gps_top_elev,a.old_plan_id,a.plan_id 
FROM  GIS.DBO.a8 a  

LEFT JOIN ( 
SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID 
FROM GIS.DBO.d8 
WHERE SDE_STATE_ID > 0 AND DELETED_AT IN 
(

SELECT l.lineage_id 
FROM gis.dbo.SDE_state_lineages l 
WHERE l.lineage_name = @P3 AND  l.lineage_id <= @P4) ) d 
ON (a.OBJECTID = d.SDE_DELETES_ROW_ID) AND (a.SDE_STATE_ID = d.SDE_STATE_ID) 
WHERE a.SDE_STATE_ID IN 
(SELECT l.lineage_id 
FROM gis.dbo.SDE_state_lineages l 
WHERE l.lineage_name = @P5 AND  l.lineage_id <= @P6) AND d.SDE_STATE_ID IS NULL ) V__8  
where (cid  = 2983)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Poor Performance - 10.5 SQL Server 2012R2

GreggRoemhildt1 — Tue, 21 Mar 2017 13:49:51 GMT

The issue I had was related to the text field size. Some of the tables were brought over from Access tables and had the field type "text" which equates to a very large text length in the arcgis equivalent. I created a new field with a shorter length (500 characters) and field calculated the data over to solve the problem.

topic Re: Poor Performance - 10.5 SQL Server 2012R2 in Geodatabase Questions

Poor Performance - 10.5 SQL Server 2012R2

Re: Poor Performance - 10.5 SQL Server 2012R2