Thomas:
Are you having slowness issues in Pro from SDE using different databases (e.g. SQL Server, Oracle, Postgres, other)?
Besides performance issues with SDE, are you by any chance using query layers to connect non-SDE databases that are spatially enabled with ST_Geometry libraries?
Although not a query layer from a SQL Server database, in conjunction with ESRI technical support I did find fundamental issues with query layers and Oracle spatially enabled databases, so you might be encountering a similar problem with query layers from your SQL Server database:
"As a recap of our most recent call:
I created a table for simple point data, assigned the OBJECTID field as NUMBER(38) field type during initial creation in Oracle from a SQL query. This gets casted as NUMBER(38,0) at the oracle table level. When I create a view from that table directly in oracle (SELECT *), the OBJECTID field in the view is assigned NUMBER(38).
When I compare the view and the table in ArcCatalog - the table's NUMBER(38,0) field is interpreted as an "Object ID" field. The view's NUMBER(38) field is interpreted as a long integer.
Even if I cast the precision when creating the view OBJECTID still gets assigned as NUMBER(38):
CREATE VIEW city_points_VIEW_num38_0 AS
SELECT CAST("OBJECTID" AS NUMBER(38,0)) AS "OBJECTID","CITY_NAME","LATITUDE","LONGITUDE","SHAPE"
FROM city_points
If this were a geodatabase, we would be able to register the view with the geodatabase and the OBJECTID field would be considered as an "Object ID" field rather than interpreting and referencing a long integer field as an Object ID field. This seems to be a limitation of the software when interpreting unique ID fields from an oracle view from a spatially enabled table, and would be most likely a defect."
ArcMap software does not handle the OBJECTID field properly which causes all sorts of downstream issues in performance and basic query functionality failing.