Poor Performance - 10.5 SQL Server 2012R2

Question

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

GreggRoemhildt1 · Accepted Answer

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.