AnsweredAssumed Answered

Poor Performance - 10.5 SQL Server 2012R2

Question asked by faribaultGIS on Jan 24, 2017
Latest reply on Mar 21, 2017 by faribaultGIS

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)

Outcomes