Slow drawing from SDE (SQL Server)

Question

I am attempting to setup two servers for testing purposes and am having a problem with connections to SDE. My setup is 2 internal virtual servers both running Windows Server 2008 R2. One is setup with SQL Server 2008 R2 and the other is setup using ArcGIS Server 10.1. The ArcGIS Server machine also has the SQL 2008 R2 Client Connectivity tools installed. Both servers for the test are on the same hardware using the same SAN for storage. I used the toolbox to create an enterprise GDB in SQL and then added that as a registered data store with AGS. I then exported a polygon of the states to SDE and published that as a map service so AGS was connecting to SDE for the data. That services takes about 5-10 seconds to draw no matter what the extent, which much slower than I thought it should be. I then published the data to AGS from a local FGDB (forcing it to copy the data to the AGS) and that draws the service almost instantly. As final test I copied the FGDB from my workstation to the data drive of the SQL server (same folder as the SQL .mdf for testing) and shared that, then registered the folder as a data store. I published the service again and this also draws instantly which tells me the problem is on the AGS connectivity to SQL Server (not a network issue as I removed that by putting the data in the same location).

One thing I do notice is that for the 5-10 seconds that it is attempting to draw when the data comes from SDE the AGS machine has both CPUs pegged at 100% while the SDE server looks like almost nothing is happening (no different than if I am pulling it from the FGDB).

Does anyone have any ideas on what could be causing this?

Thanks,
Josh

JeffAzevedo · Answer

Are your features using the Geometry spatial type? If they are, compare it against the same feature using SDE binary type. I believe you will see a huge performance boost.

*edit
I just noticed you are using Geometry Spatial Type. This was a huge problem for us across Editing, (your exact problem) snapping, measuring and queries in general.

To test this, copy a few features that are Geometry Types, and paste them into the same Dataset. Make sure the Config. Keyword is set to SDEBINARY. I'm betting all your problems go away.

Good luck.

VinceAngelo · Answer

How complex are the geometries?  Did you query the Well Known Text in your SQL test?

Using 'sdequery' will give you an idea of the cost of getting the data to the render engine.

Have you tried using a Query Layer to render the same data?

- V

JoshuaKeller · Answer

First, thank you for the help. I did some more testing based on some of what you suggested, and started with SQL profiler and performance monitor. Based on the SQL profiler and the performance monitor SQL appears to be returning the shape for the entire US within 800-1000ms and watching the network the data all appears to be transferred within about a second, then the CPU on the AGS machine chunks away for 4-10 more seconds.

So based on that, it seems like it may not be a SQL issue because SQL is getting the data to AGS within 2 seconds, AGS is then taking an extra 4-10 seconds once it gets the data to render. When it pulls the data from the network share data transfer happens slightly faster as expected, and rendering barely puts a blip on the CPU monitor.

This leads me to believe that I should be focusing on the AGS server, or there is some sort of additional overhead for AGS to process the SQL data since the same exact dataset renders so much faster out of FGDB than SDE once it gets the data. If it makes any difference, SDE is using SQL geometry for the shape.

Thanks,
Josh