Community
Select to view content in your preferred language

SQL Server not using spatial indexes for ArcGISDynamicMapServiceLayer requests

5718
11
05-20-2016 07:15 AM
SteveBurdette
by
Regular Contributor
‎05-20-2016 07:15 AM


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?

0 Kudos
11 Replies
ThomasColson
by MVP Frequent Contributor
MVP Frequent Contributor
‎05-20-2016 12:11 PM

What storage format? Binary, Geography, or Geometry?

0 Kudos
SteveBurdette
by
Regular Contributor
‎05-20-2016 12:29 PM

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

0 Kudos
ThomasColson
by MVP Frequent Contributor
MVP Frequent Contributor
‎05-21-2016 05:28 AM

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?

0 Kudos
MladenSimic1
by
Emerging Contributor
‎02-20-2018 03:41 AM

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.

0 Kudos
SteveBurdette
by
Regular Contributor
‎02-20-2018 06:16 AM

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

0 Kudos
ThomasColson
by MVP Frequent Contributor
MVP Frequent Contributor
‎02-20-2018 04:51 AM

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. 

0 Kudos
SteveBurdette
by
Regular Contributor
‎02-20-2018 06:17 AM

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.  

0 Kudos
ThomasColson
by MVP Frequent Contributor
MVP Frequent Contributor
‎02-20-2018 10:13 AM

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. 

0 Kudos