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?
Shouldn't be drawing that slow. Are you able to run jmeter from a client and generate a load, and run SQL trace on the SQL server....then run that through Database Tuning Adviser?
I never really resolved this problem...if we have a lot of features on the map we get the user to narrow down their search before we start displaying a lot of data (even using the dynamic map image service layer).
I rarely use SQL for "Map-only" applications, as the performance of the geography/geometry data-type is horrible. For your application, consider some Python magic that exports your SQL to a FGDB (perhaps nightly), then hang your map service(s) off the FGDB. You could also do something similar where a hosted service on AGOL is updated nightly (or hourly) with data from your SQL instance. You will chase SQL spatial performance to no end, and never be satisfied with it's display speeds.
That's great feedback Thomas...thanks so much. The applications that I'm referring to are letting the user add/edit/delete points/attributes and they need to see the results immediately, so the Python export wouldn't work for us in this case. Great idea, though.
There this: /blogs/HackingArcSDE/2015/07/07/witch-magic-snake-oil-medicine-and-spatial-index-tuning , or you can add several indexes of varying size and density, then monitor using your most common view queries.