We recently shifted to a new server for SQL storage. It is SQL Server 2016 the server has 24 GB of Ram and 4 sockets. We noticed a decline in performance of our larger data sets like contours. In looking at the SQL Activity monitor the queries for contours are consistently an "Expensive Query". I have added an index to one of the fields with a small amount of benefit.
A difference that I am seeing for the queries being passed to SQL is the spatial methods being passed. On the old server running SDE 10.3.1 this query is passed to the contours.
select SHAPE from GISdw.L.Contours1Foot WHERE CONTOURS1FOOT.SHAPE.Filter (@P1) = 1
While in the new server running 10.6.1 this query is being passed>
select SHAPE, CONTOURTYPE, GISDW.DCL.CONTOURS1FOOT.GDB_GEOMATTR_DATA from GISdw.DCL.Contours1Foot where CONTOURS1FOOT.SHAPE.STIntersects (@P1) = 1
Has anyone else seen this behavior or have a suggested fix?
