I have a Windows Server 2012 R2 running SQL Server 2012 R2 and ESRI Server 10.3. The server has 16 dual-core Xeon processors and 96 GB of RAM. I have a web application that is using ArcGISDynamicMapServiceLayers to display state-wide lines and points, including labels for both lines and points. We restrict the display of the lines/points until the user is zoomed to an appropriate level.
The issue that we are seeing is every time the web application requests data, the SQL Server CPU load goes to 100%. When one user is using the application performance is not bad, but when several folks start using it performance degrades quickly.
In analyzing the queries that are being used, it doesn't appear that SQL Server is using the spatial indexes at all. I've tried several things, including setting MAXDOP to 1, but nothing seems to work and SQL Server takes the CPU to 100% every time the application requests data.
Has anyone experienced this issue and been able to find a resolution?
