topic Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests in ArcGIS Enterprise Questions

SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

SteveBurdette — Fri, 20 May 2016 14:15:49 GMT

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?

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

ThomasColson — Fri, 20 May 2016 19:11:49 GMT

What storage format? Binary, Geography, or Geometry?

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

SteveBurdette — Fri, 20 May 2016 19:29:15 GMT

Our shapes are stored as Geometry data types. I created the Feature Class using the System Tools in ArcCatalog.

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

ThomasColson — Sat, 21 May 2016 12:28:27 GMT

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?

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

MladenSimic1 — Tue, 20 Feb 2018 11:41:37 GMT

Hi Steve Burdette, have you ever solved your problem? I have the same situation with the same platform - SQL Server 2012 and ArcGIS Server 10.3.

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

ThomasColson — Tue, 20 Feb 2018 12:51:19 GMT

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.

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

SteveBurdette — Tue, 20 Feb 2018 14:16:23 GMT

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).

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

SteveBurdette — Tue, 20 Feb 2018 14:17:43 GMT

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.

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

nicogis — Tue, 20 Feb 2018 16:14:41 GMT

see https://support.microsoft.com/en-in/help/2757097/sql-2012-query-optimizer-may-not-choose-to-use-a-spatial-index

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

ThomasColson — Tue, 20 Feb 2018 18:13:06 GMT

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.

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

ThomasColson — Tue, 20 Feb 2018 18:25:53 GMT

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.

Re: SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

DavidRUtledge1 — Wed, 21 Feb 2018 19:38:09 GMT

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?