SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

[SteveBurdette]

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?

[ThomasColson]

And....(crossing my fingers behind my back), Editing a SQL-based feature service in Pro is easily 10X faster than it is in Arc. Everything else is 10X slower....but at least they improved editing....

And....can you define "Web Application" and your environment? Portal, AGOL, WAB SDK? Is the feature service over HTTPS? (It should). What type of authentication? SSL and IWA will put a big performance hit on a feature service. How many instances do you have started, versus max? I find that keeping N+2, where N is the number of users I NORMALLY expect to use the service, keeps the CPU from spiking whenever a new instance spools up. On SQL, try moving the log and temp files to a physically different disk. On the GIS server and the SQL server, run this for (1 hour max!) GitHub - clinthuffman/PAL: Performance Analysis of Logs (PAL) tool when you have a bunch of users both starting the app for the first time, and heavily using it. I used this with success to justify an upgrade to the SAN that the SQL files were on. Also use SQL profiler to see how/if attribute indexes are needed, or performing poorly. How often are you backing up the DB and rebuilding indexes? If you're not backing up (full) nightly, and NOT truncating the transaction log, just viewing and panning an editable feature service will fill up that log no time. 

[DavidRUtledge1]

I have been able to resolve this behavior in some cases but there are several paths to get to this problem.

To provide a proper response I need to know how you got to this point.  Sometimes this is a question of database optimization, other times it means you have hit certain fundamental limitations of SQL spatial data.

Questions:

How many spatial indexes do you have on the tables?

How many rows in the tables?

Are your indexes being rebuilt on a regular basis?

Are you using geometry_grid or geometry_autogrid spatial indexes?

Is your database under memory pressure?

Are your log and data files on the same drive or on separate drives?