topic SQL Server Performance with large datsets in ArcGIS Enterprise Questions https://community.esri.com/t5/arcgis-enterprise-questions/sql-server-performance-with-large-datsets/m-p/530612#M20554 <HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>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.<BR />select SHAPE from GISdw.L.Contours1Foot WHERE CONTOURS1FOOT.SHAPE.Filter (@P1) = 1</P><P></P><P>While in the new server running 10.6.1 this query is being passed&gt;<BR />select SHAPE, CONTOURTYPE, GISDW.DCL.CONTOURS1FOOT.GDB_GEOMATTR_DATA from GISdw.DCL.Contours1Foot where CONTOURS1FOOT.SHAPE.STIntersects (@P1) = 1</P><P></P><P>Has anyone else seen this behavior or have a suggested fix?</P></BODY></HTML> Thu, 27 Feb 2020 17:07:53 GMT DaveWatson 2020-02-27T17:07:53Z SQL Server Performance with large datsets https://community.esri.com/t5/arcgis-enterprise-questions/sql-server-performance-with-large-datsets/m-p/530612#M20554 <HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>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.<BR />select SHAPE from GISdw.L.Contours1Foot WHERE CONTOURS1FOOT.SHAPE.Filter (@P1) = 1</P><P></P><P>While in the new server running 10.6.1 this query is being passed&gt;<BR />select SHAPE, CONTOURTYPE, GISDW.DCL.CONTOURS1FOOT.GDB_GEOMATTR_DATA from GISdw.DCL.Contours1Foot where CONTOURS1FOOT.SHAPE.STIntersects (@P1) = 1</P><P></P><P>Has anyone else seen this behavior or have a suggested fix?</P></BODY></HTML> Thu, 27 Feb 2020 17:07:53 GMT https://community.esri.com/t5/arcgis-enterprise-questions/sql-server-performance-with-large-datsets/m-p/530612#M20554 DaveWatson 2020-02-27T17:07:53Z